Database security checklist updated for 9i,10g,11g R2 version.This documentation is prepared from Sans Institute security checklist 2003, Pete Finnigan web site and Oracle.com Oracle security documentation .
27.Check the spfile and pfile
read/write permissions. Only Oracle software owner can read / write this file.
·$ cd $ORACLE_HOME/dbs
·$ ls -ltr *.ora ( spfileSID.ora)
28.Check the system tablespace for non
sys objects. If the object owner are not ORAUSER move them the default data
tablespace.
·SELECT * FROM
dba_segments
WHERE owner NOTIN('SYS','SYSTEM')AND
tablespace_name ='SYSTEM';
29.Check the users who has DBA role
privilege.
·SELECT * FROM
dba_role_privs where granted_role='DBA';
30.Check the system privileges and find
the users and roles granted "ANY" privileges.
·SELECTDISTINCT
grantee
FROM dba_sys_privs
WHEREprivilegeLIKE'%ANY%'
AND GRANTEE NOTIN
('SYS',
'DBA',
'IMP_FULL_DATABASE',
'DATAPUMP_IMP_FULL_DATABASE',
'WMSYS',
'AQ_ADMINISTRATOR_ROLE',
'EXP_FULL_DATABASE',
'SCHEDULER_ADMIN',
'OEM_MONITOR',
'SYSTEM',
'SYSMAN',
'DBSNMP',
'EXFSYS',
'OUTLN');
31.Check the “WITH ADMIN” and “WITH
GRANT” privileges and compare all databases.
·selectcount(*)from dba_tab_privs where
grantable='YES';
·selectcount(*)from dba_role_privs where
admin_option='YES';
·selectcount(*)from
dba_sys_privs where
admin_option='YES';
32.Check the users who has“CREATE LIBRARY “ privileges.
Last week we tested Oracle UTL_DBWS package and noticed some handicaps. Calling web service from Oracle database server have some unsecure issues for auditing and database security.
First of all you must install the web service which is not "default=enable" option while installing the 11G database software. So It means Oracle doesnt suggest UTL_DBWS packages If its not necessary. You have to download latest copy of dbws-callout-utility-10131.zip from Oracle Technology Network (OTN). However Oracle software installation creating other UTL_* web services and giving their execute privileges to PUBLIC.
Important note: Its possible to install UTL_DBWS with Oracle Database 10g software. So we have to revoke execute privilege from public. Be
cautious when revoing privileges granted to PUBLIC [ID 247093.1]
conn / as sysdba
Revoke execute on UTL_DBWS from PUBLIC;
compile invalid objects with utlrp.sql
Oracle reffered installing this service into user defined schema account instead of POWER USER SYS account. So you can call web service only with this user and call java packages from this user. Bu another handicap is here; Even you install the utl_dbws under the user account you have to give "CREATE PUBLIC SYNONYM" system privilege to schema account. Creating public synonym privilege is bring sharing schema data to another users even they dont have privilege to see data.
Before you install the web service you have to load java publisher to database. For 11gR2 and 11gR1 version databases are using latest Java Publisher 10.2 and you can download it also from OTN.
Public granted object counts is increase 23886 to 29537. Loading Java publisher create approximately 6000 objects. These objects are synonyms ,classes,resources and interfaces.
You must set the Shared pool size and java pool size for using UTL_DBWS.
INSTALLATION
SQL> desc sys.utl_dbws
ERROR:
ORA-04043: object sys.utl_dbws does not exist
SQL>
SQL>
SQL> SELECT owner, status, count(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE='JAVA CLASS' GROUP BY owner, status;
OWNER STATUS COUNT(*)
------------------------------ ------- ----------
MDSYS VALID 456
SYS VALID 20285
EXFSYS VALID 47
ORDSYS VALID 1871
SQL> show parameter SHARED_POOL_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL>
SQL> show parameter JAVA_POOL_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0
SQL>
SQL> alter system set SHARED_POOL_SIZE=132M scope=both;
System altered.
SQL> alter system set JAVA_POOL_SIZE=80M scope=both;
System altered.
SQL> show parameter JAVA_POOL_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 80M
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$ mv /u01/app/oracle/product/11.2.0/db_1/sqlj /u01/app/oracle/product/11.2.0/db_1/sqlj.org
[oracle@server ~]$ ls -ltr /u01/app/oracle/product/11.2.0/db_1/sqlj.org/
total 4
drwxr-xr-x 2 oracle oinstall 4096 Dec 21 2011 lib
[oracle@server ~]$
[oracle@server ~]$ ls -ltr /u01/app/oracle/product/11.2.0/db_1/sqlj.org/lib/
total 4828
-rw-r--r-- 1 oracle oinstall 438097 Aug 13 2010 runtime12.jar
-rw-r--r-- 1 oracle oinstall 438097 Aug 13 2010 runtime12ee.jar
-rw-r--r-- 1 oracle oinstall 4053906 Aug 13 2010 translator.jar
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$
********************************************************************************************************************************************
[oracle@server ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 10:51:31 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server ~]$
[oracle@server ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 10:55:49 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> alter user sys identified by "password";
User altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
********************************************************************************************************************************************
Oracle SQLJ Translator and Oracle JPublisher Downloads
CREATE USER SCOTT IDENTIFIED BY "tiger" DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP;
grant CREATE PUBLIC SYNONYM to scott;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT;
select * from dba_sys_privs where GRANTEE='SCOTT';
SELECT grantee, granted_role FROM dba_role_privs where GRANTEE = 'SCOTT';
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> @utl_dbws_decl.sql
Package created.
SQL> @utl_dbws_body.sql
Package body created.
Grant succeeded.
SQL> desc utl_dbws
PROCEDURE ADD_PARAMETER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
XML_NAME VARCHAR2 IN
Q_NAME VARCHAR2(4096) IN
P_MODE VARCHAR2 IN
FUNCTION CREATE_CALL RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
FUNCTION CREATE_CALL RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
PORT_NAME VARCHAR2(4096) IN
OPERATION_NAME VARCHAR2(4096) IN
FUNCTION CREATE_SERVICE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2(4096) IN
FUNCTION CREATE_SERVICE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
WSDL_DOCUMENT_LOCATION URITYPE IN
SERVICE_NAME VARCHAR2(4096) IN
FUNCTION GET_IN_PARAMETER_TYPES RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION GET_LOCAL_PART RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2(4096) IN
FUNCTION GET_NAMESPACE_URI RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2(4096) IN
FUNCTION GET_OPERATIONS RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
PORT VARCHAR2(4096) IN
FUNCTION GET_OUTPUT_VALUES RETURNS TABLE OF ANYDATA
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION GET_OUT_PARAMETER_TYPES RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION GET_PORTS RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
FUNCTION GET_PROPERTY RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
KEY VARCHAR2 IN
FUNCTION GET_RETURN_TYPE RETURNS VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION INVOKE RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
REQUEST XMLTYPE IN
FUNCTION INVOKE RETURNS ANYDATA
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
INPUT_PARAMS TABLE OF ANYDATA IN
PROCEDURE RELEASE_ALL_SERVICES
PROCEDURE RELEASE_CALL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
PROCEDURE RELEASE_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
PROCEDURE REMOVE_PROPERTY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
KEY VARCHAR2 IN
PROCEDURE SET_HTTP_PROXY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HTTPPROXY VARCHAR2 IN
PROCEDURE SET_LOGGER_LEVEL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LEVEL VARCHAR2 IN
PROCEDURE SET_PROPERTY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
KEY VARCHAR2 IN
VALUE VARCHAR2 IN
PROCEDURE SET_RETURN_TYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
RET_TYPE VARCHAR2(4096) IN
PROCEDURE SET_TARGET_ENDPOINT_ADDRESS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
ENDPOINT VARCHAR2 IN
FUNCTION TO_QNAME RETURNS VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME_SPACE VARCHAR2 IN
NAME VARCHAR2 IN
SQL> show user
USER is "SCOTT"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server lib]$
[oracle@server lib]$
[oracle@server lib]$
[oracle@server lib]$
[oracle@server lib]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 15:24:07 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server lib]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 15:24:07 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'shutdownHooks', '' );
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.logging.LoggingPermission', 'control', '' );
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxySet','write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','getClassLoader','');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','*','connect,resolve');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','*','read,write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','setFactory','');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
Call completed.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server lib]$
ref id: Using UTL_DBWS to Make a Database 11g Callout to a Document Style Web Service [ID 841183.1]
You could identify your needs and requirements with filling this form. Select your database and control database costs. In addition you must consider sector necessaries.
*************Availability *************** 1. Is your database mission critical ? RTO (recovery time objective) and RPO (Recover Point Objective) values? 2. Do you need 7/24 access of Database? 3. Do you need backup and recovery ? What is the needed method for backup? (RMAN, Incremental, Daily, Hot/Cold backup etc..) 4. Do you have any Disaster Recovery documentation?
*******************Scalability****************** 5. How large is your database ? What is the needed Actual disk space by application schema sufficient by 2 years ? 6. What data types do you need ? (Binary, Lob, Large, Partition keys, Tables, Index type etc..) 7. What is the database archiving method? How long archived data must be kept ? 8. What are the minimum physical requirements of database server ? (Storage, Memory, Cpu Requirements etc.. ) 9. What is the Directory Structure of the database server ? *******************Security****************** 10. How many concurrent users will have database access? And What must be their usage profiles? 11. Is there a platform dependency or requirement? ( OS, Database version etc..) ( Oracle specified Options ) 12. Is Oracle Enterprise Linux 5.5 and Oracle Database 11g Standart Edition 11.2.0.3.5 (Latest version at May-2013) Latest suitable for the database server? 13. What are the needed options during the database installation (text, spatial, partioning etc…)? 14. Oracle psu patch transition is needed regularly. Is there any risk while psu patch transitions. Is there a version that doesn’t fit application? 15. Will the applications users be created on database? If it is so what are the minimum access that should be given and what is the minimum rights for application schema user ,too ? 16. Database passwords should be case sensitive and consists of punctuation characters. Is the application server suitable for this situation?
Database security checklist updated for 9i,10g,11g R2 version.This documentation is prepared from Sans Institute security checklist 2003, Pete Finnigan web site and Oracle.com Oracle security documentation .
Please note date, instance name and cluster structure for each database before doing checklist.
1.Check
database version
•SQL>Select * from v$version;
2.Check
operating system release and patch information
·$ cat /etc/*release*
3.Check
database features
·SQL>SET SERVEROUTPUT ON
SQL>DECLARE
SQL>paramVARCHAR2(4000):='';
SQL>BEGIN
SQL>FOR C IN(select PARAMETER from v$option where value='TRUE' order by SQL>parameter asc) LOOP
SQL>param:=param || c.parameter || ',';
SQL>ENDLOOP;
SQL>DBMS_OUTPUT.PUT_LINE(param);
SQL>END;
SQL>/
4.Check
OracleDatabase software files owner and
permission information.Software owner must be oracle .
·# ls –ltr $ORACLE_HOME/bin
5.Check
Orace software owner accounts password same as username or not
·#login su – oracle (password :oracle)
6.Check
umask value.( default value for umask 0022)
8.Check
the username and passwords which are seen at process list. They could be
tirggered from crontab or open source database’s stop/ start scripts.
·# Ps –ef |grep more
9.Contact
system admin team and check the ps command restrictions. ps command would be limited or No limited.
10.Check
shell history , bash_historyfor
username and passwords. Delete password and username entries from bash_history
if they are exists.
·#history | more
11.Check
the username and password at crontab
·#crontab–l
12.Check
control file permissons
·SQL>select name fromv$controlfile;
·# cd /name
·Ls –ltr *.ctl
13.Check
the users who has ALTER SESSION and ALTER SYSTEM privileges
·SQL>SELECT grantee, admin_option
FROM dba_sys_privs
WHERE privilege IN('ALTER SYSTEM','ALTER SESSION');
14.Check
log_archive_dest1 and destn directory privilgeswho can read archive logs except for ORACLE software owner.
·SQL>SELECT VALUE
FROM gv$parameter
WHERE name LIKE'%log_archive_dest%';
15.Check
the database has any external table, check directory_name ,owner and
table_name.
·SQL>SELECT * FROM
DBA_EXTERNAL_TABLES;
16.Check
the listener service password , if its exist read the documents and disable
password authentication.
17.Check
thedatabase users which are
authenticate from proxy account.
·SQL>select * from dba_users where authentication_type<>'PASSWORD';
18.Check
the SYS and SYSTEM accounts password are same or NOT ?
19.Check
the remote login password file value. (NONE,SHARED,EXCLUSIVE)
·SQL>select * from gv$parameter where name='remote_login_passwordfile' ;
20.Check
the database users default passwords and If SYSTEM tablespace exists set the
tablespace to DATA or User defined tablespace.
·SQL> select username from dba_users where default_tablespace='SYSTEM';
21.Check
the Utl_file_dir parameter value for Oracle lower versionsfrom 11g and directory permission for Oracle
11g databases.Utl_file_dir is
deprecated at Oracle 11g database.
·select value from gv$parameter where name='utl_file_dir';
·select * from dba_directories;
·cd $directory_path
22.Check
the package privileges;
·SQL>select * from dba_tab_privs where table_name='dbms_backup_restore';
23.Check
the who has ALL_USERS viewSELECT
privileges.
·SQL>select GRANTEE,PRIVILEGE,GRANTABLE from dba_tab_privs where table_name='ALL_USERS';
24.Check
the all ‘%ALL%’ views privileges count.
·select * from dba_tab_privs where table_name like 'ALL%'
25.Check
the os authenticate users
·SQL>select value from gv$parameter where name='os_authent_prefix';
26.Check
the remote listener parameter
·SQL>select * from gv$parameter where name like '%remote_listener%';
