DBM Week 5
Lab 5 Database Security
Use the script from week 1 for the week 5 lab.
1. Create four new users
a. The first user will have full rights to the database
b. The second user grant access to the Client’s table and allow them the rights to INSERT data into the clients table
c. The third user grant rights to create user logins and reset passwords
d. The fourth user grant column rights to the Course_Activity table allow access to the Course Code and Grade column. Allow user to UPDATE a maximum of 5 times per hour.
2. Login with each user to demonstrate the rights you have given them
a. User 1 login and create a VIEW for user 2 that just displays the Client table
b. User 2 login and add 3 more clients to the Clients table
c. User 3 login create two new users
d. User 4 login and UPDATE two new entries for Course Code and Grade Column for Client 1 and 2
e. For each user run the Show Privileges command to show correct rights and permissions have been applied
3. Show Log Files: For each user show log files (since we do not have MySQL Enterprise we cannot use the Audit function but as a substitute we can use the built-in log file function)
4. Create a Stored Procedure with User 1 that displays describes the database and grant user 3 access to the stored procedure.
5. Login with user 3 and demonstrate calling the Store Procedure created in step 4.
6. Login with User 2 run an ALTER TABLE command on the COURSE table and show results.
7. Login with User 4 and run an UPDATE command on Course_Activity for client 1 and 2 on column Activity Code and show results.
McAfee Audit Module Download and Installation (all environments)
8. CHECKPOINT QUESTION: In order to proceed with the installation of the McAfee Audit Plugin for MySQL, you will need to gather some additional information. These checkpoint questions will help you to clarify this task: What version of LINUX are you running, and how do you determine this? Are you running a 32-bit or 64-bit version of the Operating system, and how do you determine this? How can you obtain/Where can you download an appropriate version of the McAfee Audit Plugin for your use? Once downloaded, how do you unpack the package to access the plugin components? What is the path of the plugin directory into which you must copy the unpacked/unzipped plugin? Record your answers to these questions, and paste them into your lab report. Download, unpack, and copy all files where they belong. (Hint: If you are unable to find a pre-built installation options through your desktop controls, such as Ubuntu Software Center, you can also download the package using a browser, or by using the wget command line interface statement. This latter statement would begin similar to: sudo wget…)
9. CHECKPOINT QUESTION: The installation of the plugin is done from within the MySQL console. What is the command you must issue to install the plugin? Record your answer in your lab report.
10. Issue the MySQL console command to install the plugin.
11. From the MySQL console, issue the command: show plugins;. Take a screenshot showing your successful results, and paste it into your lab report.
Configuring the McAfee Audit Module (all environments)
12. CHECKPOINT QUESTION: Even though the McAfee Audit Plugin in now installed, it is not yet monitoring things as we would wish. First, the Plugin must be configured. Research what events the plugin is capable of logging, and how to edit the configuration to log them. What command must be entered or what files edited in order to cause the plugin to log the following events: Connect, Quit, Failed Login, Insert, Drop, Create? Record the exact steps you must take and the commands you must enter in order to begin logging the events specified. Next, consider what actions you would need to take to generate each of these events, and document your plan for doing so. Paste both the configuration steps, and the commands you will execute to test the logging of each event into your lab report.
13. Execute each of the actions planned in the previous step to generate an event in the audit log.
14. Determine the location of the audit log (this is commonly /var/lib/mysql/mysql-audit.json). Display the log contents, and analyze them to show that each event you triggered appears in the log. Note: the log file format is not optimized for human readability. The default format generated by this plugin is a .json file, which is easily read and processed for many purposes, including generating pager or system alerts, automated threat responses, as well as reporting. Reporting software that uses such logs is known as Security Information and Event Management (SIEM) software. You may also find utilities that will read and format .json file format into a layout that is easier for humans to read. In any event, take screen shots of your log results, and annotate them to show that each of the required events was captured by the log. Paste a copy of this result into your lab report.
15. CHECKPOINT QUESTION: What has been achieved by this lab, and what steps would remain to provide a comprehensive security system, including reporting, monitoring, alerting, and Automated Threat Response (automatic countermeasures)? In your answer, give an example of a specific example of each of these, and discuss how it would operate (e.g., an Automated Threat Response might be to disable a user account, or to dynamically add a firewall rule rejecting any further traffic from a specific host computer from which a prohibited event was generated). Record your answer in the lab report.
Make sure to provide screenshots either from the command line or workbench demonstrating each of these steps.
-- Table `CLIENT`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `CLIENT` ;
CREATE TABLE IF NOT EXISTS `CLIENT` (
`CLIENT_NO` CHAR(8) NOT NULL,
`CLIENT_COMPANY` VARCHAR(35) NOT NULL,
`CLIENT_NAME` VARCHAR(35) NOT NULL,
`CLIENT_EMAIL` VARCHAR(35) NULL,
`CLIENT_PROGRAM` CHAR(3) NOT NULL,
`CLIENT_SCORE` DECIMAL(10,0) NOT NULL,
PRIMARY KEY (`CLIENT_NO`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `COURSE`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `COURSE` ;
CREATE TABLE IF NOT EXISTS `COURSE` (
`COURSE_CODE` CHAR(8) NOT NULL,
`COURSE_NAME` VARCHAR(35) NOT NULL,
`COURSE_DATE` DATE NOT NULL,
`COURSE_INSTRUCTOR` VARCHAR(35) NOT NULL,
`COURSE_LOCATION` VARCHAR(20) NOT NULL,
PRIMARY KEY (`COURSE_CODE`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `COURSE_ACTIVITY`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `COURSE_ACTIVITY` ;
CREATE TABLE IF NOT EXISTS `COURSE_ACTIVITY` (
`ACTIVITY_CODE` CHAR(8) NOT NULL,
`CLIENT_NO` CHAR(8) NOT NULL,
`COURSE_CODE` CHAR(8) NOT NULL,
`GRADE` CHAR(1) NULL,
`INSTR_NOTES` VARCHAR(50) NULL,
PRIMARY KEY (`ACTIVITY_CODE`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CORP_EXTRACT1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `CORP_EXTRACT1` ;
CREATE TABLE IF NOT EXISTS `CORP_EXTRACT1` (
`EXTRACT_NO` CHAR(3) NOT NULL,
`CLIENT_NO` CHAR(8) NOT NULL,
`CLIENT_NAME` VARCHAR(35) NOT NULL,
`CLIENT_EMAIL` VARCHAR(35) NULL,
`CLIENT_COMPANY` VARCHAR(35) NOT NULL,
`CLIENT_PROGRAM` CHAR(3) NOT NULL,
`CLIENT_SCORE` DECIMAL(10,0) NOT NULL,
`COURSE_NAME` VARCHAR(35) NOT NULL,
`COURSE_DATE` DATE NOT NULL,
`COURSE_INSTRUCTOR` VARCHAR(35) NOT NULL,
`COURSE_LOCATION` VARCHAR(20) NOT NULL,
`COURSE_STATUS` VARCHAR(10) NOT NULL,
PRIMARY KEY (`EXTRACT_NO`))
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- CLIENT rows --
INSERT INTO CLIENT VALUES('C2122542','Bryson, Inc.' ,'Smithson','smithson@bryson.com' ,'DBA',47);
INSERT INTO CLIENT VALUES('C2122356','SuperLoo, Inc.' ,'Flushing','flushing@superloo.com' ,'DBA',38);
INSERT INTO CLIENT VALUES('C2123871','D\&E Supply' ,'Singh' ,'rsingh@desupply.com' ,'EAI',42);
INSERT INTO CLIENT VALUES('C2134452','Gomez Bros.' ,'Ortega' ,'ortega@gomez.com' ,'DBA',39);
INSERT INTO CLIENT VALUES('C2256716','Dome Supply' ,'Smith' ,'smith@dome' ,'ADM',41);
-- COURSE rows --
INSERT INTO COURSE VALUES('DBA12345','DBA 101' ,'2005-10-03','Phung' ,'Kaanapali');
INSERT INTO COURSE VALUES('DBA12346','Advanced DBA' ,'2005-11-23','Browne' ,'San Mateo');
INSERT INTO COURSE VALUES('EAI12345','EAI Intro' ,'2005-11-30','Luss' ,'Danbury');
INSERT INTO COURSE VALUES('DBA12347','DBA 101' ,'2006-01-08','Fiorillo' ,'Paramus');
INSERT INTO COURSE VALUES('DBA12348','DBA 101' ,'2006-02-28','Majmundar' ,'Racine');
-- COURSE ACTIVITY rows --
INSERT INTO COURSE_ACTIVITY VALUES('A0000001','C2122542','DBA12345','A',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000002','C2122356','DBA12347','F',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000003','C2134452','DBA12345','B',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000004','C2122542','DBA12346','A',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000005','C2123871','EAI12345','A',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000006','C2122356','DBA12345',NULL,NULL);
-- CORP_EXTRACT1 rows --
INSERT INTO CORP_EXTRACT1 VALUES ('001','C2122542','Smithson','smithson@bryson.com','Bryson, Inc.','DBA',47,'EAI Intro','2007-03-01','Luss','Hilo','Enrolled');
INSERT INTO CORP_EXTRACT1 VALUES ('002','C2122356','Flushing','flushing@superloo.com','SuperLoo, Inc.','DBA',38,'DBA 101','2005-10-03','Luss','Hilo','Dropped');
INSERT INTO CORP_EXTRACT1 VALUES ('003','C2172249','Bizet','gbizet@bryson.com','Bryson, Inc.','EAI',44,'EAI Intro','2007-03-01','Luss','Hilo','Enrolled');