Difference between revisions of "MySQL separate user accounts"

From The Uniform Server Wiki
Jump to navigation Jump to search
m (Reverted edits by Upazixorys (Talk); changed back to last version by Ric)
(Punctuation and grammatical changes; some clarification and reorganization.)
 
Line 1: Line 1:
 
{{Uc nav mysql}}
 
{{Uc nav mysql}}
'''Hosting separate user accounts on MySQL server'''
+
===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.
 
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.
+
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 Uniform Server changed the program name!
+
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 ==
 
== 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:
+
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.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.
Line 15: Line 15:
 
* '''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-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.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.
+
* '''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.
+
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 ==
 
== 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:
+
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 optimized for speed.
+
* '''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-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.
 
* '''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'''''
+
'''''Uniform Server'''''
  
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.
+
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.
  
Real significance if you want to upgrade the MySQL server yourself make sure to use the correct file and rename it accordingly.
+
The essential point is if you want to upgrade the MySQL server yourself, be 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.
+
Now let's return to providing our users with control and access to their MySQL accounts using phpMyAdmin.
  
 
== MySQL user management system ==
 
== MySQL user management system ==
MySQL management is straightforward every user with the exception of root (super user) have their global select privileges disabled.
+
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 corresponding password.
+
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 privileges.
+
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 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.
+
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 ==
 
== Summary of steps required using phpMyAdmin ==
Line 54: Line 54:
 
<ol start="4">
 
<ol start="4">
 
<li> Enter new database name into '''Create new database''' field and click on '''Create'''
 
<li> Enter new database name into '''Create new database''' field and click on '''Create'''
<li> '''Click on Home''' takes you back to the home page.
+
<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
 
<li> Repeat steps 4 and 5 for each new database that requires creating
 
</ol>
 
</ol>
Line 82: Line 82:
 
<li> Click on the '''drop down''' list and '''select a database''' to assign to the user.<br>
 
<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>
 
(Alternatively enter the database name and click on go)<br>
       In either case opens the user edit privileges page for that database
+
       In either case the user edit privileges page opens for that database
 
</ol>
 
</ol>
  
Line 104: Line 104:
 
"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."
 
"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
+
Click on reload the privileges link contained in the above statement. This executes the query
  
 
  SQL query:<br>FLUSH PRIVILEGES ;
 
  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.
+
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 ==
+
=== PhpMyAdmin authentication ===
 
Authentication has been built into phpMyAdmin since version 2.0.3 and uses the following procedure:
 
Authentication has been built into phpMyAdmin since version 2.0.3 and uses the following procedure:
  
Line 120: Line 120:
 
This allows users to log into phpMyAdmin and modify their own databases.
 
This allows users to log into phpMyAdmin and modify their own databases.
  
== Set-up a central copy of phpMyAdmin ==
+
== 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:
+
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:  
 
# The folder '''phpMyAdmin''' is located in:  
 
## (3.3) diskw\home\admin\www
 
## (3.3) diskw\home\admin\www
 
## (3.5-Apollo) '''udrive\home\admin\www'''  
 
## (3.5-Apollo) '''udrive\home\admin\www'''  
# Copy this folder and all its contentents to the root folder:
+
# Copy this folder and all its contents to the root folder:
 
## (3.3) diskw\www
 
## (3.3) diskw\www
 
## (3.5) '''udrive\www'''
 
## (3.5) '''udrive\www'''
Line 153: Line 153:
 
You will be challenged to enter name and password.
 
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.  
+
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.
 
Note: Each user must have a different name and password.
  
== Dead in the water ==
+
=== Severe Security Situation ===
 
<div style="border:1px solid red; padding-left:4px; background: #ffcccc">
 
<div style="border:1px solid red; padding-left:4px; background: #ffcccc">
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 '''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>
 
</div>
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.
+
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 ==
+
=== 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:
+
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:
 
Delete all the lines and add this:
Line 170: Line 170:
 
'''IndexIgnore *'''
 
'''IndexIgnore *'''
  
It prevents viewing by a browser any folders in phpMyAdmin that do not contain an index page.
+
This prevents viewing of any folders by a browser in phpMyAdmin that do not contain an index page.
  
== Final test ==
+
=== Final test ===
 
With your servers online, type into to your browser '''<nowiki>http://your_domain/phpmyadmin/</nowiki>''' and check that everything works.
 
With your servers online, type into to your browser '''<nowiki>http://your_domain/phpmyadmin/</nowiki>''' and check that everything works.
  
Line 178: Line 178:
  
 
== Conclusion ==
 
== 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 [[MySQL phpMyAdmin | 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.
+
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.
  
'''''[[#top | Top]]'''''
 
 
----
 
----
 
{|  
 
{|  
Line 186: Line 185:
 
|}
 
|}
  
 
[[Category: UniCenter]]
 
 
[[Category: MySQL]]
 
[[Category: MySQL]]
[[Category: Facts]]
 

Latest revision as of 19:05, 14 July 2011

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:

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

Create user accounts:

  1. Click on Home (Start page)
  2. Click on Privileges link this opens User overview page.
  3. Click on Add a new User link this opens Add a new User page
  1. 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
  1. Scroll down the user page to Database-specific privileges
  2. 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
  1. 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
  1. 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)
  1. 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.


Uc small logo.gif Ric