One of the cool things about oracle is that if you have the right privileges you can learn all it’s secrets from the so-called “data dictionary.”
If you’re interested in this then you probably already know what that’s about, so here are what I consider the important data dictionary views for Oracle security:
DBA_SYS_PRIVS â€“ Probably most important, this show system privileges granted to users and roles
DBA_TAB_PRIVS â€“ While the name implies table privileges this actually incorporates view privileges, procedures, packages and functions
DBA_COL_PRIVS â€“ Not always used, but this view tracks grants on columns
DBA_ROLE_PRIVS â€“ This shows who (or what roles) a role has been granted to
These views don’t seem to have changed much from 9i to 10g (or in 8i at that matter) but it is always best to check documentation for your version of Oracle.
Stay tuned for some handy queries to run on these to get quick reports on who can see what.