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.
Purpose
Kernel extensions to the package STANDARD
AUTHID
DEFINER
Constants
Name
Data Type
Value
General
ORA_MAX_NAME_LEN
PLS_INTEGER
128
Data Types
-- 12.2
TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);
-- 12.2
TYPE ora_name_list_x IS TABLE OF VARCHAR2(2*(ORA_MAX_NAME_LEN+2)+1);
SUBTYPE dbms_id IS VARCHAR2(ORA_MAX_NAME_LEN);
SUBTYPE dbms_quoted_id IS VARCHAR2(ORA_MAX_NAME_LEN+2);
SUBTYPE dbms_id_30 IS VARCHAR2(30);
SUBTYPE dbms_quoted_id_30 IS VARCHAR2(32);
SUBTYPE dbms_id_128 IS VARCHAR2(ORA_MAX_NAME_LEN);
SUBTYPE dbms_quoted_id_128 IS VARCHAR2(ORA_MAX_NAME_LEN+2);
Dependencies
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_STANDARD'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_STANDARD';
DECLARE
cip VARCHAR2(30) := dbms_standard.client_ip_address;
BEGIN
IF LENGTH(cip) > 0 THEN
dbms_output.put_line(cip);
ELSE
dbms_output.put_line('Not Found');
END IF;
END;
/
dbms_standard.dictionary_obj_name_list(object_list OUT ora_name_list_t)
RETURN BINARY_INTEGER;
conn sys@pdbdev as sysdba
CREATE OR REPLACE FUNCTION ftest RETURN VARCHAR2 AUTHID CURRENT_USER 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 := dbms_standard.dictionary_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;
Returns the list of object owners affected by the event firing a DDL Event Trigger
dbms_standard.dictionary_obj_owner_list(owner_list OUT dbms_standard.ora_name_list_t)
RETURN BINARY_INTEGER;
conn sys@pdbdev as sysdba
CREATE TABLE uwclass.t (
testcol VARCHAR2(20));
CREATE INDEX scott.ix_t
ON uwclass.t(testcol);
CREATE INDEX hr.fbi_t
ON uwclass.t(UPPER(testcol));
CREATE OR REPLACE TRIGGER sysevent_trig
BEFORE DROP
ON DATABASE
DECLARE
olist_t ora_name_list_t;
x PLS_INTEGER;
BEGIN
x := dbms_standard.dictionary_obj_name_list(olist_t);
dbms_output.put_line('Return value: ' || TO_CHAR(x));
-- FOR i IN 1 .. x LOOP
-- dbms_output.put_line(olist_t(i));
-- END LOOP;
END sysevent_trig;
/
dbms_standard.grantee(user_list OUT ora_name_list_t)
RETURN BINARY_INTEGER;
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 := dbms_standard.grantee(g_list);
END IF;
dbms_output.put_line(TO_CHAR(n));
dbms_output.put_line(g_list(n));
END;
/
dbms_standard.original_sql_txt(sql_text OUT ora_name_list_t)
RETURN BINARY_INTEGER;
conn sys@pdbdev as sysdba
CREATE OR REPLACE TRIGGER test
AFTER GRANT
ON DATABASE
DECLARE
stmnt_list dbms_standard.ora_name_list_t;
n PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT') THEN
n := dbms_standard.original_sql_txt(stmnt_list);
dbms_output.put_line(TO_CHAR(n));
dbms_output.put_line(stmnt_list(n));
END IF;
END test;
/
Returns the text of th SQL statement that fired the trigger
dbms_standard.sql_txt(sql_text OUT ora_name_list_t)
RETURN BINARY_INTEGER;
conn sys@pdbdev as sysdba
CREATE OR REPLACE TRIGGER test
AFTER GRANT
ON DATABASE
DECLARE
stmnt_list dbms_standard.ora_name_list_t;
n PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT') THEN
n := sql_txt(stmnt_list);
dbms_output.put_line(n);
dbms_output.put_line(stmnt_list(n));
END IF;
END test;
/
Provides the UPDATING functionality in a table or instead-of trigger
Overload 1
dbms_standard.upating RETURN BOOLEAN;
See Table Triggers Demos Using Link At Page Bottom
Look for "IF UPDATING THEN"
Overload 2
dbms_standard.upating(colnam IN VARCHAR2)
RETURN BOOLEAN
conn uwclass/uwclass@pdbdev
CREATE TABLE t (
col1 VARCHAR2(10),
col2 VARCHAR2(10),
col3 VARCHAR2(10));
CREATE OR REPLACE TRIGGER updating_demo
BEFORE INSERT OR UPDATE OR DELETE
ON t
FOR EACH ROW
BEGIN
IF dbms_standard.updating('COL2') THEN
dbms_output.put_line('Updating Column 2');
END IF;
END updating_demo;
/
set serveroutput on
INSERT INTO t VALUES ('A', 'B', 'C');
UPDATE t SET col1 = 'Test';
UPDATE t SET col2 = 'Test';