MySQL, like many other databases, uses the combination of specific user definitions, privileges, and roles to control access to the data in the database. In turn, it provides various layers of security. When the database is first created, there are several users created for the purpose of not only installing various components of the database, but also to manage and administer the database functionality. You have already used administrative role users in previous labs. In this lab, you will be creating a series of different users, administering various privileges to those users, and exploring how roles help provide additional functionality to the user picture.
It could be achieved in two ways:
1) Using command line to access MySQL directly;
2) Using MySQL Workbench User and Privileges option.
Very important! Be sure to save all screenshots on Terminal and MySQL Workbench while executing your SQL code when working on the lab. |
Grading of the lab assignment will be based on the following.
Part 1: Using Command Line on Terminal
a) Add two new users. Grant super user access to one and read-only access to all tables in the database to the second user (Step 1, Step 2, and Step 3).
To prove the super-user access for User 1, please write query to SELECT, DELETE, INSERT, UPDATE from any created table and show successful results.
To prove the read-only access for User 2, please write query to SELECT, DELETE, INSERT, UPDATE from any created table. SELECT, DELETE, INSERT, UPDATE from any created table (Note: Only SELECT query will be eligible.)
Sample of commands to check access:
Mysql>show databases; – will list database names;
Mysql>use database name; – Access allowed or denied.
Add screenshots to Report.
b) Modify access of the second user by adding update access to Employee table (Step 4). Add screenshots to Report.
To prove the UPDATE privilege, please log in again as User 2 and update COMM column in Employee table.
MYSQL> UPDATE Employee SET COMM = 0.1;
Add screenshots to Report.
Assignment Step |
Description |
Points |
Step 1 |
Creating a new user using command line on Terminal |
6 |
Step 2 |
Finding information on users |
6 |
Step 3 |
Finding user privileges |
6 |
Step 4 |
Changing user specifications |
6 |
Part 2: Using MySQL Workbench
Assignment Step |
Description |
Points |
Step 1 |
Creating a new user using MySQL Workbench |
6 |
Step 2 |
Finding information on users |
6 |
Step 3 |
Finding user privileges |
6 |
Step 4 |
Changing user specifications. Assigning users to a role |
6 |
Step 5 |
Listing privileges associated with a role |
6 |
Step 6 |
Verifying role content |
6 |
Your lab session, showing any queries, create statements, or other SQL code, and the resulting return from the database–should be placed in a single file called yourname_Lab_3.txt to submit to the Dropbox for the Week 3 iLab.
Note! |
Part 2: Using MySQL Workbench
a) Add two new users. Grant ‘super user’ access to one and read-only access to all tables in the database to the second user (Step 1, Step 2, and Step 3).
To prove the super user access for User 1, please write query to SELECT, DELETE, INSERT, UPDATE from any created table and show successful results.
To prove the read-only access for User 2, please write query to SELECT, DELETE, INSERT, UPDATE from any created table. (Note: Only SELECT query will be eligible. DELETE, INSERT, UPDATE will result in an access denied message.)
Add screenshots to Report.
b) Modify access of the second user by adding update access to Employee table (Step 4).
Add screenshots to Report.
To prove the UPDATE privilege, please log in again as User 2 and update COMM column in Employee table.
MYSQL> UPDATE Employee SET COMM = 0.1;
STEP 4: Changing user specifications. Assigning users to a role.
Add UPDATE privilege on Administrative Roles tab for User 2.
STEP 6: Verifying role content.
Click on Create a new SQL tab for executing query (located on the left corner below the File tab) to start new Query page.
Please prove super user privilege for user 1 by writing and executing SELECT, INSERT, DELETE, UPDATE statements.
To prove the UPDATE privilege for User 2, log in again as User 2 and update COMM column in Employee table.
UPDATE Employee SET COMM = 0.1;
This concludes the Lab for Week 3.
WhatsApp us