DBM 449 devry Ilab 5 assignment
College of Engineering and Information Sciences
Understand and become familiar with fundamentals of DBMS security monitoring, auditing, logging, alerting, and reporting.
Install and configure a MySQL database.
Install and configure an Audit and Security module for MySQL database.
Generate logged events, and inspect contents of an Audit Log.
II. PARTS LIST
EDUPE-VT Omnymbus Virtual Machine Environment (https://devry.edupe.net:9090/) and/or:
III. PROCEDURE, Part A—Installing MySQL
In order to complete this lab, you will require full control over a LINUX host operating system into which you will install and configure the MySQL database engine. You may achieve this through the DeVry iLab environment (https://devry.edupe.net:9090/), or by installing MySQL onto your own LINUX-based computer, or even into a LINUX Virtual Machine (VM) running in a VM environment on your own computer, such as VMWARE Player, or Sun’s Virtual Box. Full instructions for installing and configuring your own VM environment are beyond the scope of this lab, so unless you have prior experience with VM’s, using the DeVry iLab or installing onto your own LINUX-based computer is recommended.
Preliminary Configuration (DeVry iLab environment only)
Log into the DeVry Omnymbus EDUPE-VT environment, https://devry.edupe.net:9090/.
Create a single VM, using Template #6: VMX-LINUX-DESKTOP-UBUNTU. While you might presume that you should choose SERVER instead of DESKTOP, the DESKTOP VM image provides many tools running from the Graphical User Interface (GUI), which you may find convenient, especially if your experience in LINUX Operating System environments is limited.
Complete all steps required by Omnymbus to complete the configuration of your LINUX host, such as changing the root password and host name. Make a note of the IP address assigned to your new VM, for future use when managing or using your VM instance.
MySQL Database Installation (all environments)
Install the current release (general, not beta) of MySQL. If you are using UBUNTU LINUX, you may do this from the UBUNTU SOFTWARE CENTER, found under APPLICATIONS on the desktop. For other versions of LINUX, research available methods for downloading and installing MySQL appropriate to your Operating System. These might include APT-GET, RedHat Package Manager (RPM), YUM, and so on.
Configure and test that your MySQL service. Be sure that the services have started, and that you are able to connect to MySQL. This may be done in a terminal session using a command line statement, such as: mysql –host=localhost –user=myname –password mydb. Alternatively, you may choose to download and install a GUI SQL administration tool, such as TORA or MySQL Workbench. The command line is installed by default; the GUI tools are optional.
Take a screenshot of your successful connection to your MySQL database, and paste this into your lab report.
We will need a database later, in order to test the audit logging of events we choose to monitor. Just about any trivial database, with a little bit of data in it will suffice. You may use SQL scripts from previous labs, or use the Internet to search for and obtain a copy of the scripts to create and populate the SAKILA sample database. Choose whatever method and schema you wish, and run the scripts to create and populate some tables. Test your data model briefly by issuing a SELECT * from one of the tables. Paste a screen shot showing the successful SELECT and the first five or six rows of the result into your lab report.
McAfee Audit Module Download and Installation (all environments)
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…)
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.
Issue the MySQL console command to install the plugin.
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)
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.
Execute each of the actions planned in the previous step to generate an event in the audit log.
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.
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.
College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 5
Laboratory Title: Database Security
Note: There is no limit on how much information you will enter under the three topics below. It is important to be clear and complete with your comments. Like a scientist you are documenting your progress in this week’s lab experiment.
Objectives: (In your own words what was this lab designed to accomplish? What was its purpose?)
Results: (Discuss the steps you used to complete your lab. Were you successful? What did you learn? What were the results? Explain what you did to accomplish each step. You can include screen shots, code listings, and so on. to clearly explain what you did. Be sure to record all results specifically directed by the lab procedure. Number all results to reflect the procedure number to which they correspond.)
Conclusions: (After completing this lab, in your own words, what conclusions can you draw from this experience?)