Home > DBA Scripts, Oracle, Oracle 10g, Oracle 11g, Oracle 9i, SQL > Oracle – Find users with DBA privilege

Oracle – Find users with DBA privilege

If you wish to know which users have been granted the dba role then you need to query the dba_role_privs in the SYS schema.

This role tells you the grantee, granted_role, whether they have admin option granted, and whether the role is their default role:

SQL> desc dba_role_privs
Name         Null?    Type
------------ -------- ------------
GRANTEE               VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION          VARCHAR2(3)
DEFAULT_ROLE          VARCHAR2(3)

To find a list of all users with DBA privilege execute the following code:

SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES

Further Reading

http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_2291.htm

Advertisements
  1. July 21, 2011 at 10:41 AM

    Good blog.

  2. June 19, 2014 at 7:55 PM

    As the admin of this site is working, no hesitation very soon it
    will be famous, due to its feature contents.

  3. Lerene
    August 14, 2017 at 6:37 PM

    DBA is not a “priv”

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: