Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Consolidated list of Oracle user rights

    We're puzzling our way through an Oracle migration, and we're to the point where we'd like to start determining the roles of developers and analysts in the database.

    Is there a consolidated list of 'user rights', for lack of a better term, like CREATE SESSION, CREATE TABLE, etc?

    It would help to see these all in one location.

    -Thanks,
    Chuck

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Chuck,

    I assume what you are looking for is a quick way to assign the most common privs to users... please correct me if I'm wrong.

    Oracle's way of helping the administrator do this is by way of assigning privileges to roles, then granting the roles to individual users. Since you can name the roles whatever you like, it is easier to create a role in the database similar to the "role" the user performs, e.g., the dba.

    Some example roles are already set up:

    CONNECT - has CREATE SESSION and ALTER SESSION privs, but also has CREATE TABLE, etc. (for your basic user).

    JAVASYSPRIV - allows the user to execute java packages.

    DBA - has all the dba necessary privs like granting privs, executing procs, altering users, etc, as well as basic stuff like CREATE SESSION.

    To get a list of all the privs granted a particular role (like CONNECT):
    SELECT * FROM dba_sys_privs WHERE grantee='CONNECT';

    To allow a user all the privs of a role:
    GRANT dba TO joe_user;

    Hope this helps!
    JoeB

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074

    For a complete list ...

    Would the following supply a complete list of priviledges:

    SELECT * FROM dba_sys_privs;

    Thanks,
    Chuck

  4. #4
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    No, the DBA_SYS_PRIVS view shows all system privileges that have been granted to users or roles.

    But your question has led me down many a dead-end... I've not been able to find a database "table" that lists all the system privileges out there. However, I did find a "table" of system privileges for Oracle 9i. Try this link:
    http://download-west.oracle.com/docs...2a.htm#2075179

    (If anyone else finds the view/table that has all the privs listed, please let me know for my own gratification. Thanks!)

    JoeB
    Last edited by joebednarz; 02-06-04 at 17:31.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074

    Incomplete list

    I keep coming across inconsistent lists of system privileges. For example, there'll be a CREATE ANY INDEX but no CREATE INDEX.

    If this information is so difficult to uncover, how do you go about providing the appropriate rights to your users and developers?

    -Chuck

  6. #6
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Actually, the CREATE INDEX privilege is the only one that is a little weird. The other privs usually have a single priv to relate to stuff in your own schema, like CREATE TRIGGER, and then a corresponding priv, CREATE ANY TRIGGER, that applies to any schema anywhere.

    The CREATE INDEX, since it is related to and depends upon the CREATE TABLE priv, if you are granted the CREATE ANY TABLE priv, you automagically get a pseudo CREATE ANY INDEX priv.

    As to which rights to give to users and developers, I have always felt that privs (i.e. security) should be most restrictive first, and simple. So, my suggestion... grant CONNECT to your users, and RESOURCE to your developers. CONNECT gives basic privs to get connected, while RESOURCE adds in the privs to create objects (tables, indexes, triggers). From there, add privs as necessary.

    JoeB

Posting Permissions

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