 |
|
Oracle Tips by Burleson |
Locating Oracle Security
Loopholes
Part of the job of a DBA is to audit
security. When dealing with a database as complex as Oracle 10g,
writing a working audit script is a formidable challenge because you
must ignore all of the internal grants and roles, and focus on
non-system users.
With increasing security concerns, Oracle DBAs
are often called-upon to perform security audits of an Oracle
environment to ensure that there are no loopholes in security.
Note: These scripts only check traditional
Oracle security and do not address “grant execute” security and
row-level security (Virtual Private Databases, a.k.a. Fine-grained
access control).
Some of the top items to check include the
following queries:
Search for any system privileges that are
granted WITH ADMIN OPTION
Privileges that are granted WITH ADMIN can be
passed to other users. Hence, many companies prohibit this option,
and others check to ensure that all user ID’s are proper. The
information for this is located in the DBA_SYS_PRIVS view, and here is
the code to do this.
Copy this code onto your Oracle database and
observe the result.
select
grantee,
privilege,
admin_option
from
sys.dba_sys_privs
where
admin_option = 'YES'
and
grantee not in (‘SYS’,’SYSTEM’);
Search for any end-users with system
privileges
Users with system-level privileges may have
access to unwanted areas of the Oracle database. For example the
“select any table” system privilege may allow unwanted access to a
user.
Copy this code onto your Oracle database and
observe the result.
select
grantee,
privilege,
admin_option
from
sys.dba_sys_privs
where
grantee not in (‘SYS’,’SYSTEM’, etc);
Search for any non-DBA roles that are
granted WITH ADMIN OPTION
This check ensures that there are no
customized roles that are granted to users using the WITH ADMIN
option. We need to query the ROLE_SYS_PRIVS view to get this
information. To be accurate, we must filter-out all of the installed
roles that some with the standard Oracle 10g software.
select
role,
privilege,
admin_option
from
sys.role_sys_privs
where
admin_option = 'YES'
and
role not in
(
'AQ_ADMINISTRATOR_ROLE',
'CONNECT',
'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE',
'JAVADEBUGPRIV',
'OEM_MONITOR',
'OLAP_DBA',
'RECOVERY_CATALOG_OWNER',
'RESOURCE',
'WKUSER'
)
Search for any non-DBA grantees that are
granted a role WITH ADMIN OPTION.
Here we check the DBA_ROLE_PRIVS view for all
roles that have been granted using the WITH ADMIN option.
Copy this code onto your Oracle database and
observe the result.
select
grantee,
granted_role,
admin_option,
default_role
from
sys.dba_role_privs
where
admin_option = 'YES'
and
grantee not in (‘SYS’,’SYSTEM’);
Search for any table privileges that can be
granted to others
Here we check the DBA_TAB_PRIVS view to locate
any table privileges that have been created as grantable.
Copy this code onto your Oracle database and
observe the result.
select
grantee,
owner,
table_name,
grantor,
privilege,
grantable
from
sys.dba_tab_privs
where
grantable = 'YES'
and
owner not in (‘SYS’,’SYSTEM’);
Search for any non-DBA who is granted DBA
and RESOURCE Roles
This query uses the DBA_ROLE_PRIVS view to
locate all grantees who are granted the DBA or the RESOURCE role.
Copy this code onto your Oracle database and
observe the result.
select
grantee,
granted_role,
admin_option,
default_role
from
sys.dba_role_privs
where
granted_role in ('RESOURCE','DBA')
and
grantee not in (‘SYS’,’SYSTEM’);
Search for all tables granted to PUBLIC
Tables that are granted to PUBLIC are open for
anyone to see. The following script checks the DBA_TAB_PRIVS view to
locate all tables that have been granted PUBLIC access.
Copy this code onto your Oracle database and
observe the result.
select
distinct
owner,
table_name,
privilege,
grantor
from
sys.dba_tab_privs
where
grantee = 'PUBLIC'
and
owner not in (‘SYS’,’SYSTEM’);
From running these scripts it should be
apparent that there is a considerable amount of Oracle-supplied
grants. In our assignment for this tutorial you will be asked to
enhance these queries to improve them by filtering-out the standard
Oracle grants. This will allow you to only see application-specific
grants.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
