DBMS_SESSION包小議（三）

除了使用ALTER SESSION設定會話的狀態，利用V$SESSION查詢會話狀態，Oracle還提供了PL/SQL介面DBMS_SESSION來查詢和設定會話相關的狀態。

描述SET_ROLE過程和IS_ROLE_ENABLED函式。

DBMS_SESSION包小議（一）：http://yangtingkun.itpub.net/post/468/498365

DBMS_SESSION包小議（二）：http://yangtingkun.itpub.net/post/468/498408

一般來說設定會話級的狀態都是透過ALTER SESSION語句，也有個別的例外，比如角色的設定是透過SET語句實現的。Oracle除了提供SQL的方法外，還提供了PL/SQL的介面，DBMS_SESSION包，將會話狀態的設定和查詢整合在這個包中。

首先利用TEST使用者登陸資料庫，檢查當前的狀態：

SQL> SHOW USER
USER 為 "TEST"
SQL> SELECT * FROM SESSION_ROLES;

未選定行

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
SELECT INSTANCE_NAME FROM V$INSTANCE
                          *
第 1 行出現錯誤:
ORA-00942: 表或檢視不存在

TEST使用者沒有訪問V$INSTANCE檢視的許可權，下面在另外一個會話以SYSDBA登陸，建立一個角色，將訪問V$INSTANCE檢視的許可權授權給這個角色，並將角色授權給TEST使用者：

SQL> SET SQLP 'SQL2> '
SQL2> CONN / AS SYSDBA
已連線。
SQL2> CREATE ROLE R_TEST;

角色已建立。

SQL2> GRANT SELECT ON V_$INSTANCE TO R_TEST;

授權成功。

SQL2> GRANT R_TEST TO TEST;

授權成功。

回到第一個會話，由於TEST使用者沒有重新登陸，因此R_TEST角色並不會馬上啟用：

SQL> DECLARE
  2     V_RESULT BOOLEAN;
  3  BEGIN
  4     V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
  5     IF V_RESULT THEN
  6             DBMS_OUTPUT.PUT_LINE('ENABLED');
  7     ELSIF NOT V_RESULT THEN
  8             DBMS_OUTPUT.PUT_LINE('DISABLED');
  9     ELSE
 10             DBMS_OUTPUT.PUT_LINE('NULL RESULT');
 11     END IF;
 12  END;
 13  /
輸入 role_name 的值:  R_TEST
原值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
新值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('R_TEST');
DISABLED

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_SESSION.SET_ROLE('R_TEST')

PL/SQL 過程已成功完成。

SQL> DECLARE
  2     V_RESULT BOOLEAN;
  3  BEGIN
  4     V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
  5     IF V_RESULT THEN
  6             DBMS_OUTPUT.PUT_LINE('ENABLED');
  7     ELSIF NOT V_RESULT THEN
  8             DBMS_OUTPUT.PUT_LINE('DISABLED');
  9     ELSE
 10             DBMS_OUTPUT.PUT_LINE('NULL RESULT');
 11     END IF;
 12  END;
 13  /
輸入 role_name 的值:  R_TEST
原值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
新值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('R_TEST');
ENABLED

PL/SQL 過程已成功完成。

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
test112

對於沒有啟用的角色，可以透過SET_ROLE過程來啟用。

需要注意的是，IS_ROLE_ENABLED函式無法分辯是一個角色沒有被啟用還是角色根本就不存在或者沒有許可權：

SQL> DECLARE
  2     V_RESULT BOOLEAN;
  3  BEGIN
  4     V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
  5     IF V_RESULT THEN
  6             DBMS_OUTPUT.PUT_LINE('ENABLED');
  7     ELSIF NOT V_RESULT THEN
  8             DBMS_OUTPUT.PUT_LINE('DISABLED');
  9     ELSE
 10             DBMS_OUTPUT.PUT_LINE('NULL RESULT');
 11     END IF;
 12  END;
 13  /
輸入 role_name 的值:  RESOURCE
原值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
新值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('RESOURCE');
DISABLED

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_SESSION.SET_ROLE('RESOURCE')
BEGIN DBMS_SESSION.SET_ROLE('RESOURCE'); END;

*
第 1 行出現錯誤:
ORA-01924: 角色 'RESOURCE' 未被授權或不存在
ORA-06512: 在 "SYS.DBMS_SESSION", line 143
ORA-06512: 在 line 1

注意一點，SET_ROLE過程後面跟的引數並非只是ROLE_NAME那麼簡單，事實上後面可以跟SET ROLE命令後面的所有內容。

看一個複雜一點的例子：

SQL2> CREATE ROLE R_PASSWORD IDENTIFIED BY ABC;  

角色已建立。

SQL2> GRANT R_PASSWORD TO TEST;

授權成功。

建立了一個帶有密碼驗證的角色，並授權給TEST使用者。

下面在TEST使用者的會話中嘗試SET_ROLE：

SQL> EXEC DBMS_SESSION.SET_ROLE('R_PASSWORD')
BEGIN DBMS_SESSION.SET_ROLE('R_PASSWORD'); END;

*
第 1 行出現錯誤:
ORA-01979: 角色 'R_PASSWORD' 的口令缺失或無效
ORA-06512: 在 "SYS.DBMS_SESSION", line 143
ORA-06512: 在 line 1

由於確實口令，直接SET_ROLE是不行的，需要在SET ROLE的時候指定口令：

SQL> EXEC DBMS_SESSION.SET_ROLE('R_PASSWORD IDENTIFIED BY ABC')

PL/SQL 過程已成功完成。

利用SET_ROLE還可以DISABLE角色：

SQL> DECLARE
  2     V_RESULT BOOLEAN;
  3  BEGIN
  4     V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
  5     IF V_RESULT THEN
  6             DBMS_OUTPUT.PUT_LINE('ENABLED');
  7     ELSIF NOT V_RESULT THEN
  8             DBMS_OUTPUT.PUT_LINE('DISABLED');
  9     ELSE
 10             DBMS_OUTPUT.PUT_LINE('NULL RESULT');
 11     END IF;
 12  END;
 13  /
輸入 role_name 的值:  R_PASSWORD
原值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
新值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('R_PASSWORD');
ENABLED

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_SESSION.SET_ROLE('NONE')

PL/SQL 過程已成功完成。

SQL> DECLARE
  2     V_RESULT BOOLEAN;
  3  BEGIN
  4     V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
  5     IF V_RESULT THEN
  6             DBMS_OUTPUT.PUT_LINE('ENABLED');
  7     ELSIF NOT V_RESULT THEN
  8             DBMS_OUTPUT.PUT_LINE('DISABLED');
  9     ELSE
 10             DBMS_OUTPUT.PUT_LINE('NULL RESULT');
 11     END IF;
 12  END;
 13  /
輸入 role_name 的值:  R_PASSWORD
原值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('&ROLE_NAME');
新值    4:      V_RESULT := DBMS_SESSION.IS_ROLE_ENABLED('R_PASSWORD');
DISABLED

PL/SQL 過程已成功完成。