SQL 使用者帳號授權

1. 新增帳號: CREATE USER 使用者名稱  IDENTIFIED BY 密碼;
2. 權限設定: GRANT 權限 ON 資料庫物件 TO 使用者名稱;
                      GRANT 角色 TO 使用者名稱
3. 取消權限: REVOKE 權限 ON 資料庫物件 FROM 使用者名稱;
4. 新增角色: CREATE ROLE 角色名稱 [ NOT IDENTIFIED | IDENTIFIED { BY 密碼 | USING [ 鋼要 .] PACKAGE | EXTERNALLY | GLOBALLY } ] ;
5. 取消角色: DROP ROLE 角色名稱
6. 更改密碼: ALTER USER  使用者名稱 IDENTIFIED BY 密碼;
7. 強制變更密碼: ALTER USER 使用者名稱 PASSWORD EXPIRE;
8. 鎖住現有的使用者: ALTER USER 使用者名稱 ACCOUNT LOCK;9. 解鎖現有的使用者: ALTER USER 使用者名稱 ACCOUNT UNLOCK;10. 刪除現有的使用者: DROP USER 使用者名稱;

GRANT ALTER   ON  TABLE_NAME TO APPS WITH GRANT OPTION;
GRANT DELETE ON  TABLE_NAME TO APPS WITH GRANT OPTION;
GRANT INDEX   ON  TABLE_NAME TO APPS WITH GRANT OPTION;
GRANT INSERT ON  TABLE_NAME TO APPS WITH GRANT OPTION;
GRANT SELECT ON  TABLE_NAME TO APPS WITH GRANT OPTION;
GRANT UPDATE ON  TABLE_NAME TO APPS WITH GRANT OPTION;
GRANT REFERENCES ON  TABLE_NAME TO APPS WITH GRANT OPTION;

1. 新增帳號:

SQL> CREATE USER elvismeng  IDENTIFIED BY elvismeng;

如以此帳號立即登入, 系統會告知下列錯誤:C:\Documents and Settings\elvismeng>sqlplus elvismeng/elvismeng
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 3月 26 10:23:04 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-01045: 使用者 ELVISMENG 缺乏 CREATE SESSION 權限; 登入被拒
系統告知使用者elvismeng並沒有CREATE SESSION的權限 (Privilege), 所以無法登入.

2. 建立權限
我們可透過IE瀏覽器來設定使用者elvismeng的權限:

我們也可以Command Line的方式設定權限:

SQL> GRANT CREATE SESSION TO elvismeng;
順利授權.
接下來, 我們驗證帳號elvismeng是否有權限登入到資料庫:

SQL> connect elvismeng/elvismeng;
已連線.
3 取消權限

使用者權限亦可依需求取消:

SQL> REVOKE CREATE SESSION FROM elvismeng;
順利撤銷.
4. 角色 (Role)
建立角色:

SQL> CREATE ROLE TEST_ROLE;
已建立角色.

SQL> GRANT CREATE SESSION TO TEST_ROLE;
順利授權.

SQL> CREATE USER TEST IDENTIFIED BY TEST;
已建立使用者.

SQL> GRANT TEST_ROLE TO TEST;
順利授權.

取消角色:
SQL> DROP ROLE TEST_ROLE;已刪除角色.

5. 更改密碼
SQL> connect elvismeng/elvismeng
已連線.

SQL> ALTER USER elvismeng IDENTIFIED BY elvismeng01;已更改使用者.

SQL> connect elvismeng/elvismeng
ERROR:
ORA-01017: 使用者名稱/密碼無效; 無法登入警告: 您已不再與 ORACLE 相連

SQL> connect elvismeng/elvismeng01
已連線. 

6. 強制變更密碼
C:\Documents and Settings\elvismeng>SQLPLUS

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 3月 26 16:09:27 2008Copyright (c) 1982, 2005, Oracle.  All rights reserved.

輸入使用者名稱:  SYSTEM
輸入密碼:

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ALTER USER elvismeng PASSWORD EXPIRE已更改使用者.

SQL> GRANT CREATE SESSION TO elvismeng;順利授權.

SQL> DISCONNECT
已切斷與 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 的連線
SQL> CONNECT elvismeng/elvismeng01
ERROR:
ORA-28001: 密碼已經屆滿

為 elvismeng 變更密碼
新密碼:
重新輸入新密碼:
變更密碼
已連線.
SQL>

7. 鎖住 / 解除現有的使用者

SQL> ALTER USER elvismeng ACCOUNT LOCK;已更改使用者.

SQL> GRANT CREATE SESSION TO elvismeng;順利授權.

SQL> DISCONNECT
已切斷與 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 的連線
SQL> CONNECT elvismeng/elvismeng
ERROR:
ORA-28000: 帳戶已被鎖定

SQL> DISCONNECT

SQL> CONNECT SYSTEM/SYSTEM
已連線.

SQL> ALTER USER elvismeng ACCOUNT UNLOCK;
已更改使用者.

SQL> CONNECT elvismeng/elvismeng
已連線.
SQL>

8. 刪除現有的使用者

SQL> DROP USER elvismeng;
已刪除使用者.

SQL> connect elvismeng/elvismeng;
ERROR:
ORA-01017: 使用者名稱/密碼無效; 無法登入
警告: 您已不再與 ORACLE 相連

---

Oracle DB 讓 B Owner 可授權 A Owner 的 Object 給 C User

範例 1: A Owner Table 授權給 B Owner, 而 B Owner 要將此 Table 授權給 C Owner.

範例 2: B Owner 建立一個 View, 而此 View 含有 A Owner Table, 要將此 View 授權給 C Owner 使用, 如下圖 :


以上兩個範例, 都是相同的意思,

就是 B Owner 要將 A Owner 授權給它的物件, 轉授權給另一個 Owner C.

以範例 2 來說, 解答步驟, 參考如下 :

步驟 1)

將 A Owner 的 Table, 其 Select 權限包含 Grant Option 授權給 B Owner, 語法如下 :

GRANT SELECT ON A.tableName TO B WITH GRANT OPTION;

p.s. 一定要加上 WITH GRANT OPTION 此保留字, 否則下一個步驟, B Owner 將無法授權 View 給 C Owner, 會有 ORA-01720 錯誤。

步驟 2)

將 B Owner 的 View, 其 Select 權限再授權給 C Owner, 語法如下：

GRANT SELECT ON B.viewName TO C;