Home » SQL & PL/SQL » SQL & PL/SQL » how to allow a user to view roles on a DB (Oracle 11.2.0.3 Enterprise)
how to allow a user to view roles on a DB [message #603342] Fri, 13 December 2013 10:05 Go to next message
rcct
Messages: 36
Registered: May 2013
Location: Ottawa, ON
Member
Could anyone advise how to give a user permissions to view all the user-defined roles in a DB? We have a servicedesk person whose job is to periodically review all the roles in a DB. I could grant him select_any_catalog, but that seems too excessive. Is there a way to view roles only? Thanks in advance.
Re: how to allow a user to view roles on a DB [message #603344 is a reply to message #603342] Fri, 13 December 2013 10:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
GRANT SELECT ON DBA_ROLES TO SERVICEDESK_PERSON;
Re: how to allow a user to view roles on a DB [message #603345 is a reply to message #603342] Fri, 13 December 2013 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My prefer way is to create a view (or a pipelined function) to retrieve information from the database to people that are not DBA.
So it should be better to create a view upon DBA_ROLES to return ONLY the roles that are relevant to this person duty.
Does he need to know who has DBA roles? No, you said it is user-defined roles (and then why user can create roles, I hope you mean application defined roles)?

In addition, it also depends on what you mean by "view". Does he need to see who have the role? Does he need to know what privileges are granted to the role?...

Once again, a specific view or function is the best way to answer this kind of request.

Re: how to allow a user to view roles on a DB [message #603674 is a reply to message #603342] Wed, 18 December 2013 06:09 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

DBA_ROLES shows all roles. I couldn't find out any criteria to filter user defined roles only.
Re: how to allow a user to view roles on a DB [message #603679 is a reply to message #603674] Wed, 18 December 2013 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

NOT IN (...)

Re: how to allow a user to view roles on a DB [message #603688 is a reply to message #603679] Wed, 18 December 2013 08:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And in 12C:

SELECT * FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N';


SY.
Re: how to allow a user to view roles on a DB [message #603698 is a reply to message #603674] Wed, 18 December 2013 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
mokarem wrote on Wed, 18 December 2013 04:09
DBA_ROLES shows all roles. I couldn't find out any criteria to filter user defined roles only.


I do not understand the term "user defined role".
what differentiates a "user defined role" from non-user defined role?
Re: how to allow a user to view roles on a DB [message #603701 is a reply to message #603698] Wed, 18 December 2013 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think we will never have any answer to our question as OP never feedback in his topics.

Re: how to allow a user to view roles on a DB [message #603703 is a reply to message #603701] Wed, 18 December 2013 09:54 Go to previous messageGo to next message
rcct
Messages: 36
Registered: May 2013
Location: Ottawa, ON
Member
By user-defined role, I mean application roles, defined by the DBA, or developer. Non-user defined roles would be roles such as imp_full_database, hs_admin_role, etc. But there is no way to destinguish between the two in dba_roles. So what is the NOT IN (...) criteria? Should we put the list of pre-defined roles in the not in list? That would give a list of user-defined roles.

[Updated on: Wed, 18 December 2013 10:04]

Report message to a moderator

Re: how to allow a user to view roles on a DB [message #603704 is a reply to message #603703] Wed, 18 December 2013 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Could you feedback to your other topics and this one in order we know if we helped and how you solved your issue in the end and so at your turn help people that will read your topics.
In addition, if we don't know the result of our time trying to help you and have the feeling we talk to a black hole it is most likely you will have no more help.


Re: how to allow a user to view roles on a DB [message #603707 is a reply to message #603704] Wed, 18 December 2013 10:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But there is no way to destinguish between the two in dba_roles.

Either create a table that contains all the roles you care about or create a table of all roles that should be ignored;
because it is only you who wish to separate roles into two distinct groups.
Re: how to allow a user to view roles on a DB [message #603712 is a reply to message #603707] Wed, 18 December 2013 10:50 Go to previous messageGo to next message
rcct
Messages: 36
Registered: May 2013
Location: Ottawa, ON
Member
Thanks Solomon, guess Oracle figured out that some people might want to distinguish between the two types and added the extra column in 12c Smile

And thanks all for your replies. My apologies Michel, I have updated my other post.
Re: how to allow a user to view roles on a DB [message #603717 is a reply to message #603712] Wed, 18 December 2013 13:45 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
rcct wrote on Wed, 18 December 2013 11:50
Thanks Solomon, guess Oracle figured out that some people might want to distinguish between the two types and added the extra column in 12c Smile


In case you are curious how Oracle does it (from my blog at work):

In Oracle 12C we can identify if object is Oracle object (created, and is maintained, by Oracle-supplied scripts) or is user object. Oracle 12c added column ORACLE_MAINTAINED to several data dictionary views: XXX_OBJECTS, XXX_OBJECTS_AE, XXX_USERS, XXX_ROLES (where XXX is USER, ALL, DBA or CDB), DBA_INVALID_OBJECTS, CDB_INVALID_OBJECTS and ALL_PROBE_OBJECTS. All these views are based on dictionary tables sys.user$ and/or sys.obj$. So how does Oracle know what user/role/object is Oracle maintained? I decided to do some digging. It appears Oracle took straight-forward approach to implement this. I noticed new files in $ORACLE_HOME/RDBMS/ADMIN directory: upuserxt.lst - list of oracle supplied users and roles and upobjxt.lst - list of oracle supplied objects (quite hefty - over 7MB). Then script catupcox.sql creates directory upg_xt_dir and two external tables: sys.userxt for upuserxt.lst and sys.objxt for upobjxt.lst. And script catuposb.sql calls script catupcox.sql, reads these external tables и and sets corresponding bit by updating data dictionary tables sys.user$ and sys.obj$. At the end script catuposb.sql drops directory upg_xt_dir and both external tables sys.userxt and sys.objxt.

SY.
Previous Topic: insert into
Next Topic: Oracle Aggregation Problem in join between tables
Goto Forum:
  


Current Time: Thu Mar 28 18:32:17 CDT 2024