Oracle grant all privileges to user

测试流程：

解锁HR用户:
SQL> alter user hr account unlock identified by hr;
User altered.

创建一个test用户
SYS@orcl11g> CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE users  	
此时test用户没有任何权限


在sys用户下建表t1
SQL> create table t1 as select * from dba_objects;
Table created.

授予HR查询sys.t1的对象权限
SQL> grant select on t1 to hr;
Grant succeeded.

SQL> conn hr/hr
Connected.

此时HR用户的对象权限
SQL>select * from user_tab_privs_recd;
OWNER      TABLE_NAME        GRANTOR             PRIVILEGE                                GRA HIE
------------------------------ ------------------------------ ------------------------------ -------------
SYS      DBMS_STATS                        SYS                            EXECUTE                                  NO  NO
SYS         T1                             SYS                            SELECT                                   NO  NO

此时HR用户的系统权限
SQL> select * from user_sys_privs;
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
HR                             CREATE VIEW                              NO
HR                             UNLIMITED TABLESPACE                     NO
HR                             CREATE DATABASE LINK                     NO
HR                             CREATE SEQUENCE                          NO
HR                             CREATE SESSION                           NO
HR                             ALTER SESSION                            NO
HR                             CREATE SYNONYM                           NO

使用HR用户执行grant all privileges
SQL> grant all privileges to test;
grant all privileges to test
*
ERROR at line 1:
ORA-01031: insufficient privileges

授予HR DBA权限
SQL> grant dba to hr;
Grant succeeded.

此时HR的对象权限以及系统权限没有变化
SQL> select * from user_sys_privs;
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
HR                             CREATE VIEW                              NO
HR                             UNLIMITED TABLESPACE                     NO
HR                             CREATE DATABASE LINK                     NO
HR                             CREATE SEQUENCE                          NO
HR                             CREATE SESSION                           NO
HR                             ALTER SESSION                            NO
HR                             CREATE SYNONYM                           NO

SQL>select * from user_tab_privs_recd;
OWNER      TABLE_NAME        GRANTOR             PRIVILEGE                                GRA HIE
------------------------------ ------------------------------ ------------------------------ -------------
SYS      DBMS_STATS                        SYS                            EXECUTE                                  NO  NO
SYS         T1                             SYS                            SELECT                                   NO  NO


执行grant all privileges
SQL> grant all privileges to test;
Grant succeeded.

SQL> conn test/test
Connected.

SQL> select * from user_tab_privs_recd;
no rows selected

SQL> select * from user_sys_privs;
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST                           DROP ANY MEASURE FOLDER                  NO
TEST                           ALTER ANY CUBE                           NO
...
...
TEST                           ALTER TABLESPACE                         NO
TEST                           AUDIT SYSTEM                             NO

200 rows selected.

SYS用户查询:
SQL> select count(*) from user_sys_privs;
  COUNT(*)
----------
       200

结论：
grant all privileges to B; 这个语句，普通用户是执行不了的，举例 如果想要普通用户A执行，A用户需要拥有DBA权限，A用户拥有DBA权限后执行 grant all privileges to B，B用户拥有的是SYS用户的全部系统权限，而不是A用户的系统权限 并且该语句只包含系统权限，不包含对象权限。