DATABASE SECURITY AND AUDITING: AUDITING GAME: A database specialist was hired b

Question

DATABASE SECURITY AND AUDITING:

AUDITING GAME:

A database specialist was hired by a company that manufactures video games. The new hire was directed to create and develop a marketing data warehouse. Five of the auditing requirements on his task list were:

1. Create the mechanism that will track all user SQL statements activity.

2. Record the times of logon and logoff, the program used to connect, and all available operating system information.

3. Disallow users from logging onto the database before 8:00A.M and after 5:00P.M.

4. Disable any user account that has not been used within the lat 17working days.

5. Track all DDL activities to a user- specified table.

Implement this solution in SQL SERVER.

Explanation / Answer

1)

Track all user viewing activity:
-------------------------------------------------------------------
audit execute procedure by FRED by access;

Using this command we can track or audit all activities done by user.

----------------------------------------------------------------------------
2)

we will create table which stores log on log off values....then we will dig all activities that we require

create table lod_data (name varchar2(30), time date, action varchar2(10));

create or replace trigger tr_logon
after logon on database
begin
insert into lod_data values (user, sysdate, 'LOGON');
commit;
end tr_logon;

create or replace trigger tr_logoff
before logoff on database
begin
insert into lod_data values (user, sysdate, 'LOGOFF');
commit;
end tr_logon;

--------------------------------------------------------------------------------------
3) 4)
CREATE OR REPLACE PROCEDURE block_at_times
AUTHID CURRENT_USER
AS
BEGIN
IF (SYS_CONTEXT ('usernmae','ip Address')
BETWEEN '192.1.2.30' and '192.1.2.40'
AND
TO_CHAR (SYSDATE, 'HH24') BETWEEN 8 AND 17)
THEN
EXECUTE IMMEDIATE 'SET ROLE hr_admin';
END IF;
END;

-----------------------------------------
5)
audit all by FRED by access;

this command will track all ddl activities

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.