課程目標：

區分系統許可權和物件許可權
賦予表許可權
透過資料字典查詢許可權
賦予角色
區分許可權和角色

許可權分類：

系統許可權，是指對資料庫進行訪問的許可權，比如建立連線，表，資料庫，表空間等
物件許可權，是指對資料庫物件內容進行操作的許可權，比如增加記錄，刪除記錄等

模式是使用者下面物件的集合

1、系統許可權

例子：透過 dba_sys_privs檢視檢視當前系統的系統許可權

SQL> select count(distinct privilege) from dba_sys_privs;

COUNT(DISTINCTPRIVILEGE)

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

202

SQL> select distinct privilege from dba_sys_privs order by 1;

PRIVILEGE

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

ADMINISTER ANY SQL TUNING SET

（1）建立使用者

語法：

例子：建立一個使用者名稱為user1，密碼為user1的使用者

SQL> create user user1 identified by user1;

User created.

例子：修改使用者user1的預設表空間為users，配額為10M，臨時表空間為temp

SQL> alter user user1 identified by user1 default tablespace users temporary tablespace temp quota 10m on users;

User altered.

（2）授予使用者系統許可權

建立好使用者後，沒有賦予許可權是不能進行登入的

例子：使用剛剛建立的使用者連線資料庫報錯，沒有建立session的許可權

SQL> conn user1/user1

ERROR:

ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

授予系統許可權的語法：

系統開發人員需要的許可權如下：

建立session
建立表
建立序列
建立檢視
建立過程

例子：授予系統許可權給使用者

SQL> conn / as sysdba

Connected.

SQL> grant create session,create table,create sequence,create view to user1;

Grant succeeded.

現在登入

SQL> conn user1/user1

Connected.

透過session_privs檢視檢視該使用者的系統許可權

SQL> select * from session_privs;

PRIVILEGE

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

CREATE SESSION

CREATE TABLE

CREATE VIEW

CREATE SEQUENCE

（3）建立和授予許可權給角色

角色是一系列許可權的集合，是為了簡化使用者許可權的分配和管理，可以將角色授予給使用者和其他角色。

例子：檢視當前使用者關於角色的許可權

SQL> conn / as sysdba

Connected.

SQL> select * from session_privs where privilege like '%ROLE%';

PRIVILEGE

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

CREATE ROLE

DROP ANY ROLE

GRANT ANY ROLE

ALTER ANY ROLE

例子：建立角色，給角色授予許可權，將角色授予使用者

SQL> create role manager;

Role created.

SQL> grant create session,create table,create sequence,create view to manager;

Grant succeeded.

SQL> create user user2 identified by user2;

User created.

SQL> grant manager,create synonym to user2;

Grant succeeded.

SQL> conn user2/user2;

Connected.

SQL> select * from session_roles;

ROLE

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

MANAGER

SQL> select * from session_privs;

PRIVILEGE

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

CREATE SESSION

CREATE TABLE

CREATE SYNONYM

CREATE VIEW

CREATE SEQUENCE

（4）修改你的密碼

使用者可以使用alter user語句修改自己的密碼

例子：修改自己的密碼

SQL> show user

USER is "USER2"

SQL> alter user user2 identified by user2;

User altered.

也可以使用password命令來修改密碼

SQL> password user2

Changing password for user2

Old password:

New password:

Retype new password:

Password changed

dba可以使用password命令修改其他使用者的密碼

SQL> conn / as sysdba

Connected.

SQL> password user2

Changing password for user2

New password:

Retype new password:

Password changed

2、物件許可權

不同的物件有不同的物件許可權
使用者有其使用者模式下所有物件的物件許可權
物件許可權可以轉授權給其他使用者或者角色
系統許可權和物件許可權不能在一條grant語句裡面

語法：

這裡一次只能針對一個物件的許可權進行授權

例子：將employees表的查詢許可權授予user1，user2

SQL> conn hr/hr

Connected.

SQL> grant select on employees to user1,user2;

Grant succeeded.

例子：將更新departments表department_name，location_id列的許可權授予user1，manager

SQL> grant update(department_name,location_id) on departments to user1,manager;

Grant succeeded.

例子：將departments表select，insert許可權授予user1，且user1可以轉授權

SQL> grant select,insert on departments to user1 with grant option;

Grant succeeded.

例子：將departments表的選擇許可權授予public，所有使用者都可以訪問

SQL> grant select on hr.departments to public;

Grant succeeded.

3、與許可權相關的資料字典檢視

例子：檢視賦予角色的系統許可權

SQL> conn hr/hr

Connected.

SQL> select * from role_sys_privs;

ROLE PRIVILEGE ADM

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

RESOURCE CREATE SEQUENCE NO

RESOURCE CREATE TRIGGER NO

RESOURCE CREATE CLUSTER NO

RESOURCE CREATE PROCEDURE NO

RESOURCE CREATE TYPE NO

RESOURCE CREATE OPERATOR NO

RESOURCE CREATE TABLE NO

RESOURCE CREATE INDEXTYPE NO

8 rows selected.

例子：檢視授予角色的表許可權

SQL> conn user2/user2;

Connected.

SQL> select * from role_tab_privs;

ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA

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

MANAGER HR DEPARTMENTS LOCATION_ID UPDATE NO

MANAGER HR DEPARTMENTS DEPARTMENT_NAME UPDATE NO

MANAGER HR JOBS SELECT NO

例子：檢視使用者擁有的角色

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_

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

USER2 MANAGER NO YES NO

SQL> conn hr/hr

Connected.

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_

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

HR RESOURCE NO YES NO

例子：檢視授予給其他使用者的物件許可權

SQL> select * from user_tab_privs_made;

GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

USER1 EMPLOYEES HR SELECT NO NO

當前使用者HR授予使用者USER1表EMPLOYEES的SELECT許可權。

例子：檢視當前使用者獲取的其他使用者授予的物件許可權

SQL> select * from user_tab_privs_recd;

OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

SYS DBMS_STATS SYS EXECUTE NO NO

SQL> conn user2/user2;

Connected.

SQL> select * from user_tab_privs_recd;

OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

HR EMPLOYEES HR SELECT NO NO

例子：查詢授予給其他使用者的及當前使用者獲取的物件許可權（user_tab_privs_made加上user_tab_privs_recd）

SQL> conn hr/hr

Connected.

SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

HR SYS DBMS_STATS SYS EXECUTE NO NO

OE HR COUNTRIES HR REFERENCES NO NO

OE HR COUNTRIES HR SELECT NO NO

USER2 HR DEPARTMENTS USER1 SELECT NO NO

USER2 HR DEPARTMENTS USER1 INSERT NO NO

PUBLIC HR DEPARTMENTS HR SELECT NO NO

USER1 HR DEPARTMENTS HR SELECT YES NO

USER1 HR DEPARTMENTS HR INSERT YES NO

OE HR DEPARTMENTS HR SELECT NO NO

OE HR EMPLOYEES HR REFERENCES NO NO

OE HR EMPLOYEES HR SELECT NO NO

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

USER2 HR EMPLOYEES HR SELECT NO NO

USER1 HR EMPLOYEES HR SELECT NO NO

MANAGER HR JOBS HR SELECT NO NO

OE HR JOBS HR SELECT NO NO

OE HR JOB_HISTORY HR SELECT NO NO

OE HR LOCATIONS HR SELECT NO NO

OE HR LOCATIONS HR REFERENCES NO NO

18 rows selected.

例子：檢視授予給其他使用者的表列的物件許可權

SQL> conn hr/hr

Connected.

SQL> select * from user_col_privs_made;

GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA

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

USER1 DEPARTMENTS LOCATION_ID HR UPDATE NO

例子：檢視當前使用者獲取的其他使用者授予的表列的物件許可權

SQL> conn user1/user1;

Connected.

SQL> select * from user_col_privs_recd;

OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA

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

HR DEPARTMENTS DEPARTMENT_NAME HR UPDATE NO

HR DEPARTMENTS LOCATION_ID HR UPDATE NO

例子：查詢授予給其他使用者的及當前使用者獲取的表列的物件許可權（user_col_privs_made加上user_col_privs_recd）

SQL> conn user1/user1;

Connected.

SQL> create table dept as select * from hr.departments;

Table created.

SQL> grant update(department_id) on dept to user2;

Grant succeeded.

SQL> select * from user_col_privs_made;

GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA

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

USER2 DEPT DEPARTMENT_ID USER1 UPDATE NO

SQL> select * from user_col_privs_recd;

OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA

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

HR DEPARTMENTS DEPARTMENT_NAME HR UPDATE NO

HR DEPARTMENTS LOCATION_ID HR UPDATE NO

SQL> select * from user_col_privs;

GRANTEE OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA

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

USER1 HR DEPARTMENTS LOCATION_ID HR UPDATE NO

USER1 HR DEPARTMENTS DEPARTMENT_NAME HR UPDATE NO

USER2 USER1 DEPT DEPARTMENT_ID USER1 UPDATE NO

例子：檢視授予給使用者的系統許可權（不包含角色裡面的系統許可權）

SQL> conn user2/user2

Connected.

SQL> select * from role_sys_privs;

ROLE PRIVILEGE ADM

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

MANAGER CREATE SEQUENCE NO

MANAGER CREATE SESSION NO

MANAGER CREATE TABLE NO

MANAGER CREATE VIEW NO

以上是當前使用者的角色的系統許可權

SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

USER2 CREATE SYNONYM NO

以上是給當前使用者單獨賦予的系統許可權

SQL> select * from session_privs;

PRIVILEGE

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

CREATE SESSION

CREATE TABLE

CREATE SYNONYM

CREATE VIEW

CREATE SEQUENCE

以上是當前使用者的所有的系統許可權

透過下面的示例可以看出使用者的session_privs是role_sys_privs和user_sys_privs的並集

SQL> conn hr/hr

Connected.

SQL> select * from role_sys_privs;

ROLE PRIVILEGE ADM

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

RESOURCE CREATE SEQUENCE NO

RESOURCE CREATE TRIGGER NO

RESOURCE CREATE CLUSTER NO

RESOURCE CREATE PROCEDURE NO

RESOURCE CREATE TYPE NO

RESOURCE CREATE OPERATOR NO

RESOURCE CREATE TABLE NO

RESOURCE CREATE INDEXTYPE NO

8 rows selected.

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

7 rows selected.

SQL> select privilege from role_sys_privs

2 union

3 select privilege from user_sys_privs;

PRIVILEGE

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

ALTER SESSION

CREATE CLUSTER

CREATE DATABASE LINK

CREATE INDEXTYPE

CREATE OPERATOR

CREATE PROCEDURE

CREATE SEQUENCE

CREATE SESSION

CREATE SYNONYM

CREATE TABLE

CREATE TRIGGER

PRIVILEGE

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

CREATE TYPE

CREATE VIEW

UNLIMITED TABLESPACE

14 rows selected.

SQL> select * from session_privs;

PRIVILEGE

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

CREATE SESSION

ALTER SESSION

UNLIMITED TABLESPACE

CREATE TABLE

CREATE CLUSTER

CREATE SYNONYM

CREATE VIEW

CREATE SEQUENCE

CREATE DATABASE LINK

CREATE PROCEDURE

CREATE TRIGGER

PRIVILEGE

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

CREATE TYPE

CREATE OPERATOR

CREATE INDEXTYPE

14 rows selected.

4、回收許可權

物件許可權級聯回收

系統許可權不能級聯回收

語法：

例子：使用者hr回收授予給使用者user1的對departments的select和insert物件許可權

SQL> conn hr/hr

Connected.

SQL> select * from user_tab_privs_made where grantee='USER1' and table_name='DEPARTMENTS';

GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

USER1 DEPARTMENTS HR INSERT YES NO

USER1 DEPARTMENTS HR SELECT YES NO

SQL> conn user1/user1

Connected.

SQL> grant select,insert on hr.departments to user2;

Grant succeeded.

SQL> conn hr/hr

Connected.

SQL> select * from user_tab_privs where table_name='DEPARTMENTS' and grantee like '%USER%';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

USER1 HR DEPARTMENTS HR INSERT YES NO

USER1 HR DEPARTMENTS HR SELECT YES NO

USER2 HR DEPARTMENTS USER1 INSERT NO NO

USER2 HR DEPARTMENTS USER1 SELECT NO NO

從這兒可以看到使用者HR將表DEPARTMENTS的INSERT和SELECT許可權授予給USER1，然後USER1又將這兩個許可權授予給了USER2。

那麼使用者HR能否直接回收使用者USER2的這兩個許可權呢？試一試

SQL> revoke select,insert on departments from user2;

revoke select,insert on departments from user2

*

ERROR at line 1:

ORA-01927: cannot REVOKE privileges you did not grant

不行哈，那先回收使用者USER1的這兩個許可權

SQL> revoke select,insert on departments from user1;

Revoke succeeded.

再檢視一下許可權呢

SQL> select * from user_tab_privs where table_name='DEPARTMENTS' and grantee like '%USER%';

no rows selected

可以看到USER1和USER2的這兩個許可權都被回收了，說明物件許可權可以級聯回收。

我們看看系統許可權可不可以級聯回收：

SQL> conn / as sysdba

Connected.

SQL> create user user3 identified by user3;

User created.

SQL> create user user4 identified by user4;

User created.

SQL> grant connect to user3;

Grant succeeded.

SQL> grant connect to user4;

Grant succeeded.

SQL> conn user3/user3;

Connected.

SQL> select * from session_privs;

PRIVILEGE

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

CREATE SESSION

SQL> conn / as sysdba

Connected.

SQL> grant create table to user3 with admin option;

Grant succeeded.

SQL> conn user3/user3;

Connected.

SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

USER3 CREATE TABLE YES

SQL> grant create table to user4;

Grant succeeded.

SQL> conn user4/user4;

Connected.

SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

USER4 CREATE TABLE NO

SQL> conn / as sysdba

Connected.

SQL> revoke create table from user3;

Revoke succeeded.

SQL> conn user4/user4;

Connected.

SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

USER4 CREATE TABLE NO

可以看到系統許可權不能級聯回收。

5、總結

6、相關習題

（1）OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence: CREATE ROLE r1 GRANT SELECT, INSERT ON oe.orders TO r1 GRANT r1 TO scott GRANT SELECT ON oe.orders TO scott REVOKE SELECT ON oe.orders FROM scott What would be the outcome after executing the statements ？

A.SCOTT would be able to query the OE.ORDERS table.

B.SCOTT would not be able to query the OE.ORDERS table.

C.The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1.

D.The REVOKE statement would give an error because the SELECT privilege has been granted to the

role R1.

答案：A

（2）Which statement correctly grants a system privilege?

A.GRANT EXECUTE ON proc1 TO PUBLIC?

B.GRANT CREATE VIEW ON table1 TO user1?

C.GRANT CREATE TABLE TO user1,user2?

D.GRANT CREATE SESSION TO ALL?

答案：C

（3）User OE, the owner of the ORDERS table, issues the following command:

GRANT SELECT ,INSERT ON orders TO hr WITH GRANT OPTION?

The user HR issues the following command:

GRANT SELECT ON oe.orders TO scott?

Then, OE issues the following command:
REVOKE ALL ON orders FROM hr?

Which statement is correct?

A.The user SCOTT loses the privilege to select rows from OE.ORDERS.
B.The user SCOTT retains the privilege to select rows from OE.ORDERS.
C.The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT.
D.The REVOKE statement generates an error because the ALL keyword cannot be used for privileges that have been granted using WITH GRANT OPTION.

答案：A

（4）SCOTT is a user in the database. Evaluate the commands issued by the DBA: 1 CREATE ROLE mgr;2 GRANT CREATE TABLE, SELECT ON oe.orders TO mgr;3 GRANT mgr, create table TO SCOTT;Which statement is true regarding the execution of the above commands?

A.Statement 1 would not execute because the WITH GRANT option is missing.

B.Statement 1 would not execute because the IDENTIFIED BY clause is missing.

C.Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.

D.Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.

答案：D

（5）View the Exhibit and examine the structure of the ORDERS table. The ORDERS table belongs to the user OE. HR is another user in the database. Evaluate the commands issued by users OE and HR in the following order: Statement 1 by user OE: GRANT SELECT, UPDATE(customer_id, order_total) ON orders TO hr;Statement 1 by user HR: SELECT * FROM oe.orders;Statement 2 by user HR: UPDATE oe.orders SET order_total= 10000;Which statement is true regarding the above commands?

A.Statement 1 by user OE would not work because the statement has to be issued by the DBA.

B.Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update.

C.There are no errors in the statements issued by OE and HR? all the statements would execute successfully.

D.Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns.

答案：C

（6）The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command: GRANT ALL ON orders, order_items TO PUBLIC；What correction needs to be done to the above statement ?
A.PUBLIC should be replaced with specific usernames.
B.ALL should be replaced with a list of specific privileges.
C.WITH GRANT OPTION should be added to the statement.
D.Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

答案：D

（7）Which two statements are true regarding roles (Choose two.)
   A.A role can be granted to itself.
   B.A role can be granted to PUBLIC.
   C.A user can be granted only one role at any point of time.
   D.The REVOKE command can be used to remove privileges but not roles from other users.
   E.Roles are named groups of related privileges that can be granted to users or other roles.

答案：BE

（8）Which statement correctly differentiates a system privilege from an object privilege ？
A.System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object.
B.System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema.
C.Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database.
D.A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.

答案：D

（9）Which statement is true regarding the SESSION_PRIVS dictionary view ?
A.It contains the current object privileges available in the user session.
B.It contains the current system privileges available in the user session.
C.It contains the object privileges granted to other users by the current user session.
D.It contains the system privileges granted to other users by the current user session.

答案：B

OCP課程14：SQL之控制使用者訪問

相關文章