Phase 1 Implementing Oracle Fine-Grained Auditing (FGA) In a previous lab, you h

Question

Phase 1 Implementing Oracle Fine-Grained Auditing (FGA)

In a previous lab, you have created DBSEC user and CUSTOMER table.

In this lab, you will implement a FGA case. The CUSTOMER table will be the one you created on the previous lab. Your task is to design and implement the following auditing functions on the CUSTOMER table.

Track all "SELECT" activities on the CUSTOMER table, including database user, operating system user, and time when the operation is performed.

Track the changes when CREDIT_LIMIT is set to a value above $5,000.

Track the CUSTOMER table when a customer record was deleted.

You need to generate one SQL script file to perform all the following tasks:

Add a policy to implement audit task 1 (SELECT). The policy will be named as AUDIT_POLICY_1_SELECT.

Add a policy to implement audit task 2 (CREDIT_LIMIT). The policy will be named as AUDIT_POLICY_2_CREDIT.

Add a policy to implement audit task 3 (DELETE on CUSTOMER table). The policy will be named as AUDIT_POLICY_3_DELETE.

Turn on the audit function.

Connect to user DBSEC, and perform the following DML functions for testing purpose:

SELECT CUSTOMER_ID, FIRST_NAME,LAST_NAME,CREDIT_LIMIT
FROM customer
WHERE CUSTOMER_ID=201340;

UPDATE customer SET
CREDIT_LIMIT=2500000
WHERE CUSTOMER_ID=201340;

DELETE from CUSTOMER WHERE CUSTOMER_ID=201340;

Show the audit trail including when and who has used/modified the CUSTOMER table.

Roll back the above change.

Tips:

You may use a drop user statement to refresh your database and enable testing. Then, you need to recreate your CUSTOMER table. But, it is not required as a part of this project.

When you need to connect to SYSTEM user, please use a syntax so that it will ask the user to type the password. A typical example will be: "connect system;"

Phase 2

Using an Oracle select an existing user and audit all its activities.

Using Oracle:

Step #1: Connect as SYSTEM and make sure that the following parameters are set:

SQL> show parameter aud

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----

audit_sys_operations                 boolean     TRUE

audit_trail                          string      DB

Step #2: Issue audit statement as follows:

AUDIT ALL BY DBSEC_CLERK

Step #3: Connect as DBSEC_CLERK and perform one DDL and DML statement.

Step #4: Connect as SYSTEM and retrieve the content of DBA_AUDIT_TRAIL.

Previous CUSTOMER TABLE:

Column Name

Data Type

SALES_REP_ID

NUMBER(4)

CUSTOMER_ID

NUMBER(8) NOT NULL

CUSTOMER_SSN

VARCHAR2(9)

FIRST_NAME

VARCHAR2(20)

LAST_NAME

VARCHAR2(20)

ADDR_LINE

VARCHAR2(80)

CITY

VARCHAR2(30)

STATE

VARCHAR2(30)

ZIP_CODE

VARCHAR2(9)

PHONE

VARCHAR2(15)

EMAIL

VARCHAR2(80)

CC_NUMBER

VARCHAR2(20)

CREDIT_LIMIT

NUMBER

GENDER

CHAR(1)

STATUS

CHAR(1)

COMMENTS

VARCHAR2(1024)

USER_NAME

VARCHAR2(30)

SELECT CUSTOMER_ID, FIRST_NAME,LAST_NAME,CREDIT_LIMIT
FROM customer
WHERE CUSTOMER_ID=201340;

UPDATE customer SET
CREDIT_LIMIT=2500000
WHERE CUSTOMER_ID=201340;

DELETE from CUSTOMER WHERE CUSTOMER_ID=201340;

Explanation / Answer

Connect system; ----------------------------------Creating the user----------------------------------- Drop user DBSEC_ADMIN cascade; create user DBSEC_ADMIN identified by "tec5363admin" Default tablespace USERS Temporary tablespace TEMP; Drop user DBSEC_CLERK cascade; create user DBSEC_CLERK identified by "tec5363clerk" Default tablespace USERS Temporary tablespace TEMP; Drop user DBSEC_DEV cascade; create user DBSEC_DEV identified by "tec5363dev" Default tablespace USERS Temporary tablespace TEMP; Drop user DBSEC cascade; create user DBSEC identified by "tec5363#1" Default tablespace USERS Temporary tablespace TEMP; Drop user VPD_CLERK1 cascade; create user VPD_CLERK1 identified by "jessie$22" Default tablespace USERS Temporary tablespace TEMP; Drop user VPD_CLERK2 cascade; create user VPD_CLERK2 identified by "lassie$46" Default tablespace USERS Temporary tablespace TEMP; grant create session, create table to DBSEC_ADMIN; grant create session, create table to DBSEC_CLERK; grant create session, create table to DBSEC_DEV; grant create session, create view, create table to DBSEC; grant create session, create table to VPD_CLERK1; grant create session, create table to VPD_CLERK2; grant all privileges to DBSEC; ---------------------------------------connecting to the specific user and creating the customer table------------------------------------ connect DBSEC/tec5363#1; --------------droping the table-------------- drop table CUSTOMER; ---------------creating the table------------ CREATE TABLE CUSTOMER ( SALES_REP_ID NUMBER(4) null, CUSTOMER_ID NUMBER(8) NOT NULL, CUSTOMER_SSN VARCHAR2(9) null, FIRST_NAME VARCHAR2(20) null, LAST_NAME VARCHAR2(20) null, ADDR_LINE VARCHAR2(80) null, CITY VARCHAR2(30) null, STATE VARCHAR2(30) null, ZIP_CODE VARCHAR2(9) null, PHONE VARCHAR2(15) null, EMAIL VARCHAR2(80) null, CC_NUMBER VARCHAR2(20) null, CREDIT_LIMIT NUMBER null, GENDER CHAR(1) null, STATUS CHAR(1) null, COMMENTS VARCHAR2(1024) null, CTL_UPD_DTTM VARCHAR2(9) null, CTL_UPD_USER VARCHAR2(30) null, CTL_REC_STAT CHAR(1) null ); -----------------------------------------------------populating the given table using the given data---------------------------------- INSERT INTO CUSTOMER ( SALES_REP_ID, CUSTOMER_ID, CUSTOMER_SSN, FIRST_NAME, LAST_NAME, ADDR_LINE, CITY, STATE, ZIP_CODE, PHONE, EMAIL, CC_NUMBER, CREDIT_LIMIT, GENDER, STATUS, COMMENTS, CTL_UPD_DTTM, CTL_UPD_USER, CTL_REC_STAT ) VALUES ( 6415, 201340, '969996970', 'Jeffrey', 'Antoine', '9938 Moreno St.', 'Champagne', 'SD' , ' 43172', '4319071613', 'JAntoine@iodmgpvjdzprccx.gov', '231146832737266', 200000 , 'M', 'A', '/T if= 50000', - audit_column => NULL, - handler_schema => NULL, - handler_module => NULL, - enable => TRUE, - statement_types => 'UPDATE'); ---------------------------droping the delete policy---------------------- EXEC DBMS_FGA.DROP_POLICY (OBJECT_SCHEMA => 'DBSEC', - OBJECT_NAME => 'customer', - POLICY_NAME => 'AUDIT_POLICY_3_DELETE'); ---------------------------creating the third policy_delete------------- EXEC DBMS_FGA.ADD_POLICY (object_schema => 'DBSEC', - object_name => 'CUSTOMER', - policy_name => 'AUDIT_POLICY_3_DELETE', - audit_condition => NULL, - audit_column => NULL, - handler_schema => NULL, - handler_module => NULL, - enable => TRUE, - statement_types => 'DELETE'); audit SELECT, UPDATE, DELETE on sys.aud$ by access; connect DBSEC/tec5363#1; -----------------------------giving permissions to the specific user----------------------------------- grant SELECT, INSERT, UPDATE, DELETE on CUSTOMER to VPD_CLERK1; grant SELECT, INSERT, UPDATE, DELETE on CUSTOMER to VPD_CLERK2; ------------------------------------connecting to the vpd user to perform audit function-------------------- connect VPD_CLERK1/jessie$22; select first_name, last_name from DBSEC.CUSTOMER where CUSTOMER_ID = 103244; update DBSEC.CUSTOMER set credit_limit=50000 where CUSTOMER_ID=103244; delete from DBSEC.CUSTOMER where CUSTOMER_ID=103244; rollback; commit; ------------------------------------connecting to VPD2 to perform the audit function------------------------------- connect VPD_CLERK2/lassie$46; select first_name, last_name from DBSEC.CUSTOMER where CUSTOMER_ID = 103244; update DBSEC.CUSTOMER set credit_limit=50000 where CUSTOMER_ID=103244; delete from DBSEC.CUSTOMER where CUSTOMER_ID=103244; rollback; commit;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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