This lab has been tested in Oracle 10g express.  You may copy and paste directly from this file. Note that if you need to edit this file, it is better to do so in NOTEPAD (copy & paste it to notepad),  word sometimes puts in characters that Oracle will not recognize it.  Also, note that as you copy & paste the stored procedures and triggers, you may need to copy & paste everything except for the slash and then paste the slash. Your job is to create an audit table in MS-SQL Server.

/in /by

Lab # 7 – part 2 – DATABASE AUDITING (lab)

This lab has been tested in Oracle 10g express.  You may copy and paste directly from this file. Note that if you need to edit this file, it is better to do so in NOTEPAD (copy & paste it to notepad),  word sometimes puts in characters that Oracle will not recognize it.  Also, note that as you copy & paste the stored procedures and triggers, you may need to copy & paste everything except for the slash and then paste the slash. Your job is to create an audit table in MS-SQL Server. Then create a trigger that puts data into the table. Then issue the command that will activate the trigger. You can translate some of the triggers in this assignment, copy and paste triggers from the web-site or create your own trigger. Regardless, your deliverable should be: a small text in English explaining what the trigger is doing and why is it useful, the Audit table, the Trigger, the command that execute the trigger.

Part  0 – only if you are doing it on a newly installed version

— Open an SQL window. Create a user & grant the user dba privileges. Example:

CREATE  USER  cit540 IDENTIFIED BY  c;

GRANT  DBA   TO  cit540;  — In SQL Server, you grant all privileges to this user through login/security

— Create another user and grant this user create session privileges. Example:

CREATE  USER   smith  IDENTIFIED  BY  s;

GRANT    CREATE  SESSION   TO  smith;

Part  1 – audit login and logout

— Login as the user CIT540 with dba privileges

CONNECT  cit540/c;

— Create a table to keep track of user login and logoff

CREATE TABLE  login_logoff

(

      USERID ​VARCHAR2(30),

      SESSIONID​NUMBER(8),

      HOST​VARCHAR2(30),

      LOGIN_DAY​DATE,

      LOGIN_TIME            VARCHAR2(10),

      LOGOUT_DAY​DATE,

     LOGOUT_TIME         VARCHAR2(10)

);

/

— Create a trigger that will insert a row in the login_logoff table every time user logs in

CREATE OR REPLACE TRIGGER

     audit_login

AFTER  LOGON  ON  DATABASE

BEGIN

INSERT  INTO  login_logoff  values (

         USER,

         sys_context (‘USERENV’,’SESSIONID’),          

​  sys_context (‘USERENV’,’HOST’),

                          sysdate,

                          to_char(sysdate, ‘hh24:mi:ss’),

                          null,

                          null  );

                 COMMIT;

                 END;          

/

— Create a trigger that will insert data in a row in the login_logoff table every time user logs out

CREATE OR REPLACE TRIGGER

    audit_logoff

BEFORE  LOGOFF ON DATABASE

BEGIN

  UPDATE  login_logoff

  SET

    logout_day = sysdate, logout_time=to_char(sysdate,’hh24:mi:ss’)

WHERE

  sys_context(‘USERENV’,’SESSIONID’)=sessionid;

END;

/

–Do not close your  SQL session. Open another SQL window and login as the user that does — not have dba privilege.

connect  smith/s;

— leave the SQL session

Exit;

— connect as administrator (in this example, cit540

Connect  cit540/c;

— From the user CIT540 session that does have DBA privileges, verify the login

SELECT USERID, SESSIONID, HOST, LOGIN_DAY, LOGIN_TIME FROM LOGIN_LOGOFF;

Display. Your results.  Howe many rows did you see ?

You should see something like (but with CIT540 and Smith) :

Logout and type in:

SELECT  *  FROM  LOGIN_LOGOFF;

—————————————–

Creating Audit table and Audit Trigger

You will copy and paste everything that is in green. Deliverable: Screenshots of query with results.

1) get current date and current user

select  getDate();

select  suser_sname();

2) Create an audit table

CREATE TABLE UpdateProductAudit (date_updated  date,  who  varchar(50));

3) Create a trigger that inserts a row into the AuditProductChanges every time the Products table is updated

create trigger AuditProductChanges on Products

after update

as

begin

insert into dbo.UpdateProductAudit values(getDate(), suser_sname());

end

go

4) udate the Products table

UPDATE Products set quantity = quantity * 1.1;

5) view the update in the audit table

SELECT * from dbo.UdateProductAudit;

6) Do the exercises in the link below. Your professor will discuss the link in class.

https://www.mssqltips.com/sqlservertip/4055/create-a-simple-sql-server-trigger-to-build-an-audit-trail/

Submit the screenshots of the results. Create the table inside your own database. Alter each table name and trigger name to have your initials.  Consequently , you also need to order each reference to the table to have your initials also.

7) Go to https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-2017

And create a server audit specification (either in SSMS or SQL)