我们知道将用户的一张表查询权限赋予另一用户的SQL语句,例如将SCOTT用户的EMP表查询权限赋予test用户:
SQL> grant select on scott.emp to test;
Grant succeeded
但是如果要将某一用户的所有表查询权限赋予另一用户,如果表较多,逐个赋予比较麻烦,我们可以用如下SQL语句执行,例如将COTT用户的所有表查询权限赋予test用户:
1、第一步:
SQL> select 'grant select on scott.'|| table_name ||' to test;' from all_tables where owner='SCOTT';
'GRANTSELECTONSCOTT.'||TABLE_N
-------------------------------------------------------------
grant select on scott.BONUS to test;
grant select on scott.CC to test;
grant select on scott.CLASSES to test;
grant select on scott.CUSTOMER to test;
grant select on scott.DEPT to test;
grant select on scott.EMP to test;
grant select on scott.EMP2 to test;
grant select on scott.GOODS to test;
grant select on scott.MYTEST to test;
grant select on scott.PURCHASE to test;
grant select on scott.SALGRADE to test;
grant select on scott.STUDENT to test;
grant select on scott.STUDENT2 to test;
grant select on scott.T to test;
grant select on scott.TEST to test;
grant select on scott.T_INFO to test;
grant select on scott.T_USER to test;
grant select on scott.USERDETAIL to test;
grant select on scott.USERINFO to test;
grant select on scott.USERS to test;
20 rows selected
2、第二步执行第一步的结果:
SQL> grant select on scott.BONUS to test;
Grant succeeded
SQL> grant select on scott.CC to test;
Grant succeeded
SQL> grant select on scott.CLASSES to test;
Grant succeeded
SQL> grant select on scott.CUSTOMER to test;
Grant succeeded
SQL> grant select on scott.DEPT to test;
Grant succeeded
SQL> grant select on scott.EMP to test;
Grant succeeded
SQL> grant select on scott.EMP2 to test;
Grant succeeded
SQL> grant select on scott.GOODS to test;
Grant succeeded
SQL> grant select on scott.MYTEST to test;
Grant succeeded
SQL> grant select on scott.PURCHASE to test;
Grant succeeded
SQL> grant select on scott.SALGRADE to test;
Grant succeeded
SQL> grant select on scott.STUDENT to test;
Grant succeeded
SQL> grant select on scott.STUDENT2 to test;
Grant succeeded
SQL> grant select on scott.T to test;
Grant succeeded
SQL> grant select on scott.TEST to test;
Grant succeeded
SQL> grant select on scott.T_INFO to test;
Grant succeeded
SQL> grant select on scott.T_USER to test;
Grant succeeded
SQL> grant select on scott.USERDETAIL to test;
Grant succeeded
SQL> grant select on scott.USERINFO to test;
Grant succeeded
SQL> grant select on scott.USERS to test;
Grant succeeded
这里只是将执行语句时候存在的表权限赋予,日后如用户有新增表需要继续f赋予。