Auditoria Oracle

PRUEBAS DE AUDITORIA DBMS_FGA


Hola a Todos

Si quieren una solución de software de Auditoría muy completa favor visitar este sitio estos locos son expertos en el tema tienen un producto excelente y muy buenos precios

www.oraconsultors.com



SQL> ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;

AUDIT_SYS_OPERATIONS = TRUE

SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = TRUE SCOPE =SPFILE;

SELECT * FROM ALL_AUDIT_POLICIES;
SELECT POLICY_NAME FROM ALL_AUDIT_POLICIES;

SELECT OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,POLICY_COLUMN,SEL,INS,UPD,DEL FROM ALL_AUDIT_POLICIES


Sistema modificado.

-- Clear down the audit trail.
CONN sys/password@db10g AS SYSDBA

ALTER TABLE AUD$ MOVE TABLESPACE USERS;

TRUNCATE TABLE fga_log$;
SELECT sql_text FROM dba_fga_audit_trail;

no rows selected.

-- Apply the policy to the SAL columnof the EMP table.
BEGIN
DBMS_FGA.add_policy(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'SAL_AUDIT',
audit_condition => NULL, -- Equivalent to TRUE
audit_column => 'SAL',
statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/

-- Test the auditing.
CONN scott/tiger@db10g
SELECT * FROM emp;
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
UPDATE emp SET sal = 10 WHERE empno = 9999;
DELETE emp WHERE empno = 9999;
ROLLBACK;

-- Check the audit trail.
SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------
SELECT * FROM emp
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1)
UPDATE emp SET sal = 10 WHERE empno = 9999
DELETE emp WHERE empno = 9999

4 rows selected.

-- Drop the policy.
CONN sys/password@db10g AS SYSDBA
BEGIN
DBMS_FGA.drop_policy(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'SAL_AUDIT');
END;
/

-- Configure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;

AUDIT ALL BY scott BY ACCESS;

-- Perform an auditable action.
CONN scott/tiger
UPDATE emp SET ename = ename;

-- Check the audit trail.
SELECT sql_text FROM dba_common_audit_trail;

SQL_TEXT
----------------------------
UPDATE emp SET ename = ename

1 row selected.

-- Unconfigure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=none SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;
-- Configure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;
AUDIT ALL BY scott BY ACCESS;

-- Perform an auditable action.
CONN scott/tiger
UPDATE emp SET ename = ename;

-- Check the audit trail.
SELECT sql_text FROM dba_common_audit_trail;

SQL_TEXT
----------------------------
UPDATE emp SET ename = ename

1 row selected.

-- Unconfigure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=none SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;

DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.

EJEMPLO PROBADO AUDITORIA

CONNECT sys/password AS SYSDBA

CREATE USER audit_test IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;


CONNECT sys/password AS SYSDBA

AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
CONN audit_test/password

CREATE TABLE test_tab (
id NUMBER
);

INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;

DROP TABLE test_tab;
SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

14 rows selected.
DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
· DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
· DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.


COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'AUDIT_TEST'
ORDER BY timestamp;

USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00 AUDIT_TEST TEST_TAB CREATE TABLE
AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00 AUDIT_TEST TEST_TAB INSERT
AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00 AUDIT_TEST TEST_TAB UPDATE
AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00 AUDIT_TEST TEST_TAB SELECT
AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00 AUDIT_TEST TEST_TAB DELETE
AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00 AUDIT_TEST TEST_TAB DROP TABLE

OLUMN db_user FORMAT A10
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT db_user,
extended_timestamp,
object_schema,
object_name,
action
FROM v$xml_audit_trail
WHERE object_schema = 'AUDIT_TEST'
ORDER BY extended_timestamp;

DB_USER EXTENDED_TIMESTAMP OBJECT_SCH OBJECT_NAM ACTION
---------- ----------------------------------- ---------- ---------- ----------
AUDIT_TEST 16-FEB-2006 14:14:33.417000 +00:00 AUDIT_TEST TEST_TAB 1
AUDIT_TEST 16-FEB-2006 14:14:33.464000 +00:00 AUDIT_TEST TEST_TAB 2
AUDIT_TEST 16-FEB-2006 14:14:33.511000 +00:00 AUDIT_TEST TEST_TAB 6
AUDIT_TEST 16-FEB-2006 14:14:33.542000 +00:00 AUDIT_TEST TEST_TAB 3
AUDIT_TEST 16-FEB-2006 14:14:33.605000 +00:00 AUDIT_TEST TEST_TAB 7
AUDIT_TEST 16-FEB-2006 14:14:34.917000 +00:00 AUDIT_TEST TEST_TAB 12

6 rows selected.

SQL>NOAUDIT ALL BY DBO;
SQL>NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY DBO;

MOVER AUDITORIA AUD$

ALTER TABLE AUD$ MOVE TABLESPACE USERS;

ALTER TABLE AUD$ MOVE TABLESPACE AUDITORIA;



drop table system.aud$_bu;

create table system.aud$_bu
tablespace tools
as (select * from sys.aud$ where 1=2);

drop table system.aud$_prev_quarter;

create table system.aud$_prev_quarter
tablespace tools
as (select * from sys.aud$ where 1=2);

CHEQUEAR TAMAÑO AUD$SIZE

Create procedure
keep_size_in_check
is
rowCount number;
begin
select count(*) into rowCount from sys.aud$ ;
if rowCount > 50000
then
commit;
set transaction use rollback segment rbs_bulk;
insert into system.aud$_bu (select * from sys.aud$);
delete from sys.aud$ ;
commit;
sys.dbms_system.ksdwrt (3,'ORA-AUDIT TRAIL: POSSIBLE DOS
ATTACK!!!! AUD$ rows moved to AUD$_BU');
end if;
end keep_size_in_check;

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'CHECK_SIZE_AUD$',
job_type => 'STORED_PROCEDURE',
job_action => ' keep_size_in_check',
start_date => SYSDATE,
repeat_interval => 'FREQ = WEEKLY');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE('CHECK_SIZE_AUD$');
END;
/

BEGIN
DBMS_SCHEDULER.RUN_JOB('CHECK_SIZE_AUD$',FALSE);
END;
/

BEGIN
DBMS_SCHEDULER.DROP_JOB('CHECK_SIZE_AUD$',FALSE);
END;
/



procedure quarterly_rotation
is

/* Procedure quarterly_rotation
* Cleans out table system.aud$_prev_quarter
* and repopulates it with the previous quarter's worth of audit info
* Should be scheduled to run every quarter
*/

begin
commit;
set transaction use rollback segment rbs_bulk;
delete from system.aud$_prev_quarter;
insert into system.aud$_prev_quarter
( select * from sys.aud$ au
where au.timestamp# < what =""> ‘manage_aud$.keep_size_in_check;’,
interval => ‘trunc(sysdate)+1/96’);
End;



Examples
Stop Auditing of SQL Statements Related to Roles: Example If you have chosen auditing for every SQL statement that creates or drops a role, then you can stop auditing of such statements by issuing the following statement:
NOAUDIT ROLE;
Stop Auditing of Updates or Queries on Objects Owned by a Particular User: Example If you have chosen auditing for any statement that queries or updates any table issued by the users hr and oe, then you can stop auditing for queries by hr by issuing the following statement:
NOAUDIT SELECT TABLE BY hr;

The preceding statement stops auditing only queries by hr, so the database continues to audit queries and updates by oe as well as updates by hr.
Stop Auditing of Statements Authorized by a Particular Object Privilege: Example To stop auditing on all statements that are authorized by DELETE ANY TABLE system privilege, issue the following statement:
NOAUDIT DELETE ANY TABLE;
Stop Auditing of Queries on a Particular Object: Example If you have chosen auditing for every SQL statement that queries the employees table in the schema hr, then you can stop auditing for such queries by issuing the following statement:
NOAUDIT SELECT
ON hr.employees;
Stop Auditing of Queries that Complete Successfully: Example You can stop auditing for queries that complete successfully by issuing the following statement:
NOAUDIT SELECT
ON hr.employees
WHENEVER SUCCESSFUL;

VER TODAS LAS POLITICAS
SELECT * FROM ALL_AUDIT_POLICIES;

SELECT OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,POLICY_COLUMN,SEL,INS,UPD,DEL FROM ALL_AUDIT_POLICIES


execute dbms_fga.drop_policy('DBO','PER_PERSONA','A_PER_PERSONA_TOTAL_DEVENGADO');
dbms_fga.enable_policy('DBO','PER_PERSONA','A_PER_PERSONA_TOTAL_DEVENGADO',TRUE);
dbms_fga.disable_policy('DBO','PER_PERSONA','A_PER_PERSONA_TOTAL_DEVENGADO');

PERFORMANCE QUERYS

SQL> EXPLAIN PLAN FOR SELECT * FROM PER_PERSONA WHERE ESTADO=1;
SQL> SELECT * FROM PLAN_TABLE

OTRA FORMA DE HACERLO ES

SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM PER_PERSONA;

SALE RESULTADO Y PLAN DE EJECUCION

SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM PER_PERSONA;

PERFORMANCE
SQL> SHOW PARAMETER CURSOR;
PARAMETR CURSOR_SHARING

SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 16M

SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS


per_persona_tuning_task



EJEMPLO DE VIRTUAL PRIVATE DATABASE


l Virtual Private Database (VPD) with Oracle
_uacct = "UA-221075-1";urchinTracker();
Virtual Private Database is also known as fine graind access control (FGAC). It allows to define which rows users may have access to.
� A simple example
In this example, it is assumed that a company consists of different departments (with each having an entry in the departments table). An employee belongs to exactly on department. A department can have secrets that go into the department_secrets table.
create table department (
dep_id int primary key,
name varchar2(30)
);

create table employee (
dep_id references department,
name varchar2(30)
);

create table department_secrets (
dep_id references department,
secret varchar2(30)
);
Filling in some truly confidential secrets:
insert into department values (1, 'Research and Development');
insert into department values (2, 'Sales' );
insert into department values (3, 'Human Resources' );

insert into employee values (2, 'Peter');
insert into employee values (3, 'Julia');
insert into employee values (3, 'Sandy');
insert into employee values (1, 'Frank');
insert into employee values (2, 'Eric' );
insert into employee values (1, 'Joel' );

insert into department_secrets values (1, 'R+D Secret #1' );
insert into department_secrets values (1, 'R+D Secret #2' );
insert into department_secrets values (2, 'Sales Secret #1');
insert into department_secrets values (2, 'Sales Secret #2');
insert into department_secrets values (3, 'HR Secret #1' );
insert into department_secrets values (3, 'HR Secret #2' );
For any employee, it must be possible to see all secrets of his department, but no secret of another department.
In order to make that happen with Oracle, we need to create a package, a trigger, and set a policy.
First, the package is created.
create or replace package pck_vpd
as
p_dep_id department.dep_id%type;

procedure set_dep_id(v_dep_id department.dep_id%type);

function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;
end pck_vpd;
/

create or replace package body pck_vpd as

procedure set_dep_id(v_dep_id department.dep_id%type) is
begin
p_dep_id := v_dep_id;
end set_dep_id;


function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is
begin
return 'dep_id = ' p_dep_id;
end predicate;

end pck_vpd;
/
Then the trigger is defined. This trigger fires whenever someone log on to the database. It finds the user's departement id (dep_id) and calls set_dep_id in the package.
create or replace trigger trg_vpd
after logon on database
declare
v_dep_id department.dep_id%type;
begin
select dep_id into v_dep_id
from employee where upper(name) = user;

pck_vpd.set_dep_id(v_dep_id);
end;
/
Finally, the policy is defined. The policy states which procedure is used to add a where clause part to the where clause if someone executes a select statement.
begin
dbms_rls.add_policy (
user,
'department_secrets',
'choosable policy name',
user,
'pck_vpd.predicate',
'select,update,delete');
end;
/
To test the setup, some users are created.
create user frank identified by frank default tablespace users temporary tablespace temp;
create user peter identified by peter default tablespace users temporary tablespace temp;
create user julia identified by julia default tablespace users temporary tablespace temp;
The necessary privileges are granted.
grant all on department_secrets to frank;
grant all on department_secrets to peter;
grant all on department_secrets to julia;

grant create session to frank;
grant create session to peter;
grant create session to julia;
A public synonym is created.
create public synonym department_secrets for department_secrets;
Frank (belonging to R+D) executes a query....
connect frank/frank;

select * from department_secrets;
DEP_ID SECRET
---------- ------------------------------
1 R+D Secret #1
1 R+D Secret #2
Peter (belonging to Sales) executes a query....
connect peter/peter;

select * from department_secrets;
DEP_ID SECRET
---------- ------------------------------
2 Sales Secret #1
2 Sales Secret #2

script ORACLE

SQL> connect dbo/power
Conectado.
SQL>
SQL> create table department (
dep_id int primary key,
name varchar2(30)
); 2 3 4

Tabla creada.

SQL> create table employee (
dep_id references department,
name varchar2(30)
); 2 3 4

Tabla creada.

SQL> create table department_secrets (
dep_id references department,
secret varchar2(30)
); 2 3 4

Tabla creada.

SQL> insert into department values (1, 'Research and Development');
insert into department values (2, 'Sales' );
insert into department values (3, 'Human Resources' );
1 fila creada.

SQL>
1 fila creada.

SQL> rollback;
insert into department values (3, 'Human Resources' );rollback
*
ERROR en linea 1:
ORA-00911: caracter no valido


SQL> rollback;

Rollback terminado.

SQL> insert into department values (1, 'Research and Development');

1 fila creada.

SQL> insert into department values (2, 'Sales' );

1 fila creada.

SQL> insert into department values (3, 'Human Resources' );

1 fila creada.

SQL> insert into employee values (2, 'Peter');

1 fila creada.

SQL> insert into employee values (3, 'Julia');

1 fila creada.

SQL> insert into employee values (3, 'Sandy');

1 fila creada.

SQL> insert into employee values (1, 'Frank');

1 fila creada.

SQL> insert into employee values (2, 'Eric' );

1 fila creada.

SQL> insert into employee values (1, 'Joel' );

1 fila creada.

SQL> insert into department_secrets values (1, 'R+D Secret #1' );

1 fila creada.

SQL> insert into department_secrets values (1, 'R+D Secret #2' );

1 fila creada.

SQL> insert into department_secrets values (2, 'Sales Secret #1');

1 fila creada.

SQL> insert into department_secrets values (2, 'Sales Secret #2');

1 fila creada.

SQL> insert into department_secrets values (3, 'HR Secret #1' );

1 fila creada.

SQL> insert into department_secrets values (3, 'HR Secret #2' );

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> create or replace package pck_vpd
as
p_dep_id department.dep_id%type;

procedure set_dep_id(v_dep_id department.dep_id%type);

function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;
end pck_vpd;
/ 2 3 4 5 6 7 8 9

Paquete creado.

SQL> create or replace package body pck_vpd as

procedure set_dep_id(v_dep_id department.dep_id%type) is
begin
p_dep_id := v_dep_id;
end set_dep_id;


function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is
begin
return 'dep_id = ' p_dep_id;
end predicate;

end pck_vpd;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Cuerpo del paquete creado.

SQL> create or replace trigger trg_vpd
after logon on database
declare
v_dep_id department.dep_id%type;
begin
select dep_id into v_dep_id
from employee where upper(name) = user;

pck_vpd.set_dep_id(v_dep_id);
end;
/ 2 3 4 5 6 7 8 9 10 11

Disparador creado.

SQL> begin
dbms_rls.add_policy (
user,
'department_secrets',
'choosable policy name',
user,
'pck_vpd.predicate',
'select,update,delete');
end;
/

2 3 4 5 6 7 8 9 10
Procedimiento PL/SQL terminado correctamente.

SQL> SQL> create user frank identified by frank default tablespace users temporary tablespace temp;

Usuario creado.

SQL> create user peter identified by peter default tablespace users temporary tablespace temp;

Usuario creado.

SQL> create user julia identified by julia default tablespace users temporary tablespace temp;

Usuario creado.

SQL> grant all on department_secrets to frank;

Concesion terminada correctamente.

SQL> grant all on department_secrets to peter;

Concesion terminada correctamente.

SQL> grant all on department_secrets to julia;

Concesion terminada correctamente.

SQL> grant create session to frank;

Concesion terminada correctamente.

SQL> grant create session to peter;

Concesion terminada correctamente.

SQL> grant create session to julia;

Concesion terminada correctamente.

SQL> create public synonym department_secrets for department_secrets;

Sinonimo creado.

SQL> connect frank/frank;
Conectado.
SQL> select * from department_secrets;

DEP_ID SECRET
---------- ------------------------------
1 R+D Secret #1
1 R+D Secret #2

SQL> connect peter/peter;
Conectado.
SQL> select * from department_secrets;

DEP_ID SECRET
---------- ------------------------------
2 Sales Secret #1
2 Sales Secret #2

SQL> EXECUTE DBMS_RLS.DROP_POLICY('DBO','department_secrets','choosable policy name');