Os explicamos en este artículo en qué consiste la auditoría de una base de datos Oracle. Para el ejemplo utilizamos Oracle Database 11g. Os mostramos para qué sirve, cómo consultar los datos de auditoría generados por Oracle y cómo desactivar esta opción y volver a activarla.

Definición de Auditoría informática

La auditoría informática es el proceso de recoger, agrupar y evaluar evidencias para determinar si un Sistema de Información salvaguarda el activo empresarial, mantiene la integridad de los datos, lleva a cabo eficazmente los fines de la organización, utiliza eficientemente los recursos, y cumple con las leyes y regulaciones establecidas. También permite detectar de forma sistemática el uso de los recursos y los flujos de información dentro de una organización y determinar qué información es crítica para el cumplimiento de su misión y objetivos, identificando necesidades, duplicidades, costes, valor y barreras, que obstaculizan flujos de información eficientes.

Auditar consiste principalmente en estudiar los mecanismos de control que están implantados en una empresa u organización, determinando si los mismos son adecuados y cumplen unos determinados objetivos o estrategias, estableciendo los cambios que se deberían realizar para la consecución de los mismos. Los mecanismos de control pueden ser directivos, preventivos, de detección, correctivos o de recuperación ante una contingencia.

Definición de Auditoría para Oracle

En el caso de Oracle Database, la auditoría es un conjunto de características que permite al administrador de la base de datos y a los usuarios hacer un seguimiento del uso de la base de datos. El administrador de base de datos puede definir la actividad de auditoría predeterminada. La información de las auditorías se almacena en el diccionario de datos, en la tablaSYS.AUD$ o en la pista de auditoría del sistema operativo (si lo permite). Lo anterior viene definido en el parámetro audit_trail.

Se pueden auditar tres tipos de acciones: intentos de inicio de sesión, accesos a objetos y acciones de la base de datos. Cuando se realizan auditorías, la funcionalidad de la base de datos es dejar constancia de los comandos correctos e incorrectos. Esto puede modificarse cuando se configura cada tipo de auditoría.

Por ejemplo, se pueden registrar todos los intentos de actualizar los datos de una tabla o sólo los intentos fallidos, también se pueden registrar todos los inicios de sesión en Oracle o sólo los intentos fallidos.

En qué consiste la auditoría en Oracle, tablas y vistas que intervienen

Oracle Database almacena en el diccionario de datos, en la tabla SYS.AUD$ o en la pista de auditoría del sistema operativo (si lo permite). Existen varias vistas que se basan en esta tabla (SYS.AUD$) para mostrar distintos resultados, según la información que se quiera obtener:

  • ALL_AUDIT_POLICIES
  • ALL_AUDIT_POLICY_COLUMNS
  • ALL_DEF_AUDIT_OPTS
  • ALL_REPAUDIT_ATTRIBUTE
  • ALL_REPAUDIT_COLUMN
  • APEX_DEVELOPER_AUDIT_LOG
  • 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
  • GV_$XML_AUDIT_TRAIL
  • KU$_AUDIT_DEFAULT_VIEW
  • KU$_AUDIT_OBJ_BASE_VIEW
  • KU$_AUDIT_OBJ_VIEW
  • KU$_AUDIT_VIEW
  • KU$_PROC_AUDIT_VIEW
  • KU$_PROCDEPOBJ_AUDIT_VIEW
  • KU$_PROCOBJ_AUDIT_VIEW
  • KU$_10_1_AUDIT_VIEW
  • MGMT$AUDIT_LOG
  • MGMT$ESA_AUDIT_SYSTEM_REPORT
  • SM$AUDIT_CONFIG
  • USER_AUDIT_OBJECT
  • USER_AUDIT_POLICIES
  • USER_AUDIT_POLICY_COLUMNS
  • USER_AUDIT_SESSION
  • USER_AUDIT_STATEMENT
  • USER_AUDIT_TRAIL
  • USER_OBJ_AUDIT_OPTS
  • USER_REPAUDIT_ATTRIBUTE
  • USER_REPAUDIT_COLUMN
  • V_$XML_AUDIT_TRAIL

Estas vistas se pueden ver ejecutando la consulta SQL:

SELECT view_name
FROM dba_views
WHERE view_name LIKE ‘%AUDIT%’
ORDER BY view_name

Las principales son:

  • DBA_AUDIT_OBJECT: guarda la información relativa a la auditoría de
  • DBA_AUDIT_SESSION: guarda la información relativa a la auditoría de los inicios de sesión de los usuarios.
  • DBA_AUDIT_TRAIL: muestra la auditoría estándar (de la tabla AUD$).
  • USER_AUDIT_TRAIL: muestra la auditoría estándar (de la tabla AUD$) relativa al usuario actual.
  • DBA_FGA_AUDIT_TRAIL: muestra información de auditoría de grano fino (obtenida de FGA_LOG$). La auditoría de grano fino (FGA) extiende la auditoría estándar y, además, captura la sentencia SQL que ha sido ejecutada.

Todo lo anterior estará condicionado al tipo de auditoría que se haya establecido para la base de datos Oracle, como se indica aquí.

Cómo comprobar si una instancia de Oracle tiene activada la auditoría

La activación de la auditoría en Oracle Database viene definida por el valor del parámetro: audit_trail. Para comprobar si la auditoría de la base de datos está activada ejecutaremos el siguiente comando SQL:

select name, value
from v$parameter
where name like ‘audit_trail’

Posibles valores del parámetro audit_trail:

  • none: desactiva la auditoría de la base de datos.
  • os: activa la auditoría de la base de datos. Los sucesos auditados se escribirán en la pista de auditoría del sistema operativo, no se auditará en Oracle sino en el sistema operativo anfitrión. Esta opción funcionará dependiendo del sistema operativo.
  • db: activa la auditoría y los datos se almacenarán en la taba SYS.AUD$ de Oracle.
  • db, extended: activa la auditoría y los datos se almacenarán en la taba SYS.AUD$ de Oracle. Además se escribirán los valores correspondientes en las columnas SQLBIND y SQLTEXT de la tabla SYS.AUD$.
  • xml: activa la auditoría de la base de datos, los sucesos será escritos en ficheros XML del sistema operativo.
  • xml, extended: activa la auditoría de la base de datos, los sucesos será escritos en el formato XML del sistema operativo, además se incluirán los valores de SqlText y SqlBind.

Para activar la auditoría:

ALTER SYSTEM SET audit_trail = «DB» SCOPE=SPFILE;

Para desactivar la auditoría ejecutaremos el siguiente comando:

ALTER SYSTEM SET audit_trail = «NONE» SCOPE=SPFILE;

Nota:

  • En Oracle 9i la auditoría viene desactivada por defecto, el valor del parámetro «audit_trail» está a «NONE».
  • En Oracle 11g la auditoría viene activada por defecto, el valor del parámetro «audit_trail» está a «DB».

Los comandos audit y noaudit

Comando audit

Funcionamiento comando audit

El comando audit permite iniciar los tipos de auditoría que a continuación se detallan. Este comando puede funcionar aunque no esté activada la auditoría de la base de datos, pero no dejará constancia, para que funcione correctamente es necesario que la auditoría esté activada, como se indica aquí.

Auditorías de inicio de sesión: cada intento de conexión con la base de datos por parte de un usuario (bien una aplicación externa o las aplicaciones del propio Oracle) puede ser auditado. El comando para iniciar la auditoría de los intentos de inicio de sesión es:

audit session;

El comando anterior auditará tanto los intentos fallidos como los aciertos. Para auditar sólo los intentos fallidos utilizaremos el comando:

audit session whenever not successful;

Para auditar sólo las conexiones correctas utilizaremos el comando:

audit session whenever successful;

Auditorías de acción: cualquier acción que afecte a un objeto de la base de datos (tabla, enlace de base de datos, espacio de tablas, sinónimo, segmento de anulación, usuario, índice, etc.) puede auditarse. Las posibles acciones que pueden auditarse (create, alter, drop) sobre estos objetos pueden agruparse para simplificar la cantidad de esfuerzo administrativo necesario para determinar y mantener las opciones de configuración de la auditoría. Por ejemplo, para auditar todos los comandos que afectan a los roles puede emplearse el comando SQL:

audit role;

Este comando activará la auditoría de las acciones: create role, alter role, drop role y set role.

También se puede ser más selectivo, por ejemplo, si queremos auditar a un usuario concreto cuando realiza la acción «update» ejecutaremos el siguiente comando:

audit update table by nombre_usuario;

De esta forma se activará la auditoría para el usuario «nombre_usuario» sólo cuando ejecute el comando «update» para cualquier tabla.

Auditorías de objeto: además de las acciones a nivel de sistema sobre objetos, también es posible auditar las acciones de manipulación de datos sobre objetos. Se pueden auditar operaciones de select, insert, update y delete sobre tablas. Este tipo de auditoría es similar a la anterior de auditoría de acción, la única diferencia es que el comando «audit» incorpora un parámetro nuevo «by session» (el registro de auditoría se escribirá una única vez por sesión) o «by access» (el registro de auditoría se escribirá cada vez que se acceda al objeto auditado).

Por ejemplo, para auditar los «insert» realizados sobre la tabla «facturacion» por acceso, el comando será:

audit insert on FACTURACION by access;

Nota: al indicar «by access» hay que tener cuidado pues registrará un suceso de auditoría por cada insert, esto puede afectar al rendimiento. De ser así siempre será mejor optar por «by session» que sólo registrará un suceso de auditoría por sesión, aunque es menos exhaustivo.

Otro ejemplo, para auditar todas las acciones realizadas en la tabla «contabilidad» por sesión utilizaremos el siguiente comando:

audit all on CONTABILIDAD by session;

El comando anterior auditará todas las acciones realizadas sobre la tabla FACTURACION (select, insert, update, delete), pero sólo un registro de auditoría por cada sesión.Otro ejemplo, para auditar las eliminaciones de registros de la tabla «nóminas»:

audit delete NOMINAS by access;

Prerrequisitos para poder ejecutar audit

Para activar la auditoría de las instrucciones SQL con el comando audit se necesita el privilegio de sistema AUDIT SYSTEM.

El usuario que se desee pueda activar la auditoría de objetos de un esquema, tiene que ser el propietario del objeto o disponer del privilegio de sistema AUDIT ANY. Además, si el objeto que eligió para la auditoría se ubica en un directorio, incluso habiéndolo creado uno mismo, se necesita el privilegio de sistema AUDIT ANY.

Para obtener los resultados de la auditoría hay que definir correctamente el parámetro de inicialización audit_trail. Se podrán definir las opciones de auditoría con el comando audit pero, si no está activada la auditoría en la base de datos, Oracle no generará los registros de auditoría.

Sintaxis comando audit

  • sql_statement_clause: activa la auditoría para una sentencia SQL concreta.
  • schema_object_clause: activa la auditoría para un objeto concreto de la base de datos.
  • WHENEVER SUCCESSFUL: activa la auditoría sólo para operaciones e instrucciones SQL en objetos de esquema que se completen con éxito.
  • WHENEVER NOT SUCCESSFUL: activa la auditoría sólo para operaciones e instrucciones SQL en objetos de esquema que originen error.

La sintaxis de este comando tiene muchas más opciones, disponibles en la ayuda de Oracle.

Comando noaudit

Funcionamiento comando noaudit

La instrucción noaudit se utiliza para detener la actividad de auditoría que se había activado previamente con la instrucción audit. Esta instrucción no influye en el parámetro audit_trail.

La instrucción noaudit debe tener la misma sintaxis que la instrucción audit que queramos detener. Por ejemplo, si hemos auditado un usuario con:

audit session by alonso;

Auditará los inicios de sesión para el usuario de Oracle «alonso», tanto los fallidos como los correctos. Para desactivar esta auditoría ejecutaremos el comando:

noaudit session by alonso;

Hay que tener en cuenta que el comando noaudit sólo desactiva la auditoría de su comando audit análogo. Por ejemplo, si ejecutamos este comando:

audit session by alonso;

Que auditará los inicios de sesión para el usuario «alonso». Y luego este otro:

audit session;

Que aditará los inicios de sesión para todos los usuarios.

Si ejecutásemos ahora el comando:

noaudit sesión;

Desactivaría el comando «audit session», pero seguría auditándose al usuario «alonso», puesto que aún estaría activo el comando «audit session by alonso».

Prerequisitos para poder ejecutar noaudit

Para detener la auditoría de las instrucciones SQL con el comando noaudit se necesita el privilegio de sistema AUDIT SYSTEM.

El usuario que se desee pueda detener la auditoría de objetos de un esquema, tiene que ser el propietario del objeto o disponer del privilegio de sistema AUDIT ANY. Además, si el objeto que eligió para la auditoría se ubica en un directorio, incluso habiéndolo creado uno mismo, se necesita el privilegio de sistema AUDIT ANY.

Sintaxis comando noaudit

  • sql_statement_clause: detiene la auditoría de una sentencia SQL concreta.
  • schema_object_clause: detiene la auditoría para un objeto concreto de la base de datos.
  • WHENEVER SUCCESSFUL: detiene la auditoría sólo para operaciones e instrucciones SQL en objetos de esquema que se completen con éxito.
  • WHENEVER NOT SUCCESSFUL: detiene la auditoría sólo para operaciones e instrucciones SQL en objetos de esquema que originen error.

La sintaxis de este comando tiene muchas más opciones, disponibles en la ayuda de Oracle.

Consultar los datos de auditoría guardados

Dependiendo del tipo de auditoría que queramos consultar utilizaremos una u otra consulta SQL.

Para el caso de la auditoría de inicio de sesión utilizaremos la siguiente consulta SQL:

Para el caso de la auditoría de acción utilizaremos la siguiente consulta SQL:

Montando el escenario, probando la auditoría de Oracle

En primer lugar accederemos a Oracle con un usuario con privilegios suficientes para crear usuarios y establecer permisos. Con este usuario crearemos un usuario en Oracle, para ello utilizaremos los cuatro comandos SQL siguientes:

A continuación accederemos a Oracle con el usuario «alonso» y crearemos una tabla para las pruebas, llamada «facturas», con el comando:

create table facturas (
codigo number primary key,
fecha date default sysdate);

Accederemos a Oracle con un usuario con privilegios suficientes para activar sucesos de auditoría y ejecutaremos el comando:

audit session by alonso;

para auditar el inicio de sesión de los usuarios.

Y ejecutaremos el comando:

audit all on facturas by access;

para auditar auditar los cambios realizados en la tabla «facturas» (update, delete, insert, select).

Volveremos a acceder a Oracle con el usuario «alonso» y realizaremos algunas inserciones, eliminaciones y select en la tabla «facturas»:

Ahora volveremos a acceder con un usuario con privilegios suficientes para consultar las vistas de auditoría y ejecutaremos la sentencia SQL para ver los accesos del usuario «alonso»:

y esta otra para ver las acciones realizadas a la tabla «facturas»:

Anexo

  • Para ejecutar las sentencias y comandos SQL anteriores se pueden utilizar:
    • Aplicaciones de terceros como TOAD.
    • Aplicación AjpdSoft Administración Bases de Datos, gratuita y disponible en esta web.
    • Utilizar la utilidad de ejecución de consultas SQL vía web (a partir de la versión 10g) del propio Oracle: Hoja de Trabajo de SQL.
  • Cambiar la tabla SYS.AUD$ a otro tablespace: la auditoría de la base de datos, si no se realiza con control, puede provocar efectos negativos en el rendimiento, además, la tabla AUD$ puede crecer considerablemente, por lo que es recomendable moverla a un tablespace distinto al de defecto: SYSTEM. Para realizar esto seguiremos los siguientes pasos:

En primer lugar crearemos el tablespace (si no lo hemos hecho ya) al que moveremos la tabla, podemos utilizar la siguiente consulta SQL para hacerlo:

Desactivaremos la auditoría de la base de datos con el comando:

A continuación moveremos la tabla al tablespace creado, con el comando SQL:

Nota: este proceso conviene realizarlo con el usuario SYS, accediendo como SYSOPER.

  • Estructura de la tabla SYS.AUD$:
CampoTipo de datosTamañoPermite nulos
SESSIONIDNUMBER22N
ENTRYIDNUMBER22N
STATEMENTNUMBER22N
TIMESTAMP#DATE7Y
USERIDVARCHAR230Y
USERHOSTVARCHAR2128Y
TERMINALVARCHAR2255Y
ACTION#NUMBER22N
RETURNCODENUMBER22N
OBJ$CREATORVARCHAR230Y
OBJ$NAMEVARCHAR2128Y
AUTH$PRIVILEGESVARCHAR216Y
AUTH$GRANTEEVARCHAR230Y
NEW$OWNERVARCHAR230Y
NEW$NAMEVARCHAR2128Y
SES$ACTIONSVARCHAR219Y
SES$TIDNUMBER22Y
LOGOFF$LREADNUMBER22Y
LOGOFF$PREADNUMBER22Y
LOGOFF$LWRITENUMBER22Y
LOGOFF$DEADNUMBER22Y
LOGOFF$TIMEDATE7Y
COMMENT$TEXTVARCHAR24000Y
CLIENTIDVARCHAR264Y
SPARE1VARCHAR2255Y
SPARE2NUMBER22Y
OBJ$LABELRAW255Y
SES$LABELRAW255Y
PRIV$USEDNUMBER22Y
SESSIONCPUNUMBER22Y
NTIMESTAMP#timestamp11Y
PROXY$SIDNUMBER22Y
USER$GUIDVARCHAR232Y
INSTANCE#NUMBER22Y
PROCESS#VARCHAR216Y
XIDRAW8Y
AUDITIDVARCHAR264Y
SCNNUMBER22Y
DBIDNUMBER22Y
SQLBINDCLOB4000Y
SQLTEXTCLOB4000Y
OBJ$EDITIONVARCHAR230Y
  • Posibles acciones a auditar, contenido de la vista audit_actions:
ACTIONNAME
0UNKNOWN
1CREATE TABLE
2INSERT
3SELECT
4CREATE CLUSTER
5ALTER CLUSTER
6UPDATE
7DELETE
8DROP CLUSTER
9CREATE INDEX
10DROP INDEX
11ALTER INDEX
12DROP TABLE
13CREATE SEQUENCE
14ALTER SEQUENCE
15ALTER TABLE
16DROP SEQUENCE
17GRANT OBJECT
18REVOKE OBJECT
19CREATE SYNONYM
20DROP SYNONYM
21CREATE VIEW
22DROP VIEW
23VALIDATE INDEX
24CREATE PROCEDURE
25ALTER PROCEDURE
26LOCK
27NO-OP
28RENAME
29COMMENT
30AUDIT OBJECT
31NOAUDIT OBJECT
32CREATE DATABASE LINK
33DROP DATABASE LINK
34CREATE DATABASE
35ALTER DATABASE
36CREATE ROLLBACK SEG
37ALTER ROLLBACK SEG
38DROP ROLLBACK SEG
39CREATE TABLESPACE
40ALTER TABLESPACE
41DROP TABLESPACE
42ALTER SESSION
43ALTER USER
44COMMIT
45ROLLBACK
46SAVEPOINT
47PL/SQL EXECUTE
48SET TRANSACTION
49ALTER SYSTEM
50EXPLAIN
51CREATE USER
52CREATE ROLE
53DROP USER
54DROP ROLE
55SET ROLE
56CREATE SCHEMA
57CREATE CONTROL FILE
59CREATE TRIGGER
60ALTER TRIGGER
61DROP TRIGGER
62ANALYZE TABLE
63ANALYZE INDEX
64ANALYZE CLUSTER
65CREATE PROFILE
66DROP PROFILE
67ALTER PROFILE
68DROP PROCEDURE
70ALTER RESOURCE COST
71CREATE MATERIALIZED VIEW LOG
72ALTER MATERIALIZED VIEW LOG
73DROP MATERIALIZED VIEW LOG
74CREATE MATERIALIZED VIEW
75ALTER MATERIALIZED VIEW
76DROP MATERIALIZED VIEW
77CREATE TYPE
78DROP TYPE
79ALTER ROLE
80ALTER TYPE
81CREATE TYPE BODY
82ALTER TYPE BODY
83DROP TYPE BODY
84DROP LIBRARY
85TRUNCATE TABLE
86TRUNCATE CLUSTER
91CREATE FUNCTION
92ALTER FUNCTION
93DROP FUNCTION
94CREATE PACKAGE
95ALTER PACKAGE
96DROP PACKAGE
97CREATE PACKAGE BODY
98ALTER PACKAGE BODY
99DROP PACKAGE BODY
100LOGON
101LOGOFF
102LOGOFF BY CLEANUP
103SESSION REC
104SYSTEM AUDIT
105SYSTEM NOAUDIT
106AUDIT DEFAULT
107NOAUDIT DEFAULT
108SYSTEM GRANT
109SYSTEM REVOKE
110CREATE PUBLIC SYNONYM
111DROP PUBLIC SYNONYM
112CREATE PUBLIC DATABASE LINK
113DROP PUBLIC DATABASE LINK
114GRANT ROLE
115REVOKE ROLE
116EXECUTE PROCEDURE
117USER COMMENT
118ENABLE TRIGGER
119DISABLE TRIGGER
120ENABLE ALL TRIGGERS
121DISABLE ALL TRIGGERS
122NETWORK ERROR
123EXECUTE TYPE
128FLASHBACK
129CREATE SESSION
130ALTER MINING MODEL
131SELECT MINING MODEL
133CREATE MINING MODEL
157CREATE DIRECTORY
158DROP DIRECTORY
159CREATE LIBRARY
160CREATE JAVA
161ALTER JAVA
162DROP JAVA
163CREATE OPERATOR
164CREATE INDEXTYPE
165DROP INDEXTYPE
166ALTER INDEXTYPE
167DROP OPERATOR
168ASSOCIATE STATISTICS
169DISASSOCIATE STATISTICS
170CALL METHOD
171CREATE SUMMARY
172ALTER SUMMARY
173DROP SUMMARY
174CREATE DIMENSION
175ALTER DIMENSION
176DROP DIMENSION
177CREATE CONTEXT
178DROP CONTEXT
179ALTER OUTLINE
180CREATE OUTLINE
181DROP OUTLINE
182UPDATE INDEXES
183ALTER OPERATOR
197PURGE USER_RECYCLEBIN
198PURGE DBA_RECYCLEBIN
199PURGE TABLESPACE
200PURGE TABLE
201PURGE INDEX
202UNDROP OBJECT
204FLASHBACK DATABASE
205FLASHBACK TABLE
206CREATE RESTORE POINT
207DROP RESTORE POINT
208PROXY AUTHENTICATION ONLY
209DECLARE REWRITE EQUIVALENCE
210ALTER REWRITE EQUIVALENCE
211DROP REWRITE EQUIVALENCE
212CREATE EDITION
213ALTER EDITION
214DROP EDITION
215DROP ASSEMBLY
216CREATE ASSEMBLY
217ALTER ASSEMBLY
218CREATE FLASHBACK ARCHIVE
219ALTER FLASHBACK ARCHIVE
220DROP FLASHBACK ARCHIVE
  • Algunos resultados tras la ejecución de comandos SQL:
    • Al ejecutar un «audit», si todo es correcto Oracle devolverá: «Audit succeeded».
    • Al ejecutar un «noaudit», si todo es correcto Oracle devolverá: «Noaudit succeeded».
    • Al ejecutar un «audit session by nombre_usuario», si no existe el usuario devolverá:

      ORA-01435: user does not exist

    • Al ejecutar un «audit», si el usuario con el que lo ejecutamos no tiene permisos suficientes mostrará el error:

      ORA-01031: privilegios insuficientes