В течении одной сессии узнавать в PL/SQL назначена ли роль

Здравствуйте!

Ситуация: пользователю назначена роль SOME_ROLE.
Вызов dbms_session.is_role_enabled это подтверждает.
В другом сеансе забираем у пользователя роль SOME_ROLE, но dbms_session.is_role_enabled показывает, что роль по-прежнему есть. Только если перелогинится, то получем правильный результат.

Как узнать обладает ли пользователь ролью в данный момент(а не на момент начала сессии) не строя иерархический запрос к dba_role_privs?

Oracle9i SQL Reference REVOKE

If you revoke a role from a user, then Oracle makes the role unavailable to the user. If the role is currently enabled for the user, the user can continue to exercise the privileges in the role's privilege domain as long as it remains enabled. However, the user cannot subsequently enable the role.

tst> create role r1;

Role created.

tst> create table t1 as select * from dual;

Table created.

tst> grant select on t1 to r1;

Grant succeeded.

tst> grant r1 to u1;

Grant succeeded.

ts2> connect u1/u1@tst
Connected.
ts2> select * from system.t1;

D
-
X

ts2> select * from session_roles;

ROLE
------------------------------
PLUSTRACE
CONNECT
RESOURCE
R1

tst> revoke r1 from u1;

Revoke succeeded.

ts2> select * from system.t1;

D
-
X

ts2> select * from session_roles;

ROLE
------------------------------
PLUSTRACE
CONNECT
RESOURCE
R1

ts2> set role all;

Role set.

ts2> select * from session_roles;

ROLE
------------------------------
PLUSTRACE
CONNECT
RESOURCE

ts2> select * from system.t1;
select * from system.t1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Вячеслав Любомудров

set role all;

Если не написать эту строчку, то список ролей не обновится.
Поскольку мне надо их хранимой процедуры узнать обладает ли пользователь ролью, то возникает проблема: если вызываю execute immediate 'set role all' или dbms_session.set_role('ALL'), то при вызове процедуры получаю сообщеие о том, что нельзя делать set role в хранимой процедуре.

Ну дык и не выполняй в хранимой процедуре
Выполняй в анонимном блоке ПЕРЕД вызовом своей ХП

+ xp authid current_user is

I00N

Вячеслав Любомудров set role all; Если не написать эту строчку, то список ролей не обновится. Поскольку мне надо их хранимой процедуры узнать обладает ли пользователь ролью

Я как-то вообще не очень понимаю, а зачем Вам в коде передергивать роли? У Вас цель - понять, доступна роль сейчас или нет. Так Вы это понимаете с помощью dbms_session.is_role_enable. Она действительно доступна, пока Вы не отконнектитесь. В чем трабел-то?

Jannny

Она действительно доступна, пока Вы не отконнектитесь. В чем трабел-то?

Роль могут забрать у пользователя в момент, когда его сессия активна. В этом случае у пользователя в этот же момент исчезнут привелегии, предоставляемые ему ролью(все устраивает), но dbms_session.is_role_enabled показывает, что роль активна(этот не устраивает).

Вячеслав Любомудров

Выполняй в анонимном блоке ПЕРЕД вызовом своей ХП

Это даст правильный результат, но в моем случае это не применимо.

dba123
Попробую

I00N

Роль могут забрать у пользователя в момент, когда его сессия активна. В этом случае у пользователя в этот же момент исчезнут привелегии, предоставляемые ему ролью

Ты совсем слепой и тупой? RTFM!

Jannny

I00N Вячеслав Любомудров set role all; Если не написать эту строчку, то список ролей не обновится. Поскольку мне надо их хранимой процедуры узнать обладает ли пользователь ролью Я как-то вообще не очень понимаю, а зачем Вам в коде передергивать роли? У Вас цель - понять, доступна роль сейчас или нет. Так Вы это понимаете с помощью dbms_session.is_role_enable. Она действительно доступна, пока Вы не отконнектитесь. В чем трабел-то?

скорее всего роли не дефолтные, паролированные и устанавливаются из хп
м.б. даже врапленной :)

I00N

Здравствуйте! Ситуация: пользователю назначена роль SOME_ROLE. Вызов dbms_session.is_role_enabled это подтверждает. В другом сеансе забираем у пользователя роль SOME_ROLE, но dbms_session.is_role_enabled показывает, что роль по-прежнему есть. Только если перелогинится, то получем правильный результат. Как узнать обладает ли пользователь ролью в данный момент(а не на момент начала сессии) не строя иерархический запрос к dba_role_privs?

First of all, dbms_session.is_role_enabled HE подтверждает пользователю назначена (I assume by назначена you mean GRANTED) роль SOME_ROLE. SOME_ROLE can be, for example, назначена пользователю but not as default role and therefore dbms_session.is_role_enabled will result in FALSE.
Anyway, if I understood you correctly, dbms_session.is_role_enabled('SOME_ROLE') returns TRUE and you want to find out if SOME_ROLE is still granted to user (directly or indirectly). Then:

Session I

SQL> CREATE OR REPLACE
  2    FUNCTION is_role_still_granted(
  3                                   p_role VARCHAR2
  4                                  )
  5      RETURN BOOLEAN
  6      AUTHID CURRENT_USER
  7      IS
  8          e_role_not_granted EXCEPTION;
  9          PRAGMA EXCEPTION_INIT(e_role_not_granted,-1924);
 10      BEGIN
 11          IF DBMS_SESSION.IS_ROLE_ENABLED(p_role)
 12            THEN
 13              BEGIN
 14                  DBMS_SESSION.SET_ROLE(p_role);
 15                  RETURN TRUE;
 16                EXCEPTION
 17                  WHEN e_role_not_granted
 18                    THEN
 19                      RETURN FALSE;
 20              END;
 21            ELSE
 22              RETURN NULL;
 23          END IF;
 24  END;
 25  /

Function created.

SQL> GRANT EXECUTE ON is_role_still_granted TO u1
  2  /

Grant succeeded.

SQL> CREATE ROLE r1
  2  /

Role created.

SQL> GRANT r1 TO u1
  2  /

Grant succeeded.

SQL>

Session II

SQL> connect u1/u1
Connected.
SQL> SELECT  *
  2    FROM  SESSION_ROLES
  3  /

ROLE
------------------------------
R1

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2      IF DBMS_SESSION.IS_ROLE_ENABLED('R1')
  3        THEN
  4          DBMS_OUTPUT.PUT_LINE('Yes');
  5        ELSE
  6          DBMS_OUTPUT.PUT_LINE('No');
  7      END IF;
  8  END;
  9  /
Yes

PL/SQL procedure successfully completed.

SQL> BEGIN
  2      IF scott.is_role_still_granted('R1')
  3        THEN
  4          DBMS_OUTPUT.PUT_LINE('Yes');
  5        ELSE
  6          DBMS_OUTPUT.PUT_LINE('No');
  7      END IF;
  8  END;
  9  /
Yes

PL/SQL procedure successfully completed.

SQL>

Session I

SQL> REVOKE r1 FROM u1
  2  /

Revoke succeeded.

Session II

SQL> SELECT  *
  2    FROM  SESSION_ROLES
  3  /

ROLE
------------------------------
R1

SQL> BEGIN
  2      IF DBMS_SESSION.IS_ROLE_ENABLED('R1')
  3        THEN
  4          DBMS_OUTPUT.PUT_LINE('Yes');
  5        ELSE
  6          DBMS_OUTPUT.PUT_LINE('No');
  7      END IF;
  8  END;
  9  /
Yes

PL/SQL procedure successfully completed.

SQL> BEGIN
  2      IF scott.is_role_still_granted('R1')
  3        THEN
  4          DBMS_OUTPUT.PUT_LINE('Yes');
  5        ELSE
  6          DBMS_OUTPUT.PUT_LINE('No');
  7      END IF;
  8  END;
  9  /
No

PL/SQL procedure successfully completed.

SQL> 

SY.