'Brief' description of some Oracle tables/views that can help you.
DICTIONARY - lists all data dictionary tables, views, synonyms accessible to user, with a brief description.
ALL_CATALOG - all tables, views, synonyms, sequences accessible to the user
The SYS user owns views that allow easier querying of the data dictionary. There are 3 classes of views -
USER_xxxx - objects owned by the user can be accessed. Views with this prefix allow the user to display information about tables created by the user and privileges ganted by the user.
ALL_xxxx - users can access objects to which they have been granted access, in addition to objects they own
DBA_xxxx - for use by users with the DBA privilege - can access any object in the database
Views that allow you to query user/role information. You may not have access to tables that start with DBA.
DBA_USERS - all users of the database
ALL_USERS - information about all users of the database
DBA_ROLES - all roles which exist in the database
DBA_ROLE_PRIVS - roles granted to users and roles
USER_ROLE_PRIVS - roles granted to current user
SESSION_ROLES - roles which the user currently has enabled
SESSION_PRIVS - privileges which the user currently has set
DBA_TAB_PRIVS - all grants on objects in the database
USER_TAB_PRIVS - grants on objects for which the user is owner, grantor or grantee
ALL_TAB_PRIVS - grants on objects for which tthe user is the grantor, grantee, owner, or an enabled role or PUBLIC is the guarantee
DBA_SYS_PRIVS - system privileges granted to users and roles
USER_SYS_PRIVS - system privileges granted to current user
I've also been granted access to a view called v$session this is useful to find out information about someone who has logged in -
select osuser, machine, username
Gives me the operating system username, machine they are using and their Oracle username.
select * from user_role_privs;
Will give role information for the current logged in user.