Oracle Database Security Checklist

Oracle Database Security Checklist
==================================
For a production Database, must need to check the following points for
better security.



1. Protecting the database environment.............................................................
2. Install only what is required..........................................................................
3. Lock and expire default user accounts...........................................................
4. Changing default user passwords...................................................................
5. Change passwords for administrative accounts.............................................
6. Change default passwords for all users...........................................................
7. Enforce password management......................................................................
8. Secure batch jobs............................................................................................
9. Manage access to SYSDBA and SYSOPER roles..........................................
10. Enable Oracle data dictionary protection......................................................
11. Follow the principle of least privilege.............................................................
12. Public privileges..............................................................................................
13. Restrict permissions on run-time facilities......................................................
14. Authenticate clients........................................................................................
15. Restrict operating system access.....................................................................
16. Secure the Oracle listener..............................................................................
17. Secure external procedures.............................................................................
18. Prevent runtime changes to listener................................................................
19. Checking network IP addresses......................................................................
20. Harden the operating system.........................................................................
21. Encrypt network traffic..................................................................................
22. Apply all security patches...............................................................................
23. Report security issues to Oracle....................................................................

Restrict DDL on a system

CREATE OR REPLACE TRIGGER SYS.restrict_ddl_in_db
BEFORE ddl ON DATABASE
DECLARE
machinename VARCHAR2(64);
message VARCHAR2(150) := 'You have no rights to execute DDL statements on DEVELOPBETA! Call the DBA! ';
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'host') INTO machinename FROM DUAL;
IF machinename IS NULL then
message := message || ' ' || 'NULL';
else
message := message || ' ' || machinename;
end if;
IF INSTR(upper(machinename),'*****WORKGROUP\XPSP3-WBB',1) <> 0
OR INSTR(lower(machinename),'bg\fed',1) <> 0
OR INSTR(lower(machinename),'bg\plamen',1) <> 0
OR INSTR(lower(machinename),'developbeta',1) <> 0
OR INSTR(lower(machinename),'oralin1',1) <> 0
OR machinename IS NULL
THEN
null;
ELSE
RAISE_APPLICATION_ERROR (num => -20000,msg => message);
END IF;
END;
/

External table in oracle 11g

External table in oracle 11g


A) First need to Create a Directory
=======================================
=======================================

SQL> CREATE DIRECTORY My_dir AS 'D:\dir_external\dir';


B) Then create External Table
=======================================
=======================================

1. External table normal in oracle 10g
=========================================

SQL> CREATE TABLE Noncompressed_all_objects
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_dir
LOCATION ( 'Noncompressed_all_obj.dmp' )
)
AS
SELECT *
FROM all_objects;


To see The size of file
-----------------------
SQL> host dir /od D:\dir_external\dir\*.DMP


2. External Table Compressed in Oracle 11g
=============================================

SQL> CREATE TABLE compressed_all_objects
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS (COMPRESSION ENABLED)----Difference
LOCATION ( 'compressed_all_obj.dmp' )
)
AS
SELECT *
FROM all_objects;

To see compressed size
------------------
SQL> host dir /od D:\dir_external\dir\*.DMP


3. External Table Compressed in Oracle 11g
==============================================

SQL> CREATE TABLE encrypted_all_objects
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS (ENCRYPTION ENABLED) ----Difference
LOCATION ( 'encrypted_all_obj.dmp' )
)
AS
SELECT *
FROM all_objects;


4. External Table Combining compression and encryption in Oracle 11g
===================================================================

SQL> CREATE TABLE combining_all_objects
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS (ENCRYPTION ENABLED COMPRESSION ENABLED)
LOCATION ( 'combining_all_obj.dmp' )
)
AS
SELECT *
FROM all_objects;

[note: To change the location of this external table to use the compressed file]

SQL> ALTER TABLE Noncompressed_all_objects LOCATION ('compressed_all_obj.dmp');

Find users with there privileges

conn / AS sysdba

SELECT grantee, privilege, admin_option
FROM sys.dba_sys_privs
WHERE (privilege LIKE '% ANY %'
OR privilege IN ('BECOME USER', 'UNLIMITED TABLESPACE')
OR admin_option = 'YES')
AND grantee NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA')

Block TOAD and other tools

CONNECT / AS SYSDBA;

CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's

IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/
SHOW ERRORS