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_enabl
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.
IF dbms_session.is_role_enabl
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
sdstuber,
- 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)
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sdstuber,
ok, great, thank you !
ok, great, thank you !
You need to grant that role to the user.
You can then manually disabling the role for the other user:
set role none;