Lab essay
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) -research paper writing service