MSSQL onDelete trigger RollBack transaction and Log deleted data

I have a scenario to rollBack any delete on table and also log whatever was delete candidate

-- Original table
CREATE TABLE dbo.tblDomain(
    DoaminId INT NULL,
    Description varchar(50) NULL
     );
-- Table Population
insert into dbo.tblDomain   
select 1,'Desc1'
union All
select 2,'Desc2'
union All
select 3,'Desc3'
union All
select 4,'Desc4';

/*---============= Creating table to store deleted records.
table structure is same as dbo.tblDomain except last 2 columns
FromDeleteUser varchar (100) –- who initiated delete
DeleteDate datetime          -- datatime of delete transaction

*/
CREATE TABLE dbo.tblDomain_tmpTrg(
    DoaminId INT NULL,
    Description varchar(500) NULL,
    FromDeleteUser varchar (100),
    DeleteDate datetime.
    FromDeleteUser varchar (100),
    DeleteDate datetime
    );
  
--===== Create trigger to fire on primary table delete
CREATE TRIGGER trg_tblDomain_DeleteLog
   ON  dbo.tblDomain
   AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- use table variable to store data to be logged before rolling back transaction
    declare @trg table (      DoaminId INT NULL,
                            Description varchar(500) NULL,
                            FromDeleteUser varchar (100),
                            DeleteDate datetime
                    )
    insert @trg select * from deleted
  
    ROLLBACK
    -- transacion rolled-back, but data is still available in @trg
    -- log it into proper table
    insert dbo.tblDomain_tmpTrg
    select * , suser_sname(), getutcdate()
    from @trg
    -- re-raise the error to make it clear!
    raiserror ('Delete from table dbo.dbo.tblDomain is not allowed, your action is logged!',16,1)
  
END
GO

--===================== Verification
--SELECT * FROM dbo.tblDomain;
--DELETE FROM dbo.tblDomain ;
--SELECT  * FROM  dbo.tblDomain_tmpTrg;
--=== Drop objects
--DROP TRIGGER trg_tblDomain_DeleteLog;
--DROP dbo.tblDomain_tmpTrg;

SQL Server Trigger - Insert the deleted record into another table with deletetime

 SQL Server Trigger - Insert the deleted record into another table with deletetime

/*
Author: Tanveer Haider Baig
Description : Trigger to fire after delete on table and populate deleted records to another table with timestamp
*/


--Create Primary table
CREATE TABLE tblTrgTest1
(
id INT,
NAME VARCHAR(10)
);
--=== Populating Table
INSERT INTO tblTrgTest1
SELECT 1,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c'
UNION ALL
SELECT 4,'d'
UNION ALL
SELECT 5,'e';
--=== Retrivening Results
SELECT * FROM tblTrgTest1;

--===== Create secondary table to host deleted values
CREATE TABLE tblTrgTest2
(
id INT,
NAME VARCHAR(10),
delDate DATETIME
);

--===== Create trigger to fire on primary table delete
CREATE TRIGGER trgTableDeleteLog
ON tblTrgTest1
AFTER DELETE
AS
INSERT INTO tblTrgTest2
SELECT * ,GETDATE()
FROM DELETED;

---===== delete records to test trigger
DELETE FROM tblTrgTest1 WHERE id IN (1,2);
DELETE FROM tblTrgTest1 WHERE id IN (3,5);

--Verify deleted records come to secondry table with timestamp
SELECT * FROM tblTrgTest2;

--Verify delete was successfull on primary
SELECT * FROM tblTrgTest1;

--=== Drop objects
DROP TRIGGER trgTableDeleteLog;
DROP TABLE tblTrgTest1;
DROP TABLE tblTrgTest2;

Oracle 10g User Auditing

I start receiving  oracle alerts for excessive sessions. So I decided to audit user to find source IP that request for session.


view audit status
SQL> SHOW PARAMETER AUDIT

audit_trail options:
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting:
    none or false - Auditing is disabled.
    db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
    db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
    xml- Auditing is enabled, with all audit records stored as XML format OS files.
    xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
    os- Auditing is enabled, with all audit records directed to the operating system's audit trail
   
   
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

SQL> SHUTDOWN

SQL> STARTUP

--Auditing only single user connection
CONNECT sys/password AS SYSDBA

SQL> audit connect by MyUser;
   
    --View audit info       
SQL> select userhost, terminal, timestamp, action_name,comment_text from dba_audit_session where username=’MYUser’;

comment_text holds IP address.

You can format your results with the following query.

select count(*),action_name,audi.username,substr(audi.s1,1,instr(audi.s1,')')-1) IP from
(
select username,action_name,substr(comment_text,instr(comment_text,'HOST=')+5,100) s1
from dba_audit_trail
where username='MYUSER'
--and action_name ='LOGON'
--and action_name in ('LOGON','LOGOFF')
)audi
group by audi.username,substr(audi.s1,1,instr(audi.s1,')')-1),action_name
order by 1 desc ;



-- Fetch Audit related views in database
    SELECT view_name
    FROM   dba_views
    WHERE  view_name LIKE 'DBA%AUDIT%'
    ORDER BY view_name;

    VIEW_NAME
    ------------------------------
    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

    14 rows selected.

    SQL>

The three main views are shown below.

    DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
    DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
    DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.

Oracle 10g user login Audit

you can audit user login or logoff request with triggers as well.


following needed

1. Table that will hold trigger output
2. Login trigger (in sys schema)
3. Logoff trigger (in sys schema)

 --------------------------------------------------------
--  DDL for Trigger output table
--------------------------------------------------------

CREATE TABLE "Schema"."TBLUSERCONNECTIONAUDIT"
   (    "DATE_CON" DATE,
    "OSUSER" VARCHAR2(100 BYTE),
    "PROGRAM" VARCHAR2(100 BYTE),
    "MACHINE" VARCHAR2(100 BYTE),
    "IP" VARCHAR2(100 BYTE),
    "ACTION" VARCHAR2(30 BYTE)
   )

--------------------------------------------------------
--  DDL for Trigger TRG_LOGON_AUDIT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SYS"."TRG_LOGON_AUDIT"
AFTER LOGON ON DATABASE
DECLARE
v_prog SYS.v_$session.program%TYPE;
v_owner SYS.v_$session.username%TYPE;
v_osuser SYS.v_$session.osuser%TYPE;
v_machine SYS.v_$session.machine%TYPE;
v_ip VARCHAR2 (20);
BEGIN
-- Change user name as required
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'USERNAME' THEN
SELECT program, username, osuser, machine,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
INTO v_prog, v_owner, v_osuser, v_machine,
v_ip
FROM SYS.v_$session
WHERE audsid = USERENV ('SESSIONID')
AND audsid != 0
AND ROWNUM = 1;

IF UPPER (v_prog) NOT LIKE '%PL%SQL%'
THEN
insert into sanmar.tblUserConnectionAudit
(DATE_CON,
osuser, program, machine, ip,action
)
VALUES (SYSDATE,
v_osuser, v_prog, v_machine, v_ip,'LOGON'
);
ELSE
NULL; -- Logging the error
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
--* Logging the error

RAISE;
END;
/
ALTER TRIGGER "SYS"."TRG_LOGON_AUDIT" ENABLE;



--------------------------------------------------------
--  DDL for Trigger TRG_LOGOFF_AUDIT
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SYS"."TRG_LOGOFF_AUDIT"
Before LOGOFF ON DATABASE
DECLARE
v_prog SYS.v_$session.program%TYPE;
v_owner SYS.v_$session.username%TYPE;
v_osuser SYS.v_$session.osuser%TYPE;
v_machine SYS.v_$session.machine%TYPE;
v_ip VARCHAR2 (20);
BEGIN
-- Change user name as required
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'USERNAME' THEN
SELECT program, username, osuser, machine,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
INTO v_prog, v_owner, v_osuser, v_machine,
v_ip
FROM SYS.v_$session
WHERE audsid = USERENV ('SESSIONID')
AND audsid != 0
AND ROWNUM = 1;

IF UPPER (v_prog) NOT LIKE '%PL%SQL%'
THEN
insert into sanmar.tblUserConnectionAudit
(DATE_CON,
osuser, program, machine, ip,action
)
VALUES (SYSDATE,
v_osuser, v_prog, v_machine, v_ip,'LOGOFF'
);
ELSE
NULL; -- Logging the error
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
--* Logging the error

RAISE;
END;
/
ALTER TRIGGER "SYS"."TRG_LOGOFF_AUDIT" ENABLE;

Auto Start Oracle on Linux

Create the file /etc/init.d/dbora like this :

#!/bin/bash
#
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
#
ORA_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 <--- to be changed
ORA_OWNER=oracle

case "$1" in
'start')
# Start the Oracle databases:
echo "Starting the Oracle databases"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
# Start the TNS listener
# su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
touch /var/lock/subsys/dbora
;;
'stop')
# Stop the TNS listener
# su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# STOP the Oracle databases
echo "Shutting down the Oracle databases"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
rm -f /var/lock/subsys/dbora
;;
*)
echo "Usage: dbora (start|stop)"
exit 1
esac

Then, issue the commands :

chmod 755 /etc/init.d/dbora
/sbin/chkconfig dbora on

Check also in the file /etc/oratab that the database(s) are configured to start automatically (you need a Y at the end of the line for each database :

ex :

APPSPROD:/u01/app/oracle/product/11.2.0/dbhome_1:Y

That's all !

 
Courtesy  :Michel Berger