Lab

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;