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
Save your time - order a paper!
Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlinesOrder Paper Now
— 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
— Create a table to keep track of user login and logoff
CREATE TABLE login_logoff
— Create a trigger that will insert a row in the login_logoff table every time user logs in
CREATE OR REPLACE TRIGGER
AFTER LOGON ON DATABASE
INSERT INTO login_logoff values (
— Create a trigger that will insert data in a row in the login_logoff table every time user logs out
CREATE OR REPLACE TRIGGER
BEFORE LOGOFF ON DATABASE
logout_day = sysdate, logout_time=to_char(sysdate,’hh24:mi:ss’)
–Do not close your SQL session. Open another SQL window and login as the user that does — not have dba privilege.
— leave the SQL session
— connect as administrator (in this example, cit540
— 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
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
insert into dbo.UpdateProductAudit values(getDate(), suser_sname());
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.
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)