MySQL separate user accounts: Difference between revisions
Jump to navigation
Jump to search
MySQL separate user accounts (view source)
Revision as of 08:29, 24 November 2010
, 24 November 2010Reverted edits by Upazixorys (Talk); changed back to last version by Ric
Upazixorys (talk | contribs) No edit summary |
m (Reverted edits by Upazixorys (Talk); changed back to last version by Ric) |
||
| Line 1: | Line 1: | ||
{{Uc nav mysql}} | {{Uc nav mysql}} | ||
'''Hosting separate user accounts on MySQL server''' | '''Hosting separate user accounts on MySQL server''' | ||
| Line 53: | Line 52: | ||
'''Create databases:''' | '''Create databases:''' | ||
<ol start="4"> | |||
<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> Repeat steps 4 and 5 for each new database that requires creating | |||
</ol> | |||
'''Create user accounts:''' | '''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) | (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 opens the user edit privileges page 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:''' | '''Refresh the MySQL grant tables:''' | ||
At the bottom of the '''User overview''' page (home | 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 | Click on reload the privileges link contained in the above statement this executes the query | ||
SQL 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. | Instructing the MySQL server to take another look at the user tables and hence puts all of your new users and privileges into operation. | ||
| Line 114: | Line 113: | ||
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: | ||
# phpMyAdmin searches the mysql.db table for entries with Select_Priv = | # phpMyAdmin searches the mysql.db table for entries with Select_Priv = "Y" belonging to the user. | ||
# If no entries found, the authentication has failed. | # If no entries found, the authentication has failed. | ||
# Otherwise, phpMyAdmin shows all databases the user is allowed to view | # Otherwise, phpMyAdmin shows all databases the user is allowed to view | ||
# If the user's global Select_Priv is | # 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. | This allows users to log into phpMyAdmin and modify their own databases. | ||
| Line 135: | Line 134: | ||
# Scroll down the file and locate these three lines (around line 51): | # 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]['auth_type'] = 'config'; // Authentication method (config, http or cookie based)? | ||
$cfg['Servers'][$i]['user'] = 'root'; // MySQL user | $cfg['Servers'][$i]['user'] = 'root'; // MySQL user | ||
$cfg['Servers'][$i]['password'] = implode ('', file ('../mysql_password')); // MySQL password (only needed | $cfg['Servers'][$i]['password'] = implode ('', file ('../mysql_password')); // MySQL password (only needed | ||
// with 'config' auth_type) | // 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]['auth_type'] = 'http'; // Authentication method (config, http or cookie based)? | ||
$cfg['Servers'][$i]['user'] = ''; // MySQL user | $cfg['Servers'][$i]['user'] = ''; // MySQL user | ||
$cfg['Servers'][$i]['password'] = ''; //MySQL password (only needed with 'config' auth_type) | $cfg['Servers'][$i]['password'] = ''; //MySQL password (only needed with 'config' auth_type) | ||
</pre> | |||
'''Testing:''' Type the following ''' | '''Testing:''' Type the following '''<nowiki>http://localhost/phpMyAdmin/</nowiki>''' into your browser. | ||
You will be challenged to enter name and password. | You will be challenged to enter name and password. | ||
| Line 159: | Line 158: | ||
== Dead in the water == | == Dead in the water == | ||
<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 '''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. | ||
</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 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. | ||
| Line 174: | Line 173: | ||
== Final test == | == Final test == | ||
With your servers online, type into to your browser ''' | With your servers online, type into to your browser '''<nowiki>http://your_domain/phpmyadmin/</nowiki>''' and check that everything works. | ||
For example | For example <nowiki>http://www.name.dyndns.org/phpMyAdmin/</nowiki> | ||
== Conclusion == | == Conclusion == | ||