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')
      [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.
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?
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)
slightwv (䄆 Netminder) 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;
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 SpecialistAuthor Commented:
slightwv, the user has the role, so I'm looking more for what sdstuber is saying


- clarification is correct, thank you.

- dba_role_privs is my alternate testing.
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)
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 SpecialistAuthor 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.
Gadsden ConsultingIT SpecialistAuthor Commented:

ok, great, thank you !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.