Auditando la base de datos

En este post, intentaré explicar la auditoría de la base de datos Oracle. Como menciono más adelante, hay dos tipos, hacia archivos o en la misma base de datos. Nos enfocaremos en ésta última.

¿Qué es la auditoría?

Es el registro hacia archivos o una tabla de la base de datos de eventos previamente activados, ocurridos en la misma. Estos eventos, pueden activarse y desactivarse según se necesite.

¿Para qué sirve?

Sirve para que el DBA, pueda tener conocimiento de lo que se está realizando en la base de datos. Por ejemplo, personas que se están conectando a la misma, o intentos de conexión fallidos, la alteración de una tabla, etcétera. Como se dice, la información es poder, y aquí, el poder es el de saber qué está sucediendo con la base de datos.

¿Cómo se activa la auditoría?

Se tiene que usar los parámetros de la base de datos audit_trail y audit_sys_operations. Estos deben estar asignados en el archivo de inicialización de la base de datos initSID.ora, o reflejado en el spfile, para que la base de datos, al ser iniciada, los tome.

audit_trail

Este es el parámetro que determina si se activa o no la auditoría. Si no está presente en el archivo, entonces se asume que no se ejecuta la auditoría. Los valores que puede tener son:

  • DB – Los eventos se registrarán en la base de datos.
  • OS – Los eventos se registran en archivos del sistema operativos, éstos se guardarán en el directorio expecificado por el parámetro audit_file_dest. Esta opción no se ve en este documento.
  • NONE – Se desactiva el registro de eventos.

audit_sys_operations

Con este parámetro, se indica si se quiere registrar en la tabla de auditoría, las acciones realizadas por un usuario SYS. El valor para esto, es TRUE.

Una vez que se han cambiado los parámetros mencionados, y reiniciado la base datos, ya está preparada la misma para comenzar a registrar los eventos. Para completar el proceso, se tiene que usar la instrucción:

audit evento_a_auditar;

Por el contrario, si se desea desactivar el registro de un evento, se usa la instrucción:

noaudit evento_a_desauditar;

Para esto, muestro aquí, algunos de los eventos que se pueden auditar:

 CLUSTER
  CREATE CLUSTER
  AUDIT CLUSTER
  DROP CLUSTER
  TRUNCATE CLUSTER
CONTEXT
  CREATE CONTEXT
  DROP CONTEXT
DATABASE LINK
  CREATE DATABASE LINK
  DROP DATABASE LINK
DIMENSION
  CREATE DIMENSION
  ALTER DIMENSION
  DROP DIMENSION
DIRECTORY
  CREATE DIRECTORY
  DROP DIRECTORY
INDEX
  CREATE INDEX
  ALTER INDEX
  DROP INDEX
NOT EXISTS
  Cualquier instrucción SQL que fallé porque el objeto no existe.
PROCEDURE
  CREATE FUNCTION
  CREATE LIBRARY
  CREATE PACKAGE
  CREATE PACKAGE BODY
  CREATE PROCEDURE
  DROP FUNCTION
  DROP LIBRARY
  DROP PACKAGE
  DROP PROCEDURE
PROFILE
  CREATE PROFILE
  ALTER PROFILE
  DROP PROFILE
PUBLIC DATABASE LINK
  CREATE PUBLIC DATABASE LINK
  DROP PUBLIC DATABASE LINK
PUBLIC SYNONYM
  CREATE PUBLIC SYNONYM
  DROP PUBLIC SYNONYM
ROLE
  CREATE ROLE
  ALTER ROLE
  DROP ROLE
  SET ROLE
ROLLBACK SEGMENT
  CREATE ROLLBACK SEGMENT
  ALTER ROLLBACK SEGMENT
  DROP ROLLBACK SEGMENT
SEQUENCE
  CREATE SEQUENCE
  DROP SEQUENCE
SESSION
  Conexiones a la base de datos
SYNONYM
  CREATE SYNONYM
  DROP SYNONYM
Privilegios
  GRANT privilegios_o_roles
  REVOKE privilegios_o_roles
TABLE
  CREATE TABLE
  DROP TABLE
  TRUNCATE TABLE
TABLESPACE
  CREATE TABLESPACE
  ALTER TABLESPACE
  DROP TABLESPACE
TRIGGER
  CREATE TRIGGER
  ALTER TRIGGER
  DROP TRIGGER
TYPE
  CREATE TYPE
  CREATE TYPE BODY
  ALTER TYPE
  DROP TYPE
  DROP TYPE BODY
USER
  CREATE USER
  ALTER USER
  DROP USER
VIEW
  CREATE VIEW
  DROP VIEW

Otras opciones son:

ALTER SEQUENCE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
COMMENT TABLE
COMMENT ON TABLE table, view, materialized view
COMMENT ON COLUMN table.column, view.column, materialized view.column
DELETE TABLE
DELETE FROM table, view
EXECUTE PROCEDURE
CALL
GRANT DIRECTORY
GRANT privilege ON directory
REVOKE privilege ON directory
GRANT PROCEDURE
GRANT privilege ON procedure, function, package
REVOKE privilege ON procedure, function, package
GRANT SEQUENCE
GRANT privilege ON sequence
REVOKE privilege ON sequence
GRANT TABLE
GRANT privilege ON table, view, materialized view.
REVOKE privilege ON table, view, materialized view
GRANT TYPE
GRANT privilege ON TYPE
REVOKE privilege ON TYPE
INSERT TABLE
INSERT INTO table, view
LOCK TABLE
LOCK TABLE table, view
SELECT SEQUENCE
  Cualquier ejecución de comando que contenga .CURRVAL o .NEXTVAL
SELECT TABLE
SELECT FROM table, view, materialized view
UPDATE TABLE
UPDATE table, view

Así, si se desea por ejemplo, auditar la creación de una tabla, se ejecutaría el siguiente comando:

audit create table;

Por otro lado, si se venía auditando la alteración de usuarios, y ya no se desea continuar con dicha auditoría, entonces, se ejecutaría:

noaudit alter user;

¿Dónde se guardan los eventos?

Todos los eventos de la auditoría, se guardan en la tabla de sys, aud$; la cual, para tener una información más clara y “limpia”, la podemos ver en dba_audit_trail. En dicha vista, podemos ver la información de manera más depurada, sin embargo, si se quiere hacer una limpieza de registros generados, tendrán que hacerse sobre aud$, para lo cual, se tiene que tener el permiso especial drop any table, o directo sobre la tabla.

Al tener el privilegio mencionado, podremos hacer un truncate a la tabla aud$ de manera directa, o simplemente un delete cuando requiramos eliminar sólo una parte de ella.

Ahora muestro la estructura de la vista dba_audit_trail y daremos explicación a los campos más importantes:

SQL> desc dba_audit_trail
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 OS_USERNAME                                        VARCHAR2(255)  USERNAME                                           VARCHAR2(30)  USERHOST                                           VARCHAR2(128)  TERMINAL                                           VARCHAR2(255)  TIMESTAMP                                          DATE  OWNER                                              VARCHAR2(30)
 OBJ_NAME                                           VARCHAR2(128)  ACTION                                    NOT NULL NUMBER
 ACTION_NAME                                        VARCHAR2(28)  NEW_OWNER                                          VARCHAR2(30)
 NEW_NAME                                           VARCHAR2(128)
 OBJ_PRIVILEGE                                      VARCHAR2(16)
 SYS_PRIVILEGE                                      VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)
 GRANTEE                                            VARCHAR2(30)
 AUDIT_OPTION                                       VARCHAR2(40)
 SES_ACTIONS                                        VARCHAR2(19)
 LOGOFF_TIME                                        DATE
 LOGOFF_LREAD                                       NUMBER
 LOGOFF_PREAD                                       NUMBER
 LOGOFF_LWRITE                                      NUMBER
 LOGOFF_DLOCK                                       VARCHAR2(40)
 COMMENT_TEXT                                       VARCHAR2(4000)
 SESSIONID                                 NOT NULL NUMBER
 ENTRYID                                   NOT NULL NUMBER
 STATEMENTID                               NOT NULL NUMBER
 RETURNCODE                                NOT NULL NUMBER  PRIV_USED                                          VARCHAR2(40)
 CLIENT_ID                                          VARCHAR2(64)
 ECONTEXT_ID                                        VARCHAR2(64)
 SESSION_CPU                                        NUMBER
 EXTENDED_TIMESTAMP                                 TIMESTAMP(6) WITH TIME ZONE
 PROXY_SESSIONID                                    NUMBER
 GLOBAL_UID                                         VARCHAR2(32)
 INSTANCE_NUMBER                                    NUMBER
 OS_PROCESS                                         VARCHAR2(16)
 TRANSACTIONID                                      RAW(8)
 SCN                                                NUMBER
 SQL_BIND                                           NVARCHAR2(2000)
 SQL_TEXT                                           NVARCHAR2(2000)

En ella estoy marcando en negritas, los campos más importantes, o los que más se usan al momento de registrar los datos. Sin embargo, pueden analizar más información generada en los demás campos. Por lo pronto, aquí explico los que comento en primera instancia:

OS_USERNAME

Es el usuario del sistema operativo, que está realizando la acción.

USERNAME

Es el usuario de la base de datos que registra la acción, o la está realizando.

USERHOST

Es el nombre de la computadora desde la cual, se ejecutó la acción que registró el evento.

TERMINAL

También en este campo, se registra el nombre de la computadora.

TIMESTAMP

Es la fecha y hora en la que se registra el evento. En la estructura de la tabla que muestro arriba, hay un extended_timestamp, que tiene un detalle más claro, incluso con centésimas de segundo, pero este campo no existe en versiones previas a 10g. Por lo que es mejor, si se desea tener un programa o código estándar, el usar este campo.

OBJ_NAME

El objeto que esté siendo afectado por la acción que registra el evento, se guardará en este campo. Por ejemplo, si se hizo un delete user, y tenemos registrado el evento, en este campo, aparacerá el nombre del usuario. Por otro lado, si se hizo un create table y también se registra el evento, este campo tendrá el nombre de la tabla creada.

ACTION_NAME

En este campo, se registra la acción que se está realizando, por ejemplo: LOGON, LOGOFF, ALTER USER, etcétera.

RETURNCODE

Este es el número de “error” que se tiene al momento de ejecutar la instrucción que registró el evento. Lo pongo entre comillas, porque si este valor es igual a 0, entonces quiere decir que la instrucción se ejecutó de manera correcta. Cuando sea este el caso, en action_name, podremos ver lo que se hizo, y en obj_name, el objeto que se vio afectado.

Para interpretar el valor que se está registrando en este campo, basta con ver los errores de tipo ORA-, por ejemplo, para el error ORA-1017, que corresponde a un usuario/password inválido, en este campo, tendremos simplemente 1017.

SQL_TEXT

Esta opción existe a partir de la versión 10g de Oracle. En ella se registra la instrucción SQL que está causando la generación del evento.

Precauciones

Al momento de generar una auditoría para una determinada cantidad de eventos, se tendrá que tener cuidado con la frecuencia con que ocurran, por la generación tan grande de información que puede llegar a existir.

Si la opción que se tiene para el audit_trail, es OS; se deberá cuidar el hecho de que no se llene el file system o directorio donde se estén generando los archivos. Si por el contrario, es DB o DB_EXTENDED, habrá que cuidar el crecimiento de la tabla AUD$ ya que reside en el tablespace SYSTEM.

Conclusiones

La auditoría como pueden ver, puede ser un arma poderosa para ver qué se está haciendo en la base de datos. No hay que descuidar lo que se vio en el último punto a manera de precaución. Si requieren más información, pueden dar clic aquí.

Si la información de este post te ha sido de utilidad o quieres que agregue algo más, deja por favor un comentario, contestaré a la brevedad.

Anuncios

3 Responses to Auditando la base de datos

  1. Pingback: Cambiando la tabla AUD$ a otro tablespace | Orlando Olguín Olvera

  2. Hola Orlando, intenté revisar la URL que tienes para más información de la auditoria y al parecer es inválido o ya no existe.

  3. Orlando Olguín Olvera says:

    Hola Eva.

    Gracias, ya la actualicé.

    Orlando.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: