Changes

Jump to navigation Jump to search

Coral: mysql create restricted user

7,829 bytes added, 13:40, 12 November 2011
Created page with "<div id="top" style="margin:0;padding: 0px 0px 0px 5px; color: #000; background-color: #FFFFEB; border: 1px solid darkslateblue;"><span id="top" style="margin:0;padding: 0px;font..."
<div id="top" style="margin:0;padding: 0px 0px 0px 5px; color: #000; background-color: #FFFFEB; border: 1px solid darkslateblue;"><span id="top" style="margin:0;padding: 0px;font-size:12px">'''''Coral'' :'''&nbsp; [[Coral: mysql intro|MySQL]] | [[Coral: mysql change password|Change password]] | [[Coral: mysql restore password|Restore password]] | [[Coral: mysql console|MySQL Console]] | [[Coral: mysql create delete database|Create delete Database]] | [[Coral: mysql create restricted user|Create Restricted User]] | [[Coral: mysql edit restricted user|Edit Restricted User]]</span></div>

{|style="margin-top:5px;"
|-valign="top"
|
<span style="margin-bottom:5px;font-size:25px;color: #31799F;">MySQL Create Restricted User</span>

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 a command window, phpMyAdmin or UniController. UniController provides a convenient menu option described bellow. To use this option ensure the MySQL server is running otherwise a warning message is produced.

__TOC__

|
{| cellpadding="0" cellspacing="0" style="margin-left:6px;margin-bottom:5px;padding-left:6px;padding-right:6px;font-size:11px;background-color: #FFFFEB;border: 1px solid darkslateblue;"
|-
|colspan="2" style="font-weight:bold;"| UniServer&nbsp;8-Coral
|-
|&nbsp;||[[Coral: Introduction|Home]]
|-
|&nbsp;||[[Coral: start quick start|Quick Start]]
|-
|&nbsp;||[[Coral: general intro|General]]
|-
|&nbsp;||[[Coral: apache intro|Apache]]
|-
|<span style="font-size: 14px;">»</span>||[[Coral: mysql intro|MySQL]]
|-
|&nbsp;||[[Coral: php intro|PHP]]
|-
|&nbsp;||[[Coral: msmtp intro|MSMTP]]
|-
|&nbsp;||[[Coral: cron intro|CRON]]
|-
|&nbsp;||[[Coral: dtdns intro|DtDNS]]
|-
|&nbsp;||[[Coral: dbbackup intro|Db Backup]]
|-
|&nbsp;||[[Coral: perl intro|Perl]]
|-
|&nbsp;||[[Coral: index main|Main Index]]
|}

|}

==Create Restricted MySQL User using UniController==
<span style="padding:6px;background-color:#99FFFF"> Server Configuration > MySQL > Create Restricted MySQL User </span>

{|
|-valign="top"
|
* 1) Enter a user name, e.g. fred
* 2) Enter the user password, e.g. fred123
* 3) Select a database to assign to the user. The database name (4) is automatically inserted.
* 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.

|
[[Image:Coral_mysql_restricted_user.gif]]
|}

'''''[[#top | Top]]'''''
==Create Restricted MySQL User using phpMyAdmin==

Start UniController 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.

{|
|-vlign="top"
|
===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.
|
[[Image:Coral_mysql_ru_phpmyadmin1.gif]]
|-vlign="top"
|

===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 Local. This user is restricted to 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'''
|
[[Image:Coral_mysql_ru_phpmyadmin2.gif]]
|-vlign="top"
|
===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) Select the privileges required for your restricted user.
* 13) Click Go; this assigns the user to the database with the privileges selected.
|
[[Image:Coral_mysql_ru_phpmyadmin3.gif]]
|}

'''''[[#top | Top]]'''''
==Create Restricted MySQL User using command window==
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'''''<br />
User name to create: '''fred123'''
User password: '''fredpass'''
Existing database: '''wordpress'''


Click MySQL console, which opens a command window. Use the MySQL Client utility from here to create our example user with restricted privileges as follows:

* mysql -uroot -proot
* GRANT SELECT, INSERT, UPDATE, DELETE ON wordpress.* TO 'fred123'@'localhost' IDENTIFIED BY 'fredpass';
* exit

The result is shown below:

<pre>
C:\UniServer\usr\local\mysql\bin>mysql -uroot -proot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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'@'localhost' IDENTIFIED BY 'fredpass';
Query OK, 0 rows affected (0.09 sec)

mysql> exit
Bye

C:\UniServer\usr\local\mysql\bin>
</pre>
'''Note:''' If you have changed the MySQL root password, remember to substitute (-proot) root with your password in the above.

'''''[[#top | Top]]'''''
==Delete Restricted MySQL User using command window==
Delete a user (example fred123) with restricted privileges as follows:


Click MySQL console which opens a command window. Use the MySQL Client utility from here to delete our example user fred123:

{|
|-valign="top"
|
Enter the following in a command window:

* mysql -uroot -proot
* DROP USER 'fred123'@'localhost';
* exit

The result is shown to the right:
|
<pre>
C:\UniServer\usr\local\mysql\bin>mysql -uroot -proot

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

mysql> exit
Bye

</pre>
|}
Note: If you have changed the MySQL root password, remember to substitute (-p'''root''') '''root''' with your password in the above.

'''''[[#top | Top]]'''''
==Related topics==

[[Coral: mysql console|MySQL Console]]<br />
[[Coral: mysql console#How to run a standard command window|How to run a standard command window]]<br />
[[Coral: mysql console#MySQL console command window short cut|MySQL console command window short cut ]]<br />


'''''[[#top | Top]]'''''

----

[[Category: Uniform Server 8-Coral]]

Navigation menu