除了使用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 过程已成功完成。