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') ;

    --授权序列的读写权限
    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 '%$%') ;