Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Distinguish between Oracle Predefined Users/Roles and customised ones
Ja S
post Apr 18 2012, 10:41 PM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 18-April 12
Member No.: 47,101



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.
Go to the top of the page
 
+Quote Post
aussie_dba
post Apr 19 2012, 07:22 AM
Post #2


Advanced Member
***

Group: Members
Posts: 611
Joined: 28-August 04
Member No.: 495



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
Go to the top of the page
 
+Quote Post
Ja S
post Apr 19 2012, 04:38 PM
Post #3


Newbie
*

Group: Members
Posts: 4
Joined: 18-April 12
Member No.: 47,101



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.
Go to the top of the page
 
+Quote Post
burleson
post Apr 20 2012, 06:13 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,330
Joined: 26-January 04
Member No.: 13



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.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Ja S
post Apr 22 2012, 05:21 PM
Post #5


Newbie
*

Group: Members
Posts: 4
Joined: 18-April 12
Member No.: 47,101



QUOTE (burleson @ Apr 20 2012, 07:13 AM) *
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.
Go to the top of the page
 
+Quote Post
Ja S
post May 1 2012, 05:18 PM
Post #6


Newbie
*

Group: Members
Posts: 4
Joined: 18-April 12
Member No.: 47,101



Any further comments/suggestions/clarifications, please?

Many thanks.
Go to the top of the page
 
+Quote Post
burleson
post May 2 2012, 07:49 AM
Post #7


Advanced Member
***

Group: Members
Posts: 11,330
Joined: 26-January 04
Member No.: 13



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]


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 29th July 2014 - 01:48 PM