Create series of different users-administering privileges

Assignment Help Database Management System
Reference no: EM13842091

Scenario and Summary

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.

Be sure to save all screenshots on Terminal and MySQL Workbench while executing your SQL code when working on the lab.

Part 1: Using CommandLine 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 (Step1, 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 login again as User 2 and update COMM column in Employee table.

MYSQL> UPDATE Employee SET COMM = 0.1;

Assignment Step

Description

Step 1

Creating a new user using command line on Terminal

Step 2

Finding information on users

Step 3

Finding user privileges

Step 4

Changing user specifications

Part 2: Using MySQL Workbench

Assignment Step

Description

Step 1

Creating a new user using MySQL Workbench

Step 2

Finding information on users

Step 3

Finding user privileges

Step 4

Changing user specifications. Assigning users to a role

Step 5

Listing privileges associated with a role

Yourlab 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!
This is a difficult assignment to grade, so please combine multiple outputs in a single document in the order of execution. Addition of headings for each step helps clarity. Also, please trim out any errors and unnecessary content.

Part 1: Using command line on Terminal

STEP 1: Creating a new user using command line on Terminal

To start, launch Start,choose MySQL 5.6 Command Line Client - Unicode

Enter password: Enter your password, which you've created during installation;Press Enter;

We'll check the existing users by:

Mysql> select Host, User from mysql.user;

Your response will look like this:
HOST USER
------------------ -----------------------
127.0.0.1 root

Localhost student1

Now, to add new user:

Mysql> CREATE USER ‘username'@'localhost' IDENTIFIED BY ‘password';

Press Enter.

Please note: Username and password are in a single quotes. Statement is ended with semicolon.

Please add before and after screenshots to the Report.

STEP 2:Finding information on users

To verify currently defined users, write and execute the query to display the following information:

Mysql>select Host, User from mysql.user;

And the result set will include a newly created user.

HOST USER
------------------ ---------------
127.0.0.1 root

Localhost student1

Localhost student2

Please add Before and After screenshots to the Report.

STEP 3: Finding User Privileges

Creating a user doesn't give any access to databases.

We have to grant the privileges to the user.

For example, command below grants ‘super user' privileges:

Mysql> GRANT ALL on *.* TO ‘user name'@‘localhost' WITH GRANT OPTION;

Press Enter.

Where ALL - all privileges;

*.* - to all databases, all tables;

Mysql>flush privileges;

Press Enter.

Mysql>quit

To remove user:

Mysql>DROP USER ‘user name'@'localhost'; Press Enter.

To show all users and their privileges:

Mysql>select * from mysqluser;

Press Enter.

To show privileges:

Mysql>show grants for ‘username'@'localhost';

The result set will include grants for user and encrypted password.

Please add Before and After screenshots to the Report.

STEP 4: Changing User Specifications

Command below grants user with UPDATE privilege to Employee table:

Mysql> GRANT UPDATE on *.Employee TO ‘user name'@‘localhost' WITH GRANT OPTION;

Press Enter.

Where

*.Employee - to all databases, Employee table;

Mysql>flush privileges;

Press Enter.

Mysql>quit

To show grants, login again and type command:

Mysql>show grants for ‘username'@'localhost';

The result set will include grants for user and encrypted password.

Please add Before and After screenshots to the Report.

Part 2: Using MySQL Workbench

a) Add twonew users. Grant ‘super user' access to one and read-only access to all tables in the database to the second user (Step1, 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 1: Creating a new user using MySQL Workbench

To start, launch Start,choose MySQL Workbench, Press Enter.

Choose your schema on Home page to connect to MySQL Server.

Enter password; Press OK.

On the left part of the screen,under the Schema, choose your database schema.

Under the Management, click User and Privileges, Enter password to connect to MYSQL Server.

Screen Administration: User and Privileges allows you to add a new user.

Click Add Account button and fill required information in Login screen.

Repeat for the second user.

Please add screenshots to the Report.

STEP 2: Finding information on users.

STEP 3: Finding user privileges.

STEP 5: Listing privileges associated with a role.

Complete Account Limits,Administrative Roles, and Schema Privileges tabs for User 1 and User 2.

Please add screenshots to the Report.

STEP 4: Changing user specifications. Assigning users to a role.

Add UPDATE privilege on Administrative Roles tab for User 2.

Please add screenshots to the Report.

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;

Reference no: EM13842091

Questions Cloud

Hardware and network plan : Hardware and Network Plan
Project management has declined effectiveness at cic : 1. With reference to tuckman team model discuss how issues of organizational structure and power play a role in the manner in which the project management has declined effectiveness at CIC..
Ensure legal compliance-maintain protection for themselves : PR professionals must adhere to rigorous ethical policies as well as ensure legal compliance and maintain protection for themselves and their organization. For the purposes of this paper, assume that you are the new PR director for an aggressive nutr..
Airplane technology security : airplane technology security
Create series of different users-administering privileges : 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.
What would legal environment of global pr practice look like : The litigious climate in which PR professionals work means that any case, civil or criminal, can draw media attention. Normal involvement with law for PR professionals falls into three general areas: What would the legal environment of a global PR pr..
Inspecting quality into a product : What is meant by the term "Inspecting Quality into a Product"? If you can't inspect quality into a product, how do you get Quality into a product? Consider some of your recent purchases and explain why you bought "that" brand or "that" product. Does ..
Significant difficulty in ensuring safety and security : Technology, including travel-driven technology, has made the world smaller. It has enabled the rapid exchange of ideas and information, further shrinking the world. This has also led to more porous borders with significant difficulty in ensuring safe..
Identify and describe the four elements of persuasion : Identify and describe the four elements of persuasion. Explain which element you think is the most influential and why. Describe how a politician might effectively use each of the four elements. Describe how an advertiser might use each element in a ..

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd