DBM438 Lab 6 Creating and Managing Users in the Database

Description

I. OBJECTIVES
Take a look at the process of creating and managing users in the database, as well as how user management helps support security.
II. ASSUMPTIONS
As a result of this lab, you will have gained a working knowledge of how to create users, grant privileges, and use those privileges to access data in the database.
III. PROCEDURE
For each lab it will be necessary for you to create an output file that will capture all of your commands and work within your MySQL session. The steps for doing this are as follows.
Create a folder on the S: drive of your Windows VM named DBM438. This will be where you will save all of your session output files.
Log into a MySQL session at the ‘root’ level. This will be the default beginning level for all of your iLab sessions.
At the mysql> prompt, type the following: tee s:dbm438labxoutputfile.log, where the ‘x’ is the lab number.
Press enter. You should receive a ‘Logging to ‘s:dbm438labxoutputfile.log’ message.
To stop writing to the log you simply type note at the prompt.
Unfortunately, you cannot copy this file from your VM to your host computer, so you will need to open the file, copy the contents, and then paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session.
NOTE: If you have to stop your lab session and then go back later you need to be sure that you are using the same file name for your output file. Doing so will allow MySQL to append the new work to the end of the file thus allowing you to save all of your lab output into one file. If you do end up using two or more file names then you will need to copy and paste the contents of the second file into the first and thus just have one file to turn in.
Review the lecture in Week 6 for additional information that can be used for this lab.
Before starting this lab, you will need to download a file from Doc Sharing that will be run later during the lab. To download this file, do the following:
Log into eCollege from your VM and download the DBM438_Lab6.SQL file from Doc Sharing to your DBM438 directory on the S: drive in your VM. You will run this script in a later step.
Startup your MySQL session from the Windows Command Line window using localhost as the host, the root user and “devrydevry” password. Once you have the MySQL> prompt, be sure to start your output file printing as described at the beginning of this lab. Now you want to set the database to devrydbm438, which is the database that was created in Lab 2.
Now you are going to go through the process of creating several users, granting them certain privileges, and using those users and privileges. During this process it will be necessary to exit your session as one user and start a new one as another user, so be sure to keep track of your output files each time you have to start a new session. Let’s get started.
As the root user (you are currently logged in as root), create a user name BOB with a password of ALONG and associate the user with LOCALHOST.
Grant privileges to Bob that will allow the user to create tables and insert data, drop tables, and select and update data in the devrydbm438 database.
Now write the statement command that will show the grants for user BOB.
Exit out of this session and start a new one as the user BOB (remember to start a new output file). Once you are logged in and have started your new output file, set the database for the session to devrydbm438.
Once again, write the statement command that will show the privileges for the user, but remember that you are doing it as the user this time and not as root.
Exit out of this session and start a new one as the root user (remember to start a new output file).
Bob has forgotten his password, so assign Bob a new password of OLINK.
Grant user Bob the ability to create a temporary table, also limit the maximum number of connections the user can have per hour to 5.
Once again, write the statement command that will show the privileges for user BOB and also write the statement that will show the user resources for this user.
As the final step for this part of the lab, you need to run the Lab6_support.sql file that you downloaded. You can do this by using the word SOURCE followed by the path to the file (S:DBM438DBM438_lab6.sql) and then hitting the ENTER key.
We are now going to create a second user, so you will need to exit out of your current session (exit out only to the C: prompt) and reconnect as the root user. Be sure to start your output file once logged in to MySQL).
As the root user (you are currently logged in as root), create a user name JACK with a password of HORNOR and associate the user with LOCALHOST.
Grant privileges to Jack that will allow the user to create tables and insert data, drop tables, select data, update data, and create a temporary table in the devrydbm438 database.
Now write the statement command that will show the grants for user Jack.
Write and execute the query using the mysql.user table to show the host and user that currently exist.
Now exit out of your current session and log back in to MySQL as user Jack. Be sure to start your output file once you are logged in.
As user Jack, write a SELECT statement to show all of the data in STUDENT table in the SAMPDB database. What was the result? Add content to the Conclusions section of your Lab Report stating your findings and why this happened.
As user Jack, write a SELECT statement to show all of the data in the EMP table. Now write the CREATE statement that will create a temporary table named EmpTemp, based on the EMP table. Now write a SELECT statement to show all of the contents of the EmpTemp table you just created.
This concludes your lab. You will need to open the output file(s) for this lab on the S: drive of your VM, copy the contents of the file, and paste them into the Lab Report document for this lab under the Lab Results section. If you use Courier New 9pt. font, formatting will look just like in the session. This completes this lab.
Grading of this lab assignment will be based on the following:
[table]
Description, Points
Objectives section is sufficiently filled out., 5
Steps 3 through 7 were satisfactorily completed., 15
Contents of output file showing session input and results were included in the iLab Report., 15
Conclusions section is sufficiently filled out., 5
Total Lab Points:, 50
[/table]
SCREENSHOTS
SOLUTION
PAYMENT
The solution includes a zip document.
Attachments [Move over files to preview content of those files]
DBM438_Week_6_Lab.zip (25.16 KB)
DBM438_Week_6_iLab_Report.docx
Price: $19
Buy Now
Checkout
Added to cart
Buy More Save More
Buy at least TWO items & save up to 30% OFF your ENTIRE order!
Rack up instant rebates in your shopping cart. Simply add items to your cart, and see the savings add up.
Discounts will automatically be applied on eligible orders.
DBM438 Lab 6 Creating and Managing Users in the Database – $19.00
DBM438 Lab 7 Creating and Managing Database Backups – $19.00
Add to Cart
Checkout
Added to cart
You May Also Like:
DBM438 Lab 1 Comparison of MySQL Oracle and SQL Server
DBM438 Lab 2 Create a new MySQL Database
DBM438 Lab 3 General Analysis and Administration of a MySQL Server and Database
DBM438 Lab 4 Taking a look at Different Data Storage Issues and Basic Table Management
DBM438 Lab 5 Optimize Queries in MySQL and Analyzing the Database
DBM438 Lab 7 Creating and Managing Database Backups