how to allow a user to view roles on a DB [message #603342] |
Fri, 13 December 2013 10:05 |
|
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 #603703 is a reply to message #603701] |
Wed, 18 December 2013 09:54 |
|
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 #603717 is a reply to message #603712] |
Wed, 18 December 2013 13:45 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
rcct wrote on Wed, 18 December 2013 11:50Thanks Solomon, guess Oracle figured out that some people might want to distinguish between the two types and added the extra column in 12c
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.
|
|
|