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.

Sounds a complex task however in reality it is extremely easy because inherently all the components are in place. Uniform Server has phpMyAdmin set-up 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 Uniform Server changed the program name!

MySQL 3.23/4.0/4.1 Programs - History

I have included this section to show 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's 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.

MySQL 5.*.** Programs

When MySQL five series was introduce support for Windows 95/98/ME and older version were dropped. The following versions are supported Windows operating system 2000; XP, Vista and Windows Server 2003 the number of programs were reduced accordingly:

• mysqld.exe This is the basic version of MySQL It includes support for all advanced features and 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 3.5-Apollo

Take a look in folder *\Uniform Server\udrive\usr\local\mysql\bin you may be surprised to find the server is mysqld-opt.exe! In reality it is mysqld-nt.exe renamed allowing backwards compatibility with older plugins and scripts.

Real significance if you want to upgrade the MySQL server yourself make sure to use the correct file and rename it accordingly.

Enough of this digression lets look at 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) have their global select privileges disabled. Each user must have a name and corresponding password. To complete the set-up assign each user one or more databases to access and assign 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 I have provided a summary for using phpMyAdmin after this show you 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 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 opens the user edit privileges page 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

Uniform Server for security reason hides phpMyAdmin from external users, access is limited to local host. 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 contentents 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 (using apanel) you will now have access to phpMyAdmin.

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

Dead in the water

You are dead in the water if you can access the account root with password root put your servers online and kiss your MySQL server good bye.

You are the only one that should know your root password remember the server defaults to root hence every one knows this. Please check out the security checklist page (apanel) and change the root password according to the instructions found on this 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 it contents:

Delete all the lines and add this:

IndexIgnore *

It prevents viewing by a browser any folders 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. 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 break Uniform Server to your hearts content when it becomes irretrievable just delete it and install a clean version.

Top

---

Ric