| # 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 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 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 |
### 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 |
A new page opens displaying privileges. 11) In the Database-specific privileges section, click the drop down menu and select the database wordpress |
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 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