Creating User with Access to Only One Database

Creating User with Access to only One Database. Let say you have a few databases in your  MySQL   . For instance  ( Sales Database, Engineering Databases, Human Resource Databases etc.

There is a new Recruit in your Company . you would wanna create access right for this new user.to one  particular Databases. In MySQL , there are 2 ways to do that .You can either use the Graphic User Interface  to configure  or the CLI (Command Line  Interface).

In this Blog , i will show you both ways on how to configure that.

Prior Starting you should have phpMyAdmin installed in your Computer  ,If you don’t have , check out the Steps on how to install Here

beside that you should have several different database  created in phpMyAdmin ,  ,If you don’t have , check out the Steps on how to  create new Database Here

 

If All Set let get started.

 

Creating User with Access to Only One Database with Graphic User Interface (GUI)

Step 1

At your browser type  ” http://localhost/phpMyAdmin ”

 

Step 2

Navigate to the Users Tab and Click ”  Add User ”

 

Step 3

Populate New User Credentials at the (Login Information Only)

(1) UserName

(2) Password

(3) Server ( this example is localhost )

Note :  Do not  global privilege , and check all , this will grant the User Access to all your Database in the Systems

Step 4

Check whether your New User is being created

In this Example

New User Name : david  Password :david

 

Step 5

At the User List . Click the  New User that you have just Created

In this Example is David

then

(1) at  ” Privilege to the following database” select  the Database that you want your new User to access. In this Example is  mydata Data Base

(2) Then Click ” go ”

Step 6

Grant the new User  , privilege , fields that he or she can access to your specific data base

In this Case I grant all Privilege to David on mydata Database

 

Step 7

After you grant access  and privileges of the specific Database  to your new User . Double check whether the Setting is being Configure

(1) go to Database

(2) Select your Specific Database, in this case  for me is mydata Database

(3) click Privelleges

(4) Check whether your new User have access to this Data base

Creating User with Access to Only One Database with Command Line Interface (CLI)

In this Example, I will create a new user  call Jane  and grant  her access to mydata Database

credential are as below

(1) Username : jane

(2) Password  : jane

(3) Host : Localhost

Step 1

Pull up and Extend the Command Line Console

and type the below

CREATE USER ‘jane’@’localhost’ IDENTIFIED BY ‘jane’;GRANT USAGE ON *.* TO ‘jane’@’localhost’ IDENTIFIED BY ‘jane’ REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

 

The above command means , Create a User call Jane at localhost with the identified Password  as ” jane ”  no maximum  limit of query jane can make no maximum  limit of connection time period .no maximum limit of times to connect

 

 

Step 2

Check whether the New User Jane is being created successfully

 

Step 3

(1) Grant the Specific Database   and privilege that the User can access

(2) In this Case is Jane accessing mydata Database

 

Type the below Code  , according to your Situation

 

GRANT ALL PRIVILEGES ON `mydata`.* TO ‘jane’@’localhost’ WITH GRANT OPTION;

 

The above code means grant all all privilege to Jane on mydata database  in localhost

 

 

Step 4

Verify whether the Command is being successfully execute

 

Step 5

Check whether Jane is listed  within the list of User which has access right to mydata Database