Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Location
    US
    Posts
    29

    Unanswered: How to get a list of all users and their roles?

    Hi Guys

    I need to get a list of all users on our prod databases as well as what roles they may have allocated to them - in particular those who have been granted the DBA role.

    Initially i tried
    SELECT * FROM user_role_privs;
    but that only shows roles granted to the current user

    I'm looking for something similar, but for all users. I've looked around online quite a bit, and eventually came up with the following:

    =============================
    How to get a list of roles granted to a user?

    You need to query the RDB$PRIVILEGES system table. The following example shows all users and roles granted to them:

    SELECT u.RDB$USER, u.RDB$RELATION_NAME
    FROM RDB$USER_PRIVILEGES u
    WHERE u.RDB$PRIVILEGE = 'M'
    ORDER BY 1, 2
    =============================

    Unfortunately when i query this table - as in the above query - i get the folowing error:

    ORA-00942: table or view does not exist

    I can't seem to find this table in any of the ORACLE doco either, so some assistance would be very much appreciated!

    cheers

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT * FROM ALL_role_privs;
    or
    SELECT * FROM DBA_role_privs;

    http://download.oracle.com/docs/cd/B...htm#sthref1193
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Location
    US
    Posts
    29
    Quote Originally Posted by anacedent View Post
    SELECT * FROM ALL_role_privs;
    or
    SELECT * FROM DBA_role_privs;

    The Data Dictionary

    Thanks very much for that.

    I could not however get the following to work:
    SELECT * FROM ALL_role_privs;
    It also returned "table or view does not exist"

    However, I've used the other table DBA_role_privs to good effect.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I could not however get the following to work:
    >SELECT * FROM ALL_role_privs;
    >It also returned "table or view does not exist"
    typically Oracle supports
    USER_SOMETHING
    ALL_SOMETHING
    DBA_SOMETHING

    In the case of *_ROLE_PRIVS, ALL_ROLE_PRIVS does not exist.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •