Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Data Dictionary Objects
ALL_TRIGGERS
TRIGGER$
USER_TRIGGERS
DBA_TRIGGERS
System Privileges
* Reqd. for ON DATABASE triggers
ADMINISTER DATABASE TRIGGER *
CREATE ANY TRIGGER
DROP TRIGGER
ALTER ANY TRIGGER
CREATE TRIGGER
Triggering Events
BEFORE / AFTER ALTER BEFORE / AFTER ANALYZE BEFORE / AFTER ASSOCIATE STATISTICS BEFORE / AFTER AUDIT BEFORE / AFTER COMMENT BEFORE / AFTER CREATE
BEFORE / AFTER DDL
BEFORE / AFTER DISASSOCIATE STATISTICS
BEFORE / AFTER DROP BEFORE / AFTER GRANT BEFORE / AFTER NOAUDIT BEFORE / AFTER RENAME BEFORE / AFTER REVOKE BEFORE / AFTER TRUNCATE INSTEAD OF CREATE (schema level only)
Find Database Level Triggers
SELECT a.obj#, a.sys_evts, b.name
FROM trigger$ a,obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject = 0;
Find Schema Level Triggers
SELECT a.obj#, a.sys_evts, b.name
FROM trigger$ a,obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject = 88;
Demo Setup
Demo Privileges
-- user uwclass creation can be found on the SETUP page of the library
conn sys@pdbdev as sysdba
GRANT create session TO uwclass;
GRANT create procedure TO uwclass;
GRANT create sequence TO uwclass;
GRANT create table TO uwclass;
GRANT create trigger TO uwclass;
GRANT create view TO uwclass;
CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action>
ON <SCHEMA | DATABASE>
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
-- exception handler
END <trigger_name>;
/
CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, NULL, USER, SYSDATE
FROM dual;
END bcs_trigger;
/
col object_name format a20
SELECT object_name, object_type
FROM user_objects;
col triggering_event format a20
SELECT trigger_name, trigger_type,
triggering_event, base_object_type
FROM user_triggers;
SELECT * FROM ddl_log;
CREATE SEQUENCE s1_test;
CREATE TABLE t1_test (
testcol VARCHAR2(20));
CREATE OR REPLACE VIEW v_test AS
SELECT * FROM t_test;
set linesize 151
SELECT operation, obj_owner, object_name
FROM ddl_log;
TRUNCATE TABLE ddl_log;
conn system/manager@pdbdev
CREATE TABLE uwclass.xyz (
testcol VARCHAR2(20));
conn uwclass/uwclass@pdbdev
SELECT operation, obj_owner, object_name
FROM ddl_log;
TRUNCATE TABLE ddl_log;
Trigger To Log A Single DDL Activity On The Database
CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action>
ON <SCHEMA | DATABASE>
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
-- exception handler
END <trigger_name>;
/
conn system/manager@pdbdev
GRANT administer database trigger TO uwclass;
conn uwclass/uwclass
CREATE OR REPLACE TRIGGER bcd_trigger
BEFORE CREATE
ON DATABASE
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, NULL, USER, SYSDATE
FROM dual;
END bcd_trigger;
/
col object_name format a20
SELECT object_name, object_type
FROM user_objects;
col triggering_event format a20
SELECT trigger_name, trigger_type,
triggering_event, base_object_type
FROM user_triggers;
SELECT * FROM ddl_log;
CREATE SEQUENCE s2_test;
CREATE TABLE t2_test (
testcol VARCHAR2(20));
CREATE OR REPLACE VIEW v_test AS
SELECT * FROM t_test;
set linesize 150
SELECT operation, obj_owner, object_name
FROM ddl_log;
TRUNCATE TABLE ddl_log;
conn system/manager@pdbdev
CREATE TABLE uwclass.xyz(
testcol VARCHAR2(20));
conn uwclass/uwclass@pdbdev
SELECT operation, obj_owner, object_name
FROM ddl_log;
DROP TRIGGER bcd_trigger;
TRUNCATE TABLE ddl_log;
Trigger To Log Multiple DDL Activities
CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action> OR <trigger_action>
ON SCHEMA
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
-- exception handlers
END <trigger_name>;
/
desc ddl_log
CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP
ON SCHEMA
DECLARE
oper ddl_log.operation%TYPE;
sql_text ora_name_list_t;
i PLS_INTEGER;
BEGIN
SELECT ora_sysevent
INTO oper
FROM dual;
i := sql_txt(sql_text);
IF oper IN ('CREATE', 'DROP') THEN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, sql_text(1), USER, SYSDATE
FROM dual;
ELSIF oper = 'ALTER' THEN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, sql_text(1), USER, SYSDATE
FROM sys.gv_$sqltext
WHERE UPPER(sql_text) LIKE 'ALTER%'
AND UPPER(sql_text) LIKE '%NEW_TABLE%';
END IF;
END ddl_trigger;
/
col operation format a20
col obj_owner format a10
SELECT * FROM ddl_log;
conn sys@pdbdev as sysdba
alter system flush shared_pool;
alter system flush shared_pool;
conn uwclass/uwclass
CREATE TABLE new_table (
charcol VARCHAR2(20));
SELECT * FROM ddl_log;
ALTER TABLE new_table
ADD (numbcol NUMBER(10));
SELECT * FROM ddl_log;
DROP TABLE new_table PURGE;
SELECT * FROM ddl_log;
TRUNCATE TABLE ddl_log;
Demo Triggers
DDL Trigger To Stop And Log Attempts To Drop Or Truncate
Requires the stored procedure below is created first
-- NOTE: Be sure to build the log_proc procedure (below) before trying to create this trigger
CREATE OR REPLACE TRIGGER save_our_db
BEFORE DROP OR TRUNCATE
ON SCHEMA
DECLARE
oper ddl_log.operation%TYPE;
BEGIN
oper := ora_sysevent;
log_proc(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name);
IF oper = 'DROP' THEN
RAISE_APPLICATION_ERROR(-20998, 'Attempt To Drop
In Production Has Been Logged');
ELSIF oper = 'TRUNCATE' THEN
RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A
Production Table Has Been Logged');
END IF;
END save_our_db;
/
SELECT * FROM ddl_log;
DROP TRIGGER ddl_trigger;
SELECT * FROM ddl_log;
ALTER TRIGGER save_our_db DISABLE;
DROP TRIGGER ddl_trigger;
SELECT * FROM ddl_log;
TRUNCATE TABLE ddl_log;
SELECT * FROM ddl_log;
ALTER TRIGGER save_our_db ENABLE;
DROP VIEW v_test;
SELECT * FROM ddl_log;
SELECT object_name FROM user_objects;
DROP SEQUENCE v1_test;
SELECT * FROM ddl_log;
SELECT object_name FROM user_objects;
DROP TRIGGER save_our_db;
TRUNCATE TABLE ddl_log;
Logging Procedure
CREATE OR REPLACE PROCEDURE log_proc (
ose ddl_log.operation%TYPE,
odoo ddl_log.obj_owner%TYPE,
odon ddl_log.object_name%TYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, 'Not Allowed', USER, SYSDATE
FROM dual;
COMMIT;
END log_proc;
/
DDL Trigger To Prevent Creating Objects That Whose Names Begin With The Letter 'X'
CREATE OR REPLACE TRIGGER no_xtabs
BEFORE CREATE
ON SCHEMA
DECLARE
x user_tables.table_name%TYPE;
BEGIN
SELECT ora_dict_obj_name
INTO x
FROM dual;
IF SUBSTR(x, 1, 1) = 'X' THEN
RAISE_APPLICATION_ERROR(-20099, 'Table Names Can Not Start With The Letter X');
END IF;
END no_xtabs;
/
CREATE TABLE ztest (
testcol VARCHAR2(20));
CREATE TABLE xtest (
testcol VARCHAR2(20));
Demo of an application that identifies and logs all DDL
conn sys@pdbdev as sysdba
CREATE TABLESPACE logging
DATAFILE 'c:\temp\logtsp01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 64K;
CREATE USER loguser
IDENTIFIED BY loguser
DEFAULT TABLESPACE logging
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA UNLIMITED ON logging;
-- note that no system privileges have been granted the table is being built by SYS
CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
BEGIN
INSERT INTO loguser.ddl_log
(user_name, ddl_date, ddl_type,
object_type, owner,
object_name)
VALUES
(ora_login_user, SYSDATE, ora_sysevent,
ora_dict_obj_type, ora_dict_obj_owner,
ora_dict_obj_name);
END ddl_trig;
/
conn uwclass/uwclass@pdbdev
CREATE TABLE t3_test (
testcol DATE);
conn / as sysdba
SELECT * FROM loguser.ddl_log;
Disable granting privileges to PUBLIC
GRANT ALL ON servers TO SCOTT;
GRANT ALL ON servers TO PUBLIC;
REVOKE ALL ON servers FROM SCOTT;
REVOKE ALL ON servers FROM PUBLIC;
CREATE OR REPLACE TRIGGER ddl_trig
BEFORE GRANT
ON DATABASE
DECLARE
g_list dbms_standard.ora_name_list_t;
n PLS_INTEGER;
BEGIN
n := ora_grantee(g_list);
FOR i IN 1..n LOOP
IF g_list(i) = 'PUBLIC' THEN
RAISE_APPLICATION_ERROR(-20997,'Public Grants Not Allowed');
END IF;
END LOOP;
END;
/
GRANT ALL ON servers TO SCOTT;
GRANT ALL ON servers TO PUBLIC;
REVOKE ALL ON servers FROM SCOTT;
REVOKE ALL ON servers FROM PUBLIC;
Disable granting privileges not part of the standard application image
conn sys@pdbdev as sysdba
CREATE OR REPLACE TRIGGER grant_control
BEFORE GRANT
ON DATABASE
DECLARE
n_list dbms_standard.ora_name_list_t;
n PLS_INTEGER;
lValid BOOLEAN := FALSE;
TYPE p_array IS TABLE OF VARCHAR2(30);
p_list p_array;
BEGIN
n := dbms_standard.privilege_list(n_list);
IF n IS NOT NULL THEN
FOR i IN 1..n_list.LAST LOOP
p_list := p_array('CONNECT',
'RESOURCE',
'CREATE ANY CONTEXT',
'CREATE ANY TYPE',
'UNLIMTED TABLESPACE');
FOR j IN 1..p_list.LAST LOOP
IF p_list(j) = n_list(i) THEN
dbms_output.put_line('Grant The Priv');
ELSE
RAISE_APPLICATION_ERROR(-20997,'CAB Approval Required');
END IF;
END LOOP;
END LOOP;
END IF;
END;
/
