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)

The hero of “The Tell-Tale Heart” is an exemplary illustration of Poe’s inconsistent storyteller, a man who can’t be trusted to tell the target reality of what is happening. His instability turns out to be quickly obvious in the primary passage of the story, when he demands his lucidity of psyche and traits any indications of franticness to his anxiety and oversensitivity, especially in the space of hearing. Nonetheless, when he completes his presentation of mental stability, he offers a record that has a progression of clear coherent holes that must be clarified by craziness. In his compositions, Poe frequently tried to catch the perspective of crazy characters, and the storyteller of this story shows jumps of thinking that more look like the rationale of dreams than they do the points of view of a typical individual.

The storyteller’s enthusiastic precariousness gives an unmistakable counterargument to his attestations of decision making ability. In basically no cases does he react in the way that one would anticipate. He is so troubled by the elderly person’s vulture-like eye that his detesting beats his adoration for the man, driving him to plan a homicide. Afterward, when he at long last prevails with regards to killing the person in question, he turns out to be emphatically lively, feeling that he has achieved his objective shrewdly and with the soundness that he connects with mental stability. In any case, the clueless conduct of the cops recommends that the storyteller has gotten basically uninformed of his conduct and his environmental factors. Since he can’t keep up the distance among the real world and his internal contemplations, he confuses his psychological disturbance with actual unsettling and misconstrues the blameless prattle of the police officers for perniciousness. In any case, he envisions the entire time that he has accurately and reasonably deciphered every one of the occasions of the story, recommending that to Poe, the way to mindlessness is the confidence in one’s objectivity.

The incongruity of the storyteller’s record in “The Tell-Tale Heart” is that despite the fact that he broadcasts himself to be too quiet to be in any way a lunatic, he is crushed by a clamor that might be deciphered as the pulsating of his own heart. In view of the shakiness of the storyteller, it is difficult to know for certain if the beating is a powerful impact, the result of his own creative mind, or a genuine sound. In any case, a reasonable coherent clarification is that when the hero is under pressure, he hears the sound of his heart, “a low, dull, fast strong, for example, a watch wrapped in cotton,” and he confuses it with the sound of the elderly person’s heart. This absence of understanding equals his absence of consciousness of his activities as he talks with the cops and features the breaches in reason which misrepresent his cases of mental soundness.

To make an account which will persuade the peruser of the hero’s unsteadiness, Edgar Allan Poe utilizes jargon that is reliably amusing or in any case bumping to incite a response in opposition to that which the storyteller wants. The expository procedure that he utilizes in his record is to control the meanings of words, yet he is never unpretentious enough to conceal his endeavor to turn the contention. Where an external onlooker may portray him as having plotted to notice the elderly person as he dozes, the storyteller tells the peruser that “you ought to have perceived how carefully I continued – with what alert – with what foreknowledge – with what dissimulation I went to work!” By misusing his selection of words, for example, “astutely” and “alert,” he looks to misdirect the peruser and clarify his activities as those of a judicious, smart person. Notwithstanding, the glaringness of his endeavor at trickiness illuminates instead of dupes his crowd.

Much as the moment portrayal of the detainee’s encounters and faculties makes an environment of expectant fear in “The Pit and the Pendulum“, Poe’s way of depicting sound turns into an especially significant vehicle for passing on the mind-set of “The Tell-Tale Heart”. His depiction of the sound in the last couple of sections of the story is set apart by reiterations that are unmistakably planned to infer the crescendo of commotion. At the point when he says, “The ringing turned out to be more unmistakable:- – It proceeded and turned out to be more particular,” we sense the structure strain. The expanding power of the thumping is again stressed by the three redundancies of the expression “yet the commotion consistently expanded.” At long last, as the storyteller’s sentences transform quickly into interjections, his reiteration of “stronger” echoes the sound of the pulsating heart, and his last screams break the strain with his admission.