Oracle DBA课程系列笔记（17）-白红宇

第十七章：权限管理

1、权限（privilege）：system privilege and object privilege

1） system privilege：针对于database 的相关权限

2） object privilege：针对于schema 的object

2、查看系统权限

10:55:12 SQL> desc system_privilege_map;

Name Null? Type

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

PRIVILEGE NOT NULL NUMBER

NAME NOT NULL VARCHAR2(40)

PROPERTY NOT NULL NUMBER

10:55:41 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数据字典视图

10:55:49 SQL> grant select any table to scott;

Grant succeeded.

10:57:59 SQL> conn scott/tiger

Connected.

10:58:03 SQL>

10:58:03 SQL> desc user_sys_privs;

Name Null? Type

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

USERNAME VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

ADMIN_OPTION VARCHAR2(3)

10:58:13 SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

SCOTT UNLIMITED TABLESPACE NO

SCOTT SELECT ANY TABLE NO

10:58:20 SQL> select * from tom.t01;

----------

10:58:29 SQL> select * from sys.dba_users;

select * from sys.dba_users

ERROR at line 1:

ORA-00942: table or view does not exist

10:58:38 SQL>

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

11:00:23 SQL> show parameter o7

NAME TYPE VALUE

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

O7_DICTIONARY_ACCESSIBILITY boolean FALSE

11:00:44 SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;

System altered.

11:00:48 SQL> startup force

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 83888372 bytes

Database Buffers 79691776 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

11:01:03 SQL> conn scott/tiger

Connected.

11:02:12 SQL> select TABLE_NAME from dba_tables where owner='SCOTT';

TABLE_NAME

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

EMPLOYEES

TEST

DEPT

EMP

BONUS

SALGRADE

EMP1

EXCEPTIONS

DEPT1

ADMIN_EXT_EMPLOYEES

10 rows selected

3、分配、回收系统权限

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

11:06:55 SQL> grant select any table to scott with admin option;

Grant succeeded.

11:07:05 SQL> conn scott/tiger

Connected.

11:07:08 SQL>

11:07:08 SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

SCOTT UNLIMITED TABLESPACE NO

SCOTT SELECT ANY TABLE YES

11:07:17 SQL> grant select any table to tom;

Grant succeeded.

11:07:22 SQL> conn tom/tom

Connected.

11:07:26 SQL>

11:07:26 SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

TOM CREATE TABLE NO

TOM SELECT ANY TABLE NO

TOM CREATE SESSION NO

11:07:30 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 ，在回收权限时，不能级联。

11:07:44 SQL> conn /as sysdba

Connected.

11:07:51 SQL>

11:07:51 SQL> revoke select any table from scott;

Revoke succeeded.

11:08:02 SQL> conn scott/tiger

Connected.

11:08:10 SQL>

11:08:10 SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

SCOTT UNLIMITED TABLESPACE NO

11:08:14 SQL> conn tom/tom

Connected.

11:08:18 SQL>

11:08:18 SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

TOM CREATE TABLE NO

TOM SELECT ANY TABLE NO

TOM CREATE SESSION NO

11:08:20 SQL>

----------必须一一收回

11:09:01 SQL> revoke select any table from tom;

Revoke succeeded.

11:09:11 SQL> conn tom/tom

Connected.

11:09:14 SQL>

11:09:14 SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

TOM CREATE TABLE NO

TOM CREATE SESSION NO

11:09:18 SQL>

4、对象权限

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

11:15:51 SQL> grant all on scott.emp to public;

----all 代表所有的对象权限，public 代表所有的用户

11:16:01 SQL> conn tom/tom

Connected.

11:16:51 SQL>

1* select * from user_tab_privs

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

TOM SCOTT EMP SCOTT SELECT NO NO

11:16:53 SQL> delete from scott.emp;

14 rows deleted.

11:17:53 SQL> rollback;

Rollback complete.

11:18:00 SQL

11:18:33 SQL> revoke all on scott.emp from public;

Revoke succeeded.

11:19:16 SQL> conn /as sysdba

Connected.

11:19:22 SQL>

11:19:22 SQL> grant update on scott.emp to tom with grant option;

Grant succeeded.

11:19:45 SQL> create user rose identified by rose ;

User created.

11:20:04 SQL> grant create session to rose;

Grant succeeded.

11:20:13 SQL> conn tom/tom

Connected.

11:20:16 SQL>

11:20:16 SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

TOM SCOTT EMP SCOTT UPDATE YES NO

TOM SCOTT EMP SCOTT SELECT NO NO

11:20:32 SQL> grant update on scott.emp to rose;

Grant succeeded.

11:20:46 SQL> conn rose/rose

Connected.

11:20:50 SQL>

11:20:50 SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

ROSE SCOTT EMP TOM UPDATE NO NO

-------------- revoke with grant option ，在回收权限时，级联。

11:20:54 SQL> conn /as sysdba

Connected.

11:21:00 SQL>

11:21:00 SQL> revoke update on scott.emp from rose;

revoke update on scott.emp from rose

ERROR at line 1:

ORA-01927: cannot REVOKE privileges you did not grant

----只能从直接授予者回收权限

11:21:14 SQL> revoke update on scott.emp from tom;

Revoke succeeded.

11:21:22 SQL> conn tom/tom

Connected.

11:21:25 SQL>

11:21:25 SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

TOM SCOTT EMP SCOTT SELECT NO NO

--------针对列授予对象权限

11:24:05 SQL> grant update(sal) on scott.emp to tom;

Grant succeeded.

11:29:39 SQL> conn tom/tom

Connected.

11:29:51 SQL>

11:29:51 SQL> update scott.emp set comm=100 where empno=7788;

update scott.emp set comm=100 where empno=7788

ERROR at line 1:

ORA-01031: insufficient privileges

11:29:53 SQL> update scott.emp set sal=10000 where empno=7788;

1 row updated.

11:30:01 SQL> rollback;

Rollback complete.

11:30:54 SQL>

1* select GRANTEE,table_name,COLUMN_NAME,PRIVILEGE from user_col_privs

GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE

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

TOM EMP SAL UPDATE

11:30:54 SQL>

视图：

显示用户授出的列权限

04:47:57 SQL>

1 select grantee,privilege,table_name||'.'||column_name

2 tab_column

3* from user_col_privs_made；

4）显示用户所具有的列权限（收到）

select privilege,table_name||'.'||column_name tab_column,

04:49:38 2 grantor

04:49:43 3 from all_col_privs_recd

04:49:53 4 where grantee='HR';

no rows selected

显示用户所授出的对象权限

04:42:47 SQL> col table_name for a10for a10

04:51:19 SQL> select grantee ,privilege ,table_name

04:51:34 2 from user_tab_privs_made;

GRANTEE PRIVILEGE TABLE_NAME

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

HR DELETE DEPT

HR SELECT DEPT

HR UPDATE DEPT

OE SELECT EMP

显示用户所具有的对象权限（收到）

04:52:45 SQL> select privilege,table_name,grantor

04:52:58 2 from all_tab_privs_recd

04:53:10 3 where grantee='HR';

PRIVILEGE TABLE_NAME GRANTOR

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

EXECUTE DBMS_STATS SYS

DELETE DEPT SCOTT

SELECT DEPT SCOTT

UPDATE DEPT SCOT

本文转自客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/791809，如需转载请自行联系原作者