管理权限（二）--系统权限的管理

 

1、查看系统权限

SQL> desc system_privilege_map;

Name                                                              Null?    Type

----------------------------------------------------------------- -------- --------------------------------------------

PRIVILEGE                                                         NOT NULL NUMBER

NAME                                                              NOT NULL VARCHAR2(40)

PROPERTY                                                          NOT NULL NUMBER

SQL> select * from system_privilege_map;

PRIVILEGE NAME                                       PROPERTY

---------- ---------------------------------------- ----------

-3 ALTER SYSTEM                                      0

-4 AUDIT SYSTEM                                      0

-5 CREATE SESSION                                    0

-6 ALTER SESSION                                     0

-7 RESTRICTED SESSION                                0

-10 CREATE TABLESPACE                                 0

-11 ALTER TABLESPACE                                  0

-12 MANAGE TABLESPACE                                 0

-13 DROP TABLESPACE                                   0

-15 UNLIMITED TABLESPACE                              0

……

-280 CREATE EXTERNAL JOB                               0

166 rows selected.

——select any table  访问dba_xxx数据字典视图

2、授予系统权限

SQL> grant select any table to scott; ——授权

Grant succeeded.

SQL> conn scott/tiger

Connected.

SQL> desc user_sys_privs;

Name                                                              Null?    Type

----------------------------------------------------------------- -------- --------------------------------------------

USERNAME                                                                   VARCHAR2(30)

PRIVILEGE                                                         NOT NULL VARCHAR2(40)

ADMIN_OPTION                                                               VARCHAR2(3)

3、查看用户拥有的系统权限

SQL> select * from user_sys_privs；——查看用户拥有的系统权限

USERNAME                       PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

SCOTT                          UNLIMITED TABLESPACE                     NO

SCOTT                          SELECT ANY TABLE                         NO

SQL> select * from tom.text2;

ID

----------

1

2

3

4

5

6

7

8

8 rows selected.

SQL> select * from sys.dba_users;

select * from sys.dba_users

*

ERROR at line 1:

ORA-00942: table or view does not exist

——默认普通用户不能去访问dba_xxx 视图，需要修改以下参数

SQL> show parameter o7

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;

System altered.

SQL> startup force

SQL> conn scott/tiger

SQL> select table_name from dba_tables where owner='SCOTT';

TABLE_NAME

-------------------------

DEPT

EMP

BONUS

SALGRADE

EMPLOYEES

ADMIN_EXT_EMPLOYEES

EMP1

7 rows selected.

4、分配、回收系统权限

grant——with admin option  （如果用户获得权限时，设置此参数，用户可以将权限再授予别的用户）

SQL> grant select any table to scott with admin option;

Grant succeeded.

SQL> conn scott/tiger

Connected.

SQL> col usrname for a10

SQL> col privilege for a30

SQL> select * from user_sys_privs;

USERNAME        PRIVILEGE                      ADMIN_OPT

--------------- ------------------------------ ---------

SCOTT           UNLIMITED TABLESPACE           NO

SCOTT           SELECT ANY TABLE               YES

SQL> grant select any table to tom;

Grant succeeded.

SQL> conn tom/tom

Connected.

SQL> select * from user_sys_privs;

USERNAME        PRIVILEGE                      ADMIN_OPT

--------------- ------------------------------ ---------

TOM             SELECT ANY TABLE               NO

TOM             CREATE SESSION                 NO

TOM             UNLIMITED TABLESPACE           NO

SQL> select * from scott.emp where rownum <3;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH      CLERK           7902 17-DEC-80        800                    20

7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

——revoke              with admin option ，在回收权限时，不能级联。

SQL> conn /as sysdba

Connected.

SQL> revoke select any table from scott;

Revoke succeeded.

SQL> conn scott/tiger

Connected.

SQL> select * from user_sys_privs;

USERNAME        PRIVILEGE                      ADMIN_OPT

--------------- ------------------------------ ---------

SCOTT           UNLIMITED TABLESPACE           NO

SQL> conn tom/tom

Connected.

SQL> select * from user_sys_privs;

USERNAME        PRIVILEGE                      ADMIN_OPT

--------------- ------------------------------ ---------

TOM             SELECT ANY TABLE               NO

TOM             CREATE SESSION                 NO

TOM             UNLIMITED TABLESPACE           NO

——系统权限必须一一收回，不能级联回收

SQL> conn /as sysdba

Connected.

SQL> revoke select any table from tom;

Revoke succeeded.

SQL> conn tom/tom

Connected.

SQL> select * from user_sys_privs;

USERNAME        PRIVILEGE                      ADMIN_OPT

--------------- ------------------------------ ---------

TOM             CREATE SESSION                 NO

TOM             UNLIMITED TABLESPACE           NO