授予与撤销权限

权限
用户权限有两类：
系统：允许用户在数据库中执行特定的操作
对象：允许用户访问和操纵特定的对象
对象权限必包含特定的对象
系统权限
每个用户 至少要有CREATE SESSION不然连数据库都连接不上
对象权限：要向对象授予权限，请执行以下操作：选择对象类型，选择对象，选择权限
撤销具有ADMIN OPTION 的系统权限
grant create session to  用户  with admin option;  用户对CREATE SESSION拥有管理的权限
撤销 不及联   仅仅只适用于系统权限
撤销具有 GRANT OPTION 对象权限
撤销  级联    回收对象权限，也会一块被收回

SQL> desc system_privilege_map   所有的系统权限
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRIVILEGE                                 NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(40)
 PROPERTY                                  NOT NULL NUMBER

SQL> desc  table_privilege_map   所有的对象权限
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRIVILEGE                                 NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(40)
 
SQL> conn / as sysdba
Connected. 
SQL> grant alter tablespace to oaec;       赋予OAEC的系统权限

Grant succeeded.
SQL> conn oaec/oaec   
Connected.

SQL> alter tablespace OAEC add datafile '/u01/app/oracle/oradata/orcl/oaec02.dbf' size 10m;

Tablespace altered.
SQL> revoke alter tablespace from oaec;    收回OAEC的系统权限

Revoke succeeded.
SQL> select * from system_privilege_map
  2  order by 2;                           查询系统权限
  
系统权限的级联和收回
SQL> create user test identified by test;   创建一个test用户 密码为test

User created.

SQL> grant create session to test;           赋予test 连接用户权限

Grant succeeded.

SQL> conn / as sysdba                         连接管理员用户
Connected.
SQL> grant select any dictionary to oaec;      赋予oaec查询数据字典的权限

Grant succeeded.

SQL> conn oaec/oaec                           连接oaec
Connected.
SQL> select count(*) from dba_objects;        执行查询  dba——objects 看数据库里所有的对象
  COUNT(*)
----------
     72621
SQL> conn / as sysdba                         连接管理员用户
Connected.
SQL> revoke select any dictionary from oaec;

Revoke succeeded.

SQL> grant select any dictionary to oaec with admin option;   给oaec查询数据字典的管理权限

Grant succeeded.

SQL> conn oaec/oaec 
Connected.
SQL> grant select any dictionary to test;                    oaec把权限赋予给test

Grant succeeded.

SQL> conn test/test
Connected.
SQL> select count(*) from dba_objects;                       test也能访问

  COUNT(*)
----------
     72621
     

SQL> show user
USER is "SYS"
SQL> revoke select any dictionary from oaec;

Revoke succeeded.

SQL> conn  test/test
Connected.
SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     72621
收回OAEC的系统权限，TEST用户并不受影响 

SQL> alter user scott identified by  scott account unlock;   解锁scott用户

User altered.

SQL> alter user oe identified by oe account unlock;         解锁oe用户

User altered.

SQL> show user
USER is "SYS"

SQL> grant select on scott.emp to hr;         管理员下把在scott用户下查询emp表的权限给hr

Grant succeeded.

SQL> conn hr/hr                               连接hr用户
Connected.
SQL> select * from scott.emp                  可以执行查询，但这一权限，hr不能赋予给其它用户
  2  ;
SQL> conn / as sysdba
Connected.

SQL> revoke select on scott.emp from hr;

Revoke succeeded.

SQL> grant select on scott.emp to hr with grant option;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> grant select on scott.emp to oe;     通过hr把访问scott下emp表的权限赋予给oe

Grant succeeded.

SQL> conn oe/oe
Connected.
SQL> select * from scott.emp;

SQL> conn / as sysdba
Connected.
SQL> revoke select on scott.emp from hr;

Revoke succeeded.

SQL> conn oe/oe
Connected.
SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist     对象权限会被级联的收回

SQL> select * from table_privilege_map;查询整个数据库的对象权限

SQL> grant all on scott.emp to hr;      把关于scott.emp的所有权限都给hr

Grant succeeded.

SQL> select grantee,owner,table_name,grantor,privilege from user_tab_privs
  2  where owner='SCOTT'
  3  and table_name='EMP';
GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE
---------- ---------- ---------- ---------- ------------------------------
HR         SCOTT      EMP        SCOTT      ALTER
HR         SCOTT      EMP        SCOTT      DELETE
HR         SCOTT      EMP        SCOTT      INDEX
HR         SCOTT      EMP        SCOTT      INSERT
HR         SCOTT      EMP        SCOTT      SELECT
HR         SCOTT      EMP        SCOTT      UPDATE
HR         SCOTT      EMP        SCOTT      REFERENCES
HR         SCOTT      EMP        SCOTT      ON COMMIT REFRESH
HR         SCOTT      EMP        SCOTT      QUERY REWRITE
HR         SCOTT      EMP        SCOTT      DEBUG
HR         SCOTT      EMP        SCOTT      FLASHBACK

11 rows selected.

ALL代表对象上的所有权限
SQL> select * from user_sys_privs;   查询所有系统权限
USERNAME   PRIVILEGE  ADMIN_
---------- ---------- ------
HR         CREATE VIE NO
           W

HR         UNLIMITED  NO
           TABLESPACE

HR         CREATE DAT NO
           ABASE LINK

HR         CREATE SEQ NO
           UENCE

USERNAME   PRIVILEGE  ADMIN_
---------- ---------- ------

HR         CREATE SES NO
           SION

HR         ALTER SESS NO
           ION

HR         CREATE SYN NO
           ONYM

7 rows selected.

SQL> conn / as sysdba
Connected.
SQL> revoke all on scott.emp from hr;

Revoke succeeded.                           收回hr下的scott.emp的所有权限