我们知道将用户的一张表查询权限赋予另一用户的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赋予。