Zero logo

MySQL Create Restricted User

MySQL privileges allow each of the users to access and utilize only the areas they need to perform their tasks. This prevents a user from accidentally accessing an area where he or she should not have access this adds to the security of the MySQL server.

When you connect to a MySQL server, the host from which you connect and the user name you specify determines your identity. With this information, the server then grants privileges based upon this identity. This page focuses on creating a user with restricted privileges.

There are three methods for creating a restricted user; you can use UniController, phpMyAdmin or MySQL prompt. UniController provides a convenient menu option described bellow. To use this option ensure the MySQL server is running otherwise the button remains greyed out.

Create Restricted MySQL User using UniController

MySQL > Create restricted MySQL User

  • 1) Enter a user name, e.g. fred
  • 2) Enter the user password, e.g. fred123
  • 3) Select a database from the list to assign to the user.
    • The database name (4) is automatically inserted.
    • Alternatively enter a database name into 4).
  • 5) Select or deselect primary user privileges as required.
  • 6) Click "Create User"


Note 1: Cancel (7) clears both the selected database and user input.

Note 2: You can manually enter a database 4) even if it does not exist. The database is assigned to the user and it is assumed you will create this database later.

 Create Restricted MySQL User

Create Restricted MySQL User using phpMyAdmin

Start Controller and start both servers, then click phpMyAmin button. To create a restricted user, proceed as follows:

Note: Assume the user to create is mike123 and password pass123. The database wordpress that the user will be assigned has aleady been created.

Open Add new user page

When first started the phpMyAdmin home page is displayed; you can always return to this page by clicking the home icon (1)

  • 1) If not at the home page, click home icon
  • 2) From the top menu bar, select Privileges
  • 3) Click Add a new User
The Add a new User page is displayed. 		Create restricted user using phpMyAdmin

Create new user

Creating a new user requires only the login information; the other fields are not required. We will assign this user to a database and set appropriate privileges as a second step.

From new user page:

  • 4) Enter user name; example mike123
  • 5) From the drop down menu, select Use text field. Enter 127.0.0.1 . This user is restricted to the IPv4 address for localhost.
  • 6) Enter password; example pass123
  • 7) Re-enter password as entered above.
  • 8) Click Create User; confirmation produced.
Note: Leave all other fields set to their defaults:
    • Database for user: None radio button selected
    • Privileges: all boxes Uncheck
    • Resource limits: all values set to 0
Create restricted user using phpMyAdmin

Assign user to a database

The new user created has now been added to the Privileges User overview page. This new entry allows a user to be assigned to a database as follows:

After creating a new use you will be at the privileges page.

  • 9) If not in Privileges page, from home menu select Privileges
  • 10) Click Edit Privileges for user mike123
 Create restricted user using phpMyAdmin

A new page opens displaying privileges.

  • 11) In the Database-specific privileges section, click the drop down menu and select the database wordpress
Create restricted user using phpMyAdmin

This directs you to the Database-specific privileges section

  • 12 and 13) Select the privileges required for your restricted user.
  • 14) Click Go; this assigns the user to the database with the privileges selected.
 Create restricted user using phpMyAdmin

Create Restricted MySQL User using MySQL Prompt

Creating a user with restricted privileges and assigning that user to an existing database can also be performed using a single SQL line.

Example user
User name to create: fred123
User password: fredpass
Existing database: wordpress

Open MySQL prompt: click > MySQL Console button.
Use the MySQL Client utility to create our example user with restricted privileges as follows:

  • GRANT SELECT, INSERT, UPDATE, DELETE ON wordpress.* TO 'fred123'@'127.0.0.1' IDENTIFIED BY 'fredpass';
  • exit
The result is shown below: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.5.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON wordpress.* TO 'fred123'@'127.0.0.1' IDENTIFIED BY 'fredpass';
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

C:\UniServerZ\core\mysql\bin>

Note: If you have changed the MySQL root password, remember to substitute (-proot) root with your password in the above.

Delete Restricted MySQL User using MySQL Prompt

Delete a user (example fred123) with restricted privileges as follows:

Open MySQL prompt: click > MySQL Console button.
Use the MySQL Client utility to delete our example user fred123:

Enter the following in a command window:

  • DROP USER 'fred123'@'127.0.0.1';
  • exit
The result is shown to the right: 		  
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.5.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates.
 All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the
 current input statement.

mysql> DROP USER 'fred123'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

C:\UniServerZ\core\mysql\bin>

Related topics

MySQL Prompt
How to run a standard command window
MySQL prompt command window short cut