Why should a DBA worry about database security? Many DBAs often say that security is not part of a DBA job, but I hold a different view. Security is everybody's job. As a member of a team, you have to play a role in ensuring that data is security. Aside from availability and reliability of data, data must be secured such that unnecessary access is minimized if not completely elimited.
There are different ways of ensuring that database files are secured. We can do this by preventing unwanted access to the database or monitoring the activities of users. Our focus here is on 4 objectives:
• User management;
• Authentication;
• Encryption; and
• Auditing
User management
It is a normal practice to lock any default account in the database that is not being used. Issue this command:
Example 1: Lock the account of a user
Alter user TOMMY account lock;
To unblock the account:
Alter user TOMMY account unlock;
Example 2: change password of a user
To change password:
Password TOMMY;
(enter new password)
To force password to expire:
Alter user TOMMY password expire;
Example 3: change tablespace of a user
To change default tablespace:
Alter user TOMMY default tablespace users;
Example 4: delete a user
To delete user;
Drop user TOMMY cascade;
Example 5: change password of a user to unlimited expiry
SQL> SELECT EXPIRY_DATE, profile, username FROM DBA_USERS WHERE account_status='OPEN';
EXPIRY_DA PROFILE USERNAME
--------- ------------------------------ ------------------------------
23-MAR-13 DEFAULT SYSTEM
23-MAR-13 DEFAULT SYS
23-MAR-13 DEFAULT DOCTOR
23-MAR-13 DEFAULT SYSMAN
23-MAR-13 MONITORING_PROFILE DBSNMP
23-MAR-13 DEFAULT MGMT_VIEW
23-MAR-13 DEFAULT PATIENTS
7 rows selected.
SQL> alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
Check status of SYS authentication
SQL> desc v$pwfile_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
SYSDBA VARCHAR2(5)
SYSOPER VARCHAR2(5)
SYSASM VARCHAR2(5)
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
change password of SYS
cd app\sam\product\11.2.0\dbhome_1\database
Orapwd file=filename password=my_password force=y
Kill a session
Sqlplus / as sysdba
select sid, username, serial# from v$session where username=’TOMMY’;
alter system kill session ’29,1020’ immediate;
or
ALTER SYSTEM KILL SESSION 'sid,serial#';
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
ENCRYPTION
setting up encryption wallet
CREATE TABLE tde_test (
id NUMBER(10),
data VARCHAR2(50)
);
INSERT INTO tde_test (id, data) VALUES (1, 'This is a secret!');
COMMIT;
location of wallet:
$ORACLE_BASE/admin/$ORACLE_SID/wallet
or change in sqlnet.ora
create a folder called wallet at "location_of_wallet"
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "myPassword";
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
example.....
create folder wallet at
$ORACLE_BASE/admin/$ORACLE_SID
C:\Users\k>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 29 09:54:05 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set encryption key authenticated by "oracle";
System altered.
NOTE: you cannot encrypt SYS owned objects.
SQL> create table system.ide (id_num number(10), name varchar2(10));
Table created.
SQL> desc id;
Name Null? Type
----------------------------------------- -------- ---------------------------
ID_NUM NUMBER(10)
NAME VARCHAR2(10)
SQL> insert into system.ide values ('100', 'Ade');
1 row created.
SQL> insert into system.ide values ('102', 'car');
1 row created.
SQL> insert into system.ide values ('103', 'bus');
1 row created.
SQL> insert into system.ide values ('104', 'lorry');
1 row created.
SQL> insert into system.ide values ('105', 'bike');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table system.ide modify id_name encrypt;
Table altered.
SQL> commit;
Commit complete.
SQL> select * from system.ide;
ID_NUM NAME
---------- ----------
100 Ade
102 car
103 bus
104 lorry
105 bike
SQL> alter system set encryption wallet close identified by "oracle";
System altered.
SQL> commit;
Commit complete.
SQL> select * from system.ide;
select * from system.ide
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> alter system set encryption wallet open identified by "oracle";
System altered.
SQL> select * from system.ide;
ID_NUM NAME
---------- ----------
100 Ade
102 car
103 bus
104 lorry
105 bike
Auditing
Disable auditing
export ORACLE_SID=orcl
sqlplus / as sysdba
noaudit all privileges;
noaudit all;
alter system set audit_trail=none scope=spfile;
truncate table sys.aud$;
commit;
shutdown immediate;
startup
Enable auditing
export ORACLE_SID=orcl
sqlplus / as sysdba
alter system set audit_trail='db','extended' scope=spfile;
alter system set audit_sys_operations=true scope=spfile;
commit;
shutdown immediate;
startup
audit session whenever successful;
audit session whenever not successful;
commit;
show parameter audit
select count(*) from sys.aud$;
SELECT * FROM DBA_STMT_AUDIT_OPTS;
SELECT * FROM DBA_PRIV_AUDIT_OPTS;
SELECT * FROM DBA_OBJ_AUDIT_OPTS;
audit ALTER any TABLE BY access;
audit CREATE any TABLE BY access;
audit DROP any TABLE BY access;
audit CREATE any PROCEDURE BY access;
audit DROP any PROCEDURE BY access;
audit ALTER any PROCEDURE BY access;
audit GRANT any privilege BY access;
audit GRANT any object privilege BY access;
audit GRANT any ROLE BY access;
audit audit system BY access;
audit CREATE external job BY access;
audit CREATE any job BY access;
audit CREATE any library BY access;
audit CREATE public DATABASE link BY access;
audit exempt access policy BY access;
audit ALTER USER BY access;
audit CREATE USER BY access;
audit ROLE BY access;
audit CREATE SESSION BY access;
audit DROP USER BY access;
audit ALTER DATABASE BY access;
audit ALTER system BY access;
audit ALTER profile BY access;
audit DROP profile BY access;
audit DATABASE link BY access;
audit system audit BY access;
audit profile BY access;
audit public synonym BY access;
audit system GRANT BY access;
audit DELETE ON sys.aud$;
audit ALTER ON DEFAULT;
audit GRANT ON DEFAULT;
Creating an audit cleaning job
Check if there is an existing job
SQL> col PARAMETER_NAME FOR a30
col PARAMETER_VALUE FOR a15
col AUDIT_TRAIL FOR a20
SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE audit_trail = 'STANDARD AUDIT TRAIL';
Drop existing job
BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
AUDIT_TRAIL_PURGE_NAME => 'CLEANUP');
END;
/
Create new job
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => sysdate-2); END;',
start_date => sysdate,
repeat_interval => 'FREQ=HOURLY;INTERVAL=12',
enabled => TRUE,
comments => 'Create an archive timestamp'
);
END;
/
launch it
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 12 /* hours */,
AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE
);
END;
/
commit;
To verify job
SQL> col JOB_NAME FOR a30
col JOB_FREQUENCY FOR a40
SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
SQL> col next_run_date FOR a50
SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs
WHERE job_name LIKE '%AUDIT%';
