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;
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!)
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.