Transcription
Enhancing Database Security:Concepts and Tools for the DBA * Scripts & Source CodePeter J. Magee, CDASQR IBE Techn ologiesPasswords in Command LinesFix for ctxctl script to eliminate appearance of CTX SYS password in command line.1. Copy ctxctl to ctxctl.secure2. In ctxctl.secure, replace the follow ing command line (line 312): exe -user username/ password -personality mask /dev/null &with these lines:CTX PASS username/ passwordexport CTX PASS ORACLE HOME/bin/ctxsecure exe mask &CTX PASS export CTX PASS3. Create a script called ctxsecure in the O RA CLE HO ME/bin directory:##File:ctxsecure#Location: ORACLE HOME/bin##This file calls the specified ConText executable application,#but prevents the CTXSYS userid and password from appearing in#the command line. This prevents the user id and password from#being visible to a "ps -ef" command.##Inputs: 1Executable ConText file name# 2Personality flag for server# CTX PASSAn environment variable holding#the CTXSYS user id and password# 1 -personality 2 /dev/null CTXEND {CTX PASS}CTXENDMini-Lesson M6, Scripts & Source Code/ Page 1
Note: do not place the characteristic !/bin/sh in the first line of ctxsecure. If a new shell is opened, CTX PASScan not be found. ctxsecure sh ould run in the same shell as ctxctl.secure.Once these steps are complete, use ctxctl.secure the same as you would use ctxctl. An additional process will begenerated by ctxctl.secure for each ConText server, so "ps -ef" output would look something like this:oracle13971395oracle13951 ORACLE HOME/bin/ctxsrv -personality LQ/bin/sh ctxctl.secureInitialization ParametersSelect values for security related initialization parameters from th e data dictionary.select * from v parameterwhere name in ('audit trail', ‘db encrypt login’, 'resource limit', 'remote os auth','remote os roles', 'os roles', 'utl file dir');SQL*Net Firew allsA sample protocol.ora file, located in the O RA CL E HOME/network/adm in directory:tcp.validnode checking yestcp.invited nodes tion Users (OPS Users)Identify externally authenticated user accounts.select username, password from dba users where password 'EXTERNAL';System PrivilegesIdentify system privileges granted to users other than SYS, SYSTEM, or DBSNM P.select p.grantee, p.privilegefrom dba sys privs p, dba users uwhere (u.username p.grantee or p.grantee 'PUBLIC') andp.grantee not in ('SYS','SYSTEM','DBSNMP');Identify system privileges granted to roles other than DBA, RESOURCE, IMP FULL DATABASE,EXP FULL DATABASE, CONNECT, and SNMPAGENT.select p.grantee, p.privilegefrom dba sys privs p, dba roles rwhere r.role p.grantee and r.role not in('DBA','RESOURCE','IMP FULL DATABASE','EXP FULL DATABASE','CONNECT','SNMPAGENT');Mini-Lesson M6, Scripts & Source Code / Page 2
The following System Privileges should only be granted to administrators, never application users:ANALYZE ANYALTER A NY RO LESELECT A NY TA BLEAUDIT ANYDRO P ANY ROLEINSERT AN Y TAB LEAUDIT SYSTEMGRA NT AN Y ROLEUPDA TE AN Y TAB LEALTER ANY CLUSTERDROP ROLLBACKSEGMENTDELETE ANY TABLEDROP ANY CLUSTERRESTRICTED SESSIONTABLESPACEALTER DATA BASECREATE ANY SEQUENCEMANAGE TABLESPACECREATE ANY INDEXALTER ANY SEQUENCEUNLIMITED TABLESPACEALTER ANY INDEXDROP ANY SEQUENCEFORCE TRANSACTIONDROP ANY INDEXSELECT ANY SEQUENCEFORCE ANY TRANSACTIONGRANT ANY PRIVILEGEALTER ANY SNAPSHOTCREATE ANY TRIGGERCREATE ANY PROCEDUREDROP ANY SNAPSHOTALTER ANY TRIGGERALTER ANY PROCEDURECREATE ANY SYNONYMDROP ANY TRIGGERDROP ANY PROCEDUREDROP ANY SYNONYMBECOME USEREXECUTE ANY PROCEDUREALTER SYSTEMCREATE ANY VIEWCREA TE PRO FILECREA TE AN Y TAB LEDROP ANY VIEWALTER P ROFILEALTER A NY TA BLECREATE DATABASE LINKDRO P PROFILEBACK UP AN Y TAB LECREATE PUB LIC DATAB ASELINKALTER RESOU RCE COSTDRO P ANY TABLEDROP PUBLIC DATABASE LINKDROP PUBLIC DATABASE LINKLOCK A NY TA BLECREATE PUBLIC SYNONYMDROP PUBLIC SYNONYMCOM MEN T ANY TABLEDROP PUBLIC SYNONYMObject PrivilegesIdentify users other than SYS and SYSTEM that have been granted ALTE R or REFE RENC ES p riveleges.select t.grantee, t.owner '.' t.table name, t.privilegefrom dba tab privs t, dba users uwhere (u.username t.grantee or t.grantee 'PUBLIC')and t.privilege in ('ALTER','REFERENCES') andt.grantee not in ('SYS','SYSTEM');Identify roles other than DBA, RESO URCE , IMP FU LL DATA BASE, EXP F ULL DA TABA SE, andCO NN ECT that have been granted A LTE R or REFE RENCES p rivileges.select t.grantee, t.owner '.' t.table name, t.privilegefrom dba tab privs t, dba roles rwhere r.role t.grantee and t.privilege in ('ALTER','REFERENCES')and r.role not in ('DBA','RESOURCE','IMP FULL DATABASE','EXP FULL DATABASE','CONNECT');Administration PrivilegesIdentify users other than SYS and SYSTEM that have AD MIN privileges on system and ob ject privileges.Mini-Lesson M6, Scripts & Source Code/ Page 3
select p.grantee, p.privilege, p.admin optionfrom dba sys privs p, dba users uwhere (u.username p.grantee or p.grantee 'PUBLIC') andp.admin option 'YES' and p.grantee not in ('SYS','SYSTEM');Identify users other than SYS and SY STEM that have AD MIN privileges on the Oracle defau lt roles.select r.grantee, r.granted role, r.admin optionfrom dba role privs r, dba users uwhere u.username r.grantee and r.granted role in('DBA','RESOURCE','IMP FULL DATABASE','EXP FULL DATABASE','CONNECT','SNMPAGENT') and r.admin option 'YES' andr.grantee not in ('SYS','SYSTEM');Predefined RolesIdentify users that have been granted on e of the Oracle default roles.select r.grantee, r.granted rolefrom dba role privs rwhere r.granted role in ('DBA','EXP FULL DATABASE','IMP FULL DATABASE','OSOPER','OSDBA') andr.grantee not in ('SYS','SYSTEM','DBA');Application RolesIdentify application roles and their properties.select r.role, r.password required from dba roles rwhere r.role not in ('DBA','RESOURCE','IMP FULL DATABASE','EXP FULL DATABASE','CONNECT','SNMPAGENT');Identify users that have been assigned to application roles.select r.grantee, r.granted role, r.admin optionfrom dba role privs r, dba users uwhere u.username r.grantee andr.granted role not in ('DBA','RESOURCE','IMP FULL DATABASE','EXP FULL DATABASE','CONNECT','SNMPAGENT');User ProfilesIdentify the idle time limit for each database user.select u.username, p.limitfrom dba users u, dba profiles pwhere u.profile p.profile and p.resource name 'IDLE TIME';Alter the profile idle time.alter profile [profile name] limit idle time [# minutes];Mini-Lesson M6, Scripts & Source Code / Page 4
Oracle7 ProfilesLock a U ser Account: Alter encrypted password to all lowercase; Oracle can’t translate so account is disabledalter user [username] identified by values ‘disabled’;Oracle8 ProfilesUser Profile Creation:Create a profile that will do the following:·The user will be timed out (disconnected) after 15 minutes of idle time.·The account will be locked after 3 failed logins.·The account can only be unlocked by the DBA.·The user has 3 grace logins to change their password after expiration.·The user cannot repeat passwords until they have been changed at least 10 times.·The password expires after 90 days.·The stored procedure verify password will be used to verify password com plexity.To create the profile ex ecute the following script.CREATE PROFILE APP USER LIMITIDLE TIME 15FAILED LOGIN ATTEMPTS 3ACCOUNT LOCK TIME UNLIMITEDPASSWORD GRACE TIME 3PASSWORD REUSE MAX 10PASSWORD LIFE TIME 90PASSWORD VERIFY FUNCTION verify password;Once the profile is created, it is assigned to the user with the ALTER U SER command:ALTER USER username PROFILE app user;Password Varification Function: based on sample function in Oracle documentation, but more strict.CREATE OR REPLACE FUNCTION verify function(username varchar2,password varchar2,old password varchar2)RETURN boolean ISn boolean;m integer;differ integer;isdigit boolean;Mini-Lesson M6, Scripts & Source Code/ Page 5
ischarboolean;ispunct boolean;digitarray varchar2(20);punctarray varchar2(25);chararray varchar2(52);BEGINdigitarray: '0123456789';chararray: XYZ';punctarray: '!"# %&() * ,-/:; ? ';--Check if the password is same as the usernameIF password username THENraise application error(-20001, 'Password same as user');END IF;--Check for the minimum length of the password (must be 6 or more)IF length(password) 6 THENraise application error(-20002, 'Password length less than 6');END IF;--Check if the password is too simple. A dictionary of words may be--maintained and a check may be made so as not to allow the words--that are too simple for the password.IF NLS LOWER(password) IN ('welcome', 'database', 'account', 'user','password', 'oracle', 'computer', 'abcd') THENraise application error(-20002, 'Password too simple');END IF;--Check if the password contains at least one letter and one digit---1. Check for the digitisdigit: FALSE;m : length(password);FOR i IN 1.10 LOOPFOR j IN 1.m LOOPIF substr(password,j,1) substr(digitarray,i,1) THENisdigit: TRUE;GOTO findchar;END IF;Mini-Lesson M6, Scripts & Source Code / Page 6
END LOOP;END LOOP;IF isdigit FALSE THENraise application error(-20003,’Password should contain at least onedigit, one character and one punctuation');END IF;--2. Check for the character findchar ischar: FALSE;FOR i IN 1.length(chararray) LOOPFOR j IN 1.m LOOPIF substr(password,j,1) substr(chararray,i,1) THENischar: TRUE;GOTO findpunct;END IF;END LOOP;END LOOP;IF ischar FALSE THENraise application error(-20003,'Password should contain at least onedigit, one character and one punctuation');END IF; endsearch --Check if the password differs from the previous password by at least--3 lettersIF old password '' THENraise application error(-20004, 'Old password is null');END IF;--Everything is fine; return TRUE ;differ : length(old password) - length(password);IF abs(differ) 3 THENIF length(password) length(old password) THENm : length(password);ELSEm: length(old password);END IF;Mini-Lesson M6, Scripts & Source Code/ Page 7
differ : abs(differ);FOR i IN 1.m LOOPIF substr(password,i,1) ! substr(old password,i,1) THENdiffer : differ 1;END IF;END LOOP;IF differ 3 THENraise application error(-20004, 'Password should differ by at \least 3 characters');END IF;END IF;--Everything is fine; return TRUE ;RETURN(TRUE);END;Lock User Account:alter user [username] account lock;Statement Level AuditsGenerate a list of all statement level audits.select audit option, success, failure from dba stmt audit opts;Enab le minimum required statement level audits:audit ALTER SYSTEM ;audit INDEX ;audit NOT EXISTS ;audit SYSTEM GRANT ;audit SYSTEM AUDIT ;audit TABLE ;audit TABLESPACE ;audit USER ;audit SESSION ;audit RESTRICTED SESSION ;Object Level AuditsGenerate a list of all object level audits.select owner, object name, object type from dba obj audit opts where ren '-/-';Identify all audits on the audit trail table.select owner '.' object name, object type,alt aud com del gra ind ins loc ren sel upd ref exeMini-Lesson M6, Scripts & Source Code / Page 8
from dba obj audit optswhere owner like 'SYS%' and object name 'AUD ' and object type 'TABLE';Set audit rename by default on all objects created after command:audit rename on default;Au dit actions on the audit trail:audit all on sys.aud ;oraudit all on system.aud ; (if ownership has been changed)Privilege Level AuditsGenerate a list of all privilege level audits.select privilege, success, failure from dba priv audit opts;Enab le minimum required privilege level audits:audit ANALYZE ANY ;audit AUDIT ANY ;audit AUDIT SYSTEM ;audit ALTER ANY CLUSTER ;audit DROP ANY CLUSTER ;audit ALTER DATABASE ;audit CREATE ANY INDEX ;audit ALTER ANY INDEX ;audit DROP ANY INDEX ;audit GRANT ANY PRIVILEGE ;audit CREATE ANY PROCEDURE ;audit ALTER ANY PROCEDURE ;audit DROP ANY PROCEDURE ;audit EXECUTE ANY PROCEDURE ;audit CREATE PROFILE ;audit ALTER PROFILE ;audit DROP PROFILE ;audit ALTER RESOURCE COST ;audit DROP PUBLIC DATABASE LINK ;audit DROP PUBLIC SYNONYM ;audit ALTER ANY ROLE ;audit DROP ANY ROLE ;audit GRANT ANY ROLE ;audit DROP ROLLBACK SEGMENT ;audit CREATE ANY SEQUENCE ;audit ALTER ANY SEQUENCE ;audit DROP ANY SEQUENCE ;audit SELECT ANY SEQUENCE ;audit ALTER ANY SNAPSHOT ;Mini-Lesson M6, Scripts & Source Code/ Page 9
audit DROP ANY SNAPSHOT ;audit CREATE ANY SYNONYM ;audit DROP ANY SYNONYM ;audit CREATE ANY TABLE ;audit ALTER ANY TABLE ;audit BACKUP ANY TABLE ;audit DROP ANY TABLE ;audit LOCK ANY TABLE ;audit COMMENT ANY TABLE ;audit SELECT ANY TABLE ;audit INSERT ANY TABLE ;audit UPDATE ANY TABLE ;audit DELETE ANY TABLE ;audit MANAGE TABLESPACE ;audit UNLIMITED TABLESPACE ;audit FORCE TRANSACTION ;audit FORCE ANY TRANSACTION ;audit CREATE ANY TRIGGER ;audit ALTER ANY TRIGGER ;audit DROP ANY TRIGGER ;audit CREATE USER ;audit BECOME USER ;audit ALTER USER ;audit DROP USER ;audit CREATE ANY VIEW ;audit DROP ANY VIEW ;audit CREATE PUBLIC DATABASE LINK ;audit DROP PUBLIC DATABASE LINK ;audit CREATE PUBLIC SYNONYM ;audit DROP PUBLIC SYNONYM ;audit CREATE DATABASE LINK ;Audit Trail MaintenanceSet the following init.ora parameters and restart the database to initialize the Oracle Job Queue:JOB QUEUE PROCESSES 1JOB QUEUE INTERVAL 30Create the following stored proced ure as SYSTE M through Server Manager or SQL*Plus:Oracle7 Version:CREATE PROCEDURE TRIM AUDIT TRAIL ASBEGINMini-Lesson M6, Scripts & Source Code / Page 10
DELETE FROM SYS.AUD WHERE TIMESTAMP TRUNC(SYSDATE-7);COMMIT;END TRIM AUDIT TRAIL;Oracle8 Version:CREATE PROCEDURE TRIM AUDIT TRAIL ASBEGINDELETE FROM SYS.AUD WHERE TIMESTAMP# TRUNC(SYSDATE-7);COMMIT;END TRIM AUDIT TRAIL;Set the job to run once per day at midnight using Server Manager or SQL*Plus:VARIABLE JOBNUM NUMBER;BEGINDBMS JOB.SUBMIT(:JOBNUM,'SYSTEM.TRIM AUDIT TRAIL; ',TRUNC(SYSDATE 1),'TRUNC(SYSDATE 1)');END;Check on the status of the job using the DB A JOB S or USER JOBS view
Mini-Lesson M6, Scripts & Source Code/ Page 1 Enhancing Database Security: Concepts and Tools for the DBA * Scripts & Source Code Peter J. Magee, CDA
