Essa semana houve uma demanda específica para configurar e trabalhar com auditoria no banco de dados Oracle, desta forma irei desvendar como utilizar a auditoria em servidores com edição Standard e Enterprise.
Oracle Standard Database – Nesta edição temos a opção de auditoria com baixa flexibilidade de ações, mas que em geral pode ajudar sua empresa a atender diversas demandas de auditoria de segurança.
Oracle Enterprise Database – Nesta edição temos a opção de auditar com ações flexíveis para ser auditado utilizando Fine Grained Auditing (FGA), nesta edição temos várias possibilidades de trabalhar com auditoria.
Como boa prática, vamos criar um tablespace e mover os objetos de auditoria para esse tablespace, evitando assim que os tablespaces do sistema cresçam muito e dificultem nossa manutenção.
1.1 – Verifique a tablespace que estão localizados os objetos utilizados na auditoria.
set colsep " | "
SET LINESIZE 145
SET PAGESIZE 9999
SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME FROM dba_segments WHERE SEGMENT_NAME in ('AUD$','FGA$');
SEGMENT_NAME | TABLESPACE_NAME
--------------------------------------------------------------------------------- | ------------------------------
AUD$ | TBS_AUDITORIA
FGA$ | SYSTEM
SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');
SEGMENT_NAME | SIZE_IN_MEGABYTES
--------------------------------------------------------------------------------- | -----------------
FGA_LOG$ | .0625
AUD$ 1.2 – Criação da tablespace TBS_AUDITORIA para utilização dos objetos audit.
CREATE TABLESPACE "TBS_AUDITORIA" DATAFILE '+DATA' SIZE 1G;1.3 – Movendo objetos de auditoria para a tablespace TBS_AUDITORIA.
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TBS_AUDITORIA');
END;
/
-- MOVENDO FGA_LOG$ PARA TABLESPACE TBS_AUDITORIA
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'TBS_AUDITORIA');
END;
/1.4 – Verificação dos objetos de auditoria na tablespace criada TBS_AUDITORIA.
set colsep " | "
SET LINESIZE 145
SET PAGESIZE 9999
SELECT SEGMENT_NAME,TABLESPACE_NAME,segment_type FROM dba_segments WHERE SEGMENT_NAME in ('AUD$','FGA$');
SEGMENT_NAME | TABLESPACE_NAME | SEGMENT_TYPE
--------------------------------------------------------------------------------- | ------------------------------ | ------------------
AUD$ | TBS_AUDITORIA | TABLE
FGA$ | SYSTEM | TABLE
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME | TABLESPACE_NAME
------------------------------ | ------------------------------
AUD$ | TBS_AUDITORIA
FGA_LOG$ | TBS_AUDITORIA
SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');
SEGMENT_NAME | SIZE_IN_MEGABYTES
--------------------------------------------------------------------------------- | -----------------
FGA_LOG$ | .0625
AUD$ | .68752 – Como habilitar e usar a auditoria na edição Standard.
/*AUDIT_TRAIL enables or disables database auditing.
Values:
none
Disables database auditing.
=======================
os
Enables database auditing and directs all audit records to the operating system's audit trail.
db
=======================
Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table).
db,extended
=======================
Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.
xml
=======================
Enables database auditing and writes all audit records to XML format OS files.
xml,extended
=======================
Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.
*/
2.1 – Antes de alterar os parâmetros, temos que ter em mente os valores que podem ser usados, como vamos configurar a auditoria Standard salvando o sql executado, devemos usar a opção db, extended.
SQL> show parameter audit;
NAME | TYPE | VALUE
------------------------------------ | ----------- | ------------------------------
audit_file_dest | string | /u01/app/oracle/admin/ora11g/a
| | dump
audit_sys_operations | boolean | FALSE
audit_syslog_level | string |
audit_trail | string | NONE
SQL>
SQL>
SQL> alter system set audit_trail=db,extended SCOPE=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area | 417546240 | bytes
Fixed Size | 2253824 | bytes
Variable Size | 180358144 | bytes
Database Buffers | 230686720 | bytes
Redo Buffers | 4247552 | bytes
Database mounted.
Database opened.
SQL> show parameter audit;
NAME | TYPE | VALUE
------------------------------------ | ----------- | ------------------------------
audit_file_dest | string | /u01/app/oracle/admin/ora11g/a
| | dump
audit_sys_operations | boolean | FALSE
audit_syslog_level | string |
audit_trail | string | DB, EXTENDED2.2 – Se você deseja habilitar a auditoria de eventos SYS.
ALTER SYSTEM SET audit_sys_operations=true SCOPE=spfile;3 – Habilitando auditoria de DDL dos usuários CESAR_TEST1 e CESAR_TEST2.
SQL>
AUDIT ALL BY CESAR_TEST1 BY ACCESS;
Audit succeeded.
SQL> AUDIT ALL BY CESAR_TEST2 BY ACCESS;
Audit succeeded.4 – Habilitando auditoria DML e DDL dos usuários CESAR_TEST1 e CESAR_TEST2.
SQL>
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR_TEST1 BY ACCESS;
Audit succeeded.
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR_TEST2 BY ACCESS;
Audit succeeded.5 – DBA_STMT_AUDIT_OPTS descreve as opções atuais de auditoria do sistema e por usuários.
SQL> SELECT * FROM dba_stmt_audit_opts ORDER BY audit_option;
USER_NAME | PROXY_NAME | AUDIT_OPTION | SUCCESS | FAILURE
------------------------------ | ------------------------------ | ---------------------------------------- | ---------- | ----------
| | ALTER ANY PROCEDURE | BY ACCESS | BY ACCESS
| | ALTER ANY TABLE | BY ACCESS | BY ACCESS
| | ALTER DATABASE | BY ACCESS | BY ACCESS
| | ALTER PROFILE | BY ACCESS | BY ACCESS
| | ALTER SYSTEM | BY ACCESS | BY ACCESS
CESAR_TEST1 | | ALTER SYSTEM | BY ACCESS | BY ACCESS
CESAR | | ALTER SYSTEM | BY ACCESS | BY ACCESS
CESAR_TEST2 | | ALTER SYSTEM | BY ACCESS | BY ACCESS
| | ALTER USER | BY ACCESS | BY ACCESS
CESAR_TEST2 | | CLUSTER | BY ACCESS | BY ACCESS
CESAR | | CLUSTER | BY ACCESS | BY ACCESS
CESAR_TEST1 | | CLUSTER | BY ACCESS | BY ACCESS
CESAR | | CONTEXT | BY ACCESS | BY ACCESS
CESAR_TEST1 | | CONTEXT | BY ACCESS | BY ACCESS
CESAR_TEST2 | | CONTEXT | BY ACCESS | BY ACCESS
| | CREATE ANY JOB | BY ACCESS | BY ACCESS
| | CREATE ANY LIBRARY | BY ACCESS | BY ACCESS
| | CREATE ANY PROCEDURE | BY ACCESS | BY ACCESS
| | CREATE ANY TABLE | BY ACCESS | BY ACCESS
| | CREATE EXTERNAL JOB | BY ACCESS | BY ACCESS
| | CREATE PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS
CESAR_TEST2 | | CREATE SESSION | BY ACCESS | BY ACCESS
| | CREATE SESSION | BY ACCESS | BY ACCESS
CESAR | | CREATE SESSION | BY ACCESS | BY ACCESS
CESAR_TEST1 | | CREATE SESSION | BY ACCESS | BY ACCESS
| | CREATE USER | BY ACCESS | BY ACCESS
CESAR_TEST1 | | DATABASE LINK | BY ACCESS | BY ACCESS
CESAR | | DATABASE LINK | BY ACCESS | BY ACCESS
| | DATABASE LINK | BY ACCESS | BY ACCESS
CESAR_TEST2 | | DATABASE LINK | BY ACCESS | BY ACCESS
CESAR_TEST2 | | DELETE TABLE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | DELETE TABLE | BY ACCESS | BY ACCESS
CESAR | | DELETE TABLE | BY ACCESS | BY ACCESS
CESAR | | DIMENSION | BY ACCESS | BY ACCESS
CESAR_TEST2 | | DIMENSION | BY ACCESS | BY ACCESS
CESAR_TEST1 | | DIMENSION | BY ACCESS | BY ACCESS
CESAR_TEST1 | | DIRECTORY | BY ACCESS | BY ACCESS
CESAR_TEST2 | | DIRECTORY | BY ACCESS | BY ACCESS
| | DIRECTORY | BY ACCESS | BY ACCESS
CESAR | | DIRECTORY | BY ACCESS | BY ACCESS
| | DROP ANY PROCEDURE | BY ACCESS | BY ACCESS
| | DROP ANY TABLE | BY ACCESS | BY ACCESS
| | DROP PROFILE | BY ACCESS | BY ACCESS
| | DROP USER | BY ACCESS | BY ACCESS
CESAR | | EXECUTE PROCEDURE | BY ACCESS | BY ACCESS
| | EXEMPT ACCESS POLICY | BY ACCESS | BY ACCESS
| | GRANT ANY OBJECT PRIVILEGE | BY ACCESS | BY ACCESS
| | GRANT ANY PRIVILEGE | BY ACCESS | BY ACCESS
| | GRANT ANY ROLE | BY ACCESS | BY ACCESS
CESAR | | INDEX | BY ACCESS | BY ACCESS
CESAR_TEST1 | | INDEX | BY ACCESS | BY ACCESS
CESAR_TEST2 | | INDEX | BY ACCESS | BY ACCESS
CESAR_TEST2 | | INSERT TABLE | BY ACCESS | BY ACCESS
CESAR | | INSERT TABLE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | INSERT TABLE | BY ACCESS | BY ACCESS
CESAR | | MATERIALIZED VIEW | BY ACCESS | BY ACCESS
CESAR_TEST1 | | MATERIALIZED VIEW | BY ACCESS | BY ACCESS
CESAR_TEST2 | | MATERIALIZED VIEW | BY ACCESS | BY ACCESS
CESAR_TEST2 | | MINING MODEL | BY ACCESS | BY ACCESS
CESAR | | MINING MODEL | BY ACCESS | BY ACCESS
CESAR_TEST1 | | MINING MODEL | BY ACCESS | BY ACCESS
CESAR | | NOT EXISTS | BY ACCESS | BY ACCESS
CESAR_TEST2 | | NOT EXISTS | BY ACCESS | BY ACCESS
CESAR_TEST1 | | NOT EXISTS | BY ACCESS | BY ACCESS
CESAR_TEST1 | | PROCEDURE | BY ACCESS | BY ACCESS
CESAR | | PROCEDURE | BY ACCESS | BY ACCESS
CESAR_TEST2 | | PROCEDURE | BY ACCESS | BY ACCESS
CESAR | | PROFILE | BY ACCESS | BY ACCESS
| | PROFILE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | PROFILE | BY ACCESS | BY ACCESS
CESAR_TEST2 | | PROFILE | BY ACCESS | BY ACCESS
CESAR | | PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS
CESAR_TEST1 | | PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS
CESAR_TEST2 | | PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS
CESAR_TEST1 | | PUBLIC SYNONYM | BY ACCESS | BY ACCESS
| | PUBLIC SYNONYM | BY ACCESS | BY ACCESS
CESAR_TEST2 | | PUBLIC SYNONYM | BY ACCESS | BY ACCESS
CESAR | | PUBLIC SYNONYM | BY ACCESS | BY ACCESS
| | ROLE | BY ACCESS | BY ACCESS
CESAR | | ROLE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | ROLE | BY ACCESS | BY ACCESS
CESAR_TEST2 | | ROLE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | ROLLBACK SEGMENT | BY ACCESS | BY ACCESS
CESAR_TEST2 | | ROLLBACK SEGMENT | BY ACCESS | BY ACCESS
CESAR | | ROLLBACK SEGMENT | BY ACCESS | BY ACCESS
CESAR_TEST1 | | SELECT TABLE | BY ACCESS | BY ACCESS
CESAR_TEST2 | | SELECT TABLE | BY ACCESS | BY ACCESS
CESAR | | SELECT TABLE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | SEQUENCE | BY ACCESS | BY ACCESS
CESAR | | SEQUENCE | BY ACCESS | BY ACCESS
CESAR_TEST2 | | SEQUENCE | BY ACCESS | BY ACCESS
CESAR | | SYNONYM | BY ACCESS | BY ACCESS
CESAR_TEST2 | | SYNONYM | BY ACCESS | BY ACCESS
CESAR_TEST1 | | SYNONYM | BY ACCESS | BY ACCESS
CESAR_TEST2 | | SYSTEM AUDIT | BY ACCESS | BY ACCESS
CESAR_TEST1 | | SYSTEM AUDIT | BY ACCESS | BY ACCESS
| | SYSTEM AUDIT | BY ACCESS | BY ACCESS
CESAR | | SYSTEM AUDIT | BY ACCESS | BY ACCESS
CESAR | | SYSTEM GRANT | BY ACCESS | BY ACCESS
CESAR_TEST2 | | SYSTEM GRANT | BY ACCESS | BY ACCESS
| | SYSTEM GRANT | BY ACCESS | BY ACCESS
CESAR_TEST1 | | SYSTEM GRANT | BY ACCESS | BY ACCESS
CESAR_TEST1 | | TABLE | BY ACCESS | BY ACCESS
CESAR | | TABLE | BY ACCESS | BY ACCESS
CESAR_TEST2 | | TABLE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | TABLESPACE | BY ACCESS | BY ACCESS
CESAR | | TABLESPACE | BY ACCESS | BY ACCESS
CESAR_TEST2 | | TABLESPACE | BY ACCESS | BY ACCESS
CESAR_TEST2 | | TRIGGER | BY ACCESS | BY ACCESS
CESAR | | TRIGGER | BY ACCESS | BY ACCESS
CESAR_TEST1 | | TRIGGER | BY ACCESS | BY ACCESS
CESAR_TEST2 | | TYPE | BY ACCESS | BY ACCESS
CESAR | | TYPE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | TYPE | BY ACCESS | BY ACCESS
CESAR | | UPDATE TABLE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | UPDATE TABLE | BY ACCESS | BY ACCESS
CESAR_TEST2 | | UPDATE TABLE | BY ACCESS | BY ACCESS
CESAR_TEST1 | | USER | BY ACCESS | BY ACCESS
CESAR_TEST2 | | USER | BY ACCESS | BY ACCESS
CESAR | | USER | BY ACCESS | BY ACCESS
CESAR | | VIEW | BY ACCESS | BY ACCESS
CESAR_TEST2 | | VIEW | BY ACCESS | BY ACCESS
CESAR_TEST1 | | VIEW | BY ACCESS | BY ACCESS
123 rows selected.6 – Faremos a conexão com os dois usuários auditados CESAR_TEST1 e CESAR_TEST1.
[oracle@lab11g ~]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 12 10:35:59 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: CESAR_TEST1
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
[oracle@lab11g ~]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 12 10:36:03 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: CESAR_TEST2
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>7 – Faremos algumas operações de inserts com o usuário CESAR_TEST1 e update com o usuário CESAR_TEST2.
[oracle@lab11g ~]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 12 10:35:59 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: CESAR_TEST1
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
insert into CESAR.TB_TESTE values (1);
1 row created.
SQL> commit;
Commit complete.
SQL>
[oracle@lab11g ~]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 12 10:36:03 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: CESAR_TEST2
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
update CESAR.TB_TESTE set id =10 where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL>
SQL> create table CESAR_TEST2.TB_TESTE2 (card number);
Table created.8 – Verificação da auditoria e operações realizadas.
SELECT
TO_CHAR (EXTENDED_TIMESTAMP,'DD-MM-RRRR HH24:MI:SS') DATA,
USERNAME, OS_USERNAME, USERHOST,TERMINAL,
OWNER,ACTION_NAME,OBJ_NAME,SQL_TEXT
FROM
DBA_AUDIT_TRAIL
WHERE USERNAME in ('CESAR_TEST1','CESAR_TEST2')
AND ACTION_NAME NOT LIKE '%SELECT%'
ORDER BY EXTENDED_TIMESTAMP DESC;
Trabalhando com Auditoria Fine Grained (FGA), esse recurso está disponível apenas na edição Enterprise.
Para este exemplo vamos demonstrar a flexibilidade das operações de auditoria na tabela salarial, onde estaremos auditando variações salariais acima de mil reais.
1 – FGA trabalhando com filtro salarial >=1000
BEGIN
DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'CESAR',
OBJECT_NAME => 'TB_SALARY',
POLICY_NAME => 'AUDITORIA_CESAR_TEST1', -- Name given to our policy.
AUDIT_CONDITION => ' SALARY >= 1000', -- Condition for the audit to record information, everything that is equal to or greater than 1000.
STATEMENT_TYPES => 'INSERT, UPDATE, DELETE, SELECT'); -- Operations that will be audited when column SALARY >=1000.
END;
/2 – Insira o salário 2000 com o usuário CESAR_TEST1.
insert into CESAR.TB_SALARY values (2000);
1 row created.
SQL> commit;
Commit complete.3 – Insira valores abaixo de 1000 onde não serão auditados.
SQL> insert into CESAR.TB_SALARY values (100);
1 row created.
SQL> commit;
Commit complete.
4 – Atualização de salário acima de 1000 com o usuário CESAR_TEST2.
update CESAR.TB_SALARY set SALARY =5000 where SALARY=2000;
1 row updated.
SQL> commit;
Commit complete.5 – Atualização do salário abaixo de 1000.
update CESAR.TB_SALARY set SALARY =50 where SALARY=5000;
1 row updated.
SQL> commit;
Commit complete.6 – Select das operações de SALÁRIO acima de 1000.
SELECT * FROM CESAR.TB_SALARY WHERE SALARY>=1000;
SELECT * FROM CESAR.TB_SALARY WHERE SALARY>=2000;7 – Consulta para verificar logs de auditoria com flexibilidade FGA.
SELECT TO_CHAR (EXTENDED_TIMESTAMP,'DD-MM-RRRR HH24:MI:SS') DATA,
DB_USER USUARIO,
OS_USER,
USERHOST HOST,
OBJECT_SCHEMA,
OBJECT_NAME,
STATEMENT_TYPE,
SQL_TEXT
FROM DBA_FGA_AUDIT_TRAIL
ORDER BY TIMESTAMP DESC;
Disabling auditing.
-- Disabling by user.
NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR_TEST1 BY ACCESS;
NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR_TEST2 BY ACCESS;-- Drop created FGA rule.
SELECT 'EXEC DBMS_FGA.DROP_POLICY('''||OBJECT_SCHEMA||''','''||OBJECT_NAME||''','''||POLICY_NAME||''');'
FROM DBA_AUDIT_POLICIES;-- Disabling at the database level.
show parameter audit;
alter system set audit_trail=none SCOPE=spfile;
shutdown immediate;
startup open;
show parameter audit;