| Check DBMS settings to determine whether users are restricted from accessing objects and data they are not authorized to access. If appropriate access controls are not implemented to restrict access to authorized users and to restrict the access of those users to objects and data they are authorized to see, this is a finding. |
The easiest way to isolate access is by using the Oracle Database Vault. To check to see if the Oracle Database Vault is installed, issue the following query:
SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
If Oracle Database Vault is installed, review its settings for appropriateness and completeness of the access it permits and denies to each type of user. If appropriate and complete, this is not a finding.
If Oracle Database Vault is not installed, review the roles and profiles in the database and the assignment of users to these for appropriateness and completeness of the access permitted and denied each type of user. If appropriate and complete, this is not a finding.
If the access permitted and denied each type of user is inappropriate or incomplete, this is a finding.
Following are code examples for reviewing roles, profiles, etc.
Find out what role the users have:
select * from dba_role_privs where granted_role = ''
List all roles given to a user:
select * from dba_role_privs where grantee = '';
List all roles for all users:
column grantee format a32
column granted_role format a32
break on grantee
select grantee, granted_role from dba_role_privs;
Use the following query to list all privileges given to a user:
lpad(' ', 2*level) || granted_role "User roles and privileges"
/* THE USERS */
username like upper('')
/* THE ROLES TO ROLES RELATIONS */
/* THE ROLES TO PRIVILEGE RELATIONS */
start with grantee is null
connect by grantee = prior granted_role;
List which tables a certain role gives SELECT access to using the query:
select * from role_tab_privs where role='' and privilege = 'SELECT';
List all tables a user can SELECT from using the query:
select * from dba_tab_privs where GRANTEE ='' and privilege = 'SELECT';
List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant - e.g., grant select on a table to Joe). The result of this query should also show through which role the user has this access or whether it was a direct grant.
Grantee,'Granted Through Role' as Grant_Type,
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = ''
'Direct Grant' as Grant_type,
null as role,
where table_name = '';