oracle开发权限管理解决方案

需求

      公司想规范oracle的权限管理，实现同一个数据有两个用户维护，一个低权用户一个高权用户， 低权用户只能增删改查，无法更改表建构创建表，给普通开发使用，高权用户可以改表结构 创建表，给项目经理或者架构师使用。 

实现思路

       假设高权用户是gao,低权用户是di，通过给gao用户表空间的CONNECT,RESOURCE角色，给di用户CONNECT角色和创建同义词的权限，把gao用户的所有表找出来给di用户增删改查权限，然后再给di再创建gao用户所有表的同义词，以后gao用户创建表，需要给di用户设置该表增删改查权限，并且di用户要再创建一个同义词。

操作

oracle版本：11.2

高权用户名：ANYTXN_V2_DEV（已存在，并且有很多表）

低权用户：    ANYTXN_V2_DEV_RD

表空间名称：ANYTXN_V2_DEV_DATA

 

#登陆system用户创建低权用户
create user anytxn_v2_dev_rd identified by "jrx12345" default tablespace ANYTXN_V2_DEV_DATA  temporary tablespace ANYTXN_DEV_DATA_TEMP profile DEFAULT;
GRANT CONNECT TO anytxn_v2_dev_rd;
alter user anytxn_v2_dev_rd quota unlimited on ANYTXN_V2_DEV_DATA;
grant create synonym to anytxn_v2_dev_rd;

#登录高权用户查询所有的表，生成授权sql
select 'GRANT SELECT,delete,insert,update ON ' || table_name || ' to anytxn_v2_dev_rd;'  from user_tables;
#把上面命令生成的sql都执行一遍

#查询所有的表生成创建同义词的所有sql
select 'create synonym ' || table_name || ' for anytxn_v2_dev.'||table_name||';' from user_tables ;
#登录低权用户执行上面查到的sql，创建所有表的同义词

#如果涉及到序列再需要查询高权用户得所有序列，然后执行相关授权sql
select 'GRANT SELECT ON ' || sequence_name || ' to cmbc_biz_rd;'  from user_sequences;

#登陆低权用户执行所有创建序列 同义词得sql
select 'create synonym ' || sequence_name || ' for cmbc_biz.'||sequence_name||';' from user_sequences;

 

单表权限维护

创建表用高权用户创建，创建后授权表的增删改查权限给低权用户

#GRANT SELECT,delete,insert,update ON SETTLEMENT_LOG to anytxn_v2_dev_rd;

登陆低权用户创建相应表的同义词

create synonym SETTLEMENT_LOG for anytxn_v2_dev.SETTLEMENT_LOG;

撤销权限

REVOKE SELECT,delete,insert,update ON SETTLEMENT_LOG FROM anytxn_v2_dev_rd;

删除同义词

DROP SYNONYM SETTLEMENT_LOG；

 

多表批量维护

通过高权用户批量授权表

select 'GRANT SELECT,delete,insert,update ON ' || table_name || ' to anytxn_v2_dev_rd;'  from user_tables;

执行上述命令生成的所有sql

名称以copy1结尾的表会授权失败，需要把表名加上双引号执行

 

批量创建同义词

高权用户执行select 'create synonym ' || table_name || ' for anytxn_v2_dev.'||table_name||';' from user_tables ;

把生成的sql都在低权用户执行

 

批量撤销权限

高权用户select 'REVOKE SELECT,delete,insert,update ON ' || table_name || ' from anytxn_v221_rd ;' from user_tables;

执行上述命令生成的sql

批量删除同义词

高权用户select 'drop synonym '||synonym_name||';' from user_synonyms

把生成的sql在低权用户执行