"Ten percent of nothing is... lemme do the math, here ...nothing, and then nothing.. carry the nothing." - Jayne Cobb, Firefly Pilot
From time to time, I am tasked with presenting a list of all users and their associated permissions. This is usually from some internal or external auditor who wants to make sure what access levels assorted users have in the database
So, while nothing special, the following bits of code would get me the information I needed. I guess we'll start with Oracle.
Authentication into Oracle (for the most part) is done via a username and a password. There are a few exceptions but not going to cover that here.
In the event that you want to be able to recreate the users in your database the following SQL script will help:
set linesize 132
set pagesize 100
set heading off
select 'create user '||username||' identified by values '''||password||
''' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||';'
from dba_users
order by username;
Placing the keyword "values" after "identified by" is telling Oracle to create a user with the same encrypted password as the source user. This is an effective way to recreate users on a different system without actually knowing the current password of the user. If you want to assign the new user's password, change the script to something like this:select 'create user '||username||' identified by newpass '||
'default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||';'
from dba_users
order by username;
Now that we have the users created, they'll need permissions.
There are three types of Privileges in Oracle: Role Privileges, Table Privileges and System Privileges.
A role in Oracle is a group of privileges that can be assigned to users or to other roles. They greatly ease security administration in my opinion because every time a new user is set up, you don't need to grant him a whole swag of individual permissions, you could just grant the WAREHOUSE_USER role or the POWER_USER role and a predefined set of privileges is assigned to that user.
Recreating the roles in your database can be done via a select from DBA_ROLES along the line of:
select 'create role '||role||';' from dba_roles order by role;
This over simplifies is a little since there are sometimes passwords associated with the role, but we'll keep it simple for now.
If you were to run this script there would be a lot of errors indicating that the role already exists since the roles that come pre-installed with Oracle are also included in the output from this statement. It would be up to the DBA running this script to really determine what was missing and cull down the output accordingly.
So now that we have users and we have roles, the first thing we should look at is what roles a user has access to. This is defined in DBA_ROLE_PRIVS.
We generate the statements once again by selecting from this table.
select 'grant '||granted_role||' to '||grantee||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
order by grantee, granted_role;
Again, there's a thing or two missing here, but this should cover the lion's share/
Users (and roles) can also be granted levels of access to user or system objects (tables, stored procedures and packages etc). The following query gets us that information, stored in DBA_TAB_PRIVS:
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||
grantee||
decode(GRANTABLE, 'YES', ' WITH GRANT OPTION', ' ') ||
decode(HIERARCHY, 'YES', ' WITH HIERARCHY OPTION', ' ') || ';'
from dba_tab_privs
order by grantee, owner, table_name;
The output from this can be long. Be warned. Again, the explanation of these options will not be covered here, the details of that can be found in the Oracle documentation at http://tahiti.oracle.com.
Finally, the System Privileges (creating objects, dropping objects etc) need to be mapped out. These are found in DBA_SYS_PRIVS.
select 'grant '||privilege||' to '||grantee||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
order by grantee, privilege
With the above steps, you've effectively mapped out your database security model. If you don't want the create statements in there, it's easy enough to select from the specific views to get the information you need.
The views starting with "DBA_" are for DBA's to use. A user can look at equivalent views being prefaced by "USER_" instead of "DBA_". These are all the objects owned by the current user. Further, there's a set of views starting "ALL_". These represent all of the objects that the current user has access to.
There's a lot of useful information in the system catalog views in Oracle - every object ever created should have entries in one of the DBA_ views.
As a final note, there's a web page here that has the above data (without the create statements) in a hierarchical view. Interesting way of looking at it.
