Distinguish between Oracle Predefined Users/Roles and customised ones Options

[Ja S]

I am looking for a query which can select all existing Oracle predefined users and roles from db.

I need it because one of my other scripts needs to know a given user name or role is a customised one or a system one.

It looks like sys.user$ does not have any column that can be used to distinguish predefined users/roles.

I could find all the list of Oracle predefined users and roles by googling and hardcoded them in a query. I can also collect all the customised users and roles as well. However, I prefer a query which can automatically reports that and minimise the maintenance cost as new names and roles, either Oracle predefined or customised, may come out at anytime.

Thanks in advance.

[aussie_dba]

Hi,

Good question!

http://www.dba-oracle.com/t_find_system_roles_script.htm

"a customised one or a system one."

All system roles are owned by the SYS user:

CODE

select distinct role name from dba_role_tab_privs where owner = 'SYS';

I know of no flag for them, but check the source in catproc.sql.



"I prefer a query which can automatically reports that and minimise the maintenance cost as new names and roles, either Oracle predefined or customised, may come out at anytime."

If you don't wamt to write your own scripts, I would get rhe Oracle script collection:

http://www.dba-oracle.com/oracle_scripts.htm

[Ja S]

All system roles are owned by the SYS user:

CODE

select distinct role name from dba_role_tab_privs where owner = 'SYS';

Thanks for your reply.

But which version of Oracle do you refer to? There is no "owner" column in dba_roles, nor sys.user$ since Oracle 10.

[burleson]

Hi,

It's DBA_ROLE_TAB_PRIVS:


http://www.dba-oracle.com/concepts/use_of_roles.htm

CODE

select distinct role name from dba_role_tab_privs where owner = 'SYS';

The script download has complete details but this one should work by itself.

[Ja S]

Hi,

It's DBA_ROLE_TAB_PRIVS:

I am afraid I have to admit I have neither seen this view in my life, nor realised it in my Oracle 10 and 11 db as I am not an experienced dba at all. I am assuming it might be the one created in an old version of Oracle, or a customised one by some experts, or has been missed in my Oracle 10/11 installation. Would you mind provideing more details?

There is an 'owner" column in role_tab_privs and user_tab_privs. Isn't it the owner of the objects not the owner of the users or roles?

Thanks.

[Ja S]

Any further comments/suggestions/clarifications, please?

Many thanks.

[burleson]

Hi,

>> I have neither seen this view in my life

Lots of notes on it:

https://www.google.com/search?sourceid=ie7&...024&bih=527


It must hev gome deprecated . . . Sorry.

Now, roles are owned internally they are not owned by individual users.
Try thos:

CODE

select
   object_name
from
   dba_objects
where
   object_type = 'VIEW'
and
   object_name like '%ROLE%';
;

[b]

[/b]