分享一个批量增加和移除用户对表的操作权限函数

最近一直有人在QQ群询问怎么设置一个用户对所有表的读权限等有关一次性对数据库或某个模式下所有表增加或移除某一个权限问题

1、对于数据库的所有表需要分多条grant 语句来实现
2、对于某一个模式下的所有表，9.0以前版本也只能通过多条grant语句来实现，而9.0以后则可以通过GRANT xxx ON ALL TABLES IN SCHEMA xxx TO xxx 来实现
3、revoke跟grant情况一下

其实这些我们可以通行守加几个管理函数来实现，封装一下，操作起来不管那个版本都是一样的

一、定义函数

文件 grant_revoke_table_install.sql --安装内容如下

CREATE OR REPLACE FUNCTION public.grant_revoke_table_check(a_schema_name varchar,a_role_name varchar,a_type varchar,a_ver INTEGER) RETURNS TEXT AS
$BODY$
DECLARE
    --传入参数检查
    -- a_schema_name:指定某个模式下，不对定是对数据库的所有表
    -- a_role_name：指定分配权限的用户
    -- a_type：指定分配的权限类型,分别是select ,insert ,update,delete,truncate,references,trugger,all,trucate (9.0后支持truncate)
    -- a_ver:是传入的版本号,如果调用时传入是0则从系统中获取
    v_type VARCHAR;
    v_ver INTEGER;
BEGIN
    IF v_ver = 0 THEN
        v_ver:=SUBSTRING(version() FROM 12 FOR 1)::INTEGER; --获取大版本号
    ELSE
        v_ver:=a_ver;
    END IF;
    IF  a_schema_name != '' THEN--如果指模式，检查模式是否存
        PERFORM 1 FROM pg_namespace WHERE nspname = a_schema_name;
        IF NOT FOUND THEN
            RETURN '指定的模式 ' || a_schema_name || ' 不存在!';
        END IF;
    END IF;
    PERFORM 1 FROM pg_roles WHERE rolname = a_role_name ;
    IF NOT FOUND THEN--检查用户是否存在
        RETURN '指定的用户 ' || a_role_name || ' 不存在!';
    END IF;
    v_type:=UPPER(a_type);
    IF v_type!='SELECT' AND v_type!='INSERT' AND v_type!='UPDATE' AND v_type!='DELETE' AND v_type!='RULE' AND v_type!='REFERENCES'
    AND v_type!='TRIGGER' AND v_type!='ALL PRIVILEGES' AND v_type!='ALL' THEN
        IF  v_ver >= 9 THEN --9.0版本后验证
            IF v_type!='TRUNCATE' THEN
                RETURN '分配权限方式 ' || v_type || ' 不存在!';
            END IF;
        ELSE
            RETURN '分配权限方式 ' || v_type || ' 不存在!';
        END IF;
    END IF;
    RETURN 'OK';
END;
$BODY$
LANGUAGE PLPGSQL;

COMMENT ON FUNCTION public.grant_revoke_table_check(a_schema_name varchar,a_role_name varchar,a_type varchar,a_ver INTEGER) IS '分配去除权限传入参数检查';

CREATE OR REPLACE FUNCTION public.grant_table (a_schema_name varchar,a_role_name varchar,a_type varchar) RETURNS TEXT AS
$BODY$
DECLARE
    -- a_schema_name:指定某个模式下，不对定是对数据库的所有表
    -- a_role_name：指定分配权限的用户
    -- a_type：指定分配的权限类型,分别是select ,insert ,update,delete,truncate,references,trugger,all,trucate (9.0后支持truncate)
    -- 另外这个函数将系统模式pg_catalog,information_schema下的系统表排除在外,一般情况下我们都不建议用户将表立在这两个模式下
    v_sql TEXT;
    v_rec RECORD;
    v_ret TEXT;
    v_ver INTEGER;
BEGIN
    v_ver:=SUBSTRING(version() FROM 12 FOR 1)::INTEGER; --获取大版本号
    v_ret:=public.grant_revoke_table_check(a_schema_name,a_role_name,a_type,v_ver);
    IF v_ret!='OK' THEN
        RETURN v_ret;
    END IF;
    IF  v_ver < 9 THEN --9.0版本前分配
        IF  a_schema_name != '' THEN    --指定了要设置的权限是某个模式中的表
             v_sql:='SELECT schemaname,tablename FROM pg_tables WHERE schemaname=''' || a_sche || ''' AND schemaname!=''pg_catalog'' AND schemaname!=''information_schema'' ';
        ELSE
             v_sql:='SELECT schemaname,tablename FROM pg_tables WHERE schemaname!=''pg_catalog'' AND schemaname!=''information_schema'' ';
        END IF;
        FOR v_rec IN EXECUTE v_sql LOOP
              EXECUTE 'GRANT ' || a_type || ' ON "' || v_rec.schemaname || '"."'||v_rec.tablename || '" TO ' || a_role_name;
        END LOOP;
    ELSE
        IF  a_schema_name != '' THEN    --指定了要设置的权限是某个模式中的表
            v_sql:='GRANT '|| a_type || ' ON  ALL TABLES IN SCHEMA '|| a_schema_name ||' TO '||a_role_name;
            EXECUTE v_sql;
        ELSE
            v_sql:='SELECT DISTINCT schemaname FROM pg_tables WHERE schemaname!=''pg_catalog'' AND schemaname!=''information_schema'' ';
            FOR v_rec IN EXECUTE v_sql LOOP
              EXECUTE 'GRANT ' || a_type || ' ON ALL TABLES IN SCHEMA "' || v_rec.schemaname|| '" TO ' || a_role_name;
            END LOOP;
        END IF;
    END IF;
    RETURN 'OK';
END;
$BODY$
LANGUAGE PLPGSQL;

COMMENT ON FUNCTION public.grant_table (a_schema_name varchar,a_role_name varchar,a_type varchar) IS '给指定用户分配某个模式下表的操作权限'; 

CREATE OR REPLACE FUNCTION public.grant_table (a_role_name varchar,a_type varchar) RETURNS TEXT AS
$BODY$
DECLARE
BEGIN
    RETURN public.grant_table('',a_role_name,a_type);
END;
$BODY$
LANGUAGE PLPGSQL;

COMMENT ON FUNCTION public.grant_table (a_role_name varchar,a_type varchar) IS '给指定用户分配所有表的操作权限'; 

CREATE OR REPLACE FUNCTION public.grant_table (a_role_name varchar) RETURNS TEXT AS
$BODY$
DECLARE
BEGIN
    RETURN public.grant_table('',a_role_name,'ALL PRIVILEGES');
END;
$BODY$
LANGUAGE PLPGSQL;

COMMENT ON FUNCTION public.grant_table (a_role_name varchar) IS '给指定用户分配所有表的所有操作权限';                  

CREATE OR REPLACE FUNCTION public.revoke_table (a_schema_name varchar,a_role_name varchar,a_type varchar) RETURNS TEXT AS
$BODY$
DECLARE
    -- a_schema_name:指定某个模式下，不对定是对数据库的所有表
    -- a_role_name：指定分配权限的用户
    -- a_type：指定分配的权限类型,分别是select ,insert ,update,delete,truncate,references,trugger,all,trucate (9.0后支持truncate)
    -- 另外这个函数将系统模式pg_catalog,information_schema下的系统表排除在外,一般情况下我们都不建议用户将表立在这两个模式下
    v_sql TEXT;
    v_rec RECORD;
    v_ret TEXT;
    v_ver INTEGER;
BEGIN
    v_ver:=SUBSTRING(version() FROM 12 FOR 1)::INTEGER; --获取大版本号
    v_ret:=public.grant_revoke_table_check(a_schema_name,a_role_name,a_type,v_ver);
    IF v_ret!='OK' THEN
        RETURN v_ret;
    END IF;
    IF  v_ver < 9 THEN --9.0版本前分配
        IF  a_schema_name != '' THEN    --指定了要设置的权限是某个模式中的表
             v_sql:='SELECT schemaname,tablename FROM pg_tables WHERE schemaname=''' || a_sche || ''' AND schemaname!=''pg_catalog'' AND schemaname!=''information_schema'' ';
        ELSE
             v_sql:='SELECT schemaname,tablename FROM pg_tables WHERE schemaname!=''pg_catalog'' AND schemaname!=''information_schema'' ';
        END IF;
        FOR v_rec IN EXECUTE v_sql LOOP
              EXECUTE 'REVOKE ' || a_type || ' ON "' || v_rec.schemaname || '"."'|| v_rec.tablename || '" FROM ' || a_role_name;
        END LOOP;
    ELSE  --9.0后的版本
        IF  a_schema_name != '' THEN    --指定了要设置的权限是某个模式中的表
            v_sql:='REVOKE '|| a_type || ' ON  ALL TABLES IN SCHEMA '|| a_schema_name || ' TO ' || a_role_name;
            EXECUTE v_sql;
        ELSE
            v_sql:='SELECT DISTINCT schemaname FROM pg_tables WHERE schemaname!=''pg_catalog'' AND schemaname!=''information_schema'' ';
            FOR v_rec IN EXECUTE v_sql LOOP
              EXECUTE 'REVOKE ' || a_type || ' ON ALL TABLES IN SCHEMA "' || v_rec.schemaname || '" FROM ' || a_role_name;
            END LOOP;
        END IF;
    END IF;
    RETURN 'OK';
END;
$BODY$
LANGUAGE PLPGSQL;

COMMENT ON FUNCTION public.revoke_table (a_schema_name varchar,a_role_name varchar,a_type varchar)  IS '删除指定用户某个模式下表的操作权限'; 

CREATE OR REPLACE FUNCTION public.revoke_table (a_role_name varchar,a_type varchar) RETURNS TEXT AS
$BODY$
DECLARE
BEGIN
    RETURN public.revoke_table('',a_role_name,a_type);
END;
$BODY$
LANGUAGE PLPGSQL;

COMMENT ON FUNCTION public.revoke_table (a_role_name varchar,a_type varchar) IS '删除指定用户所有表的操作权限'; 

CREATE OR REPLACE FUNCTION public.revoke_table (a_role_name varchar) RETURNS TEXT AS
$BODY$
DECLARE
BEGIN
    RETURN public.revoke_table('',a_role_name,'ALL PRIVILEGES');
END;
$BODY$
LANGUAGE PLPGSQL;

COMMENT ON FUNCTION public.revoke_table (a_role_name varchar) IS '删除指定用户所有表的所有操作权限';  

文件 grant_revoke_table_uninstall.sql --反安装内容如下

DROP FUNCTION  public.grant_revoke_table_check(a_schema_name varchar,a_role_name varchar,a_type varchar,a_ver INTEGER) ; 

DROP FUNCTION  public.grant_table (a_schema_name varchar,a_role_name varchar,a_type varchar);
DROP FUNCTION  public.grant_table (a_role_name varchar,a_type varchar);
DROP FUNCTION  public.grant_table (a_role_name varchar); 

DROP FUNCTION public.revoke_table (a_schema_name varchar,a_role_name varchar,a_type varchar) ;
DROP FUNCTION public.revoke_table (a_role_name varchar,a_type varchar) ;
DROP FUNCTION public.revoke_table (a_role_name varchar) ;

二、测试结果

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\pgsql>h:

H:\>cd pgsql

H:\pgsql>cd bin

H:\pgsql\bin>setenv

H:\pgsql\bin>SET PGDATA=H:/pgsql/data

H:\pgsql\bin>SET PGUSER=postgres

H:\pgsql\bin>SET PGDATABASE=cysoft_database

H:\pgsql\bin>SET PGPORT=5432

H:\pgsql\bin>SET PGHOST=127.0.0.1

H:\pgsql\bin>SET PGCLIENTENCODING=gbk

H:\pgsql\bin>psql -d test -U postres
psql: FATAL:  role "postres" does not exist

H:\pgsql\bin>psql -d test -U postgres
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# \e
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
test=# CREATE ROLE pgsqldb WITH LOGIN;
CREATE ROLE
test=# \c test pgsqldb
You are now connected to database "test" as user "pgsqldb".
test=> select * from t1;
ERROR:  permission denied for relation t1
test=> select * from t2;
ERROR:  permission denied for relation t2
test=> \c test postgres
You are now connected to database "test" as user "postgres".
test=# select grant_table('pgsqldb','select');
 grant_table
-------------
 OK
(1 行)

test=# \c test pgsqldb
You are now connected to database "test" as user "pgsqldb".
test=> select * from t1;
 id |   mc
----+---------
  1 | 阿弟
  2 | laser
  3 | postbbs
(3 rows)

test=> select * from t2;
 id | t1_id |     rq
----+-------+------------
  1 |     1 | 2013-04-11
  2 |     1 | 2013-04-12
(2 rows)

test=> insert into t1 values (66666,'pgsqldb');
ERROR:  permission denied for relation t1
test=> \c test postgres
You are now connected to database "test" as user "postgres".
test=# select grant_table('pgsqldb','insert');
 grant_table
-------------
 OK
(1 行)

test=# \c test pgsqldb
You are now connected to database "test" as user "pgsqldb".
test=> insert into t1 values (66666,'pgsqldb');
INSERT 89365707 1
test=> update t1 set id =88888 where id=66666;
ERROR:  permission denied for relation t1
test=> delete from t1 where id=66666;
ERROR:  permission denied for relation t1
test=> \c test postgres
You are now connected to database "test" as user "postgres".
test=# select grant_table('pgsqldb','all');
 grant_table
-------------
 OK
(1 行)

test=# \c test pgsqldb
You are now connected to database "test" as user "pgsqldb".
test=> update t1 set id =88888 where id=66666;
UPDATE 1                      ^
test=> delete from t1  where id=88888;
DELETE 1
test=> \c test postgres
You are now connected to database "test" as user "postgres".
test=# select grant_table('a','pgsqldb','all');
     grant_table
----------------------
 指定的模式 a 不存在!
(1 行)

test=# select grant_table('','pgsqldb1','all');
         grant_table
-----------------------------
 指定的用户 pgsqldb1 不存在!
(1 行)

test=# select grant_table('','pgsqldb','all1');
        grant_table
---------------------------
 分配权限方式 ALL1 不存在!
(1 行)

test=# select grant_table('','pgsqldb','truncate');
          grant_table
-------------------------------
 分配权限方式 TRUNCATE 不存在!
(1 行)

test=# select revoke_table('pgsqldb');
 revoke_table
--------------
 OK
(1 行)

test=# \c test pgsqldb
You are now connected to database "test" as user "pgsqldb".
test=> select * from t1;
ERROR:  permission denied for relation t1
test=> select * from t2;
ERROR:  permission denied for relation t2
test=> delete from t1;
ERROR:  permission denied for relation t1
test=> update t1 set id=111;
ERROR:  permission denied for relation t1
test=> insert into t1 values(1,'pgsqldb');
ERROR:  permission denied for relation t1
test=>

时间： 2024-11-01 17:09:02

分享一个批量增加和移除用户对表的操作权限函数的相关文章

分享一个批量导出当前实例下的所有linkedserver脚本

原文:分享一个批量导出当前实例下的所有linkedserver脚本分享一个批量导出当前实例下的所有linkedserver脚本很多时候,我们都需要导出实例下面的登录用户,job,linkedserver等等导出job比较复杂,下午写了一个脚本把所有的linkedserver导出来,但是密码不会显示出来下面脚本在SQL2008 R2下面测试通过 -- ============================================= -- Author: <桦仔> -- Blog

MySQL给用户加库操作权限

给用户增加操作权限 GRANT ALL PRIVILEGES ON `DB_NAME`.* TO 'USER_NAME'@'HOST' WITH GRANT OPTION; grant 普通数据用户,查询.插入.更新.删除数据库中所有表数据的权利. grant select on testdb.* to common_user@'%' grant insert on testdb.* to common_user@'%' grant update on testdb.* to common_u

分享一个.NET版的多选文件上传操作示例

文件上传,无刷新上传,文件批量上传,批量无刷新上传,这些词这年头好像非常多,不管那种上传能满足实际的需求就是好的,一般上传文件时,点文件浏览框,只能选择单个文件,如果要上传多个,浏览多次,然后点击批量上传就可以了,也很好的,也不刷新也有进度显示,完全不错的.网上也有关于上传文件的能够多选的例子,点浏览时可以按住ctrl键或者shift键选择多个,然后上传.但是好像这样的.NET版的不多,朋友发我一个整理过的Demo,之后我又稍微整理了下,觉得还好,可以分享给大家一下,好与不好就不说,因为没

分享一个SQLServer中bigint转int带符号时报错的解决函数【干货】

有一个需求是要在一个云监控的状态值中存储多个状态(包括可同时存在的各种异常.警告状态)使用了位运算机制在一个int型中存储. 现在监控日志数据量非常大(亿级别)需要对数据按每小时.每天进行聚合,供在线报表使用. 状态分了3个级别:正常(0).警告(1).异常(2),聚合时需要使用max选择最差的状态,就需要对状态值进行处理加上级别和状态位个数,就要借助bigint型来做运算了, 问题是再将bigint 转为 int时获取原始状态值时,SQLServer报错了: 消息 8115,级别 16,状态

MySQL给用户加库操作权限sql语句

语法如下代码如下复制代码 GRANT ALL PRIVILEGES ON `DB_NAME`.* TO 'USER_NAME'@'HOST' WITH GRANT OPTION; 例 grant 普通数据用户,查询.插入.更新.删除数据库中所有表数据的权利. 代码如下复制代码 grant select on testdb.* to common_user@'%' grant insert on testdb.* to common_user@'%' grant update on t

问个问题 - -, 一个酒店要批量增加自定义的房号比如1-100 1和100都是自己填的添加之前怎么判断会不会有重复呢?

问题描述问个问题--,一个酒店要批量增加自定义的房号比如1-1001和100都是自己填的添加之前怎么判断会不会有重复呢?EF怎么写?SQL也行.. 解决方案解决方案二:房号定义成primary或者unique,插入的时候如果重复自然会出错你也可以简单的select一下,看看有没有结果,但是并发情况下不保险解决方案三:批量增加自定义的房号集合,写个方法sql查询集合里面哪些房号已经添加过,添加过的房号,返回序列集合list出来.解决方案四:过来学习下知识解决方案五:联合主键呗或者因为只是一个酒

V5Shop分享一个独立网店快速提升PR和销售额的方法

V5Shop分享一个独立网店快速提升PR和销售额的方法很多独立网店的店主都在为宣传推广头疼.在现在这个产品丰富的市场经济环境下,各行各业的竞争都异常激烈,"酒香也怕巷子深"已经成为网商们的共识.作为独立网店,独立运营固然可以避免许多"拼低价"的竞争,客户对网店和品牌的忠诚度也有保证,但是在推广这个问题上,却难住了不少网商. 许多网商之所以过于依赖淘宝等大型平台,就是因为这些平台能够为其带来源源不断的客户,其不用为如何招揽客户,如何推广网店而劳心.有得必有失,这同样

力洋SEO分享一个百试百灵的新站收录方法

网站收录页面的多少决定了网站的长尾关键词排名分布,在此同时也影响着网站的访问量,进而影响网站的订单交易,业务合作,提高网站收录页面就成为每个站长必定的工作.有些站长为了提高网站收录页面,开始采集更新,或者直接复制更新,但最近的百度调整算法令这些站长吃亏了,百度不但没有收录这些页面,还减少了以前收录的重复页面,成千上万的收录是每个站长都向往的.对于新站收录,很多站长不知所措,网站建设完成了一个多月了,百度依然不肯收录网站内容页面,只是单纯的收录了首页,今天力洋SEO分享一个新站百试百灵的收录方法:

javascript-ASP批量增加自动计算请帮忙

问题描述 ASP批量增加自动计算请帮忙日期部位 ? 供应单位 ? ? ? 序号标号小票方量单位理论重量单位小票重量抽磅重量量差运输车号备注说明 1 ? m? KG/m? function $(obj){ return document.getElementById(obj); } var num = 0; function row(id){ //构造函数 this.id = $(id); } row.prototype = { //插入行 insert:function(