| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsfga.sql |
| First Available |
9.0.1 |
| Constants |
| Name |
Data Type |
Value |
Description |
| EXTENDED |
PLS_INTEGER |
1 |
Includes SQL Text and SQL Bind |
| DB |
PLS_INTEGER |
2 |
Sends the audit trail to the SYS.FGA_LOG$ table in the database and omits SQL Text and SQL Bind. |
| DB_EXTENDED (default) |
PLS_INTEGER |
3 |
|
| XML |
PLS_INTEGER |
4 |
Writes the audit trail in XML files sent to the operating system and omits SQL Text and SQL Bind. |
| ALL_COLUMNS |
BINARY_INTEGER |
1 |
|
| ANY_COLUMNS (default) |
BINARY_INTEGER |
0 |
|
|
| Dependencies |
| ALL_AUDIT_POLICIES |
DBMS_FGA_LIB |
FGA_UTIL |
| DBA_AUDIT_POLICIES |
FGA$ |
GV$XML_AUDIT_TRAIL |
| DBA_FGA_AUDIT_TRAIL |
FGA_LOG$ |
USER_AUDIT_POLICIES |
|
| Initialization Parameters |
col name format a30
col value format a40
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%'; |
| Pragmas |
PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO) |
| Security Model |
Execute is granted to the EXECUTE_CATALOG_ROLE |
| Subprograms |
|
| |
ADD_POLICY (new 11.2.0.1 parameter)  |
| Create a new audit policy |
dbms_fga.add_policy(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
audit_condition IN VARCHAR2 := NULL,
audit_column IN VARCHAR2 := NULL,
handler_schema IN VARCHAR2 := NULL,
handler_module IN VARCHAR2 := NULL, -- alerting mechanism
enable IN BOOLEAN := NULL,
statement_types IN VARCHAR2 := 'SELECT',
audit_trail IN PLS_INTEGER := 3,
audit_column_opts IN BINARY_INTEGER DEFAULT 0,
policy_owner IN VARCHAR2 := NULL); |
exec dbms_fga.add_policy(
object_schema=>'UWCLASS',
object_name=> 'FGA_DEMO',
policy_name=> 'UW Audit',
audit_condition=> 'status = ''A''',
audit_column=> 'last_name, salary',
handler_schema => 'UWCLASS',
handler_module=> 'FGA_HANDLER',
enable => TRUE,
statement_types => 'INSERT, UPDATE'
audit_trail => DBMS_FGA.DB_EXTENDED,
audit_column_opts => dbms_fga.all_columns); |
| |
| DISABLE_POLICY |
| Disable an audit policy |
dbms_fga.disable_policy(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2); |
| exec dbms_fga.disable_policy('UWCLASS', 'emp', 'UWAudit'); |
| |
| DROP_POLICY |
| Drop an audit policy |
dbms_fga.drop_policy(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2); |
| exec dbms_fga.drop_policy('UWCLASS', 'FGA_DEMO', 'UWAudit'); |
| |
| ENABLE_POLICY |
| Enable or disable an audit policy |
dbms_fga.enable_policy(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2
enable IN BOOLEAN := TRUE); |
exec dbms_fga.enable_policy('UWCLASS', 'emp', 'UWAudit', TRUE);
set linesize 121
col audit_type format a20
col os_user format a25
col userhost format a15
col sql_bind format a20
SELECT audit_type, session_id, os_user, userhost, session_cpu, scn, sql_bind
FROM dba_common_audit_trail; |
| |
| DBMS_FGA Demo |
| As SYS |
conn / as sysdba
desc fga_log$
SELECT COUNT(*)
FROM fga_log$;
desc dba_common_audit_trail
SELECT COUNT(*)
FROM dba_common_audit_trail;
GRANT EXECUTE ON dbms_fga TO uwclass;
GRANT select ON dba_audit_policies TO uwclass;
GRANT select ON dba_fga_audit_trail TO uwclass;
col name format a30
col value format a40
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';
ALTER SYSTEM SET audit_sys_operations = TRUE SCOPE=SPFILE;
-- will require a restart so change it back
ALTER SYSTEM SET audit_sys_operations = FALSE SCOPE=SPFILE;
-- ALTER SYSTEM SET audit_file_dest = <dir> DEFERRED; |
| Session 1 |
/ as sysdba
desc fga_log$
SELECT COUNT(*)
FROM fga_log$;
GRANT EXECUTE ON dbms_fga TO uwclass;
GRANT select ON dba_audit_policies TO uwclass;
conn uwclass/uwclass
CREATE TABLE fga_demo
(
person_id NUMBER(5),
last_name VARCHAR2(25),
salary NUMBER(9,3),
status VARCHAR2(1));
ALTER TABLE fga_demo
ADD CONSTRAINT pk_fga_demo
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 0;
ALTER TABLE fga_demo
ADD CONSTRAINT cc_fga_demo_status
CHECK (status IN ('A','I'));
CREATE TABLE fga_tab (
owner VARCHAR2(30),
table_name VARCHAR2(30),
policy_name VARCHAR2(30));
CREATE OR REPLACE PROCEDURE fga_handler (
sname VARCHAR2, tname VARCHAR2, pname VARCHAR2) AUTHID DEFINER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO fga_tab
(owner, table_name, policy_name)
VALUES
(sname, tname, pname);
COMMIT;
END fga_handler;
/ |
exec dbms_fga.add_policy(object_schema=>'UWCLASS', object_name=> 'FGA_DEMO', policy_name=> 'UW_Audit', audit_condition=> 'status = ''A''', audit_column=> 'last_name, salary',
handler_schema => 'UWCLASS', handler_module=> 'FGA_HANDLER', enable => TRUE, statement_types => 'INSERT, UPDATE, SELECT',audit_trail => DBMS_FGA.DB_EXTENDED,
audit_column_opts => dbms_fga.all_columns);
desc dba_audit_policies
set linesize 140
col policy_text format a30
SELECT object_schema, object_name, policy_name
FROM dba_audit_policies;
SELECT policy_text, policy_column, enabled
FROM dba_audit_policies;
SELECT pf_schema, pf_package, pf_function
FROM dba_audit_policies;
SELECT sel, ins, upd, del, audit_trail, policy_column_options
FROM dba_audit_policies;
SELECT * FROM fga_tab;
SELECT * FROM fga_demo;
GRANT ALL on fga_demo TO abc;
-- run Session 2
SELECT * FROM fga_demo;
SELECT * FROM fga_tab; |
| Session 2 |
CREATE SYNONYM fga_demo FOR uwclass.fga_demo;
INSERT INTO fga_demo
(person_id, last_name, salary, status)
VALUES
(1, 'Morgan', 2500, 'A');
COMMIT;
UPDATE fga_demo
SET salary = salary * 1.05;
COMMIT;
UPDATE fga_demo
SET status = 'I';
COMMIT;
UPDATE fga_demo
SET salary = salary * 1.05;
COMMIT;
UPDATE fga_demo
SET status = 'A';
COMMIT;
UPDATE fga_demo
SET salary = salary * 1.05;
COMMIT; |
| Clean up |
conn / as sysdba
SELECT COUNT(*)
FROM fga_log$;
DELETE FROM fga_log$;
COMMIT; |
|
