MySQL separate user accounts

MySQL: General information.

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 or 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 phpMyAdmin page for more details including images.

Start servers and phpMyAdmin:

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

Create databases:

4. Enter new database name into Create new database field and click on Create
5. Click on Home which takes you back to the home page.
6. Repeat steps 4 and 5 for each new database that requires creating

Create user accounts:

7. Click on Home (Start page)
8. Click on Privileges link this opens User overview page.
9. Click on Add a new User link this opens Add a new User page

10. Work down this page and fill in the details as follows:
    1. Enter user name (make sure use text field is selected in drop down list)
    2. Host: from the drop down list select Any Host
    3. Password: Enter password (make sure use text field is selected in drop down list)
    4. Re-type: Enter the password again
    5. Make sure all global privileges are UnChecked
    6. Click on Go this creates the user account and displays the user page

11. Scroll down the user page to Database-specific privileges
12. Click on the drop down list and select a database to assign to the user.
    (Alternatively enter the database name and click on go)
    In either case the user edit privileges page opens for that database

13. In the edit privileges check the following:
    1. Data: SELECT, INSERT, UPDATE and DELETE
    2. Structure: CREATE, ALTER, INDEX and DROP
    3. Administration: Leave all UnChecked
    4. Click Go a confirmation page is displayed

14. Repeat steps 7 to 13 for each new user.

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:
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:

1. phpMyAdmin searches the mysql.db table for entries with Select_Priv = "Y" belonging to the user.
2. If no entries found, the authentication has failed.
3. Otherwise, phpMyAdmin shows all databases the user is allowed to view
4. 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:

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

$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)

5. Edit the lines to look like this:

$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)

Testing: Type the following http://localhost/phpMyAdmin/ 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

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.

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 http://your_domain/phpmyadmin/ and check that everything works.

For example http://www.name.dyndns.org/phpMyAdmin/

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

---

Ric