Database Security EXPERT

It should be logically organized and professionally done (no spelling errors, clear writing). The lab project must be submitted as ONE DOCUMENT in either WORD OR PDF FORMAT. You might have to alter the tables a bit, create a new table or add rows that can be used to illustrate that your security works. If you don’t want to reuse an old project you have the option of starting from scratch (from a blank sheet of paper). The comments below cover everything I can think of that could be in your project. There are options, e.g., if you can fully implement your security with roles, views, grants, procedures, and triggers you would not have to have any security labels or a VPD. If you implement your security with VPD you might not need any roles, views, or triggers. If you implemented your security with the Oracle Security Labels you might not need any VPD etc. Not every example I give below applies to every project. The lab project report should be as long as it needs to be. I have had reports from 20-30 pages to 160 pages. Long listings of big tables to not add value, but do inflate page length! You probably need less than 10-15 rows in most of the tables. Example of security implementation should be targeted (an update against 1-3 rows not the whole table so that you then demonstrate

It should have sections such as

Introduction

Timeline

Conceptual & Logical Data Model

Physical Design

Implementation

Security

Verification of Database Security Implementation

Introduction:

What is the project about? (A pet store, a small corner supermarket, a

doctor’s office, etc). What are the key users and what are their functions?

(A pet store has an owner, clerks, and maybe an office manger that does

the banking, payroll)

Timeline:

the logical series of steps that must be done and when they must be done

and what order

Conceptual Model:

The functional roles of the various users and database and user

requirements, design assumptions, entity types and relationships, ER

diagram(s), constraints/dependencies,

Logical Model:

Rules Used to Map ER Diagram to Relations, ER-to-Relational Mapping,

Map of ERD to relations (tables), functional dependencies, normalized

relations (should all be 3NF or BCNF) Notional Data Elements for each

Table

Physical Model:

DDL to create basic relations (tables, indexes if any, non security related

triggers, procedures or functions). Security DDL/DML should be in the

security section

Implementation:

Insert statements into Tables, queries to show table populations (Select *)

– you should only have a small number of rows in each table, enough not

to be trivial, but no need to have more that one-five record of each type

(one owner, 2-3 clerks, one office manager, three different types of pet,

etc); examples of the most frequent queries for the business (e.g., what

pets do we have in inventory, what is the cheapest or most expensive pet

(could list by price) what was the payroll last week, month, year)

Security:

The most important section: What is your security plan? What are your

security policies that support that plan? What security procedures will

support the plan and policies? What is your security model? And the

security implementation (users created with passwords, roles created,

privileges granted, views created for security reasons, security triggers,

procedures, functions, labels, etc). Evidence of the implementation (scripts that show the DDL to create the users, grant the privileges, etc and the results (able created, grants affected, etc). Evidence that the security works as it is supposed to (users can do what they are supposed to be able and can not do things that they are prohibited from doing or that they are not explicitly supposed to do

References: If any

Attached is only an example

(

Lab Project

)

(
2
)

Table of Contents
Introduction 4

Timeline

5
Conceptual Model: 6

Users:

6

Views:

7

Roles:

7
Logical Model: 8
Physical Model: 9

Security Implementation:

21

USER CREATION

21

CREATE ROLES AND GRANT PRIVILEGES

21
Security Policy A: 22
Test Case scenario 1: 22
Test Case Scenario 2: 23
Test Case scenario 3: 23
Test case scenario 4: 23
Security Policy B: 24
Test case scenario 1: 24
Test Case Scenario 2: 24
Security Policy C: 25
Test case scenario 1: 25
Test case scenario 2: 26
Security Policy D: 27
Test case scenario 1: 27
Test case scenario 2: 28
Security Policy E: 29
Test case scenario 1: 29
Test case scenario 1: 34
Security Policy F: 36
Test case scenario 1: 36
Test case scenario 2: 39
Security Policy G: 40

CREATE TABLE

41

CREATE POLICY

41

CREATE SECURITY LEVELS: U, C, S, TS

41

CREATE SECURITY LABELS: U, C, S, TS

42

GRANT USERS PRIVILEGES TO THE TABLE

42

SET USER LEVELS

43

APPLY THE POLICY TO THE USERLVL TABLE

44

INSERT DATA INTO USERLVL TABLE

44

VIEW ACCESS FOR EACH USER LEVEL

45

Introduction

Purest Retail Store has recently encountered database security breaches. The database stores customer’s personal information and their purchases, so it is important that only authorized users have access to the data in the database. The company’s internal IT staff built the database with no constraints on the tables to prevent duplication and unauthorized users from viewing the data.

The owner of the store lost lots of loyal customers due to the breaches and tasked the Security Manager to find a solution to ensure that the data in the database is more secure and only accessible to authorized users. The security manager did a full risk analysis and determined that the IT department needs to establish better security practices and take the necessary measurements to improve the security of the database and its users. In addition, the security policy and plan should be frequently reviewed and updated to reflect the growing changes of the database and users. The following are the necessary implementation of database security measures:

· Security Policy and Plan

· Security procedures

· Account setup for users that includes passwords

· Assigning roles and privileges to users

· Establishing Access Control

· Granting authorized users access to the database

Timeline

Because the company has suffered great loss in both revenue and customers, it is important that these security measures are implemented immediately. The following is the timeline of completing the security policy and procedures.

Conceptual Model:

The following table consists of all the users who have access to the database. Each user is assigned a unique userid and password, specific privileges, and a role. The following are roles:

DBA Role – The DBA role will have full control of the database and can review any activities conducted by any of the other users

CustomerLookup Role – The customer lookup role allows authorized users to select from the customers table and the orders and order_detail table

ETLUSER Role – This role is for any authorized user to be able to insert records into the table, delete, update and select.

Employee Role – allows authorized users to lookup their own account.

Users:

User Name

Userid

Privileges

Database Access

ROLE

Jack Walker

JWALKER

CREATE SESSION

DBA

ADMIN01, EMP

Bev Smith

BSMITH

CREATE SESSION

Insert, Select, Update To

CUSTOMER

& Product

EMP

William Noton

WNOTON

CREATE SESSION

Insert, Select, Update To CUSTOMER & Product

EMP

Sheri Jackson

SJACKSON

CREATE SESSION

Insert, Select, Update To Order_Line and Orders

EMP

Lily Jones

LJONES

CREATE SESSION

Select on Customer, Orders

CUST_ORDER, EMP

Sean Kiles

SKILES

CREATE SESSION

Insert and Update On Customer and Orders

EMP

Lee Wiles

LWILES

CREATE SESSION

Delete, Insert on Any, Update on Any, Select on Any-MANAGER

MANAGER01,EMP

Views:

Views

Userid

Database Access

ROLE

Vw_customerOrder

LJONES

SELECT

CUST_ORDER

Vw_updateEmployeeAddr

ALL ACTIVE USERS

Update (

EMP_ADDR

)

EMP

Vw_UpdateCustomerInfo

BSMITH, WNOTON

UPDATE

vw_ViewEmployeeRecord

ALL ACTIVE USERS

SELECT

EMP

Roles:

Roles

Userid

Database Access

ADMIN01

JWALKER

DBA

MANAGER01

LWILES

OBJECT PRIV

EMP

ALL ACTIVE USERS

SELECT & Update (EMP_ADDR) per user

CUST_ORDER

LJONES

SELECT on vw_customerOder

Logical Model:

Figure 1.1 Database ERD

Figure 1.2 Data Model Definition

Physical Model:

DDL

/*—————————————————*/

/* CREATE CUSTOMER TABLE */

/*—————————————————*/

DROP TABLE CUSTOMER CASCADE CONSTRAINTS;

CREATE TABLE CUSTOMER

(

CUS_ID

NUMBER(10) NOT NULL,

CUS_LNAME

VARCHAR2(35 BYTE),

CUS_FNAME

VARCHAR2(25 BYTE),

CUS_PHONE

VARCHAR2(25 BYTE),

CUS_ADDR

VARCHAR2(40 BYTE),

CUS_CITY

VARCHAR2(20 BYTE),

CUS_STATE

VARCHAR2(2 BYTE),

CUS_ZIP

VARCHAR2(10 BYTE),

Constraint PK_CUSID PRIMARY KEY (CUS_ID)

) ;

COMMENT ON TABLE CUSTOMER IS ‘Contains the master list of customers that have placed at least one order.’;

COMMENT ON COLUMN CUSTOMER.CUS_ID IS ‘Unique system generated number.’;

COMMENT ON COLUMN CUSTOMER.CUS_LNAME IS ‘Customers last name.’;

COMMENT ON COLUMN CUSTOMER.CUS_FNAME IS ‘Customers first name.’;

COMMENT ON COLUMN CUSTOMER.CUS_ADDR IS ‘Customers street address.’;

COMMENT ON COLUMN CUSTOMER.CUS_CITY IS ‘The City where the customer lives.’;

COMMENT ON COLUMN CUSTOMER.CUS_STATE IS ‘State where the customer lives.’;

COMMENT ON COLUMN CUSTOMER.CUS_ZIP IS ‘Unique identifier for a City and State location (United States).’;

OUTPUT:

DESC CUSTOMER

Name Null Type

——— ——– ————

CUS_ID NOT NULL NUMBER(10)

CUS_LNAME VARCHAR2(35)

CUS_FNAME VARCHAR2(25)

CUS_PHONE VARCHAR2(25)

CUS_ADDR VARCHAR2(40)

CUS_CITY VARCHAR2(20)

CUS_STATE VARCHAR2(2)

CUS_ZIP VARCHAR2(10)

/*—————————————————*/

/* CREATE

EMPLOYEE

TABLE */

/*—————————————————*/

DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;

CREATE TABLE EMPLOYEE

(

EMP_ID

NUMBER(10) NOT NULL,

EMP_FNAME

VARCHAR2(25 BYTE),

EMP_LNAME

VARCHAR2(35 BYTE),

EMP_NAME VARCHAR2(25),

EMP_PHONE

VARCHAR2(25 BYTE),

EMP_ADDR VARCHAR2(40 BYTE),

EMP_CITY

VARCHAR2(30),

EMP_STATE

VARCHAR2(2),

EMP_ZIP

VARCHAR2(10 BYTE),

CONSTRAINT PK_EMPID PRIMARY KEY (EMP_ID)

);

COMMENT ON TABLE EMPLOYEE IS ‘The Employee table contains demographic information for each sales associates.’;

COMMENT ON COLUMN EMPLOYEE.EMP_ID IS ‘Unique system generated number.’;

COMMENT ON COLUMN EMPLOYEE.EMP_FNAME IS ‘Employee”s first name.’;

COMMENT ON COLUMN EMPLOYEE.EMP_LNAME IS ‘Employee”s last name.’;

COMMENT ON COLUMN EMPLOYEE.EMP_ADDR IS ‘Employee street address.’;

COMMENT ON COLUMN EMPLOYEE.EMP_CITY IS ‘The City where the Employee lives.’;

COMMENT ON COLUMN EMPLOYEE.EMP_STATE IS ‘State where the Employee lives.’;

COMMENT ON COLUMN EMPLOYEE.EMP_ZIP IS ‘Unique identifier for a City and State location (United States).’;

OUTPUT:

/*—————————————————*/

/* CREATE

PRODUCT

TABLE */

/*—————————————————*/

DROP TABLE PRODUCT CASCADE CONSTRAINTS;

CREATE TABLE PRODUCT

(

PRO_ID

NUMBER(10) NOT NULL,

PRO_DESC

VARCHAR2(255 BYTE),

PRO_QTY

NUMBER(10),

PRO_COST

NUMBER(18,2),

CONSTRAINT PK_PROID PRIMARY KEY(PRO_ID)

);

COMMENT ON TABLE PRODUCT IS ‘Each Product record identifies an item that is available for a Customer to purchase.’;

COMMENT ON COLUMN PRODUCT.PRO_ID IS ‘Unique system generated number.’;

COMMENT ON COLUMN PRODUCT.PRO_DESC IS ‘Production description.’;

COMMENT ON COLUMN PRODUCT.PRO_COST IS ‘Unit cost of the product.’;

OUTPUT:

/*—————————————————*/

/* CREATE

ORDERS

TABLE */

/*—————————————————*/

DROP TABLE ORDERS CASCADE CONSTRAINTS;

CREATE TABLE ORDERS

(

ORD_ID

NUMBER(10) NOT NULL,

ORD_DATE

DATE,

CUS_ID NUMBER(10),

EMP_ID NUMBER(10),

Constraint PK_ORDID PRIMARY KEY(ORD_ID),

Constraint FK_CUSID FOREIGN KEY (CUS_ID) REFERENCES CUSTOMER,

CONSTRAINT FK_EMPID FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE

);

COMMENT ON TABLE ORDERS IS ‘Each Order record represent a purchase made by a Customer.’;

COMMENT ON COLUMN ORDERS.ORD_ID IS ‘Unique system generated number.’;

COMMENT ON COLUMN ORDERS.ORD_Date IS ‘Date and time the order was entered, e.g., when the status changes.’;

COMMENT ON COLUMN ORDERS.CUS_ID IS ‘Unique system generated number.’;

COMMENT ON COLUMN ORDERS.EMP_ID IS ‘Unique system generated number.’;

OUTPUT:

/*—————————————————*/

/* CREATE

ORDER_LINE

TABLE */

/*—————————————————*/

DROP TABLE ORDER_LINE CASCADE CONSTRAINTS;

CREATE TABLE ORDER_LINE

(

ORL_ID

NUMBER(10) NOT NULL,

PRO_ID NUMBER(10),

ORL_QTY

NUMBER(10),

ORD_ID NUMBER(10) NOT NULL,

ORL_TOTCOST

NUMBER(18,2),

Constraint PK_ORL_ID PRIMARY KEY (ORL_ID),

Constraint FK_PROID FOREIGN KEY (PRO_ID) REFERENCES PRODUCT,

Constraint FK_ORDID FOREIGN KEY (ORD_ID) REFERENCES ORDERS

);

COMMENT ON TABLE ORDER_LINE IS ‘Each Order Line record identifies the item(s) placed on an Order.’;

COMMENT ON COLUMN ORDER_LINE.ORL_ID IS ‘Unique system generated number.’;

COMMENT ON COLUMN ORDER_LINE.PRO_ID IS ‘Foreign key to product.’;

COMMENT ON COLUMN ORDER_LINE.ORL_QTY IS ‘Total quantity of the product ordered.’;

COMMENT ON COLUMN ORDER_LINE.ORD_ID IS ‘Foreign key to orders.’;

COMMENT ON COLUMN ORDER_LINE.ORL_TOTCOST IS ‘Total cost of the order (ORDER_LINE.ORL_TOTQTY * ORDER.ORD_COST).’;

OUTPUT:

/*—————————————————*/

/* CREATE INDEX */

/*—————————————————*/

CREATE INDEX FK_ORDID ON ORDER_LINE(ORD_ID);

CREATE INDEX FK_PROID ON ORDER_LINE(PRO_ID);

CREATE INDEX FK_CUSID ON ORDERS(CUS_ID);

CREATE INDEX FK_EMPID ON ORDERS(EMP_ID);

DML:

/*—————————————————*/

/* CUSTOMER */

/*—————————————————*/

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (1,’FILARSKI’,’John’,’1628181183′,’54 Rogers City Street’, ‘Columbia’, ‘MD’,’21045′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (2,’DOPKE’,’John’,’4749616559′,’13 Posen Street’, ‘Columbia’, ‘MD’,’21045′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (3,’BARBER’,’Eustace’,’1038163575′,’53 Flint Street’, ‘Columbia’, ‘MD’,’21045′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (4,’CHOJNACKI’,’Chelsea’,’8047649647′,’44 Bay City Street’, ‘Germantown’, ‘MD’,’20876′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (5,’CIUPKA’,’John’,’2197269717′,’30 Wilson Street’, ‘Germantown’, ‘MD’,’20876′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (6,’BALTRUCZWIC’,’fname’,’3749008801′,’75 Posen Street’, ‘Germantown’, ‘MD’,’20876′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (7,’CALCUTT’,’Tena’,’3691697299′,’69 Westland Street’, ‘Elicott City’, ‘MD’, ‘21044’);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (8,’GABRYSIAK’,’Martha’,’2667749123′,’20 Long Rapids Street’, ‘Elicott City’, ‘MD’, ‘21044’);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (9,’CHOJNACKI’,’Bernadine’,’9238274028′,’68 Rogers City Street’, ‘Silver Spring’, ‘MD’,’20910′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (10,’BURTON’,’Charles’,’1613454712′,’38 Harrisville Street’, ‘Silver Spring’, ‘MD’,’20910′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR, CUS_CITY, CUS_STATE, CUS_ZIP) values (11,’GERARD’,’Eva’,’9209862959′,’14 Dearborn Road’, ‘Silver Spring’, ‘MD’,’20910′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (12,’CARON’,’Charlotte’,’5792851541′,’44 Tawas Place’, ‘Silver Spring’, ‘MD’,’20910′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (13,’DUMSCH’,’John’,’5806348205′,’65 Lachine Drive’, ‘College Park’, ‘MD’,’20873′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (14,’GRULKE’,’Albert’,’7769502494′,’42 Flint Rd’, ‘College Park’, ‘MD’,’20873′);

Insert into CUSTOMER (CUS_ID,CUS_LNAME,CUS_FNAME,CUS_PHONE,CUS_ADDR,CUS_CITY, CUS_STATE, CUS_ZIP) values (15,’COPPICK’,’John’,’6525978039′,’71 Hillman Street’, ‘College Park’, ‘MD’,’20873′);

OUTPUT:

/*—————————————————*/

/* EMPLOYEE */

/*—————————————————*/

Insert into EMPLOYEE (EMP_ID,EMP_LNAME,EMP_FNAME, EMP_NAME, EMP_PHONE,EMP_ADDR, EMP_CITY, EMP_STATE, EMP_ZIP) values (1,’Wiles’,’LEE’,’LWILES’,’4638143636′,’12 Caledonia Place’, ‘Columbia’, ‘MD’,’21045′);

Insert into EMPLOYEE (EMP_ID,EMP_LNAME,EMP_FNAME, EMP_NAME, EMP_PHONE,EMP_ADDR, EMP_CITY, EMP_STATE, EMP_ZIP) values (2,’Jones’,’Lily’,’LJONES’,’2618669777′,’14 Carolina Street’, ‘Columbia’, ‘MD’,’21045′);

Insert into EMPLOYEE (EMP_ID,EMP_LNAME,EMP_FNAME, EMP_NAME, EMP_PHONE,EMP_ADDR, EMP_CITY, EMP_STATE, EMP_ZIP) values (3,’Kiles’,’Sean’,’SKILES’,’6073493126′,’6 Oscoda Street’, ‘Columbia’, ‘MD’,’21045′);

Insert into EMPLOYEE (EMP_ID,EMP_LNAME,EMP_FNAME, EMP_NAME, EMP_PHONE,EMP_ADDR, EMP_CITY, EMP_STATE, EMP_ZIP) values (4,’Jackson’,’Sheri’,’SJACKSON’,’7266708260′,’17 Harrisville Street’, ‘Washington’, ‘DC’,’20001′);

Insert into EMPLOYEE (EMP_ID,EMP_LNAME,EMP_FNAME, EMP_NAME, EMP_PHONE,EMP_ADDR, EMP_CITY, EMP_STATE, EMP_ZIP) values (5,’Noton’,’William’,’WNOTON’,’6984196073′,’71 Lincoln Place’, ‘Germantown’, ‘MD’,’20876′);

Insert into EMPLOYEE (EMP_ID,EMP_LNAME,EMP_FNAME, EMP_NAME, EMP_PHONE,EMP_ADDR, EMP_CITY, EMP_STATE, EMP_ZIP) values (6,’SMITH’,’BEV’,’BSMITH’,’3078249316′,’41 Onaway Road’, ‘Elicott City’, ‘MD’, ‘21044’);

Insert into EMPLOYEE (EMP_ID,EMP_LNAME,EMP_FNAME, EMP_NAME, EMP_PHONE,EMP_ADDR, EMP_CITY, EMP_STATE, EMP_ZIP) values (7,’Walker’,’Jack’,’JWALKER’,’9416111764′,’28 Barton City Street’, ‘Silver Spring’, ‘MD’,’20910′);

OUTPUT:

/*—————————————————*/

/* PRODUCT */

/*—————————————————*/

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (1,138.65,’Torque Wrench 3/4″drive preset Wr. 15A 150 ft-lbs.’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (2,44.84,’Dial-Type Torque Wrench dial dual wr. 1/2″ 0-175 ft-lbs.’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (3,182.9,’Impact Socket 3/4″ Square Drive Double Square 1-3/8′,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (4,178.18,’Impact Socket 1″ Square Drive Standard 3-3/16′,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (5,138.06,’CYLINDER ASSEMBLY – 1700 SERIES’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (6,144.55,’GUIDE HANDLE – 1700 SERIES’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (7,148.09,’CYL/CUTTERHEAD ASSEM. (1753-8913CN)’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (8,148.09,’CYL/CUTTERHEAD ASSEMBLY (1752-1713A)’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (9,152.22,’1752-1713CDX CYL/CUTTERHEAD ASSEMBLY’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (10,152.22,’21124LK-1713LK CYL/CUTTERHEAD ASSEMBLY’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (11,173.46,’ASSEMBLY 1753 CYL-1713PQ NUT SPLITTER’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (12,330.99,’1752-1713T CYL/CUTTERHEAD ASSEMBLY’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (13,384.68,’1752-1713TA CYL/CUTTERHEAD ASSEMBLY’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (14,381.73,’1752-1713TC CYL/CUTTERHEAD ASSEMBLY’,1000);

Insert into PRODUCT (PRO_ID,PRO_COST,PRO_DESC,PRO_QTY) values (15,123.9,’Impact Socket 1″ Square Drive Deep 3-3/16′,1000);

OUTPUT:

/*—————————————————*/

/* ORDERS */

/*—————————————————*/

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (1,to_date(’12-NOV-19′,’DD-MON-RR’),1,1);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (2,to_date(’12-NOV-19′,’DD-MON-RR’),2,2);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (3,to_date(’12-NOV-19′,’DD-MON-RR’),3,3);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (4,to_date(’12-NOV-19′,’DD-MON-RR’),4,4);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (5,to_date(’12-NOV-19′,’DD-MON-RR’),5,5);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (6,to_date(’12-NOV-19′,’DD-MON-RR’),6,6);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (7,to_date(’12-NOV-19′,’DD-MON-RR’),7,7);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (8,to_date(’12-NOV-19′,’DD-MON-RR’),8,8);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (9,to_date(’12-NOV-19′,’DD-MON-RR’),9,9);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (10,to_date(’12-NOV-19′,’DD-MON-RR’),10,10);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (11,to_date(’12-NOV-19′,’DD-MON-RR’),11,11);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (12,to_date(’12-OCT-02′,’DD-MON-RR’),12,12);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (13,to_date(’12-OCT-02′,’DD-MON-RR’),13,13);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (14,to_date(’12-OCT-02′,’DD-MON-RR’),14,14);

Insert into ORDERS (ORD_ID,ORD_DATE,CUS_ID,EMP_ID) values (15,to_date(’12-OCT-02′,’DD-MON-RR’),15,15);

OUTPUT:

/*—————————————————*/

/* ORDER_LINE */

/*—————————————————*/

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (1,1,1,1,138.65);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (2,2,1,2,44.84);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (3,3,1,3,182.9);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (4,4,1,4,178.18);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (5,5,1,5,138.06);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (6,6,1,6,144.55);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (7,7,1,7,148.09);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (8,8,1,8,148.09);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (9,9,1,9,152.22);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (10,10,1,10,152.22);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (11,11,1,11,173.46);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (12,12,1,12,330.99);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (13,13,1,13,384.68);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (14,14,1,14,381.73);

Insert into ORDER_LINE (ORL_ID,PRO_ID,ORL_QTY,ORD_ID,ORL_TOTCOST) values (15,15,1,15,123.9);

OUTPUT:

CREATE VIEW:

Create or Replace View vw_UpdateEmployeeAddr AS

(

Select EMP_ID, EMP_FNAME, EMP_LNAME, EMP_ADDR

FROM EMPLOYEE

WHERE EMP_Name = User

);
OUTPUT:

> Create or Replace View vw_ViewEmployeeRecord AS

(

Select *

FROM EMPLOYEE
WHERE EMP_Name = User
)

view VW_VIEWEMPLOYEERECORD created.

Create or Replace View vw_CustomerOrder AS

(

Select CUS_FNAME, CUS_LNAME, ORD_ID, ORD_DATE

FROM Customer

c, Orders o

WHERE c.CUS_ID=o.CUS_ID

);

Create or Replace View vw_UpdateCustomerInfo AS

(

Select CUS_FNAME, CUS_LNAME, CUS_ADDR, CUS_CITY, CUS_STATE, CUS_ZIP

FROM Customer
);

Security Implementation:

USER CREATION

CREATE USER LWILES IDENTIFIED BY aaaaaa1#;

CREATE USER LJONES IDENTIFIED BY bbbbb1b#;

CREATE USER SKILES IDENTIFIED BY ssssss2#;

CREATE USER SJACKSON IDENTIFIED BY jjjjjjjS2;

CREATE USER WNOTON IDENTIFIED BY wwwwwww3#;

CREATE USER BSMITH IDENTIFIED BY bssssss7#;

CREATE USER JWALKER IDENTIFIED BY wwwwww12#;

OUTPUT:

CREATE ROLES AND GRANT PRIVILEGES

CREATE ROLE EMP;

CREATE ROLE ADMIN01;

CREATE ROLE MANAGER01;

CREATE ROLE CUST_ORDER;

GRANTS

> GRANT DBA TO ADMIN01

GRANT succeeded.

> GRANT SELECT ON vw_CustomerOrder TO CUST_ORDER

GRANT succeeded.

> GRANT ALL ON CUSTOMER TO MANAGER01

GRANT succeeded.

> GRANT ALL ON ORDERS TO MANAGER01

GRANT succeeded.

> GRANT ALL ON ORDER_LINE TO MANAGER01

GRANT succeeded.

> GRANT ALL ON EMPLOYEE TO MANAGER01

GRANT succeeded.

> GRANT ALL ON PRODUCT TO MANAGER01

GRANT succeeded.

> GRANT UPDATE (EMP_ADDR) ON vw_UpdateEmployeeAddr TO EMP

GRANT succeeded.

> GRANT CREATE SESSION TO LWILES, LJONES, SKILES, SJACKSON, WNOTON, BSMITH, JWALKER

GRANT succeeded.

> GRANT ADMIN01 TO JWALKER

GRANT succeeded.

> GRANT MANAGER01 TO LWILES

GRANT succeeded.

> GRANT EMP TO LWILES, LJONES, SKILES, SJACKSON, WNOTON, BSMITH, JWALKER

GRANT succeeded.

> GRANT CUST_ORDER TO LJONES

GRANT succeeded.

> GRANT INSERT, SELECT, UPDATE ON CUSTOMER TO WNOTON, BSMITH

GRANT succeeded.

> GRANT INSERT, SELECT, UPDATE ON PRODUCT TO WNOTON, BSMITH

GRANT succeeded.

> GRANT INSERT, SELECT, UPDATE ON ORDER_LINE TO SJACKSON

GRANT succeeded.

> GRANT INSERT, SELECT, UPDATE ON ORDERS TO SJACKSON

GRANT succeeded.

> GRANT UPDATE ON vw_UpdateCustomerInfo TO BSMITH, WNOTON

GRANT succeeded.

> Grant SELECT ON vw_ViewEmployeeRecord TO EMP

Grant succeeded.

Security Policy A: Jack Walker is the only DBA and the only person allowed full control of the database. Instead of granting DBA to the user JWALKER, this user will be granted the role ADMIN01. This will allow easy the company to easily and quickly update the privileges for any DBA including JWALKER.

Test Case scenario 1: To verify the privileges JWALKER has on the DB668A01. He will connect as himself, create a test table, insert a record, create a user, grant the user to the table and then Drop the user and drop the test table.

> Connect JWALKER/wwwwww12#

Connected

> Show user

USER is JWALKER

> Drop table DB668A01.test1

table DB668A01.TEST1 dropped.

> Create table DB668A01.TEST1

( TestId number not null,

testname varchar2(25))

table DB668A01.TEST1 created.

> Insert Into DB668A01.TEST1 Values(1, ‘Test Name’)

1 rows inserted.

> Create User TESTUSER identified by test1

user TESTUSER created.

> Grant Resource, Connect to TESTUSER

Grant succeeded.

> GRANT SELECT ON DB668A01.TEST1 to TESTUSER

GRANT succeeded.

Connection created by CONNECT script command disconnected

Test Case Scenario 2: Connect as Testuser and select from table DB668A01.TEST1

> Connect TESTUSER/test1

Connected
> Show user

USER is TESTUSER

> Select * from DB668A01.TEST1

TESTID TESTNAME

—— ————————-

1 Test Name

Connection created by CONNECT script command disconnected

Test Case scenario 3: Connect as JWALKER, Drop User and Test1

> Connect JWALKER/wwwwww12#
Connected
> Show user
USER is JWALKER

> Drop user TESTUSER

user TESTUSER dropped.

> Drop table DB668A01.test1
table DB668A01.TEST1 dropped.
Connection created by CONNECT script command disconnected

Test case scenario 4: Verify a user not granted the ADMIN01 Role cannot create a table on DB668A01.

> Connect LJONES/bbbbb1b#

Connected
> Show user

USER is LJONES

>

Create table DB668A01.test1

( TestId number not null,
testname varchar2(25))

Error starting at line 6 in command:

Create table DB668A01.test1
( TestId number not null,
testname varchar2(25))

Error at Command Line:6 Column:0

Error report:

SQL Error: ORA-01031: insufficient privileges

01031. 00000 – “insufficient privileges”

*Cause:

An attempt was made to change the current username or password

without the appropriate privilege. This error also occurs if

attempting to install a database without the necessary operating

system privileges.

When Trusted Oracle is configure in DBMS MAC, this error may occur

if the user was granted the necessary privilege at a higher label

than the current login.

*Action:

Ask the database administrator to perform the operation or grant

the required privileges.

For Trusted Oracle users getting this error although granted the

the appropriate privilege at a higher label, ask the database

administrator to regrant the privilege at the appropriate label.

Connection created by CONNECT script command disconnected

Security Policy B: The EMP role was created to maintain and manage how an employee can view and update his/her employee record. This is so that we don’t have to grant privileges per each user for the employee table. Because we want to restrict access of what records the users can view and update, two stored procedures were created: DB668A01.vw_UpdateEmployeeAddr and DB668A01.vw_ViewEmployeeRecord.

Test case scenario 1: Connect as any user and update another user’s address using the DB668A01.vw_UpdateEmployeeAddr. The connect user should only be able to update his/her addr info.

> Connect LWILES/aaaaaa1#

Connected
> Show user

USER is LWILES

> Update DB668A01.vw_UpdateEmployeeAddr

Set EMP_ADDR = ‘129 Piny Branch RD’

Where EMP_ID =2 –This is user LJONES

0 rows updated.

> Update DB668A01.vw_UpdateEmployeeAddr
Set EMP_ADDR = ‘129 Piny Branch RD’

Where EMP_ID =1 –This is user LWILES

1 rows updated.

Connection created by CONNECT script command disconnected

Test Case Scenario 2: Connect as any user to view DB668A01.vw_ViewEmployeeRecord and select ALL rows to verify the records the user can see. The connected user should only be able to view his/her record.

> Connect SJACKSON/jjjjjjjS2

Connected

> Show User

USER is SJACKSON

> Select * from DB668A01.vw_ViewEmployeeRecord

EMP_ID EMP_FNAME EMP_LNAME EMP_NAME EMP_PHONE EMP_ADDR EMP_CITY EMP_STATE EMP_ZIP

—— ——— ——— ——– ——— ——– ——– ——— ——-

4 Sheri Jackson SJACKSON 7266708260 17 Harrisville Street Washington DC 20001

Connection created by CONNECT script command disconnected

Security Policy C: The MANAGER01 role was created to maintain and manage tables in the database for all managers. The MANAGER01 role has Select, Update, Insert, and Delete privileges on all the tables in the database.

Test case scenario 1: Connect as user LWILES, verify the role of the user is Manager01, test the role by performing each of the functions for the tables. Use Employee table as the performing table since the Manager01 only has the ability to make changes to this table.

Select * FROM DBA_ROLE_PRIVS

Where GRANTEE = ‘LWILES’;

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
——- ———— ———— ————

LWILES CONNECT NO YES

LWILES RESOURCE NO YES

LWILES MANAGER01 NO YES

LWILES EMP NO YES

> Connect LWILES/aaaaaa1#
Connected
> Show User
USER is LWILES

>

INSERT INTO DB668A01.EMPLOYEE VALUES(8, ‘Sam’, ‘Stephens’, ‘SSTEPHENS’, ‘9910112003’, ‘APT 1 Spin Crest Dr’, ‘Highland’, ‘MD’, ‘20777’)

1 rows inserted.

>

Select * FROM DB668A01.EMPLOYEE

EMP_ID EMP_FNAME EMP_LNAME EMP_NAME EMP_PHONE EMP_ADDR EMP_CITY EMP_STATE EMP_ZIP

———- ————————- ———————————– ————————- ————————- —————————————- —————————— ——— ———-

8 Sam Stephens SSTEPHENS 9910112003 APT 1 Spin Crest Dr Highland MD 20777

1 LEE Wiles LWILES 4638143636 129 Piny Branch RD Columbia MD 21045

2 Lily Jones LJONES 2618669777 14 Carolina Street Columbia MD 21045

3 Sean Kiles SKILES 6073493126 6 Oscoda Street Columbia MD 21045

4 Sheri Jackson SJACKSON 7266708260 17 Harrisville Street Washington DC 20001

5 William Noton WNOTON 6984196073 71 Lincoln Place Germantown MD 20876

6 BEV SMITH BSMITH 3078249316 41 Onaway Road Elicott City MD 21044

7 Jack Walker JWALKER 9416111764 28 Barton City Street Silver Spring MD 20910

8 rows selected

>

Update DB668A01.EMPLOYEE

Set EMP_FNAME = ‘Samantha’

Where EMP_ID = 8

1 rows updated.

>

Delete From DB668A01.EMPLOYEE Where EMP_ID = 8

1 rows deleted.

Connection created by CONNECT script command disconnected

Test case scenario 2: Test another user to see if he/she can perform the same functions as LWILES.

Results: As you can see William Notion is not able to View, Insert, Update, or Delete an entry from DB668A01.EMPLOYEE.

> Connect WNOTON/wwwwwww3#

Connected
> Show User

USER is WNOTON

> INSERT INTO DB668A01.EMPLOYEE VALUES(8, ‘Sam’, ‘Stephens’, ‘SSTEPHENS’, ‘9910112003’, ‘APT 1 Spin Crest Dr’, ‘Highland’, ‘MD’, ‘20777’)

Error starting at line 54 in command:

INSERT INTO DB668A01.EMPLOYEE VALUES(8, ‘Sam’, ‘Stephens’, ‘SSTEPHENS’, ‘9910112003’, ‘APT 1 Spin Crest Dr’, ‘Highland’, ‘MD’, ‘20777’)

Error at Command Line:54 Column:21

Error report:

SQL Error: ORA-00942: table or view does not exist

00942. 00000 – “table or view does not exist”

*Cause:
*Action:
> Select * FROM DB668A01.EMPLOYEE

Error starting at line 55 in command:

Select * FROM DB668A01.EMPLOYEE

Error at Command Line:55 Column:23

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Update DB668A01.EMPLOYEE
Set EMP_FNAME = ‘Samantha’
Where EMP_ID = 8

Error starting at line 56 in command:

Update DB668A01.EMPLOYEE
Set EMP_FNAME = ‘Samantha’
Where EMP_ID = 8

Error at Command Line:56 Column:16

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Delete From DB668A01.EMPLOYEE Where EMP_ID = 8

Error starting at line 59 in command:

Delete From DB668A01.EMPLOYEE Where EMP_ID = 8

Error at Command Line:59 Column:21

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
Connection created by CONNECT script command disconnected

Security Policy D: The CUST_ORDER role was created so allow sales staff to view customer’s orders. A view was created to display this information. The view is DB668A01.vw_CustomerOrder and it was granted SELECT to the CUST_ORDER role.

Test case scenario 1: Connect as a user and verify whether this user has SELECT only on the view.

> Connect LJONES/bbbbb1b#
Connected
> Show User
USER is LJONES

>

Select * FROM DB668A01.vw_CustomerOrder

CUS_FNAME CUS_LNAME ORD_ID ORD_DATE

————————- ———————————– ———- ———

John FILARSKI 1 12-NOV-19

John DOPKE 2 12-NOV-19

Eustace BARBER 3 12-NOV-19

Chelsea CHOJNACKI 4 12-NOV-19

John CIUPKA 5 12-NOV-19

fname BALTRUCZWIC 6 12-NOV-19

Tena CALCUTT 7 12-NOV-19

Martha GABRYSIAK 8 12-NOV-19

Bernadine CHOJNACKI 9 12-NOV-19

Charles BURTON 10 12-NOV-19

Eva GERARD 11 12-NOV-19

Charlotte CARON 12 12-OCT-02

John DUMSCH 13 12-OCT-02

Albert GRULKE 14 12-OCT-02

John COPPICK 15 12-OCT-02

15 rows selected

>

Update DB668A01.vw_CustomerOrder

Set CUS_FNAME = ‘Paula’

Where ORD_ID = 6

Error starting at line 55 in command:
Update DB668A01.vw_CustomerOrder
Set CUS_FNAME = ‘Paula’
Where ORD_ID = 6

Error at Command Line:55 Column:16

Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 – “insufficient privileges”

*Cause: An attempt was made to change the current username or password

without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.

*Action: Ask the database administrator to perform the operation or grant

the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
Connection created by CONNECT script command disconnected

Test case scenario 2: Connect as another user and verify whether this user has access to this view DB668A01.vw_CustomerOrder.

> Connect BSMITH/bssssss7#

Connected
> Show User

USER is BSMITH

> Select * FROM DB668A01.vw_CustomerOrder
Error starting at line 54 in command:
Select * FROM DB668A01.vw_CustomerOrder

Error at Command Line:54 Column:23

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:

> Update DB668A01.vw_CustomerOrder

Set CUS_FNAME = ‘Paula’
Where ORD_ID = 6
Error starting at line 55 in command:
Update DB668A01.vw_CustomerOrder
Set CUS_FNAME = ‘Paula’
Where ORD_ID = 6
Error at Command Line:55 Column:16
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
Connection created by CONNECT script command disconnected

Security Policy E: Users WNOTON and BSMITH were granted INSERT, SELECT, and Update on Customer and Product table. They are the only ones besides MANAGER01 and ADMIN01 allowed object privileges (except Delete) on these two tables. Verify their privileges and verify no other user besides MANAGER01 (LWILES) and ADMIN01 (JWALKER) have access.

Test case scenario 1: Connect as the privileged user, WNOTON and BSMITH and verify object privileges on Customer and Product table.

USER#1:

> Connect BSMITH/bssssss7#
Connected
> Show User

USER is BSMITH

> INSERT INTO DB668A01.CUSTOMER VALUES(16, ‘TANG’, ‘WANG’, ‘2901110920’, ’34 Weather Drive’, ‘Silver Spring’, ‘MD’, ‘20910’)

1 rows inserted.

>

Update DB668A01.CUSTOMER

Set CUS_FNAME = ‘WAGNER’

Where CUS_ID = 16

1 rows updated.

>

Select * From DB668A01.CUSTOMER

CUS_ID CUS_LNAME CUS_FNAME CUS_PHONE CUS_ADDR CUS_CITY CUS_STATE CUS_ZIP

———- ———————————– ————————- ————————- —————————————- ——————– ——— ———-

16 TANG WAGNER 2901110920 34 Weather Drive Silver Spring MD 20910

1 FILARSKI John 1628181183 54 Rogers City Street Columbia MD 21045

2 DOPKE John 4749616559 13 Posen Street Columbia MD 21045

3 BARBER Eustace 1038163575 53 Flint Street Columbia MD 21045

4 CHOJNACKI Chelsea 8047649647 44 Bay City Street Germantown MD 20876

5 CIUPKA John 2197269717 30 Wilson Street Germantown MD 20876

6 BALTRUCZWIC fname 3749008801 75 Posen Street Germantown MD 20876

7 CALCUTT Tena 3691697299 69 Westland Street Elicott City MD 21044

8 GABRYSIAK Martha 2667749123 20 Long Rapids Street Elicott City MD 21044

9 CHOJNACKI Bernadine 9238274028 68 Rogers City Street Silver Spring MD 20910

10 BURTON Charles 1613454712 38 Harrisville Street Silver Spring MD 20910

11 GERARD Eva 9209862959 14 Dearborn Road Silver Spring MD 20910

12 CARON Charlotte 5792851541 44 Tawas Place Silver Spring MD 20910

13 DUMSCH John 5806348205 65 Lachine Drive College Park MD 20873

14 GRULKE Albert 7769502494 42 Flint Rd College Park MD 20873

15 COPPICK John 6525978039 71 Hillman Street College Park MD 20873

16 rows selected

>

Delete From DB668A01.CUSTOMER Where CUS_ID= 16

Error starting at line 62 in command:

Delete From DB668A01.CUSTOMER Where CUS_ID= 16

Error at Command Line:62 Column:21

Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 – “insufficient privileges”
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
Connection created by CONNECT script command disconnected
> Connect BSMITH/bssssss7#
Connected
> Show User
USER is BSMITH

> INSERT INTO DB668A01.PRODUCT VALUES(16, ‘Black TShirt Small’, 1000, 3000.99)

1 rows inserted.

>

Update DB668A01.PRODUCT

Set PRO_COST = ‘3000.00’

Where PRO_ID = 16

1 rows updated.

>

Select * From DB668A01.PRODUCT

PRO_ID PRO_DESC PRO_QTY PRO_COST

———- ————————————————————————————————————————————————————————————————————————————————————— ———- ——————

16 Black TShirt Small 1000 3000

1 Torque Wrench 3/4″drive preset Wr. 15A 150 ft-lbs. 1000 138.65

2 Dial-Type Torque Wrench dial dual wr. 1/2″ 0-175 ft-lbs. 1000 44.84

3 Impact Socket 3/4″ Square Drive Double Square 1-3/8 1000 182.9

4 Impact Socket 1″ Square Drive Standard 3-3/16 1000 178.18

5 CYLINDER ASSEMBLY – 1700 SERIES 1000 138.06

6 GUIDE HANDLE – 1700 SERIES 1000 144.55

7 CYL/CUTTERHEAD ASSEM. (1753-8913CN) 1000 148.09

8 CYL/CUTTERHEAD ASSEMBLY (1752-1713A) 1000 148.09

9 1752-1713CDX CYL/CUTTERHEAD ASSEMBLY 1000 152.22

10 21124LK-1713LK CYL/CUTTERHEAD ASSEMBLY 1000 152.22

11 ASSEMBLY 1753 CYL-1713PQ NUT SPLITTER 1000 173.46

12 1752-1713T CYL/CUTTERHEAD ASSEMBLY 1000 330.99

13 1752-1713TA CYL/CUTTERHEAD ASSEMBLY 1000 384.68

14 1752-1713TC CYL/CUTTERHEAD ASSEMBLY 1000 381.73

15 Impact Socket 1″ Square Drive Deep 3-3/16 1000 123.9

16 rows selected

>

Delete From DB668A01.PRODUCT Where PRO_ID= 16

Error starting at line 62 in command:
Delete From DB668A01.PRODUCT Where PRO_ID= 16
Error at Command Line:62 Column:21
Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 – “insufficient privileges”
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
Connection created by CONNECT script command disconnected

USER#2:

> Connect WNOTON/wwwwwww3#
Connected
> Show User

USER is WNOTON

> INSERT INTO DB668A01.CUSTOMER VALUES(17, ‘TANG’, ‘Stephy’, ‘2901110920’, ’34 Weather Drive’, ‘Silver Spring’, ‘MD’, ‘20910’)

1 rows inserted.
> Update DB668A01.CUSTOMER

Set CUS_FNAME = ‘Stephany’

Where CUS_ID = 17

1 rows updated.
> Select * From DB668A01.CUSTOMER
CUS_ID CUS_LNAME CUS_FNAME CUS_PHONE CUS_ADDR CUS_CITY CUS_STATE CUS_ZIP
———- ———————————– ————————- ————————- —————————————- ——————– ——— ———-
16 TANG WAGNER 2901110920 34 Weather Drive Silver Spring MD 20910

17 TANG Stephany 2901110920 34 Weather Drive Silver Spring MD 20910

1 FILARSKI John 1628181183 54 Rogers City Street Columbia MD 21045
2 DOPKE John 4749616559 13 Posen Street Columbia MD 21045
3 BARBER Eustace 1038163575 53 Flint Street Columbia MD 21045
4 CHOJNACKI Chelsea 8047649647 44 Bay City Street Germantown MD 20876
5 CIUPKA John 2197269717 30 Wilson Street Germantown MD 20876
6 BALTRUCZWIC fname 3749008801 75 Posen Street Germantown MD 20876
7 CALCUTT Tena 3691697299 69 Westland Street Elicott City MD 21044
8 GABRYSIAK Martha 2667749123 20 Long Rapids Street Elicott City MD 21044
9 CHOJNACKI Bernadine 9238274028 68 Rogers City Street Silver Spring MD 20910
10 BURTON Charles 1613454712 38 Harrisville Street Silver Spring MD 20910
11 GERARD Eva 9209862959 14 Dearborn Road Silver Spring MD 20910
12 CARON Charlotte 5792851541 44 Tawas Place Silver Spring MD 20910
13 DUMSCH John 5806348205 65 Lachine Drive College Park MD 20873
14 GRULKE Albert 7769502494 42 Flint Rd College Park MD 20873
15 COPPICK John 6525978039 71 Hillman Street College Park MD 20873

17 rows selected

>

Delete From DB668A01.CUSTOMER Where CUS_ID= 17

Error starting at line 74 in command:

Delete From DB668A01.CUSTOMER Where CUS_ID= 17

Error at Command Line:74 Column:21

Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 – “insufficient privileges”
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
Connection created by CONNECT script command disconnected
> Connect WNOTON/wwwwwww3#
Connected
> Show User
USER is WNOTON

> INSERT INTO DB668A01.PRODUCT VALUES(17, ‘White TShirt Small’, 1000, 3000.99)

1 rows inserted.
> Update DB668A01.PRODUCT
Set PRO_COST = ‘3000.00’

Where PRO_ID = 17

1 rows updated.
> Select * From DB668A01.PRODUCT
PRO_ID PRO_DESC PRO_QTY PRO_COST
———- ————————————————————————————————————————————————————————————————————————————————————— ———- ——————
16 Black TShirt Small 1000 3000

17 White TShirt Small 1000 3000

1 Torque Wrench 3/4″drive preset Wr. 15A 150 ft-lbs. 1000 138.65
2 Dial-Type Torque Wrench dial dual wr. 1/2″ 0-175 ft-lbs. 1000 44.84
3 Impact Socket 3/4″ Square Drive Double Square 1-3/8 1000 182.9
4 Impact Socket 1″ Square Drive Standard 3-3/16 1000 178.18
5 CYLINDER ASSEMBLY – 1700 SERIES 1000 138.06
6 GUIDE HANDLE – 1700 SERIES 1000 144.55
7 CYL/CUTTERHEAD ASSEM. (1753-8913CN) 1000 148.09
8 CYL/CUTTERHEAD ASSEMBLY (1752-1713A) 1000 148.09
9 1752-1713CDX CYL/CUTTERHEAD ASSEMBLY 1000 152.22
10 21124LK-1713LK CYL/CUTTERHEAD ASSEMBLY 1000 152.22
11 ASSEMBLY 1753 CYL-1713PQ NUT SPLITTER 1000 173.46
12 1752-1713T CYL/CUTTERHEAD ASSEMBLY 1000 330.99
13 1752-1713TA CYL/CUTTERHEAD ASSEMBLY 1000 384.68
14 1752-1713TC CYL/CUTTERHEAD ASSEMBLY 1000 381.73
15 Impact Socket 1″ Square Drive Deep 3-3/16 1000 123.9
17 rows selected

>

Delete From DB668A01.PRODUCT Where PRO_ID= 17

Error starting at line 74 in command:
Delete From DB668A01.PRODUCT Where PRO_ID= 17
Error at Command Line:74 Column:21
Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 – “insufficient privileges”
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
Connection created by CONNECT script command disconnected

Test case scenario 1: Connect as another user and verify object privileges on these tables Customer and Product table.

Result: As you can see indicated in red that user SJACKSON does not have sufficient privilege to view the PRODUCT and CUSTOMER Table in Database DB668A01 and cannot perform any object privileges.

> Connect SJACKSON/jjjjjjjS2
Connected
> Show User
USER is SJACKSON

>

INSERT INTO DB668A01.PRODUCT VALUES(16, ‘White TShirt Small’, 1000, 3000.99)

Error starting at line 66 in command:

INSERT INTO DB668A01.PRODUCT VALUES(16, ‘White TShirt Small’, 1000, 3000.99)

Error at Command Line:66 Column:21

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Update DB668A01.PRODUCT
Set PRO_COST = ‘3000.00’
Where PRO_ID = 16

Error starting at line 68 in command:

Update DB668A01.PRODUCT
Set PRO_COST = ‘3000.00’
Where PRO_ID = 16

Error at Command Line:68 Column:16

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Select * From DB668A01.PRODUCT

Error starting at line 72 in command:

Select * From DB668A01.PRODUCT

Error at Command Line:72 Column:23

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:

> Delete From DB668A01.PRODUCT Where PRO_ID= 16

Error starting at line 74 in command:
Delete From DB668A01.PRODUCT Where PRO_ID= 16
Error at Command Line:74 Column:21
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:

>

INSERT INTO DB668A01.CUSTOMER VALUES(16, ‘TANG’, ‘Stephy’, ‘2901110920’, ’34 Weather Drive’, ‘Silver Spring’, ‘MD’, ‘20910’)

Error starting at line 76 in command:

INSERT INTO DB668A01.CUSTOMER VALUES(16, ‘TANG’, ‘Stephy’, ‘2901110920’, ’34 Weather Drive’, ‘Silver Spring’, ‘MD’, ‘20910’)

Error at Command Line:76 Column:21

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Update DB668A01.CUSTOMER
Set CUS_FNAME = ‘Stephany’
Where CUS_ID = 16

Error starting at line 78 in command:

Update DB668A01.CUSTOMER
Set CUS_FNAME = ‘Stephany’
Where CUS_ID = 16

Error at Command Line:78 Column:16

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Select * From DB668A01.CUSTOMER

Error starting at line 82 in command:

Select * From DB668A01.CUSTOMER

Error at Command Line:82 Column:23

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:

> Delete From DB668A01.CUSTOMER Where CUS_ID= 16

Error starting at line 84 in command:

Delete From DB668A01.CUSTOMER Where CUS_ID= 16

Error at Command Line:84 Column:21

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
Connection created by CONNECT script command disconnected

Security Policy F: User SJACKSON was granted INSERT, SELECT, and Update on ORDERS and ORDER_LINE table. This user is the only user besides MANAGER01 and ADMIN01 allowed object privileges (except Delete) on these two tables. Verify the user privileges and verify another user besides MANAGER01 (LWILES) and ADMIN01 (JWALKER) don’t have access.

Test case scenario 1: Connect as the privileged user, SJACKSON and verify object privileges on ORDERS and ORDER_LINE table.

> Connect SJACKSON/jjjjjjjS2
Connected
> Show User
USER is SJACKSON

>

INSERT INTO DB668A01.ORDERS VALUES(16, to_date(’12-NOV-19′,’DD-MON-RR’), 5, 3)

1 rows inserted.

>

Update DB668A01.ORDERS

Set CUS_ID = 1

Where ORD_ID = 16

1 rows updated.

>

Select * from DB668A01.ORDERS

ORD_ID ORD_DATE CUS_ID EMP_ID

———- ——— ———- ———-

16 12-NOV-19 1 3

1 12-NOV-19 1 1

2 12-NOV-19 2 2

3 12-NOV-19 3 3

4 12-NOV-19 4 4

5 12-NOV-19 5 5

6 12-NOV-19 6 6

7 12-NOV-19 7 7

8 12-NOV-19 8 1

9 12-NOV-19 9 2

10 12-NOV-19 10 3

11 12-NOV-19 11 4

12 12-OCT-02 12 5

13 12-OCT-02 13 6

14 12-OCT-02 14 7

15 12-OCT-02 15 7

16 rows selected

>

DELETE FROM DB668A01.ORDERS WHERE ORD_ID = 16

Error starting at line 59 in command:
DELETE FROM DB668A01.ORDERS WHERE ORD_ID = 16
Error at Command Line:59 Column:21
Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 – “insufficient privileges”
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.

>

INSERT INTO DB668A01.ORDER_LINE VALUES(16, 1, 5,16, 139.10)

1 rows inserted.

>

Update DB668A01.ORDER_LINE

Set ORL_TOTCOST = 150.14

Where ORL_ID = 16

1 rows updated.

>

Select * from DB668A01.ORDER_LINE

ORL_ID PRO_ID ORL_QTY ORD_ID ORL_TOTCOST

———- ———- ———- ———- ——————

16 1 5 16 150.14

1 1 1 1 138.65

2 2 1 2 44.84

3 3 1 3 182.9

4 4 1 4 178.18

5 5 1 5 138.06

6 6 1 6 144.55

7 7 1 7 148.09

8 8 1 8 148.09

9 9 1 9 152.22

10 10 1 10 152.22

11 11 1 11 173.46

12 12 1 12 330.99

13 13 1 13 384.68

14 14 1 14 381.73

15 15 1 15 123.9

16 rows selected

>

DELETE FROM DB668A01.ORDER_LINE WHERE ORL_ID = 16

Error starting at line 69 in command:

DELETE FROM DB668A01.ORDER_LINE WHERE ORL_ID = 16

Error at Command Line:69 Column:21

Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 – “insufficient privileges”
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
*Action: Ask the database administrator to perform the operation or grant
the required privileges.
For Trusted Oracle users getting this error although granted the
the appropriate privilege at a higher label, ask the database
administrator to regrant the privilege at the appropriate label.
Connection created by CONNECT script command disconnected

Test case scenario 2: Connect as another user besides Manager01 and Admin01 users and see if they have object privilege on Orders and Order_line Tables.

> Connect SKILES/ssssss2#

Connected
> Show User

USER is SKILES

> INSERT INTO DB668A01.ORDERS VALUES(16, to_date(’12-NOV-19′,’DD-MON-RR’), 5, 3)

Error starting at line 51 in command:

INSERT INTO DB668A01.ORDERS VALUES(16, to_date(’12-NOV-19′,’DD-MON-RR’), 5, 3)

Error at Command Line:51 Column:21

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Update DB668A01.ORDERS
Set CUS_ID = 1
Where ORD_ID = 16

Error starting at line 53 in command:

Update DB668A01.ORDERS
Set CUS_ID = 1
Where ORD_ID = 16

Error at Command Line:53 Column:16

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Select * from DB668A01.ORDERS

Error starting at line 57 in command:

Select * from DB668A01.ORDERS

Error at Command Line:57 Column:23

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:

> DELETE FROM DB668A01.ORDERS WHERE ORD_ID = 16

Error starting at line 59 in command:
DELETE FROM DB668A01.ORDERS WHERE ORD_ID = 16
Error at Command Line:59 Column:21
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> INSERT INTO DB668A01.ORDER_LINE VALUES(16, 1, 5,16, 139.10)

Error starting at line 61 in command:

INSERT INTO DB668A01.ORDER_LINE VALUES(16, 1, 5,16, 139.10)

Error at Command Line:61 Column:21

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Update DB668A01.ORDER_LINE
Set ORL_TOTCOST = 150.14
Where ORL_ID = 16

Error starting at line 63 in command:

Update DB668A01.ORDER_LINE
Set ORL_TOTCOST = 150.14
Where ORL_ID = 16

Error at Command Line:63 Column:16

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
> Select * from DB668A01.ORDER_LINE

Error starting at line 67 in command:

Select * from DB668A01.ORDER_LINE

Error at Command Line:67 Column:23

Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:

> DELETE FROM DB668A01.ORDER_LINE WHERE ORL_ID = 16

Error starting at line 69 in command:
DELETE FROM DB668A01.ORDER_LINE WHERE ORL_ID = 16
Error at Command Line:69 Column:21
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:

Security Policy G: Create a policy to define four levels of security for the User Table. The policy should be as follows for the following individuals.

USR_ID

USR_NAME

USR_SAL

USR_DEPT

USR_SECLV

001

JWALKER

95000

C100

TS

002

LWILES

75000

C200

S

003

BSMITH

60000

C200

C

004

WNOTON

60000

C200

C

005

SJACKSON

65000

C200

C

006

SKILES

65000

C200

C

007

LJONES

55000

C300

U

CREATE TABLE

CREATE TABLE DB668A01.USERLVL

(

USR_ID NUMBER NOT NULL,

USR_NAME VARCHAR2(25),

USR_SAL NUMBER,

USR_DEPT VARCHAR2(25)

);

CREATE POLICY

EXECUTE SA_SYSDBA.CREATE_POLICY(‘USROLS’, ‘USR01’, ‘READ_CONTROL’);

CREATE SECURITY LEVELS: U, C, S, TS

EXECUTE SA_COMPONENTS.CREATE_LEVEL(‘USROLS’, 1201, ‘U’, ‘UNCLASSIFIED’);

EXECUTE SA_COMPONENTS.CREATE_LEVEL(‘USROLS’, 2201, ‘C’, ‘CONFIDENTIAL’);

EXECUTE SA_COMPONENTS.CREATE_LEVEL(‘USROLS’, 3201, ‘S’, ‘SENSITIVE’);

EXECUTE SA_COMPONENTS.CREATE_LEVEL(‘USROLS’, 4201, ‘TS’, ‘TOP_SECRET’);

CREATE SECURITY LABELS: U, C, S, TS

EXECUTE SA_LABEL_ADMIN.CREATE_LABEL(‘USROLS’, 1201, ‘U’);

EXECUTE SA_LABEL_ADMIN.CREATE_LABEL(‘USROLS’, 2201, ‘C’);

EXECUTE SA_LABEL_ADMIN.CREATE_LABEL(‘USROLS’, 3201, ‘S’);

EXECUTE SA_LABEL_ADMIN.CREATE_LABEL(‘USROLS’, 4201, ‘TS’);

GRANT USERS PRIVILEGES TO THE TABLE

GRANT SELECT ON DB668A01.USERLVL TO LJONES;

GRANT SELECT ON DB668A01.USERLVL TO SKILES;

GRANT SELECT ON DB668A01.USERLVL TO BSMITH;

GRANT SELECT ON DB668A01.USERLVL TO WNOTON;

GRANT SELECT ON DB668A01.USERLVL TO SJACKSON;

GRANT SELECT ON DB668A01.USERLVL TO LWILES;

GRANT SELECT ON DB668A01.USERLVL TO JWALKER;

SET USER LEVELS

EXECUTE SA_USER_ADMIN.SET_LEVELS(‘USROLS’, ‘LJONES’, ‘U’);

EXECUTE SA_USER_ADMIN.SET_LEVELS(‘USROLS’, ‘SKILES’, ‘C’);

EXECUTE SA_USER_ADMIN.SET_LEVELS(‘USROLS’, ‘BSMITH’, ‘C’);

EXECUTE SA_USER_ADMIN.SET_LEVELS(‘USROLS’, ‘WNOTON’, ‘C’);

EXECUTE SA_USER_ADMIN.SET_LEVELS(‘USROLS’, ‘SJACKSON’, ‘C’);

EXECUTE SA_USER_ADMIN.SET_LEVELS(‘USROLS’, ‘LWILES’, ‘S’);

EXECUTE SA_USER_ADMIN.SET_LEVELS(‘USROLS’, ‘JWALKER’, ‘TS’);

APPLY THE POLICY TO THE USERLVL TABLE

EXECUTE SA_POLICY_ADMIN.APPLY_TABLE_POLICY(‘USROLS’, ‘DB668A01’, ‘USERLVL’);

INSERT DATA INTO USERLVL TABLE

INSERT INTO DB668A01.USERLVL VALUES(001, ‘JWALKER’, 95000, ‘C100’, 4201);

INSERT INTO DB668A01.USERLVL VALUES(002, ‘LWILES’, 75000, ‘C200’, 3201);

INSERT INTO DB668A01.USERLVL VALUES(003, ‘BSMITH’, 60000, ‘C200’, 2201);

INSERT INTO DB668A01.USERLVL VALUES(004, ‘WNOTON’, 60000, ‘C200’, 2201);

INSERT INTO DB668A01.USERLVL VALUES(005, ‘SJACKSON’, 65000, ‘C200’, 2201);

INSERT INTO DB668A01.USERLVL VALUES(006, ‘SKILES’, 65000, ‘C200’, 2201);

INSERT INTO DB668A01.USERLVL VALUES(007, ‘LJONES’, 55000, ‘C300’, 1201);

VIEW ACCESS FOR EACH USER LEVEL

User LWILES – S level

CONNECT LWILES/aaaaaa1#;

SHOW USER;

SELECT * FROM DB668A01.USERLVL;

User LJONES – U level

CONNECT LJONES/bbbbb1b#;