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),
SESSIONIDNUMBER(8),
HOSTVARCHAR2(30),
LOGIN_DAYDATE,
LOGIN_TIME VARCHAR2(10),
LOGOUT_DAYDATE,
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)
