PDF de programación - Practicas Tema9 - Auditoría

Administración Básica de Oracle10g. Prácticas auditoría.

PRACTICAS TEMA 9.

AUDITORÍA.

1. Ver la descripción de las vistas ALL_DEF_AUDIT_OPTS, DBA_AUDIT_OBJECT,
DBA_AUDIT_SESSION,
DBA_AUDIT_TRAIL,
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, y DBA_STMT_AUDIT_OPTS.
Averiguar el significado de cada uno de los campos usando la documentación
en línea.

DBA_AUDIT_STATEMENT,

SQL> desc ALL_DEF_AUDIT_OPTS

Nombre ¿Nulo? Tipo
-------------------------------------- -------- ------------------
VARCHAR2(3)
ALT
AUD
VARCHAR2(3)
VARCHAR2(3)
COM
VARCHAR2(3)
DEL
GRA
VARCHAR2(3)
VARCHAR2(3)
IND
VARCHAR2(3)
INS
VARCHAR2(3)
LOC
REN
VARCHAR2(3)
VARCHAR2(3)
SEL
VARCHAR2(3)
UPD
REF
VARCHAR2(3)
EXE
VARCHAR2(3)
...

2. Ver la descripción de la vista SYS.AUD$.

SQL> desc sys.aud$

?Nulo?

Tipo

Nombre
----------------------------------------------------------------------
SESSIONID
ENTRYID
STATEMENT
TIMESTAMP#
USERID
USERHOST
TERMINAL

NUMBER
NUMBER
NUMBER
DATE
VARCHAR2(30)
VARCHAR2(128)
VARCHAR2(255)

NOT NULL
NOT NULL
NOT NULL

© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla

1

Administración Básica de Oracle10g. Prácticas auditoría.

ACTION#
RETURNCODE
OBJ$CREATOR
OBJ$NAME
AUTH$PRIVILEGES
AUTH$GRANTEE
NEW$OWNER
NEW$NAME
SES$ACTIONS
SES$TID
LOGOFF$LREAD
LOGOFF$PREAD
LOGOFF$LWRITE
LOGOFF$DEAD
LOGOFF$TIME
COMMENT$TEXT
CLIENTID
SPARE1
SPARE2
OBJ$LABEL
SES$LABEL
PRIV$USED
SESSIONCPU
NTIMESTAMP#
PROXY$SID
USER$GUID
INSTANCE#
PROCESS#
XID
AUDITID
SCN
DBID
SQLBIND
SQLTEXT

NOT NULL
NOT NULL

NUMBER
NUMBER
VARCHAR2(30)
VARCHAR2(128)
VARCHAR2(16)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(128)
VARCHAR2(19)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
DATE
VARCHAR2(4000)
VARCHAR2(64)
VARCHAR2(255)
NUMBER
RAW(255)
RAW(255)
NUMBER
NUMBER
TIMESTAMP(6)
NUMBER
VARCHAR2(32)
NUMBER
VARCHAR2(16)
RAW(8)
VARCHAR2(64)
NUMBER
NUMBER
CLOB
CLOB

3. Habilitar la auditoría de base de datos modificando convenientemente el
fichero de parámetros de inicialización de forma que se registren los apuntes
de auditoría en base de datos.

Para comenzar a auditar debe modificarse el fichero de parámetros de
inicialización, añadiendo el parámetro AUDIT_TRAIL y asignándole el valor DB.

© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla

2

Administración Básica de Oracle10g. Prácticas auditoría.

4. Comprobar que usuarios de base de datos tienen asignados los privilegios

AUDIT SYSTEM y AUDIT ANY.

SQL> connect system
Enter password:

Connected.

SQL> select * from dba_sys_privs where privilege ='AUDIT ANY';

GRANTEE PRIVILEGE
--------------------------------------------------------------------------
DBA
IMP_FULL_DATABASE

AUDIT ANY
AUDIT ANY

YES
NO

ADM

SQL> select * from dba_sys_privs where privilege ='AUDIT SYSTEM';

GRANTEE PRIVILEGE
--------------------------------------------------------------------------
DBA

AUDIT SYSTEM

ADM

YES

5. Auditar todas las conexiones exitosas y fallidas a la base de datos.

SQL> audit session;

Auditoria terminada correctamente.

6. Abrir una sesión a la base de datos como usuario “SYSTEM” y como usuario
“prueba01”. Comprobar que información se ha guardado en la auditoría
como consecuencia de las operaciones anteriores (consultar la vista
DBA_AUDIT_TRAIL).

SQL> select substr(os_username,1,10) usuario_so, substr(username,1,12)
tiempo_conexion,
from

usuario,
to_char(logoff_time,'dd-mm-yyyy hh24:mi:ss')
dba_audit_trail order by username,timestamp,logoff_time;

to_char(timestamp,'dd-mm-yyy hh24:mi:ss')

tiempo_desconexion





USUARIO_SO USUARIO TIEMPO_CONEXION TIEMPO_DESCONEXION
--------------------------------------------------------------------------------------
oracle
oracle

15-10-006 10:04:08
15-10-006 10:04:24

PRUEBA01
SYSTEM

© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla

3

Administración Básica de Oracle10g. Prácticas auditoría.

7. Auditar las conexiones exitosas y fallidas a la base de datos de los usuarios

“prueba01” y “prueba02”.

SQL> audit session by prueba01, prueba02;

Auditoria terminada correctamente.

8. Conectarse a la base de datos como usuario “prueba02” introduciendo una
clave errónea. Comprobar el apunte realizado en la auditoría del sistema
(consultar la vista DBA_AUDIT_SESSION).

to_char(timestamp,'dd-mm-yyy hh24:mi:ss')

SQL> select substr(os_username,1,10) usuario_so, substr(username,1,12)
tiempo_conexion,
from
by

usuario,
to_char(logoff_time,'dd-mm-yyyy hh24:mi:ss')
dba_audit_session
username,timestamp,logoff_time

tiempo_desconexion

username='PRUEBA02'

where







order







USUARIO_SO USUARIO TIEMPO_CONEXION TIEMPO_DESCONEXION
--------------------------------------------------------------------------------------
oracle

15-10-006 10:08:43

PRUEBA02

9. Auditar el uso de SELECT TABLE, DELETE TABLE, UPDATE TABLE e INSERT
TABLE en cualquier caso (fallido o no) y por sentencia individual. Comprobar
las opciones activas de auditoría (vista DBA_STMT_AUDIT_OPTS).

SQL> audit select table, delete table, update table, insert table by access;

Auditoria terminada correctamente.

SQL> select user_name usuario, audit_option opcion, success, failure from

DBA_STMT_AUDIT_OPTS order by user_name;

OPCION

USUARIO
-----------------------------------------------------------------------------------
PRUEBA01
PRUEBA02

SUCCESS

FAILURE

CREATE SESSION BY ACCESS
CREATE SESSION BY ACCESS
BY ACCESS
INSERT TABLE
DELETE TABLE
BY ACCESS
BY ACCESS
SELECT TABLE

BY ACCESS
BY ACCESS
BY ACCESS
BY ACCESS
BY ACCESS

© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla

4

Administración Básica de Oracle10g. Prácticas auditoría.

CREATE SESSION BY ACCESS
BY ACCESS
UPDATE TABLE

BY ACCESS
BY ACCESS

7 filas seleccionadas.

10.Conectarse como usuario “prueba01”. Comprobar si se ha creado la tabla

“codigos_notas” en el "tablespace" ACADEMO, en caso contrario creadla.

CREATE TABLE CODIGOS_NOTAS
(CODIGO varchar2(3),
DESCRIPCION varchar2(20))
TABLESPACE ACADEMO
STORAGE (INITIAL 64K
NEXT 64K
MINEXTENTS 3
MAXEXTENTS 10);

Otorgar privilegios para consultar, insertar, actualizar y borrar registros

de la tabla “codigos_notas” al usuario "prueba02".

Conectarse como usuario “prueba02” y realizar las siguientes

operaciones:

-
-

Seleccionar todos los registros de la tabla “codigos_notas”.
Insertar en la tabla “codigos_notas” los siguientes registros:

‘0’, ‘NO PRESENTADO’
‘1’, ‘APROBADO’

Consultar los registros de auditoría generados para las operaciones

anteriores (vista DBA_AUDIT_OBJECT).

SQL> connect prueba01
Enter password:
Conectado.

SQL> CREATE TABLE CODIGOS_NOTAS
2 (CODIGO varchar2(3),
3 DESCRIPCION varchar2(20))
4 TABLESPACE ACADEMO
5 STORAGE (INITIAL 64K
6 NEXT 64K
7 MINEXTENTS 3
8* MAXEXTENTS 10)

© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla

5

Administración Básica de Oracle10g. Prácticas auditoría.

Tabla creada.

SQL> grant select, insert, update, delete on codigos_notas to prueba02;

Grant succeeded.

SQL> connect prueba02
Enter password:
Conectado.

SQL> select * from prueba01.codigos_notas;

ninguna fila seleccionada

SQL> insert into prueba01.codigos_notas values ('0','NO PRESENTADO');

1 fila creada.

SQL> insert into prueba01.codigos_notas values ('1','APROBADO');

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> connect system
Enter password:
Conectado.

SQL> select substr(os_username,1,10) usuario_so, substr(username,1,12) usuario,
tiempo_conexion,
to_char(timestamp,'dd-mm-yyy
from
to_char(logoff_time,'dd-mm-yyyy hh24:mi:ss')
dba_audit_session
by
username,timestamp,logoff_time;

tiempo_desconexion

username='PRUEBA02'

hh24:mi:ss')



order



where









USUARIO_SO USUARIO
TIEMPO_CONEXION TIEMPO_DESCONEXION
--------------------------------------------------------------------------------------
oracle
oracle

15-10-006 10:08:43
15-10-006 10:24:43 15-10-2006 10:26:52

PRUEBA02
PRUEBA02

SQL> select substr(os_username,1,10) usuario_so, substr(username,1,12) usuario,
to_char(timestamp,'dd-mm-yyy hh24:mi:ss') tiempo_conexion, substr(owner,1,10)
propie, substr(obj_name,1,15) objeto, substr(action_name,1,10) accion from

© Francisco Fernández Martínez y Juan Luis Serradilla Amarilla

6

Administración Básica de Oracle10g. Prácticas auditoría.

dba_audit_object where username='PRUEBA02' order by os_username,timestamp,
owner,obj_name,action_name;

USUARIO_SO USUARIO TIEMPO_CONEXION PROPIE OBJETO

ACCION

---------------------------------------------------------------------------------------------------------
SELECT
oracle
SYS
oracle
SELECT
SYS
SELECT
oracle
SYSTEM PRODUCT_PRIVS
SYSTEM PRODUCT_PRIVS
oracle
SELECT
SYSTEM SQLPLUS_PRODUCT SELECT
oracle
oracle
SYSTEM SQLPLUS_PRODUCT SELECT
SELECT
oracle
INSERT
oracle
oracle
INSERT

15-10-006 10:24:43
15-10-006 10:24:43
15-10-006 10:24:43
15-10-006 10:24:43
15-10-006 10:24:43
15-10-006 10:24:43
15-10-006 10:25:17 PRUEBA01 CODIGOS_NOTAS
15-10-006 10:25:31 PRUEBA01 CODIGOS_NOTAS
15-10-006 10:25:41 PRUEBA01 CODIGOS_NOTAS

PRUEBA02
PRUEBA02
PRUEBA02
PRUEBA02
PRUEBA02
PRUEBA02
PRUEBA02
PRUEBA02
PRUEBA02

DUAL
DUAL

9 filas seleccionadas.

11.Consultar las opciones por defecto de auditoría de objetos

(ALL_DEF_AUDIT_OPTS).

SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
---------------------------------------------------------------------------------
-/-

-/- -/- -/- -/-

-/- -/- -/- -/-

-/-

-/-

-/-

-/-

-/- -/-

12.Especificar las opciones de auditoría por defecto para los objetos creados en
un futuro de forma que se registre información siempre que se produzca un
“alter”, “grant”, “insert”, “update” o “delete”.

SQL> audit alter, grant,