| 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. |
| XML+EXTENDED |
PLS_INTEGER |
5 |
may no longer exist |
| ALL_COLUMNS |
BINARY_INTEGER |
1 |
|
| ANY_COLUMNS (default) |
BINARY_INTEGER |
0 |
|
|
| Dependencies |
| fga$ |
fga_log$
|
| all_audit_policies |
gv$xml_audit_trail |
|
dba_audit_policies |
user_audit_policies |
|
dba_fga_audit_trail |
|
|
| 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 |
| |
| ADD_POLICY |
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); |
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) 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',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; |
|
