http://blog.csdn.net/leshami/article/details/5688875

-================================

--Oracle 使用者、物件許可權、系統許可權

--================================

一、使用者與模式

使用者：對資料庫的訪問，需要以適當使用者身份透過驗證，並具有相關許可權來完成一系列動作

SYS使用者，預設始終建立，且未被鎖定，擁有資料字典及其關聯的所有物件

SYSTEM使用者，預設始終建立，且未被鎖定，可以訪問資料庫內的所有物件

模式(schema)：是某個使用者擁有所有物件的集合。具有建立物件許可權並建立了物件的使用者稱為擁有某個模式

注意：建立資料庫物件(檢視,表等)的任一使用者都擁有一個以該使用者名稱稱開頭的模式，且被視為模式使用者

二、建立及修改使用者

條件：需要具有建立使用者的許可權，如sys,system,sysdba,dba role等

語法：

CREATE USER user

IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS external name }

[DEFAULT TABLESPACE tablespace_name]

[TEMPORARY TABLESPACE tablespace_name]

[QUOTA {n {[K|M] | UNLIMITED } ON tablespace_name

QUOTA {n {[k|M] | UNLIMITED } ON tablespace_name ... ]

[PASSWORD EXPIRE]

[ACCOUNT { LOCK | UNLOCK }]

[PROFILE { profile_name | DEFAULT }]

eg:

CREATE USER robinson IDENTIFIED BY tiger;

--省略了DEFAULT TABLESPACE和TEMPORARY TABLESPACE 時，則由database_properties中對應的引數確定

SQL> SELECT property_name,property_value FROM database_properties WHERE property_name LIKE 'DEFAULT%';

PROPERTY_NAME PROPERTY_VALUE

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

DEFAULT_TEMP_TABLESPACE TEMP

DEFAULT_PERMANENT_TABLESPACE USERS

DEFAULT_TBS_TYPE SMALLFILE

更多關於表空間的請參考：Oracle 表空間與資料檔案

1.修改使用者

修改使用者的語法同建立使用者，僅僅講關鍵字create替換為alter，alter user可以修改除使用者名稱之外的任一屬性

ALTER USER robinson ACCOUNT LOCK;

2.修改密碼

DBA 可以建立使用者和修改密碼

使用者本人可以使用ALTER USER 語句修改密碼

SQL> ALTER robinson IDENTIFIED BY newpassword;

3.刪除使用者：

DROP USER username [CASCADE]

CASECADE 連同使用者建立的物件一併刪除，如果該使用者建立了物件，要加CASCADE刪除，否則刪除不掉

另外，不能刪除當前正在與ORACLE伺服器相連的使用者。

4.改變使用者在表空間上的配額：

ALTER USER username QUOTA 0 ON system;

ALTER USER scott QUOTA UNLIMITED ON USERS;

ALTER USER dog QUOTA 30M ON system;

5.檢視使用者表空間配額（dba_ts_quotas)：

SQL> SELECT USERNAME,TABLESPACE_NAME,MAX_BYTES/1024/1024 "Max MB"

2 FROM dba_ts_quotas WHERE USERNAME='SCOTT';

USERNAME TABLESPACE_NAME Max MB

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

SCOTT SYSTEM 30

6.檢視特定物件下使用者所擁有的物件

使用dba_objects檢視

SQL> SELECT owner,object_name, object_type FROM dba_objects WHERE owner= 'SCOTT';

三、ORACLE許可權：

系統許可權: 允許使用者執行特定的資料庫動作，如建立表、建立索引、連線例項等

物件許可權: 允許使用者操縱一些特定的物件，如讀取檢視，可更新某些列、執行儲存過程等

1.系統許可權

超過一百多種有效的許可權（SELECT * FROM SYSTEM_PRIVILEGE_MAP查）

資料庫管理員具有高階許可權以完成管理任務，例如:

–建立新使用者

–刪除使用者

–刪除表

–備份表

a.常用的系統許可權：

CREATE SESSION 建立會話

CREATE SEQUENCE 建立序列

CREATE SYNONYM 建立同名物件

CREATE TABLE 在使用者模式中建立表

CREATE ANY TABLE 在任何模式中建立表

DROP TABLE 在使用者模式中刪除表

DROP ANY TABLE 在任何模式中刪除表

CREATE PROCEDURE 建立儲存過程

EXECUTE ANY PROCEDURE 執行任何模式的儲存過程

CREATE USER 建立使用者

DROP USER 刪除使用者

CREATE VIEW 建立檢視

b.授予使用者系統許可權

GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...]

[WITH ADMIN OPTION];

PUBLIC 所有使用者

WITH ADMIN OPTION 使使用者同樣具有分配許可權的權利，可將此許可權授予別人

SQL> GRANT CREATE SESSION,CREATE TABLE,CREATE USER TO scott;

SQL> GRANT EXECUTE ANY PROCEDURE TO scott WITH ADMIN OPTION;

SQL> CONN scott; --scott具有WITH ADMIN OPTION，故可以將EXECUTE ANY PROCEDURE授予robinson

Enter password:

Connected.

SQL> GRANT EXECUTE ANY PROCEDURE TO robinson;

Grant succeeded.

SQL> GRANT EXECUTE ANY PROCEDURE TO PUBLIC; --將EXECUTE ANY PROCEDURE授予所有使用者

Grant succeeded.

SQL> CONN system/redhat; --使用system為robinson授予CREATE TABLE、CREATE SESSION許可權

Connected.

SQL> GRANT CREATE TABLE,CREATE SESSION TO robinson;

Grant succeeded.

c.使用系統許可權

--使用robinson具有建立會話、建立表

SQL> CREATE TABLE tb1 AS SELECT * FROM USER_TABLES; --下面提示沒有許可權在users表空間建立物件

CREATE TABLE tb1 AS SELECT * FROM USER_TABLES

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

SQL> CONN sys as sysdba; --使用sys帳戶登陸併為robinson在users表空間指定配額後可以建立表tb1

Enter password:

Connected.

SQL> ALTER USER robinson QUOTA 10M ON USERS;

User altered.

SQL> CONN robinson/lion;

Connected.

SQL> CREATE TABLE tb1 AS SELECT * FROM USER_TABLES;

Table created.

d.檢視系統許可權

dba_sys_privs --針對所有使用者被授予的系統許可權

user_sys_privs --針對當前登陸使用者被授予的系統許可權

SQL> SELECT grantee,privilege,admin_option FROM dba_sys_privs

2 WHERE grantee IN ('SCOTT','ROBINSON')

3 ORDER BY grantee;

GRANTEE PRIVILEGE ADM

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

ROBINSON CREATE SESSION NO

ROBINSON CREATE TABLE NO

ROBINSON EXECUTE ANY PROCEDURE NO

SCOTT CREATE PUBLIC SYNONYM NO

SCOTT CREATE SESSION NO

SCOTT CREATE SYNONYM NO

SCOTT CREATE TABLE NO

SCOTT CREATE USER NO

SCOTT CREATE VIEW NO

SCOTT EXECUTE ANY PROCEDURE YES

SCOTT UNLIMITED TABLESPACE NO

e.回收系統許可權

REVOKE {privilege | role} FROM {user_name | role_name | PUBLIC}

--下面的示例中並沒有回收掉原來由scott授予給robisnon EXECUTE ANY PROCEDURE 的許可權

SQL> REVOKE EXECUTE ANY PROCEDURE FROM scott;

Revoke succeeded.

SQL> select grantee,privilege,admin_option from dba_sys_privs

2 where grantee in ('SCOTT','ROBINSON') and privilege = 'EXECUTE ANY PROCEDURE'

3 order by grantee;

GRANTEE PRIVILEGE ADM

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

ROBINSON EXECUTE ANY PROCEDURE NO

注意：對於使用with admin option 為某個使用者授予系統許可權，那麼對於被這個使用者授予相同許可權的所有

使用者來說，取消該使用者的系統許可權並不會級聯取消這些使用者的相同許可權

2.物件許可權

不同的物件具有不同的物件許可權

物件的擁有者擁有所有許可權

物件的擁有者可以向外分配許可權

ORACLE一共有種物件許可權

物件許可權表檢視序列過程

修改(alter) √ √

刪除(delete) √ √

執行(execute) √

索引(index) √

插入(insert) √ √

關聯(references) √ √

選擇(select) √ √ √

更新(update) √ √

a.物件授權

GRANT object_priv｜ALL [(columns)]

ON object

TO {user|role|PUBLIC}

[WITH GRANT OPTION];

ALL：所有物件許可權

PUBLIC：授給所有的使用者

WITH GRANT OPTION：允許使用者再次給其它使用者授權

b.授予系統許可權與授予物件許可權的語法差異：

授予物件許可權時需要指定關鍵字ON，從而能夠確定許可權所應用的物件。對於表和檢視可以指定特定的列來授權。

--物件授權示例

SQL> SHOW USER;

USER is "SCOTT"

SQL> GRANT SELECT ON emp TO robinson;

Grant succeeded.

SQL> GRANT UPDATE(sal,mgr) ON emp TO robinson WITH GRANT OPTION;

Grant succeeded.

--新建立一個使用者john，使用robinson賬戶授予更新scott.emp(sal,mgr)的許可權

SQL> CREATE USER john IDENTIFIED BY john;

User created.

SQL> GRANT CREATE SESSION TO john;

Grant succeeded.

SQL> CONN ROBINSON/LION

Connected.

SQL> GRANT UPDATE(sal,mgr) ON scott.emp TO john; --授予scott.emp(sal,mgr)的更新許可權

Grant succeeded.

SQL> UPDATE scott.emp SET sal = sal + 100 WHERE ename = 'SCOTT'; --成功更新

1 row updated.

--向資料庫中所有使用者分配許可權

SQL> GRANT SELECT ON dept TO PUBLIC;

Grant succeeded.

c.查詢許可權分配情況

資料字典檢視描述

ROLE_SYS_PRIVS 角色擁有的系統許可權

ROLE_TAB_PRIVS 角色擁有的物件許可權

USER_TAB_PRIVS_MADE 查詢授出去的物件許可權(通常是屬主自己查）

USER_TAB_PRIVS_RECD 使用者擁有的物件許可權

USER_COL_PRIVS_MADE 使用者分配出去的列的物件許可權

USER_COL_PRIVS_RECD 使用者擁有的關於列的物件許可權

USER_SYS_PRIVS 使用者擁有的系統許可權

USER_TAB_PRIVS 使用者擁有的物件許可權

USER_ROLE_PRIVS 使用者擁有的角色

--查詢已授予的物件許可權(即某個使用者對哪些表對哪些使用者開放了物件許可權)

SQL> SELECT * FROM user_tab_privs_made; --下面是scott使用者開放的物件許可權

GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

PUBLIC DEPT SCOTT SELECT NO NO

ROBINSON EMP SCOTT SELECT NO NO

--查詢列上開放的物件許可權

SQL> SELECT * FROM user_col_privs_made;

GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA

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

ROBINSON EMP SAL SCOTT UPDATE YES

JOHN EMP MGR ROBINSON UPDATE NO

ROBINSON EMP MGR SCOTT UPDATE YES

JOHN EMP SAL ROBINSON UPDATE NO

--查詢已接受的物件特權(即某個使用者被授予了哪些表上的哪些物件特權)

SQL> SELECT * FROM user_tab_privs_recd;

OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

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

SCOTT EMP SCOTT SELECT NO NO

--查詢使用者已接受列的物件許可權

SQL> SELECT * FROM user_col_privs_recd;

OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA

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

SCOTT EMP MGR SCOTT UPDATE YES

SCOTT EMP SAL SCOTT UPDATE YES

d.收回物件許可權

使用REVOKE 語句收回許可權

使用WITH GRANT OPTION 子句所分配的許可權同樣被收回

REVOKE {privilege [, privilege...]|ALL}

ON object

FROM {user[, user...]|role|PUBLIC}

[CASCADE CONSTRAINTS];

CASCADE CONSTRAINTS 為處理引用完整性時需要

--收回許可權示例

SQL> conn scott/tiger;

Connected.

SQL> REVOKE SELECT ON emp FROM robinson;

Revoke succeeded.

SQL> REVOKE UPDATE(sal,mgr) ON emp FROM robinson; --注意此處的提示revoke的是整個表，而非列

REVOKE UPDATE(sal,mgr) ON emp FROM robinson

*

ERROR at line 1:

ORA-01750: UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column

SQL> REVOKE UPDATE ON emp FROM robinson;

Revoke succeeded.

--使用者robinson的update 許可權被revoke,曾級聯賦予john的許可權也被收回，

--如下提示表、檢視不存在，user_col_privs_recd中無記錄

SQL> CONN john/john;

Connected.

SQL> UPDATE scott.emp SET sal = sal - 100 WHERE ename = 'SCOTT';

UPDATE scott.emp SET sal = sal - 100 WHERE ename = 'SCOTT'

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> SELECT * FROM user_col_privs_recd;

no rows selected

注意：如果取消某個使用者的物件許可權，對於該使用者使用with grant option授予其它使用者相同許可權來說，

將級聯刪除這些使用者許可權

e.其它

檢查DBA許可權的使用者

select * from dba_role_privs where granted_role='DBA';

檢視使用者具有的系統許可權：

SELECT * FROM session_privs;

四、總結

1.使用create user語句建立使用者，alter user語句修改使用者，其語法大致相同

drop user username [CASCADE] 會刪除使用者所擁有的所有物件及資料

2.系統許可權允許使用者在資料庫中執行特定的操作，如執行DDL語句。

with admin option 使得該使用者具有將自身獲得的許可權授予其它使用者的功能

但收回系統許可權時，不會從其它帳戶級聯取消曾被授予的相同許可權

3.物件許可權允許使用者對資料庫物件執行特定的操作，如執行DML語句。

with grant option 使得該使用者具有將自身獲得的物件許可權授予其它使用者的功能

但收回物件許可權時，會從其它帳戶級聯取消曾被授予的相同許可權

4.系統許可權與物件許可權授予時的語法差異為物件許可權使用了ON object_name 子句

5. PUBLIC 為所有的使用者

6. ALL：物件許可權中的所有物件許可權

Oracle 使用者、物件許可權、系統許可權

相關文章