备案控制台
探索云世界
开发者社区 数据库 文章 正文

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

2016-03-05 2344
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 最近一直有人在QQ群询问怎么设置一个用户对所有表的读权限等有关一次性对数据库或某个模式下所有表增加或移除某一个权限问题

最近一直有人在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=>

文章标签：
关系型数据库
数据库
SQL
Windows
PostgreSQL
阿弟@pgsql
目录
相关文章
老虎也淘气
|
7月前
|
BI 数据处理 开发工具
【批量创建，删除工作表】
【批量创建，删除工作表】
老虎也淘气
51 0
宋wz
|
10月前
|
C++
c++一些简单操作
c++一些简单操作
宋wz
47 0
不懂开发的程序猿
|
11月前
|
分布式计算 网络协议 Ubuntu
DStreams的操作
DStreams的操作
不懂开发的程序猿
80 0
清雨小竹
|
索引 Windows
ListControl操作
ListControl操作
清雨小竹
55 0
Iric
|
Linux
文件控制操作
文件控制操作
Iric
80 0
游客obmvqjtme7jkc
|
SQL Java 数据库连接
MyBatisCRUD操作
MyBatisCRUD操作
游客obmvqjtme7jkc
116 0
码农技术君
|
C#
C#编程-90：Hashtale相关操作
C#编程-90：Hashtale相关操作
码农技术君
48 0
古城小白
|
关系型数据库 MySQL
有数据进行更新 没有进行新增 怎么操作
有数据进行更新 没有进行新增
古城小白
271 0
金色海洋
|
安全
【自然框架】 权限 的视频演示（二）： 权限到字段、权限到记录
继续。这里演示权限到字段和权限到记录。            权限到字段有两种安全级别，      1、低安全级别。有些项目不需要做到控制每一个字段是否显示，那么就可以采用这种级别。低安全级别就是：如果一个节点里面没有设置可以访问哪些字段，那么就默认为不需要做到控制字段的程度，就是说节点里的字段都是可以访问的。
金色海洋
1213 0
wsc449
|
关系型数据库 MySQL Oracle
增加、删除、更新触发器
第一步：新建触发器表 SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `dataex_trigger_record`; CREATE TABLE `dataex_trig...
wsc449
1741 0

热门文章

最新文章

  • 1
    阿里云各个地域节点测试IP（国内+海外）Ping值延迟测试（全解析）
  • 2
    基于Flink的实时日志分析系统实践
  • 3
    阿里云的SLB，植入cookie和重写cookie有什么区别？
  • 4
    uni-app 开源资源汇总
  • 5
    一文了解阿里云对象存储OSS
  • 6
    SQL SERVER中的OLEDB等待事件
  • 7
    kindle3使用技巧
  • 8
    sd卡无法写操作
  • 9
    普京顾问警告或征收科技税 谷歌苹果可能受创
  • 10
    CIO：传统企业如何做好转型？
  • 1
    手动给docusaurus添加一个搜索
    17
  • 2
    深入白盒测试：代码级透视与质量保证
    16
  • 3
    网络安全与信息安全：防御前线的关键技术与意识
    17
  • 4
    云端守卫：融合云计算与网络安全的未来之路
    16
  • 5
    nginx 配置代理ip访问https的域名配置
    17
  • 6
    基于Java的中国传统面食介绍网站的设计与实现(源码+lw+部署文档+讲解等)
    31
  • 7
    Python搭建代理IP池实现存储IP的方法
    26
  • 8
    外部中断的使用方法
    24
  • 9
    基于Java的快递信息管理系统的设计与实现(源码+lw+部署文档+讲解等)
    25
  • 10
    深入理解MySQL中的UPDATE JOIN语句
    45

    • 相关电子书

    更多
  • 《用管控策略设定多账号组织全局访问边界》
  • 事务、全局索引、透明分布式
  • 低代码开发师（初级）实战教程

    • 相关实验场景

    更多
  • 通过函数计算实现网站文件自动处理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用交互方式实现数据查询、插入、修改和删除操作
  • 使用Count功能批量创建资源
    • 下一篇
    阿里云oss简介和使用流程