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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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'
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gadsden ConsultingIT SpecialistAuthor Commented:

ok, great, thank you !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.