View source for MySQL separate user accounts

{{Uc nav mysql}}
===Hosting separate user accounts on MySQL server===

Hosting separate user accounts on your MySQL server requires a properly configured MySQL user management system and the use of phpMyAdmin-advanced authentication.

While it sounds like a complex task, in reality it is extremely easy because all the components are inherently in place. The Uniform Server has phpMyAdmin set up initally for local access by user root. This is easily changed to provide a central copy for users to access via the Internet and maintain their individual databases.

Before diving in, it is worth looking at a little MySQL history. This helps in understanding why The Uniform Server changed the executable name.

== MySQL 3.23/4.0/4.1 Programs - History ==
I have included this section to show that there were actually several versions of the MySQL server to choose from:

* '''mysqld.exe''' This is the basic version of MySQL if you run Windows 95, 98, or ME. It includes support for all advanced features, and includes debug code to provide additional information in the case of a system crash.
* '''mysqld-opt.exe''' This version of the server lacks a few of the advanced features of the basic server, and does not include the debug code. It's optimized to run quickly on today's processors. This is the version of choice for beginners running Windows 95, 98, or ME.
* '''mysqld-nt.exe''' This version of the server is compiled and optimized like mysqld-opt, but is designed to run under Windows NT/2000/XP as a service. If you're using any of those operating systems, this is probably the server for you.
* '''mysqld-max.exe''' This version is like mysqld-opt, but contains advanced features that support transactions.
* '''mysqld-max-nt.exe''' This version is similar to mysqld-nt, but has advanced features that support transactions.

After installing full versions of MySQL, all the above programs are located in the bin directory. Use mysql-opt for Win98x/ME and mysqld-nt on Windows NT/2000/XP.  Actually, don't use these versions any more, except to do archival or laboratory studies. 

== MySQL 5.*.** Programs ==
When MySQL five series was introduced, support for Windows 95/98/ME and older versions were dropped. The following versions are supported: Windows operating system 2000, XP, Windows Server 2003, Vista and Windows 7. The number of programs were reduced accordingly:

* '''mysqld.exe''' This is the basic version of MySQL It includes support for all advanced features and is optimized for speed.
* '''mysqld-debug.exe''' This is similar to the basic version of MySQL and includes debug code to provide additional information.
* '''mysqld-nt.exe''' This version of the server is compiled and optimized like mysqld, but is designed to run as a service.

'''''Uniform Server'''''

If you look in folder *\usr\local\mysql\bin you may be surprised to find the server is named '''mysqld-opt.exe'''! In reality it is '''mysqld-nt.exe'''. It was renamed to allow backwards compatibility with older plugins and scripts.

The essential point is if you want to upgrade the MySQL server yourself, be sure to use the correct file and rename it accordingly.

Now let's return to providing our users with control and access to their MySQL accounts using phpMyAdmin.

== MySQL user management system ==
MySQL management is straightforward. Every user, with the exception of root (super user), has global select privileges disabled.
Each user must have a name and a corresponding password.
To complete the set-up, assign each user one or more databases to access and assign the appropriate privileges.

That covers the management system. To set-up each user account you can use either [[MySQL Console | MySQL console]] or [[MySQL phpMyAdmin | phpMyAdmin]]. Click the appropriate link for details. Below is a summary for using phpMyAdmin. After this we'll see how to install a central copy of phpMyAdmin for your users to access on-line.

== Summary of steps required using phpMyAdmin ==
Check the [[MySQL phpMyAdmin | phpMyAdmin]] page for more details including images.

'''Start servers and phpMyAdmin:'''

# Start Apache Server by double clicking on '''Server_Start.bat'''
# From apanel start MySQL server click on '''Run Mysql''' link
# From apanel start phpMyAdmin click on '''phpMyAdmin''' link

'''Create databases:'''

<ol start="4">
<li> Enter new database name into '''Create new database''' field and click on '''Create'''
<li> '''Click on Home''' which takes you back to the home page.
<li> Repeat steps 4 and 5 for each new database that requires creating
</ol>

'''Create user accounts:'''

<ol start="7">
<li> Click on '''Home''' (Start page)
<li> Click on '''Privileges''' link this opens User overview page.
<li> Click on '''Add a new''' User link this opens Add a new User page
</ol>

<ol start="10">
<li> Work down this page and fill in the details as follows:
<ol start="1" >
<li> '''Enter user name''' (make sure use text field is selected in drop down list)
<li> Host: from the drop down list select '''Any Host'''
<li> Password: '''Enter password''' (make sure use text field is selected in drop down list)
<li> Re-type: Enter the password again
<li> Make sure all global privileges are UnChecked
<li> Click on '''Go''' this creates the user account and displays the user page
</ol>
</ol>

<ol start="11">
<li> Scroll down the user page to '''Database-specific privileges'''
<li> Click on the '''drop down''' list and '''select a database''' to assign to the user.<br>
(Alternatively enter the database name and click on go)<br>
      In either case the user edit privileges page opens for that database
</ol>

<ol start="13">
<li> In the edit privileges check the following:
<ol>
<li> '''Data:''' SELECT, INSERT, UPDATE and DELETE
<li> '''Structure:''' CREATE, ALTER, INDEX and DROP
<li> '''Administration:''' Leave all UnChecked
<li> Click '''Go''' a confirmation page is displayed
</ol>
</ol>
<ol start="14">
<li>Repeat steps 7 to 13 for each new user.
</ol>

'''Refresh the MySQL grant tables:'''

At the bottom of the '''User overview''' page (home > privileges) you will see the following statement:

"phpMyAdmin gets the users' privileges directly from MySQL's privilege tables. The content of these tables may differ from the privileges the server uses, if they have been changed manually. In this case, you should '''reload the privileges''' before you continue."

Click on reload the privileges link contained in the above statement. This executes the query

 SQL query:<br>FLUSH PRIVILEGES ;

instructing the MySQL server to take another look at the user tables and hence puts all of your new users and privileges into operation.

=== PhpMyAdmin authentication ===
Authentication has been built into phpMyAdmin since version 2.0.3 and uses the following procedure:

# phpMyAdmin searches the mysql.db table for entries with Select_Priv = "Y" belonging to the user.
# If no entries found, the authentication has failed.
# Otherwise, phpMyAdmin shows all databases the user is allowed to view
# If the user's global Select_Priv is "Y", all databases in the system are shown.

This allows users to log into phpMyAdmin and modify their own databases.

== Set up a central copy of phpMyAdmin ==
For security reasons, The Uniform Server hides phpMyAdmin from external users; access is limited to localhost. Using a copy of this with minor changes allows users to administer their own databases over the Internet. A central copy of phpMyAdmin is placed in the root folder with authentication enabled as follows:

# The folder '''phpMyAdmin''' is located in: 
## (3.3) diskw\home\admin\www
## (3.5-Apollo) '''udrive\home\admin\www''' 
# Copy this folder and all its contents to the root folder:
## (3.3) diskw\www
## (3.5) '''udrive\www'''
# Open the configuration file '''config.inc.php''' into a text editor, file located in folder:
## (3.3) diskw\www\phpMyAdmin
## (3.5) udrive\www\phpMyAdmin
# Scroll down the file and locate these three lines (around line 51):

<pre>
$cfg['Servers'][$i]['auth_type'] = 'config';      // Authentication method (config, http or cookie based)?
$cfg['Servers'][$i]['user'] = 'root';             // MySQL user
$cfg['Servers'][$i]['password'] = implode ('', file ('../mysql_password')); // MySQL password (only needed
                                                  // with 'config' auth_type)
</pre>

<ol start=5>
<li> Edit the lines to look like this:
</ol>
<pre>
$cfg['Servers'][$i]['auth_type'] = 'http';       // Authentication method (config, http or cookie based)?
$cfg['Servers'][$i]['user'] = '';                // MySQL user
$cfg['Servers'][$i]['password'] = '';            //MySQL password (only needed with 'config' auth_type)
</pre>

'''Testing:''' Type the following '''<nowiki>http://localhost/phpMyAdmin/</nowiki>''' into your browser.
You will be challenged to enter name and password.

Enter user root and the password you set for the MySQL server.  You will now have access to phpMyAdmin. 

Note: Each user must have a different name and password.

=== Severe Security Situation ===
<div style="border:1px solid red; padding-left:4px; background: #ffcccc">
You are '''STANDING OUTSIDE NAKED''' if you put your servers online leaving '''MySQL account root with password root'''!  Kiss your MySQL server goodbye.  It will be hacked and attacked.
</div>
You are the only one that should know your root password! Remember, the server defaults to root so everyone knows this. Please check out the security checklist page and change the root password according to the instructions on that page.

=== File .htaccess ===
One final touch is to copy the file .htaccess (the one contained in your root folder will do) into the folder phpMyAdmin and edit its contents:

Delete all the lines and add this:

'''IndexIgnore *'''

This prevents viewing of any folders by a browser in phpMyAdmin that do not contain an index page.

=== Final test ===
With your servers online, type into to your browser '''<nowiki>http://your_domain/phpmyadmin/</nowiki>''' and check that everything works.

For example <nowiki>http://www.name.dyndns.org/phpMyAdmin/</nowiki>

== Conclusion ==
Well that about wraps it up for user accounts on a MySQL server -- easy to implement. Even if you are not going to host user accounts, it is still worth reading the [[MySQL phpMyAdmin | phpMyAdmin]] page to get a feel for its power. Remember, you can cobble The Uniform Server to your hearts content. When it becomes irretrievably broken, just delete it and install a clean version.

----
{| 
| [[Image:uc_small_logo.gif]] || [[User:Ric|Ric]]
|}

[[Category: MySQL]]