Oracle has three totally
separate and distinct ways for managing data access.
- Grant privilege security
– The traditional approach of granting privileges to users or
roles.
- Grant Execute security
– This manages security, but all users must be granted execute privileges on
all the procedures within the application.
- Row-level security
(a.k.a. fine-grained access control, Virtual private databases)
– This scheme enhances traditional grant security by appending a WHERE clause
to all SQL, depending on the current user ID.
Oracle security has become extremely complex, and an
important job of a DBA is to audit security. When dealing with a database as
complex as Oracle, 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 below 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).
The items below are some of the top items that I check when
auditing an Oracle database. Of course, you may have other queries, and I am
always interested in new techniques. Feel free to e-mail me at
info@remote-dba.net with suggested improvements.
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.
select
grantee,
privilege,
admin_option
from
sys.dba_sys_privs
where
admin_option = 'YES'
and
grantee not in (‘SYS','SYSTEM',
etc);
Search for any end-users with system privileges
Users with system-level privileges may have access to
unwanted areas of the database. For example the "select any table" system
privilege may allow unwanted access to a user.
select
grantee c1,
privilege c2,
admin_option c3
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 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.
select
grantee,
granted_role,
admin_option,
default_role
from
sys.dba_role_privs
where
admin_option = 'YES'
and
grantee not in (‘SYS','SYSTEM',
etc);
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.
select
grantee,
owner,
table_name,
grantor,
privilege,
grantable
from
sys.dba_tab_privs
where
grantable = 'YES'
and
owner not in (‘SYS','SYSTEM',
etc);
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.
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',
etc);
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.
select distinct
owner,
table_name,
privilege,
grantor
from
sys.dba_tab_privs
where
grantee = 'PUBLIC'
and
owner not in (‘SYS','SYSTEM',
etc);
Generating a filer condition
The huge amount of system grants at Oracle install time
makes a bewildering array of users. If you install the full array of
Oracle options, Oracle will create over 170 user IDs inside the Oracle database.
Here is the filter condition that I use to ignore the
Oracle-supplied users:
Where
owner not in
(
'ADAMS',
'ADLDEMO',
'ADMIN',
'APPLSYS',
'APPLYSYSPUB',
'APPS',
'AQ',
'AQDEMO',
'AQJAVA',
'AQUSER',
'AUDIOUSER',
'AURORA$JIS$UTILITY$',
'AURORA$ORB$UNAUTHENTICATED',
'BC4J',
'BLAKE',
'CATALOG',
'CDEMO82',
'CDEMOCOR',
'CDEMORID',
'CDEMOUCB',
'CENTRA',
'CIDS',
'CIS',
'CISINFO',
'CLARK',
'COMPANY',
'COMPIERE',
'CQSCHEMAUSER',
'CSMIG',
'CTXDEMO',
'CTXSYS',
'DBA',
'DBI',
'DBSNMP',
'DEMO',
'DEMO8',
'DEMO9',
'DES',
'EJSADMIN',
'EMP',
'ESTOREUSER',
'EVENT',
'FINANCE',
'FND',
'FROSTY',
'GPFD',
'GPLD',
'HCPARK',
'HLW',
'HR',
'IMAGEUSER',
'IMEDIA',
'JMUSER',
'JONES',
'LBACSYS',
'LIBRARIAN',
'MASTER',
'MDSYS',
'MFG',
'MIGRATE',
'MILLER',
'MMO2',
'MODTEST',
'MOREAU',
'MTS_USER',
'MTSSYS',
'MXAGENT',
'NAMES',
'OAS_PUBLIC',
'OCITEST',
'ODS',
'ODSCOMMON',
'OE',
'OEMADM',
'OLAPDBA',
'OLAPSVR',
'OLAPSYS',
'OMWB_EMULATION',
'OPENSPIRIT',
'ORACACHE',
'ORAREGSYS',
'ORDPLUGINS',
'ORDSYS',
'ORACLE',
'OSE$HTTP$ADMIN',
'OSP22',
'OUTLN',
'OWA',
'OWA_PUBLIC',
'PANAMA',
'PATROL',
'PERFSTAT',
'PLSQL',
'PM',
'PO',
'PO7',
'PO8',
'PORTAL30',
'PORTAL30_DEMO',
'PORTAL30_PUBLIC',
'PORTAL30_SSO',
'PORTAL30_SSO_PS',
'PORTAL30_SSO_PUBLIC',
'POWERCARTUSER',
'PRIMARY',
'PUBSUB',
'QS',
'QS_ADM',
'QS_CB',
'QS_CBADM',
'QS_CS',
'QS_ES',
'QS_OS',
'QS_WS',
'RE',
'REP_MANAGER',
'REP_OWNER',
'REP_OWNER',
'REPADMIN',
'RMAIL',
'RMAN',
'SAMPLE',
'SAP',
'SCOTT',
'SDOS_ICSAP',
'SECDEMO',
'SH',
'SITEMINDER',
'SLIDE',
'STARTER',
'STRAT_USER',
'SWPRO',
'SWUSER',
'SYMPA',
'SYS',
'SYSADM',
'SYSMAN',
'SYSTEM',
'TAHITI',
'TDOS_ICSAP',
'TESTPILOT',
'TRACESVR',
'TRAVEL',
'TSDEV',
'TSUSER',
'TURBINE',
'ULTIMATE',
'USER',
'USER0',
'USER1',
'USER2',
'USER3',
'USER4',
'USER5',
'USER6',
'USER7',
'USER8',
'USER9',
'UTLBSTATU',
'VIDEOUSER',
'VIF_DEVELOPER',
'VIRUSER',
'VRR1',
'WEBCAL01',
'WEBDB',
'WEBREAD',
'WKSYS',
'WWW',
'WWWUSER',
'XPRT',
)
Putting it all together
The full audit script is extremely useful for auditing unknown
Oracle databases, and it has
proved indispensable for locating security loopholes. Sadly, the script is
hundreds of lines of code, and it is too cumbersome to publish here
