oracle赋予当前⽤户查询另⼀个⽤户下表的查询权限
⽤户：CRJDATA，ZZ
场景：⽤户CRJDATA有⽤户ZZ指定表的查询权限。
1.给他⼀些权限，包括连接权限，因为他要创建同义词，还需要给他同义词
  grant connect to CRJDATA;
  grant create synonym to CRJDATA;
  grant create session to CRJDATA;
  grant create sequence to CRJDATA;
2.因为需要把ZZ的所有表的查询权限给CRJDATA。所以需要所有表的grant select on table_name to CRJDATA语句，不可能⼀句⼀句去写，因此⽤select 吧所有的grant语句查出来直接执⾏
  select 'grant select on '||owner||'.'||object_name||' to CRJDATA;'
  from dba_objects
  where owner in ('ZZ')
  and object_type='TABLE';
把所有结果复制出来，在UserB 下执⾏⼀遍
  grant select on ZZ.Table1 to CRJDATA;
  grant select on ZZ.Table2 to CRJDATA;
  grant select on ZZ.Table3 to CRJDATA;
  (也可以赋予序列，视图的查询权限)
  grant select  any sequence  to CRJDATA;
3.需要给UserB⽤户下所有表创建同义词，但是考虑到之前已经创建过⼀些表的同义词，因此把所有创建同义词的语句select出来在UserA⽤户下执⾏。
  SELECT 'create or replace SYNONYM CRJDATA. ' || object_name|| ' FOR ' || owner || '.' || object_name|| ';'
  from dba_objects
  where owner in ('ZZ')
  and object_type='TABLE';
把所有结果复制出来登录UserA⽤户执⾏
  create or replace SYNONYM  CRJDATA. T_KDXF_ACCOUNT FOR ZZ.Table1 ;
  create or replace SYNONYM  CRJDATA. T_KDXF_ACCOUNT FOR ZZ.Table2 ;
  create or replace SYNONYM  CRJDATA. T_KDXF_ACCOUNT FOR ZZ.Table3 ;
4.全⾯使⽤grant命令
--⾸先是CPR账号
--授权表上的读写权限
select'grant all on '||owner||'.'||table_name||' to hisuser;'from dba_tables
where owner = 'CPR';
--授权视图上的读写权限
select'grant all on '||owner||'.'||view_name||' to hisuser;'from dba_views
where owner = 'CPR';
--授权函数和存储过程的读写权限
select'grant execute on '||owner||'.'||name||' to hisuser;'from dba_source
where owner = 'CPR' and type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','TYPE') ;
oracle 新建用户--授权序列的读写权限
select'grant all on '||sequence_owner||'.'||sequence_name||' to hisuser;'from dba_sequences where sequence_owner = 'CPR' ;
--创建同义词
select'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;'from dba_synonyms
where table_owner='CPR' ;
select'create or replace public synonym '||view_name||' for '||owner||'.'||view_name||' ;'from dba_views
where owner = 'CPR' and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%') ;
--然后是system账号
--授权表上的读写权限
select'grant all on '||owner||'.'||table_name||' to hisuser;'from dba_tables
where owner = 'SYSTEM' and table_name NOT LIKE '%$%';
--授权视图上的读写权限
select'grant all on '||owner||'.'||view_name||' to hisuser;'from dba_views
where owner = 'SYS';
--授权函数和存储过程的读写权限
select DISTINCT 'grant execute on '||owner||'.'||name||' to hisuser;'from dba_source
where owner = 'SYS' and type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','TYPE') AND name NOT LIKE '%$%'
--授权序列的读写权限
select'grant all on '||sequence_owner||'.'||sequence_name||' to hisuser;'from dba_sequences where sequence_owner = 'SYSTEM' AND sequence_name NOT LIKE '%$%';    --创建同义词
select'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;'from dba_synonyms
where table_owner='SYS' and synonym_name NOT LIKE '%$%';
select'create or replace public synonym '||view_name||' for '||owner||'.'||view_name||' ;'from dba_views
where owner = 'SYS' and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%') ;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。