需求:

1.授予cjc0204用户，chen0204用户下所有表的查询权限；
2.授予cjc0204用户，chen0204用户下所有表的查询、DML权限；
3.授予cjc0204用户，chen0204用户下所有表的查询、DML、DDL权限；

创建测试数据:

disql sysdba/******:5238
create user cjc0204 identified by "******";
create user chen0204 identified by "******";
grant resource to cjc0204;
grant resource to chen0204;

两个用户分别创建表:

create table t0204(id int);
insert into t0204 values(1);
commit;

场景1:授予cjc0204用户，chen0204用户下所有表的查询权限

默认没有跨用户读写权限

dmdba@CJC-DB-01:/home/dmdba$disql cjc0204/******:5238
SQL> select * from chen0204.t0204;
select * from chen0204.t0204;
[-5504]:没有[CHEN0204.t0204]对象的查询权限.
已用时间: 0.733(毫秒). 执行号:0.

模式级别授权:
默认不支持模式级别授权

SQL> grant select on schema chen0204 to cjc0204;
grant select on schema chen to cjc;
第1 行附近出现错误[-2201]:无效的数据库对象.
已用时间: 0.516(毫秒). 执行号:0.

需要先修改参数GRANT_SCHEMA:

SQL> select para_name,DEFAULT_VALUE,PARA_VALUE,PARA_TYPE,DESCRIPTION from v$dm_ini where para_name='GRANT_SCHEMA';
行号     para_name    DEFAULT_VALUE PARA_VALUE PARA_TYPE DESCRIPTION                        
---------- ------------ ------------- ---------- --------- -----------------------------------
1          GRANT_SCHEMA 0             0          IN FILE   Whether to allow to grant to schema

将 0 改成 1，静态参数，需要重启实例生效:

vi dm.ini 
GRANT_SCHEMA                    = 1                     #Whether to allow to grant to schema

再次执行授权:

dmdba@CJC-DB-01:/db/dm8/data/cjc$disql sysdba/******:5238
SQL> grant select on schema chen0204 to cjc0204;

可以查询chen0204模式下的表数据

dmdba@CJC-DB-01:/home/dmdba$disql cjc0204/******:5238
SQL> select * from chen0204.t0204;
行号     id         
---------- -----------
1          1
已用时间: 0.848(毫秒). 执行号:46201.

不支持dml和ddl：

SQL> insert into chen0204.t0204 values(1);
insert into chen0204.t0204 values(1);
[-5501]:没有[CHEN0204.t0204]对象的插入权限.
已用时间: 0.365(毫秒). 执行号:0.
SQL> drop table chen0204.t0204;
drop table chen0204.t0204;
第1 行附近出现错误[-5535]:没有删除表[t0204]权限.
已用时间: 0.284(毫秒). 执行号:0.

场景2:授予cjc0204用户，chen0204用户下所有表的查询、DML权限

先回收 之前的 select on schema chen0204 权限,测试 all privileges 权限:

dmdba@CJC-DB-01:/home/dmdba$disql sysdba/******:5238
SQL> revoke select on schema chen0204 from cjc0204;
SQL> grant all privileges on schema chen0204 to cjc0204;

验证授权:

dmdba@CJC-DB-01:/home/dmdba$disql cjc0204/******:5238
SQL> select * from chen0204.t0204;
行号     id         
---------- -----------
1          1
已用时间: 0.867(毫秒). 执行号:43701.
SQL> insert into chen0204.t0204 values(2);
影响行数 1
已用时间: 0.486(毫秒). 执行号:43702.
SQL> update chen0204.t0204 set id=100 where id=1;
影响行数 1
已用时间: 2.887(毫秒). 执行号:43703.
SQL> delete from chen0204.t0204 where id=100;
影响行数 1
已用时间: 0.844(毫秒). 执行号:43704.
SQL> commit;
操作已执行
已用时间: 2.695(毫秒). 执行号:43705.
SQL> select * from chen0204.t0204;
行号     id         
---------- -----------
1          2
已用时间: 0.298(毫秒). 执行号:43706.

all privileges权限:只有 select 和 DML权限，没有DDL权限:

SQL> create table chen0204.t2(id int);
create table chen0204.t2(id int);
第1 行附近出现错误[-5515]:没有创建表权限.
已用时间: 0.363(毫秒). 执行号:0.

场景3:授予cjc0204用户，chen0204用户下所有表的查询、DML、DDL权限

添加DDL授权:以create table为例:
注意:grant create any table 权限是对所有用户/模式下的create权限，权限较大，谨慎授予，不支持针对某一个用户/模式

dmdba@CJC-DB-01:/home/dmdba$disql sysdba/******:5238
SQL> grant create any table to cjc0204;
grant create any table to cjc0204;
第1 行附近出现错误[-5567]:授权者没有此授权权限.
已用时间: 0.525(毫秒). 执行号:0.

需要先改参数:

SQL> show parameter ENABLE_DDL_ANY_PRIV
行号     para_name           para_value
---------- ------------------- ----------
1          ENABLE_DDL_ANY_PRIV 0
SQL> sp_set_para_value(1,'ENABLE_DDL_ANY_PRIV',1);
DMSQL 过程已成功完成
已用时间: 13.923(毫秒). 执行号:43802.
SQL> show parameter ENABLE_DDL_ANY_PRIV
行号     para_name           para_value
---------- ------------------- ----------
1          ENABLE_DDL_ANY_PRIV 1

SQL> grant create any table to cjc0204;
操作已执行
已用时间: 7.144(毫秒). 执行号:44002.

dmdba@CJC-DB-01:/home/dmdba$disql cjc0204/******:5238
服务器[LOCALHOST:5238]:处于主库打开状态
登录使用时间 : 2.269(ms)
disql V8
SQL> create table chen0204.t2(id int);
操作已执行
已用时间: 10.173(毫秒). 执行号:44601.
SQL> insert into chen0204.t2 values(1);
影响行数 1
已用时间: 0.698(毫秒). 执行号:44602.
SQL> commit;
操作已执行
已用时间: 2.701(毫秒). 执行号:44603.
SQL> truncate table chen0204.t2;
truncate table chen0204.t2;
第1 行附近出现错误[-5564]:没有清空表[t2]权限.
已用时间: 0.249(毫秒). 执行号:0.
SQL> drop table chen0204.t2;
drop table chen0204.t2;
第1 行附近出现错误[-5535]:没有删除表[t2]权限.
已用时间: 0.255(毫秒). 执行号:0.
SQL> create table chen.tt0205(id int);
操作已执行
已用时间: 10.469(毫秒). 执行号:44604.

欢迎关注我的公众号《 IT小Chen》