Please this Lab exercise on Oracle databases, and then let me know if you will be able to accurately complete it. Run all queries using the command line prompt.

Page 1 of 35

Database Security
Task 1:
1. Create a user (schema) called DBST668. Grant DBA role to user DBST668.
Query:
create user DBST668 identified by DBST668;
grant DBA to DBST668;
Page 2 of 35

2. Create table and insert data into Student table.
Query:
create table STUDENT (STUDENTID number(3) NOT NULL,NAME varchar2(255) NOT
NULL,MAJOR varchar2(255) NOT NULL,STATUS varchar2(2) NOT NULL,ADDRESS
varchar2(300) NOT NULL,GPA number(5,2) NOT NULL,PRIMARY KEY(STUDENTID));
insert into STUDENT(STUDENTID,NAME,MAJOR,STATUS,ADDRESS,GPA)
values(100,'ABLE','HISTORY','SR','1 UTAH',3.00);
insert into STUDENT(STUDENTID,NAME,MAJOR,STATUS,ADDRESS,GPA)
values(100,'BAKER','ACCOUNTING','JR','2 IOWA',2.70);
insert into STUDENT(STUDENTID,NAME,MAJOR,STATUS,ADDRESS,GPA)
values(300,'CHARLES','MATH','SR','3 MAINE',3.50);
insert into STUDENT(STUDENTID,NAME,MAJOR,STATUS,ADDRESS,GPA)
values(400,'DRAKE','COMPUTER SCIENCE','FR','4 IDAHO',2.80);
insert into STUDENT(STUDENTID,NAME,MAJOR,STATUS,ADDRESS,GPA)
values(500,'ELLIOT','COMPUTER SCIENCE','SM','5 NEVADA',3.25);
Page 3 of 35

3. Create table and insert data into Faulty table.
Query:
create table FACULTY (FACULTYID number(4) NOT NULL,NAME varchar2(255) NOT
NULL,ORGCD varchar2(3) NOT NULL,ADDRESS varchar2(255) NOT NULL,CC number(16) NOT
NULL,SALARY number(6) NOT NULL,RANK varchar2(255) NOT NULL,TITLE varchar2(4) NOT
NULL,PRIMARY KEY(FACULTYID));
INSERT INTO FACULTY(FACULTYID,NAME,ORGCD,ADDRESS,CC,SALARY,RANK,TITLE)
VALUES(0980,'MARTIN','IM','11 MAIN',4560123450001234,250000,'DEAN','CEO');
INSERT INTO FACULTY(FACULTYID,NAME,ORGCD,ADDRESS,CC,SALARY,RANK,TITLE)
VALUES(5430,'SEAVER','IS','12 SOUTH',4560123450002345,180000,'PROFESSOR','CIO');
INSERT INTO FACULTY(FACULTYID,NAME,ORGCD,ADDRESS,CC,SALARY,RANK,TITLE)
VALUES(7650,'LOONEY','IT','14 NORTH',4560123450003456,160000,'INSTRUCTOR','CISO');
INSERT INTO FACULTY(FACULTYID,NAME,ORGCD,ADDRESS,CC,SALARY,RANK,TITLE)
VALUES(9870,'MILLS','SA','16 EAST',4560123450004567,90000,'LECTURER','CFO');
INSERT INTO FACULTY(FACULTYID,NAME,ORGCD,ADDRESS,CC,SALARY,RANK,TITLE)
VALUES(9990,'BOND','INT','007 NE‘,4560123450005678,225000,'COACH','SPY');
Page 4 of 35

4. Create table and insert data into Offering table.
Query:
Create table OFFERING (OFFERINGNUM number(4) NOT NULL,COURSENUM varchar2(5) NOT
NULL,FACULTYID number(4) NOT NULL,TERM varchar2(6) NOT NULL,YEAR number(4) NOT
NULL,TIME varchar2(5) NOT NULL,PRIMARY KEY(OFFERINGNUM),FOREIGN
KEY(FACULTYID) REFERENCES FACULTY(FACULTYID));
Insert into OFFERING(OFFERINGNUM,COURSENUM,FACULTYID,TERM,YEAR,TIME)
values(1111,’IS320’,5430,’FALL’,2012,’10 AM’);
Insert into OFFERING(OFFERINGNUM,COURSENUM,FACULTYID,TERM,YEAR,TIME)
values(1233,’IS320’,0980,’FALL’,2012,’11 AM’);
Insert into OFFERING(OFFERINGNUM,COURSENUM,FACULTYID,TERM,YEAR,TIME)
values(2222,’IS460’,7650,’FALL’,2013,’10 AM’);
Insert into OFFERING(OFFERINGNUM,COURSENUM,FACULTYID,TERM,YEAR,TIME)
values(3333,’IS480’,5430,’FALL’,2013,’11 AM’);
Page 5 of 35

5. Create table and insert data into Enrollment table.
Query:
Create table ENROLLMENT (OFFERINGNUM number(4) NOT NULL,STUDENTID number(3) NOT
NULL,FOREIGN KEY(OFFERINGNUM) REFERENCES OFFERING(OFFERINGNUM),FOREIGN
KEY(STUDENTID) REFERENCES STUDENT(STUDENTID));
Insert into ENROLLMENT(OFFERINGNUM,STUDENTID) values(1111,100);
Insert into ENROLLMENT(OFFERINGNUM,STUDENTID) values(1233,500);
Insert into ENROLLMENT(OFFERINGNUM,STUDENTID) values(2222,300);
Insert into ENROLLMENT(OFFERINGNUM,STUDENTID) values(3333,400);
Page 6 of 35

Task 2:
1. Create Roles ‘Student’. Grant the Student Role the privilege to ‘Select on Student’. Grant all students
the Student Role
Query:
Create role Student;
grant select on DBST668.STUDENT to Student;
Create user ABLE identified by ABLE100;
Grant create session to ABLE;
grant Student to ABLE;

Page 7 of 35
2. Demonstrate the results of a student query on the Student table.
Query: connect ABLE/ABLE100;
Select * from DBST668.STUDENT;

Page 8 of 35

3. Create all student as user. Grant create session to all students.
Query:
Create user BAKER identified by BAKER200;
Grant create session to BAKER;
grant Student to BAKER;
Create user CHARLES identified by CHARLES300;
Grant create session to CHARLES;
grant Student to CHARLES;
Create user DRAKE identified by DRAKE400;
Grant create session to DRAKE;
grant Student to DRAKE;
Create user ELLIOT identified by ELLIOT500;
Grant create session to ELLIOT;
grant Student to ELLIOT;


Page 9 of 35


Page 10 of 35

4. Demonstrate the results of a student query on the Student table.
Query:
connect BAKER/BAKER200;
Select * from DBST668.STUDENT;
connect CHARLES/CHARLES300;
Select * from...