We help IT Professionals succeed at work.

how to use dbms_session.is_role_enabled in my own session for another user

I have this line of code that I need to test for other users:

   IF dbms_session.is_role_enabled('REGISTRAR')
   THEN
      [such and such]

This works when running AS the user, but I need to test this FOR a user in my own session. is there a way to do that ? Like if there was a parameter for user.

for testing purposes, I can do this another way without using dbms_session but would prefer to be as close to the actual logic as possible.
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
To test it for another user:
You need to grant that role to the user.

You can then manually disabling the role for the other user:
set role none;
Most Valuable Expert 2011
Top Expert 2012
Commented:
To clarify your question.


You log in as user X.

I log in as user Y.
slightwv also logs in as user Y.

Role R is assigned to user Y.

Your question is:  Can you, as user X determine if my session of Y has R enabled or not, and does slighwv's session of Y have R enabled or not?

You are NOT interested in determing if R is a default role for Y, because that does not tell you if any particular session has the role enabled or not.


So - you wouldn't just need to specify a user, you would also need to specify a session.  Correct?

If not, please reexplain.
Most Valuable Expert 2011
Top Expert 2012
Commented:
and, just in case you are only interested in the default flag, just query dba_role_privs

SELECT default_role
  FROM dba_role_privs
 WHERE grantee = 'THE_OTHER_USER' AND granted_role = 'THE_ROLE'
Gadsden ConsultingIT Specialist

Author

Commented:
slightwv, the user has the role, so I'm looking more for what sdstuber is saying

sdstuber,

- clarification is correct, thank you.

- dba_role_privs is my alternate testing.
Most Valuable Expert 2011
Top Expert 2012

Commented:
unfortunately  dba_role_privs doesn't actually give you what you are looking for.

It will tell you if it's possible to enable (you can't enable role that isn't granted) but not if it actually is or is not enabled.

As far as I know there is no view that will tell you the active roles for other sessions.
Even the underlying x$ view behind  SESSION_ROLES still only applies to your own session.

The next best option I can think of is to create a DDL trigger to capture the SET ROLE command and log it to a table that you can query and join to v$session.

SInce SID values can be reused, you'll probably need to key your logging table by INST_ID, SID, LOGON_TIME  to ensure you have unique entries. (only need INST_ID if RAC)
Most Valuable Expert 2011
Top Expert 2012

Commented:
possibly better than using a trigger would be to use application roles
that is, roles with the "IDENTIFIED USING your_package"  option.

it's the same idea, but it consolidates the enabling of the role with the logging.

the downside to this is your users and applications can't call "SET ROLE" directly, they must use your authorizing package.  the trigger method can be installed without requiring code changes to legacy systems.
Gadsden ConsultingIT Specialist

Author

Commented:
sdstuber, thanks a lot for the tips. It's a lot of hoop-jumping (but good to know), and my Plan B will suffice.

So I just am looking at dba_role_privs and it's giving me what I want.

But good tips that I'll tuck away.
Most Valuable Expert 2011
Top Expert 2012
Commented:
one more option  but maybe not viable -

if you are interested in the role status while the user is executing certain code,  you could modify that code to use dbms_application_info to write the status of the role to v$session client_info as it enters that code block and (optionally) clear it when it exits.

or, instead of dbms_appliction_info use any other logging method, write to a table, file, dbms_pipe, etc.
the dbms_application_info method is nice in that it's fast and nontransactional (i.e. doesn't require a commit)
Gadsden ConsultingIT Specialist

Author

Commented:
sdstuber,

ok, great, thank you !