The Uniform Server Wiki

MySQL separate user accounts: Difference between revisions

Page Discussion

MySQL separate user accounts (view source)

Revision as of 08:29, 24 November 2010

549 bytes removed ,  24 November 2010
m
Reverted edits by Upazixorys (Talk); changed back to last version by Ric
No edit summary
m (Reverted edits by Upazixorys (Talk); changed back to last version by Ric)
Line 1: Line 1:
=[http://ybyfonojot.co.cc Under Construction! Please Visit Reserve Page. Page Will Be Available Shortly]=
{{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">
<ol start="4">
&lt;li&gt; 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'''
&lt;li&gt; '''Click on Home''' takes you back to the home page.
<li> '''Click on Home''' takes you back to the home page.
&lt;li&gt; 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
&lt;/ol&gt;
</ol>


'''Create user accounts:'''
'''Create user accounts:'''


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


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


&lt;ol start=&quot;11&quot;&gt;
<ol start="11">
&lt;li&gt; Scroll down the user page to '''Database-specific privileges'''
<li> Scroll down the user page to '''Database-specific privileges'''
&lt;li&gt; Click on the '''drop down''' list and '''select a database''' to assign to the user.&lt;br&gt;
<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)&lt;br&gt;
(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
&lt;/ol&gt;
</ol>


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


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


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


&quot;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.&quot;
"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:&lt;br&gt;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.
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 = &quot;Y&quot; belonging to the user.
# 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 &quot;Y&quot;, all databases in the system are shown.
# 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):


&lt;pre&gt;
<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)
&lt;/pre&gt;
</pre>


&lt;ol start=5&gt;
<ol start=5>
&lt;li&gt; Edit the lines to look like this:
<li> Edit the lines to look like this:
&lt;/ol&gt;
</ol>
&lt;pre&gt;
<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)
&lt;/pre&gt;
</pre>


'''Testing:''' Type the following '''&lt;nowiki&gt;http://localhost/phpMyAdmin/&lt;/nowiki&gt;''' into your browser.
'''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 ==
&lt;div style=&quot;border:1px solid red; padding-left:4px; background: #ffcccc&quot;&gt;
<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.
&lt;/div&gt;
</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 '''&lt;nowiki&gt;http://your_domain/phpmyadmin/&lt;/nowiki&gt;''' 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.


For example &lt;nowiki&gt;http://www.name.dyndns.org/phpMyAdmin/&lt;/nowiki&gt;
For example <nowiki>http://www.name.dyndns.org/phpMyAdmin/</nowiki>


== Conclusion ==
== Conclusion ==
Retrieved from "https://wiki.uniformserver.com/Special:MobileDiff/4810"