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.
Be sure to view the full listing of monographs in Morgan's Library
Purpose
System Events are built-in functions that capture and return metadata about the execution of DDL. The object names used on this page ORA_... are the commonly used synonyms for the functions.
Note the close parallel between these events and corresponding SYS_CONTEXT calls and a number of functions owned by SYS (see links at page bottom).
May not work on single Windows machine: Thus the NVL
CREATE OR REPLACE FUNCTION client_ip_address RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.client_ip_address;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER LOGON
ON DATABASE
BEGIN
IF (ora_sysevent='LOGON') THEN
INSERT INTO event_log
(client_ipadd)
VALUES
(NVL(ora_client_ip_address, 'N/A'));
END IF;
COMMIT;
END sysevent_trig;
/
TRUNCATE TABLE event_log;
conn uwclass/uwclass@pdbdev
SELECT client_ipadd FROM event_log;
ORA_DATABASE_NAME
Database name
CREATE OR REPLACE FUNCTION database_name RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.database_name;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO event_log
(database_name)
VALUES
(ora_database_name);
COMMIT;
END sysevent_trig;
/
TRUNCATE TABLE event_log;
conn uwclass/uwclass@pdbdev
SELECT database_name FROM event_log;
ORA_DES_ENCRYPTED_PASSWORD
The DES encrypted password of the user being created or altered
CREATE OR REPLACE FUNCTION des_encrypted_password(
user IN VARVCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.des_encrypted_password(user);
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON DATABASE
BEGIN
INSERT INTO event_log
(database_name, encrypt_pwd)
VALUES
(ora_database_name, ora_des_encrypted_password);
COMMIT;
END sysevent_trig;
/
TRUNCATE TABLE event_log;
ALTER USER uwclass
IDENTIFIED BY sowhat;
ALTER USER uwclass
IDENTIFIED BY uwclass;
col encrypt_pwd format a40
SELECT database_name, encrypt_pwd FROM event_log;
ORA_DICT_OBJ_NAME
Name of the dictionary object on which the DDL operation occurred
CREATE OR REPLACE FUNCTION dictionary_obj_name RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.dictionary_obj_name;
END;
/
DROP TRIGGER sysevent_trig;
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
BEGIN
INSERT INTO event_log
(database_name, obj_name)
VALUES
(ora_database_name, ora_dict_obj_name);
COMMIT;
END sysevent_trig;
/
TRUNCATE TABLE event_log;
desc parent
ALTER TABLE parent
ADD (event_when TIMESTAMP(9));
desc parent
SELECT database_name, obj_name FROM event_log;
ORA_DICT_OBJ_NAME_LIST
Return the list of object names of objects being modified by the event
CREATE OR REPLACE FUNCTION dictionary_obj_name_list
(object_list OUT ora_name_list_t) RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.dictionary_obj_name_list(object_list);
END;
/
DROP TRIGGER sysevent_trig;
CREATE FUNCTION ftest RETURN VARCHAR2 IS
BEGIN
RETURN 'ZZYZX';
END ftest;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ASSOCIATE STATISTICS
ON DATABASE
DECLARE
nlist_t ora_name_list_t;
x PLS_INTEGER;
BEGIN
IF ora_sysevent='ASSOCIATE STATISTICS' THEN
x := ora_dict_obj_name_list(nlist_t);
END IF;
FOR i IN 1 .. x LOOP
dbms_output.put_line(nlist_t(i));
END LOOP;
END sysevent_trig;
/
set serveroutput on
ASSOCIATE STATISTICS WITH FUNCTIONS ftest DEFAULT SELECTIVITY 10;
ORA_DICT_OBJ_OWNER
Owner of the dictionary object on which the DDL operation occurred
CREATE OR REPLACE FUNCTION dictionary_obj_owner RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.dictionary_obj_owner;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
BEGIN
INSERT INTO event_log
(obj_owner)
VALUES
(ora_dict_obj_owner);
COMMIT;
END sysevent_trig;
/
desc parent
ALTER TABLE parent
MODIFY (event_when TIMESTAMP(6));
SELECT * FROM event_log;
ORA_DICT_OBJ_OWNER_LIST
Return the list of object owners of objects being modified by the event
CREATE OR REPLACE FUNCTION dictionary_obj_owner_list
(owner_list out ora_name_list_t)
RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.dictionary_obj_owner_list(owner_list);
END;
/
-- ora_dict_obj_name_list is a synonym for dictionary_obj_name_list.
DECLARE
x PLS_INTEGER;
olist_t ora_name_list_t; <- verify this for function
BEGIN
IF ora_sysevent = 'ASSOCIATE STATISTICS' THEN
x := ora_dict_obj_owner_list(olist_t);
END IF;
FOR i IN 1 .. x LOOP
dbms_output.put_line(olist_t(i));
END LOOP;
END;
/
ORA_DICT_OBJ_TYPE
Type of the dictionary object on which the DDL operation occurred
CREATE OR REPLACE FUNCTION dictionary_obj_type RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.dictionary_obj_type;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
BEGIN
INSERT INTO event_log
(obj_type)
VALUES
(ora_dict_obj_type);
COMMIT;
END sysevent_trig;
/
desc parent
ALTER TABLE parent
MODIFY (event_when DATE);
SELECT * FROM event_log;
ORA_GRANTEE
Return the grantees of a GRANT DCL statement
CREATE OR REPLACE FUNCTION grantee (user_list OUT ora_name_list_t)
RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.grantee(user_list);
END;
/
-- ora_dict_obj_name_list is a synonym for dictionary_obj_name_list.
conn sys@pdbdev as sysdba
CREATE OR REPLACE TRIGGER ddl_trig
BEFORE GRANT
ON DATABASE
DECLARE
g_list dbms_standard.ora_name_list_t;
n PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT') THEN
n := ora_grantee(g_list);
END IF;
dbms_output.put_line(TO_CHAR(n));
dbms_output.put_line(g_list(n));
END;
/
set serveroutput on
GRANT select ON uwclass.servers TO PUBLIC;
REVOKE select ON uwclass.servers FROM PUBLIC;
ORA_INSTANCE_NUM
Instance number
CREATE OR REPLACE FUNCTION instance_num RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.instance_num;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO event_log
(instance_num)
VALUES
(ora_instance_num);
COMMIT;
END sysevent_trig;
/
conn uwclass/uwclass@pdbdev
SELECT * FROM event_log;
ORA_IS_ALTER_COLUMN
Returns true if the specified column is altered
CREATE OR REPLACE FUNCTION is_alter_column(column_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
RETURN dbms_standard.is_alter_column(column_name);
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
BEFORE ALTER
ON SCHEMA
BEGIN
IF ora_is_alter_column('PERSON_ID') THEN
RAISE_APPLICATION_ERROR(-20001, 'Primary Key Column Can Not Be Altered');
END IF;
END sysevent_trig;
/
ALTER TABLE parent
MODIFY (last_name VARCHAR2(25));
ALTER TABLE parent
MODIFY (person_id NUMBER(6));
ORA_IS_CREATING_NESTED_TABLE
Returns true if the current event is creating a nested table
CREATE OR REPLACE FUNCTION is_creating_nested_table
RETURN BOOLEAN IS
BEGIN
RETURN dbms_standard.is_creating_nested_table;
END;
/
IF (ora_sysevent = 'CREATE'
AND ora_dict_obj_type = 'TABLE'
AND ora_is_creating_nested_table) THEN
INSERT INTO event_tab
(
VALUES
('A nested table is created');
END IF;
ORA_IS_DROP_COLUMN
Returns true if the specified column is dropped
CREATE OR REPLACE FUNCTION is_drop_column(column_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
RETURN dbms_standard.is_drop_column(column_name);
END;
/
conn uwclass/uwclass@pdbdev
CREATE TABLE t (
col1 DATE,
col2 DATE,
col3 DATE);
set serveroutput on
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
DECLARE
drop_col BOOLEAN;
BEGIN
IF (ora_sysevent = 'ALTER'
AND ora_dict_obj_type = 'TABLE') THEN
drop_col := ora_is_drop_column('COL2');
END IF;
IF drop_col THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/
ALTER TABLE t DROP COLUMN col3;
ALTER TABLE t DROP COLUMN col2;
ORA_IS_SERVERERROR
Returns TRUE if given error is on error stack
CREATE OR REPLACE FUNCTION is_servererror(errno IN BINARY_INTEGER)
RETURN BOOLEAN IS
BEGIN
RETURN dbms_standard.is_servererror(errno);
END;
/
set serveroutput on
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER DDL
ON DATABASE
DECLARE
error_number NUMBER := SQLCODE;
BEGIN
dbms_output.put_line(TO_CHAR(error_number));
IF ora_is_servererror(error_number) THEN
dbms_output.put_line('Oops!: ' || sqlerrm);
ELSE
dbms_output.put_line('Ok: ' || sqlerrm);
END IF;
END sysevent_trig;
/
CREATE TABLE t (col DATE);
ORA_LOGIN_USER
Login user name
CREATE OR REPLACE FUNCTION login_user RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.login_user;
END;
/
SELECT ora_login_user
FROM dual;
ORA_ORIGINAL_SQL_TXT
Returns the SQL text of the original
SQL statement triggering a server
error. The text may have been rewritten due to cursor sharing or SQL
translation and therefore different from the final text executed.
CREATE OR REPLACE FUNCTION original_sql_txt(sql_text
OUT ora_name_list_t)
RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.original_sql_txt(sql_text);
END;
/
Returns the list of privileges being granted by the grantee or the list of privileges revoked from the revokee in the OUT parameter; RETURNs the number of privileges in the RETURN value
CREATE OR REPLACE FUNCTION privilege_list(
priv_list OUT ora_name_list_t) RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.privilege_list(priv_list);
END;
/
IF (ora_sysevent = 'GRANT' OR ora_sysevent = 'REVOKE') THEN
numpriv := ora_privilege_list(priv_list);
END IF;
ORA_REVOKEE
Returns the revokees of a revoke event in the OUT parameter; RETURNs the number of revokees in the RETURN value
CREATE OR REPLACE FUNCTION revokee (user_list OUT ora_name_list_t) RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.revokee(user_list);
END;
/
IF (ora_sysevent = 'REVOKE') THEN
num_of_users := ora_revokee(user_list);
END IF;
ORA_SERVER_ERROR
Given a position (1 for top of stack), it RETURNs the error number at that position on error stack
CREATE OR REPLACE FUNCTION server_error (position IN BINARY_INTEGER)
RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.server_error(position);
END;
/
CREATE OR REPLACE TRIGGER afterdrop_trig
AFTER DROP
ON DATABASE
BEGIN
INSERT INTO event_log
(obj_name, server_error_num)
VALUES
(ora_dict_obj_name, ora_server_error(1));
END afterdrop_trig;
/
CREATE TABLE t(
testcol DATE);
DROP TABLE t PURGE;
SELECT * FROM event_log;
ORA_SERVER_ERROR_DEPTH
Returns the total number of error messages on the error stack
CREATE OR REPLACE FUNCTION server_error_depth
RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.server_error_depth;
END;
/
n := ora_server_error_depth;
ORA_SERVER_ERROR_MSG
Given a position (1 for top of stack), it RETURNs the error message at that position on error stack
CREATE OR REPLACE FUNCTION server_error_msg (position IN BINARY_INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.server_error_msg(position);
END;
/
CREATE OR REPLACE TRIGGER afterdrop_trig
AFTER DROP
ON DATABASE
BEGIN
INSERT INTO event_log
(obj_name, server_error_msg)
VALUES
(ora_dict_obj_name, NVL(ora_server_error_msg(1),'No Error'));
END afterdrop_trig;
/
CREATE TABLE t(
testcol DATE);
DROP TABLE t PURGE;
SELECT * FROM event_log;
ORA_SERVER_ERROR_NUM_PARAMS
Given a position (1 for top of stack), it RETURNs the number of strings that have been substituted into the error message using a format like "%s"
CREATE OR REPLACE FUNCTION server_error_num_params (
position IN BINARY_INTEGER) RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.server_error_num_params(position);
END;
/
n := ora_server_error_num_params(1);
ORA_SERVER_ERROR_PARAM
Given a position (1 for top of stack) and a parameter number, RETURNs the matching substitution value (%s, %d, and so on) in the error message
CREATE OR REPLACE FUNCTION server_error_param(
position IN BINARY_INTEGER, param IN BINARY_INTEGER)
RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.server_error_param(position, param);
END;
/
-- for example, the second %s in a message: "Expected %s, found %s" param := ora_server_error_param(1,2);
ORA_SPACE_ERROR_INFO aka SPACE_ERROR_INFO
Returns true if the error is related to an out-of-space condition, and fills in the OUT parameters with information about the object that caused the error
space_error_info(
error_number OUT NUMBER,
error_type OUT VARCHAR2,
object_owner OUT VARCHAR2,
table_space_name OUT VARCHAR2,
object_name OUT VARCHAR2,
sub_object_name OUT VARCHAR2);
IF (space_error_info(eno,typ,owner,ts,obj, subobj) = TRUE) THEN
dbms_output.put_line=('The object '|| obj || ' owned by ' || owner || ' has run out of space.');
END IF;
ORA_SQL_TXT
Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken into multiple PL/SQL table elements
The function RETURN value shows the number of elements are in the PL/SQL table.
CREATE OR REPLACE FUNCTION sql_txt (sql_text OUT ora_name_list_t)
RETURN BINARY_INTEGER IS
BEGIN
RETURN dbms_standard.sql_txt(sql_text);
END;
/
CREATE OR REPLACE TRIGGER afterdrop_trig
AFTER DROP
ON DATABASE
DECLARE
sql_text ora_name_list_t;
n NUMBER;
BEGIN
n := ora_sql_txt(sql_text);
INSERT INTO event_log
(obj_name, sql_txt)
VALUES
(ora_dict_obj_name, 'text of triggering statement: ' || sql_text(n));
END afterdrop_trig;
/
CREATE TABLE t(c DATE);
DROP TABLE t PURGE;
SELECT * FROM event_log;
ORA_SYSEVENT
System event firing the trigger: Event name is same as that in the syntax.
Returns a VARCHAR2(20)
CREATE OR REPLACE FUNCTION sysevent RETURN VARCHAR2 IS
BEGIN
RETURN dbms_standard.sysevent;
END;
/
See Undocumented Oracle Link At Page Bottom
ORA_WITH_GRANT_OPTION
Returns TRUE if the privileges are granted with grant option
CREATE OR REPLACE FUNCTION with_grant_option RETURN BOOLEAN IS
BEGIN
RETURN dbms_standard.with_grant_option;
END;
/
IF (ora_sysevent = 'GRANT' AND ora_with_grant_option = TRUE) THEN
INSERT INTO event_log
(grant_opt)
VALUES
('with grant option');
END IF;