Part I – Security Matrix.
Go to adbc.kennesaw.edu. Note that you need Java Virtual Machine installed on your computer to run adbc. Go to Security module, matrix sub model as shown in the Figure 0 below. Go through the tutorial. If you are working on this Lab Task in lab then you might need to follow the following steps to get the site working for you on your PC. These steps are required to allow the blocked sites by Java on your machine.
1. Start the Java Control Panel
a. Launch the Windows Start menu
b. Click on Programs
c. Find the Java program listing
d. Click Configure Java to launch the Java Control Panel
2. Setting the security level
a. In the Java Control Panel, click on the Security tab.
b. Keep the Security level as High
c. Under the Exception Site list, Click Edit Site List.
d. Click on Add
e. Type in the URL http://adbc.kennesaw.edu/
f. Click Ok
g. Click OK to save changes made to the Java Control Panel and restart browser for the changes.
Figure 0 – Security Matrix
Question 1: Write your reflection (understanding) of two paragraphs on security matrix you have
observed in the above tutorial. [Marks: 10]
MySQL
Comparing MySQL with Oracle and list main differences in two paragraphs. For these differences you can refer to the following tutorial for reading and this tutorial (link) will also help you in the
lab task…… http://www.tutorialspoint.com/mysql/ [Marks: 10]
Oracle Documentation:
http://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm#CHDIIBJH
Part 2 – Creating a simple VPD in MySQL
Please download MySQL from this link… https://www.dropbox.com/s/y2oeo47zwzr66wo/MySQL.rar?dl=0 then unzip and install it on your machines.
Launch MySQL
OR
Figure 1 – Launching MySQL
After you launch mySQL, you should receive the screen on Figure 2. Type in the password that you entered while installing (Figure 3)
Figure 2 – log-‐in to MYSQL
If you type in your password correctly from Figure 2 in launching MYSQL, you should receive the screen on Figure 3
Figure 3 – MySQL Command Line Client
VIRTUAL PRIVATE DATABASES – EXAMPLE 1
Deliverables: SQL Query and Result (Show your work: SQL QUERY AND RESULT)
In this part of the lab we will create a Database and the below table with data using scripts provided in this document in Bold and Italic.
STUDENT
STUDENTID NAME MAJOR STATUS ADDRESS GPA
100 LUCAS HISTORY SR 1 TEXAS 3.00
200 VANESSA ACCOUNTING JR 2 GEORGIA 2.70
300 LARISSA MATH SR 3 GEORGIA 3.50
400 JACKSON CS FR 4 IDAHO 2.80
500 ZEUS CS SM 5 MAINE 3.25
Now, you are logged in as root (DBA).
Create Database
2) Create and open the database by copying and pasting the following code into the sql prompt:
create database university;
use university;
Create Table and Insert Data into the Table.
3) Create the student table by copying and pasting the following script;
create table STUDENT
(STUDENTID decimal (3) not null,
NAME VARchar(10),
MAJOR VARchar(16),
STATUS VARchar(2),
ADDRESS varchar(12),
GPA decimal(3,2), PRIMARY KEY (STUDENTID)
);
4) Insert data into the Student table by copying and pasting the following script;
insert into Student Values (100, ‘LUCAS’, ‘HISTORY’, ‘SR’, ‘1 TEXAS’, 3.00);
insert into Student Values (200, ‘VANESSA’, ‘ACCOUNTING’, ‘JR’, ‘2 GEORGIA’, 2.70); insert into Student Values (300, ‘LARISSA’, ‘MATH’, ‘SR’, ‘3 GEORGIA’, 3.50);
insert into Student Values (400, ‘JACKSON’, ‘COMPUTER SCIENCE’, ‘FR’, ‘4 IDAHO’, 2.80); insert into Student Values (500, ‘ZEUS’, ‘COMPUTER SCIENCE’, ‘SM’, ‘5 MAINE’, 3.25);
5) To verify if it is successful, you can type
SELECT * FROM Student;
Question: How many rows do you see?
As root user (DBA), I see all rows in Student Table
Create Users.
6) To create the users, copy and paste the commands below into root’s SQL prompt.
DROP USER LUCAS;
CREATE USER ‘LUCAS’ IDENTIFIED BY ‘L’;
DROP USER VANESSA;
CREATE USER ‘VANESSA’ IDENTIFIED BY ‘V’;
DROP USER LARISSA;
CREATE USER ‘LARISSA’ IDENTIFIED BY ‘L’;
DROP USER JACKSON;
CREATE USER ‘JACKSON’ IDENTIFIED BY ‘J’;
DROP USER ZEUS;
CREATE USER ‘ZEUS’ IDENTIFIED BY ‘Z’;
7) To verify if it was successful, type
SELECT user FROM mysql.user;
Create a view and grant access to the view
8) Create view studentView using following script;
CREATE view studentView
as
select * from student where CONCAT(name, ‘@localhost’) = user();
9) Grant select privileges on studentView to the users;
GRANT SELECT ON StudentView TO ‘LUCAS’, ‘LARISSA’, ‘VANESSA’, ‘JACKSON’, ‘ZEUS’;
Login as different users, execute the same query command and see different results.
10) Launch a MS-‐DOS window Start button -‐> Programs -‐> Accessories -‐> Command Prompt login as LUCAS by copying and pasting the following script;
cd c:Program FilesMySQLMySQL Server 5.5bin mysql ‐uLUCAS ‐pL
11) Query the studentView by copying and pasting the following script from SQL Command Line;
USE university
SELECT * FROM StudentView;
Question: How many rows do you see and why?
12) Login as LARISSA using MS-‐DOS.
cd c:Program FilesMySQLMySQL Server 5.5bin mysql ‐uLARISSA ‐pL
13) From the sql command line, execute the following script;
USE university
SELECT * FROM StudentView;
Question: How many rows do you see and why?
User Larissa sees only her record in the StudentView.
Note that user Larissa has typed in the exact same SQL command as user Lucas.
Part III – Creating a simple VPD in Oracle
Repeat these same steps you did for MySQL, but using Oracle using cit640.
Creating a Student Table
At home, I used the following Oracle Syntax and it yielded the same result:
CREATE TABLE Student
( StudentID float(24) NOT NULL,
name varchar2(10),
major varchar2(16),
status varchar2(2),
address varchar2(12),
GPA float(24),
CONSTRAINT student_pk PRIMARY KEY (StudentID)
);
Inserting Data into the Table
Using the same command as in MySQL:
insert into Student Values (100, ‘LUCAS’, ‘HISTORY’, ‘SR’, ‘1 TEXAS’, 3.00);
insert into Student Values (200, ‘VANESSA’, ‘ACCOUNTING’, ‘JR’, ‘2 GEORGIA’, 2.70); insert into Student Values (300, ‘LARISSA’, ‘MATH’, ‘SR’, ‘3 GEORGIA’, 3.50);
insert into Student Values (400, ‘JACKSON’, ‘COMPUTER SCIENCE’, ‘FR’, ‘4 IDAHO’, 2.80); insert into Student Values (500, ‘ZEUS’, ‘COMPUTER SCIENCE’, ‘SM’, ‘5 MAINE’, 3.25);
Selecting all rows from Table Student (same as in MySQL)
Creating Users (same as in MySQL but without ‘)
CREATE USER LUCAS IDENTIFIED BY L;
DROP USER VANESSA;
CREATE USER VANESSA IDENTIFIED BY V;
DROP USER LARISSA;
CREATE USER LARISSA IDENTIFIED BY L;
DROP USER JACKSON;
CREATE USER JACKSON IDENTIFIED BY J;
DROP USER ZEUS;
CREATE USER ZEUS IDENTIFIED BY Z;
Creating a view and grant access to the view
CREATE VIEW studentview as
SELECT * FROM cit640.student WHERE student.name = user;
GRANT SELECT ON StudentView TO LUCAS, LARISSA,VANESSA, JACKSON, ZEUS; Grant create session to LUCAS, LARISSA,VANESSA, JACKSON, ZEUS;
Login as different users, execute the same query command to see different results
In oracle we use the above command to connect:
connect lucas/L;
select * from cit640.studentview;
Do the The same for Larissa and other created users
Part IV – Database Inferences and VPD (do it in ORACLE)
VPD is a way to help combat Database Inference problem. Go to adbc.kennesaw.edu. Select the SECURITY model and the DATABASE INFERECENCE. Read through the Datatabase Inference sub-‐ module. Go through the tutorials of Inference I, Inference II and Inference III. At the end of each
example, take a snapshot of the screen to show you completed the example.
After you read the examples, you will implement a simple VPD example that will help combat the Database Inference problem showed in example III.
14) Login as DBA user CIT640. Create the CARGO_TRANSPORT TABLE and insert the data. Note that we substitute Top Secret with 5, Secret with 4, Classified with 3, e.g.
DROP TABLE Cargo_Transport;
create table Cargo_Transport
(FLIGHT_NUM decimal (3) not null,
CARGO_HOLD char(3),
CARGO_DESC VARchar(16),
CLASSIF DECIMAL(2)
);
/
insert into cargo_Transport Values (706, ‘CH1’, ‘Sox’, 1);
insert into cargo_Transport Values (706, ‘CH2’, ‘Boots’, 1);
insert into cargo_Transport Values (706, ‘CH3’, ‘Nuclear Bomb’, 5);
insert into cargo_Transport Values (706, ‘CH4’, ‘Shirts’, 1);
insert into cargo_Transport Values (706, ‘CH5’, ‘Guns’, 4);
15) Create a table called to ACCESS PRIVILEGES. Insert three users: PrivateSmith with priority TS=5, CoronelK with priority S=4 and GeneralJones with priority C=3.
drop table access_priv;
CREATE TABLE Access_Priv
(userName varchar(14) ,
CLASSIF decimal (2)
);
/
insert into Access_Priv values (‘PRIVATESMITH’, 3); insert into Access_Priv values (‘CORONELK’, 4); insert into Access_Priv values (‘GENERALJONES’, 5);
16) Create a view called CARGO that returns all cargos if the priority is 5. All Cargos except for the TOP SECRET cargos if the priority is 4. All cargos except for the Top secret and Secret Cargos if the priority is 3.
CREATE VIEW CARGO as
SELECT FLIGHT_NUM, CARGO_HOLD, CARGO_DESC
FROM CARGO_TRANSPORT, ACCESS_PRIV WHERE
USER = ACCESS_PRIV.USERNAME
AND
ACCESS_PRIV.CLASSIF
>=
CARGO_TRANSPORT.CLASSIF;
17) Create the three users and grant them access to the cargo view
CREATE USER GENERALJONES IDENTIFIED BY G; CREATE USER CORONELK IDENTIFIED BY K; CREATE USER PRIVATESMITH IDENTIFIED BY P;
GRANT CONNECT TO GENERALJONES, CORONELK, PRIVATESMITH; GRANT SELECT ON CARGO TO GENERALJONES, CORONELK, PRIVATESMITH;
4.1. List all the rows in access_priv table
4.2. List all the rows in cargo_transport table
18) Connect as General and query the CARGO view.
CONNECT GENERALJONES/G;
SELECT * FROM CIT640.CARGO;
Question: How many rows did the user see and why?
19) Connect as Coronel and query the CARGO view.
CONNECT CORONELK/K;
SELECT * FROM CIT640.CARGO;
Question: How many rows did the user see and why?
