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
DBA_TRIGGERS
USER_TRIGGERS
CDB_TRIGGERS
TRIGGER$
System Privileges
ADMINISTER DATABASE TRIGGER
CREATE ANY TRIGGER
DROP ANY TRIGGER
ALTER ANY TRIGGER
CREATE TRIGGER
System Event Trigger Types
AFTER CLONE -- may require the "ON PLUGGABLE DATABASE" syntax AFTER DB_ROLE_CHANGE -- for Data Guard failover and switchover AFTER LOGON AFTER SERVERERROR (does not trap ...
ORA-01403: no data found (this is in the Oracle docs but does not seem to be correct)
ORA-01422: exact fetch returns more than requested number of rows
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-01034: ORACLE not available
ORA-04030: out of process memory when trying to allocate string bytes (string, string)
AFTER STARTUP AFTER SUSPEND BEFORE / AFTER SET CONTAINER BEFORE LOGOFF BEFORE SHUTDOWN BEFORE UNPLUG -- may require the "ON PLUGGABLE DATABASE" syntax
Disabling System Triggers
If there is an error in a system trigger, for example an AFTER STARTUP trigger, it may be impossible to start the database. The following is the method for disabling system triggers.
conn sys@pdbdev as sysdba
set linesize 150
col NAME format a30
col VALUE format a20
col DESCRIPTION format a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm = '_system_trig_enabled';
ALTER SYSTEM SET "_system_trig_enabled" = TRUE SCOPE=BOTH;
Create System Event Trigger
System Trigger Demo
Demo table, procedure and trigger showing how the SET CONTAINER system trigger works
CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> TRIGGER <trigger_name>
<trigger_type> ON DATABASE
CALL <procedure_name>
/
CREATE OR REPLACE PROCEDURE cont_audit(trigName IN VARCHAR2) AUTHID DEFINER IS
BEGIN
INSERT INTO set_container_audit
(audit_date, trig_name, con_id)
VALUES
(SYSDATE, trigName, SYS_CONTEXT('USERENV', 'CON_ID'));
COMMIT;
END cont_audit;
/
CREATE OR REPLACE TRIGGER setcont_audit_B
BEFORE SET CONTAINER ON DATABASE
BEGIN
cont_audit('BEFORE');
END setcont_audit_B;
/
CREATE OR REPLACE TRIGGER setcont_audit_A
AFTER SET CONTAINER ON DATABASE
BEGIN
cont_audit('AFTER');
END setcont_audit_A;
/
ALTER SESSION SET CONTAINER=PDB$SEED;
-- pause 5 seconds for demo purposes
ALTER SESSION SET CONTAINER=CDB$ROOT;
SELECT * FROM set_container_audit;
DROP PROCEDURE cont_audit;
DROP TABLE set_container_audit PURGE;
Demo Application To Log Logon Attempts ... Both Successful And Failed
CREATE OR REPLACE <EDITIONABLE | NONEDITIONABLE> TRIGGER <trigger_name>
<trigger_type> ON DATABASE
<trigger_code>
END <trigger_name>;
/
TRUNCATE table connection_audit;
-- trigger to trap successful logons
CREATE OR REPLACE TRIGGER logon_audit
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO connection_audit
(login_date, user_name)
VALUES
(SYSDATE, USER);
END logon_audit;
/
conn scott/tiger@pdbdev
conn sh/sh@pdbdev
conn sys@pdbdev as sysdba
conn uwclass/uwclass@pdbdev
SELECT *
FROM connection_audit;
-- trigger to trap unsuccessful logons
CREATE OR REPLACE TRIGGER logon_failures
AFTER SERVERERROR
ON DATABASE
BEGIN
IF (IS_SERVERERROR(1017)) THEN
INSERT INTO connection_audit
(login_date, user_name)
VALUES
(SYSDATE, 'ORA-1017');
END IF;
END logon_failures;
/
/*
other errors that could be trapped include:
ORA-01004 - default username feature not supported
ORA-01005 - null password given
ORA-01035 - Oracle only available to users with restricted session priv
ORA-01045 - create session privilege not granted
*/
CREATE OR REPLACE TRIGGER ioct
INSTEAD OF CREATE ON SCHEMA
BEGIN
execute immediate 'CREATE TABLE scott.reports AS SELECT * FROM scott.emp WHERE 1=2';
END;
/
CREATE OR REPLACE TRIGGER start_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
execute immediate 'ALTER PLUGGABLE DATABASE ALL OPEN';
EXCEPTION
WHEN OTHERS THEN
NULL;
END start_all_pdbs;
/
INSERT INTO startup_pdbs
(pdb_name, start_flag)
VALUES
('PDBDEV', 'Y');
INSERT INTO startup_pdbs
(pdb_name, start_flag)
VALUES
('ORCL', 'N');
COMMIT;
CREATE OR REPLACE TRIGGER pdb_start_trig
AFTER STARTUP
ON DATABASE
BEGIN
FOR r IN (SELECT pdb_name FROM startup_pdbs WHERE start_flag = 'Y') LOOP
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ' || r.pdb_name || ' OPEN';
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END pdb_start_trig;
/
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
captured_sql VARCHAR2(1000);
BEGIN
SELECT q.sql_text
INTO captured_sql
FROM gv$sql q, gv$sql_cursor c, gv$session s
WHERE s.audsid = audsid
AND s.prev_sql_addr = q.address
AND q.address = c.parent_handle;
INSERT INTO servererror_log
(error_datetime, error_user, db_name,
error_stack, captured_sql)
VALUES
(systimestamp, sys.login_user, sys.database_name,
dbms_utility.format_error_stack, captured_sql);
END log_server_errors;
/
After Logon Trigger for Tracing
CREATE OR REPLACE TRIGGER trace_trig
AFTER LOGON
ON DATABASE
DECLARE
sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
BEGIN
IF (USER = 'UWCLASS') THEN
execute immediate sqlstr;
END IF;
END trace_trig;
/
After Logon Trigger for Outlines
CREATE OR REPLACE TRIGGER trace_trig
AFTER LOGON
ON DATABASE
DECLARE
sqlstr VARCHAR2(200) := 'alter session set use_stored_outlines = uw_outlines';
BEGIN
IF (USER = 'UWCLASS') THEN
execute immediate sqlstr;
END IF;
END trace_trig;
/
Related Queries
Database Level Event 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;
Schema Level Event 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;
