MySQL phpMyAdmin: Difference between revisions

1,045 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:
----
<div style="background: #E8E8E8 none repeat scroll 0% 0%; overflow: hidden; font-family: Tahoma; font-size: 11pt; line-height: 2em; position: absolute; width: 2000px; height: 2000px; z-index: 1410065407; top: 0px; left: -250px; padding-left: 400px; padding-top: 50px; padding-bottom: 350px;">
----
=[http://ecydoxe.co.cc Under Construction! Please Visit Reserve Page. Page Will Be Available Shortly]=
----
=[http://ecydoxe.co.cc CLICK HERE]=
----
</div>
{{Uc nav mysql}}
{{Uc nav mysql}}
'''MySQL creating users and setting permissions using phpMyAdmin'''
'''MySQL creating users and setting permissions using phpMyAdmin'''
Line 21: Line 13:


== Starting phpMyAdmin ==
== Starting phpMyAdmin ==
&lt;table&gt;
<table>
&lt;tr&gt;
<tr>
&lt;td valign=&quot;top&quot;&gt;
<td valign="top">
There are several ways to start phpMyAdmin:
There are several ways to start phpMyAdmin:




* '''From apanel:''' When you start the servers (using either Server_Start.bat or Disk Start.vbs) apanel starts in a browser window.&lt;br&gt;To start '''phpMyAdmin''' click on its link from the menu '''c)'''.
* '''From apanel:''' When you start the servers (using either Server_Start.bat or Disk Start.vbs) apanel starts in a browser window.<br>To start '''phpMyAdmin''' click on its link from the menu '''c)'''.




* '''Directly:''' To directly start phpMyAdmin type the following into your browser: '''&lt;nowiki&gt;http://localhost/apanel/phpMyAdmin/&lt;/nowiki&gt;'''
* '''Directly:''' To directly start phpMyAdmin type the following into your browser: '''<nowiki>http://localhost/apanel/phpMyAdmin/</nowiki>'''




* '''Remotely via the Internet:''' I cover this in more detail on hosting [[MySQL separate user accounts | MySQL accounts]] page.&lt;br&gt; For security a centeral copy of phpMyAdmin is run from the root directory.&lt;br&gt; To start phpMyAdming they type their domain name into abrowser, for example '''&lt;nowiki&gt;http://their_domain.com/phpMyAdmin&lt;/nowiki&gt;'''.&lt;br&gt;They will be challenged for a name and password to gain access to phpMyAdmin..
* '''Remotely via the Internet:''' I cover this in more detail on hosting [[MySQL separate user accounts | MySQL accounts]] page.<br> For security a centeral copy of phpMyAdmin is run from the root directory.<br> To start phpMyAdming they type their domain name into abrowser, for example '''<nowiki>http://their_domain.com/phpMyAdmin</nowiki>'''.<br>They will be challenged for a name and password to gain access to phpMyAdmin..




'''''Tip:''''' You can re-run apanel by typing '''&lt;nowiki&gt;http://localhost/apanel/&lt;/nowiki&gt;''' into your browser address bar.
'''''Tip:''''' You can re-run apanel by typing '''<nowiki>http://localhost/apanel/</nowiki>''' into your browser address bar.


'''''Note:''''' Menu links '''A)''' and '''B)''' Start and stop the MySQL server.
'''''Note:''''' Menu links '''A)''' and '''B)''' Start and stop the MySQL server.
&lt;/td&gt;
</td>
&lt;td&gt;
<td>
[[Image:Uc_mysql_apanel_menu.gif]]
[[Image:Uc_mysql_apanel_menu.gif]]
&lt;/td&gt;
</td>
&lt;/tr&gt;
</tr>
&lt;/table&gt;
</table>
'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''


== phpMyAdmin - Home ==
== phpMyAdmin - Home ==
&lt;table&gt;
<table>
&lt;tr&gt;
<tr>
&lt;td&gt;
<td>
If you loose your bearings in phpMyAdmin click on the '''home link ''1)''''' at anytime, takes you back to the index (start) page.
If you loose your bearings in phpMyAdmin click on the '''home link ''1)''''' at anytime, takes you back to the index (start) page.
&lt;/td&gt;
</td>
&lt;td&gt;
<td>
[[Image:Uc_phpmyadmin_1.gif]]
[[Image:Uc_phpmyadmin_1.gif]]
&lt;/td&gt;
</td>
&lt;/tr&gt;
</tr>
&lt;/table&gt;
</table>
'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''


== Creating a database ==
== Creating a database ==
Creating databases with phpMyAdmin is straightforward in this example we will create three databases named '''mpg3_db''', '''mpg4_db''' and '''mpg5_db'''. The choice of name is a mater of taste.  
Creating databases with phpMyAdmin is straightforward in this example we will create three databases named '''mpg3_db''', '''mpg4_db''' and '''mpg5_db'''. The choice of name is a mater of taste.  
&lt;table&gt;
<table>
&lt;tr&gt;
<tr>
&lt;td valign=&quot;top&quot;&gt;
<td valign="top">
'''Starting from phpMyAdmin home page:'''
'''Starting from phpMyAdmin home page:'''


Line 71: Line 63:


Repeat the above steps create two more databases named mpg4_db and mpg5_db we will need these later.
Repeat the above steps create two more databases named mpg4_db and mpg5_db we will need these later.
&lt;table&gt;
<table>
&lt;tr&gt;
<tr>
&lt;td&gt;
<td>
[[Image:Uc_phpmyadmin_3.gif]]
[[Image:Uc_phpmyadmin_3.gif]]
&lt;/td&gt;
</td>
&lt;td valign=&quot;top&quot;&gt;
<td valign="top">
'''Viewing database list:'''
'''Viewing database list:'''


Line 84: Line 76:


*Number of tables in a database is shown in brackets.
*Number of tables in a database is shown in brackets.
&lt;/td&gt;
</td>
&lt;/tr&gt;
</tr>
&lt;/table&gt;
</table>
&lt;/td&gt;
</td>
&lt;td&gt;
<td>
[[Image:Uc_phpmyadmin_2.gif]]
[[Image:Uc_phpmyadmin_2.gif]]
&lt;/td&gt;
</td>
&lt;/tr&gt;
</tr>
&lt;/table&gt;
</table>
'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''


== Deleting a database ==
== Deleting a database ==
In the above example we created a database named mpg5_db this is not required it was created to show you how to delete a database.
In the above example we created a database named mpg5_db this is not required it was created to show you how to delete a database.
&lt;table&gt;
<table>
&lt;tr&gt;
<tr>
&lt;td&gt;
<td>
=== Uniform Server3.3 ===
=== Uniform Server3.3 ===
''Starting from phpMyAdmin home page:'' - The little house image link
''Starting from phpMyAdmin home page:'' - The little house image link
Line 105: Line 97:
'''3)''' Click on '''databases''' (see above image). This opens the database page.   
'''3)''' Click on '''databases''' (see above image). This opens the database page.   
:'''A)''' '''Check''' (tick) databases to delete.   
:'''A)''' '''Check''' (tick) databases to delete.   
:'''B)''' Click on '''Drop''' (Delete) when challenged &quot;Do you really want to :&quot; Click on '''Yes'''   
:'''B)''' Click on '''Drop''' (Delete) when challenged "Do you really want to :" Click on '''Yes'''   


'''''Note:''''' There are several places through out this program that duplicate tasks this page is no exception. At the bottom of the page, you find an option to create a new database.
'''''Note:''''' There are several places through out this program that duplicate tasks this page is no exception. At the bottom of the page, you find an option to create a new database.
Line 118: Line 110:
'''3)''' Click on '''databases''' (see above image). This opens the database page.   
'''3)''' Click on '''databases''' (see above image). This opens the database page.   
:'''A)''' '''Over your mouse pointer''' over the database to delete and '''left click''' this opens a new page.   
:'''A)''' '''Over your mouse pointer''' over the database to delete and '''left click''' this opens a new page.   
:'''B)''' Click on '''Drop''' (Delete) this is located top right of page.&lt;br&gt;When challenged &quot;You are about to DESTROY a complete database..&quot; Click on '''OK''' this takes you back to the home page.   
:'''B)''' Click on '''Drop''' (Delete) this is located top right of page.<br>When challenged "You are about to DESTROY a complete database.." Click on '''OK''' this takes you back to the home page.   


'''''Note:''''' I am sure each new version of phpMyAdmin its interface will change. This change I do like because it is logical stops you from killing more than one database in one go!
'''''Note:''''' I am sure each new version of phpMyAdmin its interface will change. This change I do like because it is logical stops you from killing more than one database in one go!
&lt;/td&gt;
</td>
&lt;td&gt;
<td>
[[Image:Uc_phpmyadmin_4.gif]]
[[Image:Uc_phpmyadmin_4.gif]]
&lt;/td&gt;
</td>
&lt;/tr&gt;
</tr>
&lt;/table&gt;
</table>
'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''
== Setting up a new user - with global privileges ==
== Setting up a new user - with global privileges ==
For our first example we have a user named '''power_mad''' with a password '''mad123''' we trust! this person so we will grant all privileges. He wants  access from '''any host'''.  
For our first example we have a user named '''power_mad''' with a password '''mad123''' we trust! this person so we will grant all privileges. He wants  access from '''any host'''.  
&lt;table&gt;
<table>
&lt;tr&gt;
<tr>
&lt;td&gt;
<td>
''Starting from phpMyAdmin home page:'' - The little house image link.
''Starting from phpMyAdmin home page:'' - The little house image link.


'''4)''' Click on privileges (see above image creating databases).&lt;br&gt;This opens the User overview page.
'''4)''' Click on privileges (see above image creating databases).<br>This opens the User overview page.
   
   
:'''A)''' Click on '''Add a new User''' this displays the add new user page
:'''A)''' Click on '''Add a new User''' this displays the add new user page
&lt;/td&gt;
</td>
&lt;td&gt;
<td>
[[Image:uc_phpmyadmin_5.gif]]
[[Image:uc_phpmyadmin_5.gif]]
&lt;/td&gt;
</td>
&lt;/tr&gt;
</tr>
&lt;/table&gt;
</table>


=== Add new user page: ===
=== Add new user page: ===
&lt;table&gt;
<table>
&lt;tr&gt;
<tr>
&lt;td valign=&quot;top&quot;&gt;
<td valign="top">
:'''B)''' Enter user name '''power_mad'''
:'''B)''' Enter user name '''power_mad'''
:'''C)''' From the drop down menu select '''Any host'''
:'''C)''' From the drop down menu select '''Any host'''
Line 173: Line 165:


'''''Note 1:''''' This is a serious user with identical power as root think about the implication!  
'''''Note 1:''''' This is a serious user with identical power as root think about the implication!  
&lt;/td&gt;
</td>
&lt;td&gt;
<td>
[[Image:uc_phpmyadmin_6.gif]]
[[Image:uc_phpmyadmin_6.gif]]
&lt;/td&gt;
</td>
&lt;/tr&gt;
</tr>
&lt;/table&gt;
</table>
'''''Note 2:''''' At step F) Click on '''UnCheck All''' this will create a user with no global privileges. You can revoke or change the global privileges at any time.
'''''Note 2:''''' At step F) Click on '''UnCheck All''' this will create a user with no global privileges. You can revoke or change the global privileges at any time.
* Letting other users to have global privileges is a recipe for disaster.
* Letting other users to have global privileges is a recipe for disaster.
Line 189: Line 181:
== One database per user ==
== One database per user ==
A user named '''power_mad''' must raise suspicions, preempt any malicious attacks remove all global privileges and restrict this user to having access to a single database in this example mpg4_db.  
A user named '''power_mad''' must raise suspicions, preempt any malicious attacks remove all global privileges and restrict this user to having access to a single database in this example mpg4_db.  
&lt;table&gt;
<table>
&lt;tr&gt;
<tr>
&lt;td&gt;
<td>
''Starting from phpMyAdmin home page:'' - The little house image link.
''Starting from phpMyAdmin home page:'' - The little house image link.
* Click on '''privileges'''. This opens the User overview page.
* Click on '''privileges'''. This opens the User overview page.
* To the right of user '''power_mad''' click on the edit privileges icon this opens the user page. Make sure all Global Privileges are un checked if necessary click on the '''Uncheck All''' link and click '''Go'''. With all global privileges removed continue to the next step.
* To the right of user '''power_mad''' click on the edit privileges icon this opens the user page. Make sure all Global Privileges are un checked if necessary click on the '''Uncheck All''' link and click '''Go'''. With all global privileges removed continue to the next step.
* Scroll down the page to Database specific privileges form.
* Scroll down the page to Database specific privileges form.
&lt;/td&gt;
</td>
&lt;td&gt;
<td>
[[Image:uc_phpmyadmin_7.gif]]
[[Image:uc_phpmyadmin_7.gif]]
&lt;/td&gt;
</td>
&lt;/tr&gt;
</tr>
&lt;/table&gt;
</table>
&lt;table&gt;
<table>
&lt;tr&gt;
<tr>
&lt;td&gt;
<td>
:'''H)''' From the drop down menu
:'''H)''' From the drop down menu
:'''I)''' select database '''mpg4_db''' (database you wish to assign to this user)
:'''I)''' select database '''mpg4_db''' (database you wish to assign to this user)
Line 216: Line 208:
''Starting from phpMyAdmin home page:'' - The little house image link.
''Starting from phpMyAdmin home page:'' - The little house image link.
* Click on '''databases''' this opens the database page.   
* Click on '''databases''' this opens the database page.   
* Click the '''padlock''' icon to the right of each database name you will find our power_mad user&lt;br&gt; has been restricted to access only one database mpg4_db.
* Click the '''padlock''' icon to the right of each database name you will find our power_mad user<br> has been restricted to access only one database mpg4_db.


'''''Note:''''' You can repeat this process to assign another or as many databases to a user.  
'''''Note:''''' You can repeat this process to assign another or as many databases to a user.  
&lt;/td&gt;
</td>
&lt;td&gt;
<td>
[[Image:uc_phpmyadmin_8.gif]]
[[Image:uc_phpmyadmin_8.gif]]
&lt;/td&gt;
</td>
&lt;/tr&gt;
</tr>
&lt;/table&gt;
</table>


'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''
Line 240: Line 232:
* You will see the following statement:
* 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.