Cambio en una Tabla

LuisMarquez
17 de Septiembre del 2004
Buenos dias, quisiera saber si existe alguna manera de saber el usuario quien hizo un cambio en un registro de una Tabla ORACLE

Gracias.
Luis Marquez

LuisMarquez
17 de Septiembre del 2004
Existe alguna manera de activar auditoria a un grupo de tablas o usuario y como se hace?

sys
17 de Septiembre del 2004
Scope & Application: ~~~~~~~~~~~~~~~~~~~~ This article intends to demonstrate some examples of output of auditing. The article is divided in 1. Auditing administrative connections 2. Auditing on object Level 3. Auditing on privilege Level 4. Auditing statements Shown is for each section what can be audited, how to check what is audited and some examples of the output of auditing. Auditing: ~~~~~~~~~ Auditing can be set on statement level, object level and privilege level. Also administrative connections are logged. 1. Auditing administrative connections -------------------------------------- Administrative connections are CONNECT INTERNAL and CONNECT / AS SYSDBA. There are some good examples of output of audited administrative connections for Unix and for NT in: Note 103964.1: RDBMS: How to Audit Connect Internal Using Oracle Server Note 99137.1 : Setting up, Interpreting Auditing Using Windows NT Event Viewer In RDBMS ver. 9.2 and up, it is now possible to audit the SYS user with the parameter AUDIT_SYS_OPERATIONS , for details, please check this note: Note:174340.1 : Audit SYS user Operations 2. Auditing on object Level --------------------------- Objects that can be audited are : tables, views, sequences, packages, stored procedures/functions. Note that because some objects may be dependant of other objects (example function -> view -> table ) as a result several audit records might be inserted when these objects are audited. Object auditing options are set for all users of the database and cannot be set for individual users. What options can be set? This can be seen from all_def_audit_opts. Example object auditing options ------------------------------- SQL> connect system/manager SQL> select * from all_def_audit_opts; Result: ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE --- --- --- --- --- --- --- --- --- --- --- --- --- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- Example of auditing scott.emp ----------------------------- SQL> connect system/manager SQL> audit select on scott.emp by session; => Audit succeeded. Check: Which objects are audited SQL> col owner format a7 SQL> col object_name format a7 SQL> select * from dba_obj_audit_opts where owner='SCOTT' and OBJECT_NAME='EMP'; result: OWNER OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE ------- ------- --------- --- --- --- --- --- --- --- --- --- --- --- --- --- SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/- Generate some audit information: SQL> connect scott/tiger SQL> select * from emp; => all scott.emp's rows are shown SQL> connect t/tl SQL> select * from scott.emp; => ERROR at line 1: ORA-00942: table or view does not exist SQL> connect system/manager SQL> select * from scott.emp; => all scott.emp's rows are shown Results of auditing: SQL> connect system/manager SQL> col username format a8 SQL> col priv_used format 999 SQL> / SQL> select username, priv_used, ses_actions from dba_audit_object where obj_name='EMP' and owner='SCOTT'; Result: USERNAME PRIV_USED SES_ACTIONS -------- ---------------------------------------- ---------------- SYSTEM SELECT ANY TABLE ---------S--- T ---------F--- SCOTT ---------S--- 3. Auditing on privilege Level ------------------------------ All system privileges can be audited. The different privileges can be selected from system_privilege_map. If you attempt to use a value that does not belong to the list, you get the following error: SQL> audit drop snapshot by access; audit drop snapshot by access * ERROR at line 1: ORA-00956: missing or invalid auditing option Example: auditable system privileges ------------------------------------ SQL> connect system/manager SQL> select * from system_privilege_map; Result (this result is version dependant and still going up): PRIVILEGE NAME ---------- ---------------------------------------- -3 ALTER SYSTEM -4 AUDIT SYSTEM -5 CREATE SESSION -6 ALTER SESSION -7 RESTRICTED SESSION -10 CREATE TABLESPACE -11 ALTER TABLESPACE -12 MANAGE TABLESPACE -13 DROP TABLESPACE ..... not entire result is shown ...... -167 GRANT ANY PRIVILEGE -172 CREATE SNAPSHOT -173 CREATE ANY SNAPSHOT -174 ALTER ANY SNAPSHOT -175 DROP ANY SNAPSHOT -194 WRITEDOWN DBLOW -195 READUP DBHIGH -196 WRITEUP DBHIGH -197 WRITEDOWN -198 READUP -199 WRITEUP Example of auditing CREATE TABLE by scott and system ---------------------------------------------------- SQL> connect system/manager SQL> audit create table by scott, system; => Audit succeeded. This means each create table is audited for system and scott once per session. Should each create table be audited then the clause 'by access' must be added. check: Which privileges are audited SQL> col user_name format a7 SQL> col privilege format a15 SQL> connect system/manager SQL> select * from sys.dba_priv_audit_opts; Result: USER_NA PRIVILEGE SUCCESS FAILURE ------- --------------- ---------- ---------- SCOTT CREATE TABLE BY ACCESS BY ACCESS SYSTEM CREATE TABLE BY ACCESS BY ACCESS Generate some audit information: SQL> connect scott/tiger SQL> create table t1 (c1 number); create table t1 (c1 number) * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> create table tsc (c1 number); => table created SQL> connect t/tl SQL> create table tsc (c1 number); => table created SQL> connect system/manager SQL> create table scott.t1(c1 number); => create table scott.t1(c1 number) ERROR at line 1: ORA-00955: name is already used by an existing object SQL> create table tsc (c1 number); => table created Results of auditing: SQL> connect system/manager SQL> col username format a8 SQL> col priv_used format 999 SQL> / SQL> select username, priv_used, ses_actions from dba_audit_object; Result: USERNAME PRIV_USED SES_ACTIONS -------- ---------------------------------------- ------------------- SCOTT CREATE TABLE SCOTT CREATE TABLE SYSTEM CREATE TABLE SQL> connect system/manager SQL> select action, action_name, username from dba_audit_trail ; Result: ACTION ACTION_NAME USERNAME ---------- --------------------------- -------- 1 CREATE TABLE SCOTT 1 CREATE TABLE SCOTT 1 CREATE TABLE SYSTEM 4. Auditing statements ---------------------- Statement auditing is the selective auditing of related groups of statements for a type of database structure or type of schema object for ddl (data definition language) or dml (data manipulation language) statements. For example when 'audit table' is audited create, alter and drop table statements are audited. When 'audit select table' is audited all select statements from table, view, snapshot.. will be audited. The difference with privilege auditing is that in the latter case a system privilege is audited. For example the 'audit create table' will only audit create table and not alter or drop table statements. When similar statement and privilege options are set only one audit record is generated. Example: auditable statements ----------------------------- The statements taht can be adited can be seen from stmt_audit_option_map. SQL> connect system/manager SQL> select * from stmt_audit_option_map; Result (this result is version dependant and still going up): OPTION# NAME PROPERTY ---------- ---------------------------------------- ---------- 3 ALTER SYSTEM 0 4 SYSTEM AUDIT 0 5 CREATE SESSION 0 6 ALTER SESSION 0 7 RESTRICTED SESSION 0 8 TABLE 0 9 CLUSTER 0 10 CREATE TABLESPACE 0 11 ALTER TABLESPACE 0 12 MANAGE TABLESPACE 0 13 DROP TABLESPACE 0 ..... not entire result is shown ...... 221 CONTEXT 0 234 ON COMMIT REFRESH 0 235 EXEMPT ACCESS POLICY 0 236 RESUMABLE 0 237 SELECT ANY DICTIONARY 0 238 DEBUG CONNECT SESSION 0 239 DEBUG CONNECT USER 0 240 DEBUG CONNECT ANY 0 241 DEBUG ANY PROCEDURE 0 242 DEBUG PROCEDURE 0 165 rows selected. SQL> select * from stmt_audit_option_map where name like '%TABLE%'; Result: OPTION# NAME PROPERTY ---------- ---------------------------------------- ---------- 8 TABLE 0 10 CREATE TABLESPACE 0 11 ALTER TABLESPACE 0 12 MANAGE TABLESPACE 0 13 DROP TABLESPACE 0 14 TABLESPACE 0 15 UNLIMITED TABLESPACE 0 40 CREATE TABLE 0 41 CREATE ANY TABLE 0 42 ALTER ANY TABLE 0 43 BACKUP ANY TABLE 0 44 DROP ANY TABLE 0 45 LOCK ANY TABLE 0 46 COMMENT ANY TABLE 0 47 SELECT ANY TABLE 0 48 INSERT ANY TABLE 0 49 UPDATE ANY TABLE 0 50 DELETE ANY TABLE 0 54 ALTER TABLE 0 57 LOCK TABLE 0 58 COMMENT TABLE 0 65 SELECT TABLE 0 66 INSERT TABLE 0 67 UPDATE TABLE 0 68 DELETE TABLE 0 69 GRANT TABLE 0 Example of auditing TABLE by scott and system --------------------------------------------- SQL> connect system/manager SQL> audit table by scott, system; => Audit succeeded. check: Which statements are audited SQL> col user_name format a8 SQL> col proxy_name format a6 SQL> col audit_option format a9 SQL> col privilege format a15 SQL> connect system/manager SQL> select * from dba_stmt_audit_opts; Result: USER_NAM PROXY_ AUDIT_OPT SUCCESS FAILURE -------- ------ --------- ---------- ---------- SYSTEM TABLE BY ACCESS BY ACCESS SCOTT TABLE BY ACCESS BY ACCESS Generate some audit information: SQL> connect scott/tiger SQL> create table t1 (c1 number); create table t1 (c1 number) * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> drop table t1; => table dropped SQL> connect system/manager SQL> create table scott.t1(c1 number); => table created SQL> drop table no; => * ERROR at line 1: ORA-00942: table or view does not exist Results of auditing: SQL> connect system/manager SQL> col username format a8 SQL> col priv_used format 999 SQL> select username, priv_used, ses_actions from dba_audit_object; Result: USERNAME PRIV_USED SES_ACTIONS -------- ---------------------------------------- ------------------- SCOTT CREATE TABLE SCOTT CREATE TABLE SYSTEM CREATE TABLE SQL> connect system/manager SQL> select action, action_name, username from dba_audit_trail ; Result: ACTION ACTION_NAME USERNAME ---------- --------------------------- -------- 1 CREATE TABLE SCOTT 12 DROP TABLE SCOTT 1 CREATE TABLE SYSTEM 12 DROP TABLE SYSTEM SQL> select username, priv_used, ses_actions from dba_audit_object; Result: USERNAME PRIV_USED SES_ACTIONS -------- ---------------------------------------- ------------------- SCOTT CREATE TABLE SCOTT SYSTEM CREATE ANY TABLE SYSTEM Example of auditing SELECT TABLE by scott and system ---------------------------------------------------- SQL> connect system/manager SQL> audit select table by scott, system; => Audit succeeded. Check: Which statements are audited SQL> col user_name format a8 SQL> col proxy_name format a6 SQL> col audit_option format a13 SQL> col privilege format a15 SQL> connect system/manager SQL> select * from dba_stmt_audit_opts; Result: USER_NAM PROXY_ AUDIT_OPTION SUCCESS FAILURE -------- ------ ------------- ---------- ---------- SYSTEM SELECT TABLE BY SESSION BY SESSION SCOTT SELECT TABLE BY SESSION BY SESSION Generate some audit information: SQL> connect scott/tiger SQL> delete from emp where ename='KING'; SQL> connect system/manager SQL> insert into scott.emp (empno, ename) values (1, 'TEST'); SQL> select * from scott.emp; Results of auditing: SQL> connect system/manager SQL> col username format a8 SQL> col priv_used format 999 SQL> select username, priv_used, ses_actions from dba_audit_object; Result: USERNAME PRIV_USED SES_ACTIONS -------- ---------------------------------------- ------------------- SCOTT ---------S------ SCOTT ---------S------ SYSTEM ---------S------ SYSTEM SELECT ANY TABLE ---------S------ SYSTEM ---------S------ SYSTEM ---------S------ SYSTEM ---------S------ SCOTT ---------S------ SYSTEM ---------S------ SYSTEM ---------S------ SYSTEM ---------S------ SQL> connect system/manager SQL> select action, action_name, username from dba_audit_trail ; Result: ACTION ACTION_NAME USERNAME ---------- --------------------------- -------- 103 SESSION REC SCOTT 103 SESSION REC SCOTT 103 SESSION REC SYSTEM 103 SESSION REC SYSTEM 103 SESSION REC SYSTEM 103 SESSION REC SYSTEM 103 SESSION REC SYSTEM 103 SESSION REC SCOTT 103 SESSION REC SYSTEM 103 SESSION REC SYSTEM 103 SESSION REC SYSTEM 103 SESSION REC SYSTEM