Crear auditoria User Oracle
Consultar si NO esta activa la Auditoria en la BD
SQL> select name , value from v$parameter where name like 'audit_trail';
audit_trail NONE
Activa la auditoria
ALTER SYSTEM SET audit_trail = “DB” SCOPE=SPFILE;
Desactivar la auditoria
ALTER SYSTEM SET audit_trail = “NONE” SCOPE=SPFILE;
Consultar la auditoria por usuario
SELECT returncode, username,
action_name,
timestamp,
logoff_time,
comment_text,
sessionid
FROM dba_audit_trail
WHERE username ='NOMBRE_USER'
order by timestamp desc ;
Copiar la auditoria en una tabla
CREATE TABLE AUDIT_26022015
TABLESPACE TS_AUDITORIA
AS
SELECT *
FROM SYS.AUD$;
_________________________________________________________
-- Consultar auditorias por fecha
SELECT * FROM SYS.AUD$ WHERE SYS.AUD$.NTIMESTAMP# BETWEEN '13062016 07:00:00' AND '13062016 10:00:00';
-- Consultar auditorias por acción
SELECT * FROM SYS.AUD$ WHERE SYS.AUD$.ACTION# LIKE '157';
-- Consultar auditorias con usuarios y objetos
SELECT S.USERHOST, S.TERMINAL, S.SPARE1, S.NTIMESTAMP#, S.OBJ$CREATOR,T.NAME ,S.OBJ$NAME FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T
WHERE S.ACTION# = T.ACTION AND S.OBJ$CREATOR IS NOT NULL;
-- Consultar auditorias por tablas u objetos
SELECT S.USERHOST, S.TERMINAL, S.SPARE1, S.NTIMESTAMP#, S.OBJ$CREATOR,T.NAME ,S.OBJ$NAME FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T
WHERE S.ACTION# = T.ACTION AND S.OBJ$CREATOR IS NOT NULL AND S.OBJ$NAME = 'NOM_TABLA U OBJETO';
SELECT S.USERHOST, S.TERMINAL, S.SPARE1, S.NTIMESTAMP#, S.OBJ$CREATOR,T.NAME ,S.OBJ$NAME FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T
WHERE S.ACTION# = T.ACTION AND S.OBJ$CREATOR IS NOT NULL AND S.OBJ$NAME LIKE '%NOM_TABLA U OBJETO%';
-- Consultar Auditorias por tablas u objetos operacion INSERT
SELECT S.USERHOST, S.TERMINAL, S.SPARE1, S.NTIMESTAMP#, S.OBJ$CREATOR,T.NAME ,S.OBJ$NAME FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T
WHERE S.ACTION# = T.ACTION AND S.OBJ$CREATOR IS NOT NULL AND S.OBJ$NAME = 'NOM_TABLA U OBJETO' AND T.NAME LIKE '%INSERT%';
-- Consultar auditoria completa sin valores Nulo
SELECT S.SESSIONID, S.ENTRYID, S.USERHOST, S.TERMINAL,'SQL' || T.NAME,S.RETURNCODE, S.OBJ$CREATOR ,S.OBJ$NAME, S.SPARE1, S.PRIV$USED,
S.NTIMESTAMP#,S.INSTANCE#,S.PROCESS#,S.XID,S.SCN ,S.DBID ,S.SQLBIND,S.SQLTEXT FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T
WHERE S.ACTION# = T.ACTION AND S.OBJ$CREATOR IS NOT NULL;
-- Consultar auditoria completa
SELECT S.SESSIONID, S.ENTRYID, S.USERHOST, S.TERMINAL,'SQL' || T.NAME,S.RETURNCODE, S.OBJ$CREATOR ,S.OBJ$NAME, S.SPARE1, S.PRIV$USED,
S.NTIMESTAMP#,S.INSTANCE#,S.PROCESS#,S.XID,S.SCN ,S.DBID ,S.SQLBIND,S.SQLTEXT FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T
WHERE S.ACTION# = T.ACTION;
-- Consultar auditoria completa POR FECHA
SELECT S.SESSIONID, S.ENTRYID, S.USERHOST, S.TERMINAL,'SQL' || T.NAME,S.RETURNCODE, S.OBJ$CREATOR ,S.OBJ$NAME, S.SPARE1, S.PRIV$USED,
S.NTIMESTAMP#,S.INSTANCE#,S.PROCESS#,S.XID,S.SCN ,S.DBID ,S.SQLBIND,S.SQLTEXT FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T
WHERE S.ACTION# = T.ACTION AND (S.NTIMESTAMP# BETWEEN '01012015 01:00:00' AND '31122015 12:00:00');
___________________________________________________________
Valores de Auditorias
Valores:
– 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.
Activar Auditorias
Algunas auditorias para tablas
Auditoria total para una tabla
audit all on ESQUEMA.NOM_TABLE by session;
Auditoria INSERT para la tabla
audit insert on ESQUEMA.NOM_TABLE by access;
Auditoria DELETE para la tabla
audit delete on ESQUEMA.NOM_TABLE by access;
Auditoria UPDATE para la tabla
audit update on ESQUEMA.NOM_TABLE by access;
Auditoria INSERT y DELETE para la tabla
audit insert,delete on ESQUEMA.NOM_TABLE by access;
Algunas auditorias para tablas
audit update table by SIAS_M6;
____________________________________________________________
¿Que Mirar Cuando Auditamos una Base de Datos Oracle?
Enviado por D7n0s4ur70 en Nov 13, 2009 | 5 Opiniones
Cuando se realiza una auditoria, siempre es recomendable seguir una serie de pasos para no pasar por algo ningún detalle que pueda resultar crucial para nuestra investigación, a continuación les dejo un pequeño listado de las cosas a las que debemos echarle una ojeada cuando auditamos una base de datos oracle.
1). Determinar si en la BD esta activo el modo de operación en ARCHIVELOG o NONARCHIVELOG. Si este no esta activo, la evidencia de ataque o cambios serán sobrescritos por un nuevo redo.
Se puede determinar realizando una sentencia SQL a la BD:
SELECT VALUE V$PARAMETER WHERE FROM NAME=’archiv_log_start’;
SQL> archive log list;
Modo log de la base de datos Modo de No Archivado
Archivado automatico Desactivado
Destino del archivo /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Secuencia de log en linea mas antigua 49115
Secuencia de log actual 49117
SQL>
2). Análisis de los Oracle Data Blocks, para determinar:
Registros eliminados
Localizar bloques asignados a tablas (OBJETOS DE INTERÉS)
Seguimiento de Objetos creados y eliminados
Localización de tablas eliminadas
Localización de Funciones eliminadas
3). Obtención del SID de la BD
4). Enumeración de usuarios
SELECT USERID, ACTION#, RETURNCODE, TIMESTAMP# FROM SYS.AUD$;
SELECT USERID, COMMENT$TEXT FROM SYS.AUD$;
5). Consultar Ataques de Fuerza bruta o Diccionario a cuentas de usuario
SELECT USERID, ACTION#, RETURNCODE, TIMESTAMP# FROM SYS.AUD$;
SELECT NAME, LCOUNT FROM SYS.USER$ WHERE LCOUNT>0;
SELECT NAME, LTIME FROM SYS.USER$ WHERE ASTATUS = 4;
6). Consulta de Ataques de Fuerza Bruta a la cuenta SYS
7). Consulta de intentos del exploit AUTH_ALTER_SESSION
SELECT USERID, ACTION#, RETURNCODE, TIMESTAMP# FROM SYS.AUD$;
8). Consulta de intentos de iniciar una sesiÛn la base de datos a travÈs de XML (XDB)
SELECT USERID, ACTION#, RETURNCODE, TIMESTAMP# FROM SYS.AUD$;
SELECT COMMENT$TEXT FROM SYS.AUD$ WHERE USERID = ‘DBSNMP’;
SELECT TERMINAL,SPARE1,TIMESTAMP# FROM SYS.AUD$ WHERE USERID=’DBSNMP’;
9). Consulta si la Auditoria esta habilitada
SELECT USER_ID, SESSION_ID, SAMPLE_TIME FROM SYS.WRH$_ACTIVE_SESSION_HISTORY ORDER BY SAMPLE_TIME;
SELECT USERID, ACTION#, RETURNCODE, TIMESTAMP# FROM SYS.AUD$;
SELECT USERID,ACTION#,TIMESTAMP#,LOGOFF$TIME FROM AUD$;
10). Consulta del archivo sqlnet.log,Agntsrvc.log, spfilesid.ora, o el init.ora todas las ubicaciones referentes a estos parámetros:
audit_file_dest ——-----------> Sistema de Auditoria (ORACLE_HOME/rdbms/audit)
background_dump_dest ——-> archivo alert.log y tracer de procesos ($ORACLE_HOME/admin/$ORACLE_SID/bdump)
core_dump_dest ——--------> archivos Oracle core dump ($ORACLE_HOME/DBS/)
db_recovery_file_dest ——----> redo logs, flashback logs, y RMAN backups
user_dump_dest ——--------> Archivos trace debuggin procesos/usuarios (/oracle/utrc)
utl_file_dir ——-----------------> Especifica uno o m·s directorios que Oracle debe utilizar para PL/SQL archivos E/S.
control_files —--------------—-> Especifica uno o varios nombres de archivos de control de Oracle
db_create_file_dest ——--------> Especifica la ubicación predeterminada de archivos de datos administrados por Oracle.
db_create_online_log_dest_n—-> Especifica la ubicación de los redo logs y file control
log_archive_dest ——-----------> Es aplicable solo si la BD esta en modo de ARCHIVELOG
log_archive_dest_n ——--------> Define hasta 10 archivos de registros logs.
11). Consulta de archivos Log Listener (ORACLE_HOME/network/admin/listener.ora) (lsnrctl status me dara la ubicación actual)
12). Revisión de los LOGS de sentencias(SQL $ORACLE_HOME/bin/LOGIN.SQL,$ORACLE_HOME/dbs/LOGIN.SQL,$ORACLE_HOME/SQLPlus/admin/glogin.sql)
13). Consultando informacion de los inicios de Sesion:
SELECT USER_ID, SESSION_ID, SAMPLE_TIME FROM SYS.WRH$_ACTIVE_SESSION_HISTORY
14). Consultar una lista de usuarios y roles, usuarios con función de DBA, para buscar inconsistencias,o usuarios creados por un atacante y la generación de contraseñas fuertes con la validación de los hash, cuentas bloqueadas, tiempos de password
SELECT USER#, NAME, ASTATUS, PASSWORD, CTIME, PTIME, LTIME FROM SYS.USER$ WHERE TYPE#=1;
SELECT U.NAME AS “GRANTEE”, U2.NAME AS “ROLE” FROM SYS.USER$ U,SYS.USER$ U2, SYS.SYSAUTH$ A WHERE U.USER# = A.GRANTEE# AND PRIVILEGE# = U2.USER#;
15). Consultar una lista de objetos y privilegios en el sistema
SELECT U.NAME AS “GRANTEE”, P.NAME AS “PRIVILEGE”, U2.NAME AS “OWNER”, O.NAME AS “OBJECT” FROM SYS.USER$ U, SYS.USER$ U2,SYS.TABLE_PRIVILEGE_MAP P, SYS.OBJ$ O, SYS.OBJAUTH$ A WHERE U.USER# =A.GRANTEE# AND A.OBJ# = O.OBJ# AND P.PRIVILEGE = A.PRIVILEGE# AND O.OWNER#=U2.USER#;
SQL> SELECT OBJ#, OWNER#, NAME, TYPE#, CTIME, MTIME, STIME FROM SYS.OBJ$ ORDER BY CTIME ASC;
16). Consulta de tablas eliminadas
SELECT U.NAME, R.ORIGINAL_NAME, R.OBJ#, R.DROPTIME, R.DROPSCN FROM SYS.RECYCLEBIN$ R, SYS.USER$ U WHERE R.OWNER#=U.USER#;
17). Consulta de Directorios, archivos datos, archivos externos, tablas externas, buscando elementos perdidos o ubicados en sitios diferentes por el atacante.
SELECT T.NAME AS “TABLESPACE”, D.NAME AS “FILNAME” FROM V$DATAFILE D, TS$ T WHERE T.TS#=D.TS#;
SELECT U.NAME AS “OWNER”, O.NAME AS “DIRECTORY”, D.OS_PATH AS “PATH” FROM SYS.OBJ$ O, SYS.USER$ U, SYS.DIR$ D WHERE U.USER#=O.OWNER# AND O.OBJ#=D.OBJ#;
SELECT O.NAME, D.DEFAULT_DIR FROM SYS.OBJ$ O, SYS.EXTERNAL_TAB$ D WHERE D.OBJ# = O.OBJ#;
18). El Monitor del Sistema (SMON) MON_MOD$ Table
SELECT U.NAME AS “OWNER”, O.NAME AS “OBJECT”, M.OBJ#, M.INSERTS,M.UPDATES, M.DELETES, M.TIMESTAMP FROM SYS.MON_MODS$ M, SYS.USER$ U,SYS.OBJ$ O WHERE O.OBJ#=M.OBJ# AND U.USER#=O.OWNER#;
19). Revision de Triggers al encendido, apagado, inicio y terminacion de sesion
SELECT U.NAME AS “OWNER”, O.NAME AS “ENABLED_TRIGGER_NAME”,DECODE(T.TYPE#, 0, ‘BEFORE’,2, ‘AFTER’,’NOTSET’) AS “WHEN” FROM SYS.OBJ$ O, SYS.TRIGGER$ T, SYS.USER$ U WHERE O.OBJ#=T.OBJ# AND O.OWNER# = U.USER# AND ENABLED=1;
SELECT U.NAME AS “OWNER”, O.NAME AS “ENABLED_TRIGGER_NAME” FROM SYS.OBJ$ O, SYS.TRIGGER$ T, SYS.USER$ U WHERE O.OBJ#=T.OBJ# AND O.OWNER# = U.USER# AND ENABLED=1 AND BITAND(T.SYS_EVTS,1) = 1;
SELECT U.NAME AS “OWNER”, O.NAME AS “ENABLED_TRIGGER_NAME” FROM SYS.OBJ$ O, SYS.TRIGGER$ T, SYS.USER$ U WHERE O.OBJ#=T.OBJ# AND O.OWNER# = U.USER# AND ENABLED=1 AND BITAND(T.SYS_EVTS,2) = 2;
SELECT U.NAME AS “OWNER”, O.NAME AS “ENABLED_TRIGGER_NAME” FROM SYS.OBJ$ O, SYS.TRIGGER$ T, SYS.USER$ U WHERE O.OBJ#=T.OBJ# AND O.OWNER# = U.USER# AND ENABLED=1 AND BITAND(T.SYS_EVTS,8) = 8;
SELECT U.NAME AS “OWNER”, O.NAME AS “ENABLED_TRIGGER_NAME” FROM SYS.OBJ$ O, SYS.TRIGGER$ T, SYS.USER$ U WHERE O.OBJ#=T.OBJ# AND O.OWNER# = U.USER# AND ENABLED=1 AND BITAND(T.SYS_EVTS,16) = 16;
20). Consulta de librerias, que puedan estar ejecutando codigo arbitrario(malicioso)
SELECT U.NAME AS “OWNER”, O.NAME AS “LIBRARY”, L.FILESPEC AS “PATH” FROM SYS.LIBRARY$ L, SYS.USER$ U, SYS.OBJ$ O WHERE O.OBJ#=L.OBJ# AND O.OWNER#=U.USER#;
21). Consultas de FlashBack (nuevos privilegios, derechos asignados, nuevos objetos, objetos eliminados) entre la tabla actual y la anterior en un tiempo determinado.
SELECT GRANTEE#, PRIVILEGE# FROM SYS.SYSAUTH$ MINUS SELECT GRANTEE#, PRIVILEGE# FROM SYS.SYSAUTH$ AS OF TIMESTAMP(SYSDATE – INTERVAL ‘3600’ MINUTE);
SELECT NAME FROM SYS.OBJ$ MINUS SELECT NAME FROM SYS.OBJ$ AS OF TIMESTAMP(SYSDATE – INTERVAL ‘156’ MINUTE);
SELECT NAME FROM SYS.OBJ$ AS OF TIMESTAMP(SYSDATE – INTERVAL ‘156’ MINUTE) MINUS SELECT NAME FROM SYS.OBJ$;
22). Consulta de las tablas RECYLEBIN$ y OBJ$
SQL> SELECT MTIME, NAME, OWNER#, OBJ# FROM SYS.OBJ$ WHERE NAME LIKE ‘BIN$%’;
23). Consultas la Administracion automatica Deshacer ( UNDOTBS01.DBF)
SELECT SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,EXTENTS,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE ‘_SYSSMU%$’;
24). Consulta de los logs del Apache (Oracle Application Server)
__________________________________________________________________________________
CONSULTAS AUDITORIAS
SELECT S.SESSIONID, S.ENTRYID, S.USERHOST, S.TERMINAL,'SQL' || T.NAME,S.RETURNCODE, S.OBJ$CREATOR ,S.OBJ$NAME, S.SPARE1, S.PRIV$USED, S.NTIMESTAMP#,S.INSTANCE#,S.PROCESS#,S.XID,S.SCN ,S.DBID ,S.SQLBIND,S.SQLTEXT FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T WHERE S.ACTION# = T.ACTION AND TO_DATE(TO_CHAR(S.NTIMESTAMP#,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('20/05/2022','DD/MM/YYYY') AND TO_DATE('20/05/2022','DD/MM/YYYY') AND T.ACTION LIKE '1';
--------------------------------------------------------------------------------------------
SELECT S.SESSIONID, S.ENTRYID, S.USERHOST, S.TERMINAL,'SQL' || T.NAME,S.RETURNCODE, S.OBJ$CREATOR ,S.OBJ$NAME, S.SPARE1, S.PRIV$USED, S.NTIMESTAMP#,S.INSTANCE#,S.PROCESS#,S.XID,S.SCN ,S.DBID ,S.SQLBIND,S.SQLTEXT FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS TWHERE T.ACTION LIKE '1';
-----------------------------------------------------------------------------------------
SELECT S.SESSIONID, S.ENTRYID, S.USERHOST, S.TERMINAL,'SQL' || T.NAME,S.RETURNCODE, S.OBJ$CREATOR ,S.OBJ$NAME, S.SPARE1, S.PRIV$USED, S.NTIMESTAMP#,S.INSTANCE#,S.PROCESS#,S.XID,S.SCN ,S.DBID ,S.SQLBIND,S.SQLTEXT FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T WHERE T.ACTION like ('1') AND S.OBJ$CREATOR IS NOT NULL ORDER BY S.NTIMESTAMP#;
SELECT * FROM SYS.AUD$ S WHERE SYS.AUD$.ACTION# LIKE '1';
SELECT S.USERHOST, S.TERMINAL, S.SPARE1, S.NTIMESTAMP#, S.OBJ$CREATOR,T.NAME ,S.OBJ$NAME FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T WHERE S.ACTION# = T.ACTION AND S.OBJ$CREATOR IS NOT NULL;
--------------------------------------------------------------------------------------------AUDIT FECHAS
SELECT S.SESSIONID, S.ENTRYID, S.USERHOST, S.TERMINAL,'SQL' || T.NAME,S.RETURNCODE,S.OBJ$CREATOR ,S.OBJ$NAME, S.SPARE1, S.PRIV$USED, S.NTIMESTAMP#,S.INSTANCE#,S.PROCESS#,S.XID,S.SCN ,S.DBID ,S.SQLBIND,S.SQLTEXT FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T WHERE S.ACTION# = T.ACTION AND TO_DATE(TO_CHAR(S.NTIMESTAMP#,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('09/02/2022','DD/MM/YYYY') AND TO_DATE('10/02/2022','DD/MM/YYYY');
SELECT TO_CHAR(TO_DATE(S.NTIMESTAMP#,'DD/MM/YYYY'),'DD/MM/YYYY') FROM SYS.AUD$ S;
SELECT TO_DATE(TO_CHAR(S.NTIMESTAMP#,'DD/MM/YYYY'),'DD/MM/YYYY') FROM SYS.AUD$ S;
------------------------------------------------------------------------------------------------
SELECT S.SESSIONID, S.ENTRYID, S.USERHOST, S.TERMINAL,'SQL' || T.NAME,S.RETURNCODE, S.OBJ$CREATOR ,S.OBJ$NAME, S.SPARE1, S.PRIV$USED, S.NTIMESTAMP#,S.INSTANCE#,S.PROCESS#,S.XID,S.SCN ,S.DBID ,S.SQLBIND,S.SQLTEXT FROM SYS.AUD$ S, SYS.AUDIT_ACTIONS T WHERE T.ACTION LIKE '1' AND S.OBJ$CREATOR IS NOT NULL;