MySQL phpMyAdmin: Difference between revisions

From The Uniform Server Wiki
Jump to navigation Jump to search
mNo edit summary
No edit summary
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 13: Line 21:


== Starting phpMyAdmin ==
== Starting phpMyAdmin ==
<table>
&lt;table&gt;
<tr>
&lt;tr&gt;
<td valign="top">
&lt;td valign=&quot;top&quot;&gt;
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.<br>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.&lt;br&gt;To start '''phpMyAdmin''' click on its link from the menu '''c)'''.




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




* '''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..
* '''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..




'''''Tip:''''' You can re-run apanel by typing '''<nowiki>http://localhost/apanel/</nowiki>''' into your browser address bar.
'''''Tip:''''' You can re-run apanel by typing '''&lt;nowiki&gt;http://localhost/apanel/&lt;/nowiki&gt;''' 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.
</td>
&lt;/td&gt;
<td>
&lt;td&gt;
[[Image:Uc_mysql_apanel_menu.gif]]
[[Image:Uc_mysql_apanel_menu.gif]]
</td>
&lt;/td&gt;
</tr>
&lt;/tr&gt;
</table>
&lt;/table&gt;
'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''


== phpMyAdmin - Home ==
== phpMyAdmin - Home ==
<table>
&lt;table&gt;
<tr>
&lt;tr&gt;
<td>
&lt;td&gt;
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.
</td>
&lt;/td&gt;
<td>
&lt;td&gt;
[[Image:Uc_phpmyadmin_1.gif]]
[[Image:Uc_phpmyadmin_1.gif]]
</td>
&lt;/td&gt;
</tr>
&lt;/tr&gt;
</table>
&lt;/table&gt;
'''''[[#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.  
<table>
&lt;table&gt;
<tr>
&lt;tr&gt;
<td valign="top">
&lt;td valign=&quot;top&quot;&gt;
'''Starting from phpMyAdmin home page:'''
'''Starting from phpMyAdmin home page:'''


Line 63: Line 71:


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.
<table>
&lt;table&gt;
<tr>
&lt;tr&gt;
<td>
&lt;td&gt;
[[Image:Uc_phpmyadmin_3.gif]]
[[Image:Uc_phpmyadmin_3.gif]]
</td>
&lt;/td&gt;
<td valign="top">
&lt;td valign=&quot;top&quot;&gt;
'''Viewing database list:'''
'''Viewing database list:'''


Line 76: Line 84:


*Number of tables in a database is shown in brackets.
*Number of tables in a database is shown in brackets.
</td>
&lt;/td&gt;
</tr>
&lt;/tr&gt;
</table>
&lt;/table&gt;
</td>
&lt;/td&gt;
<td>
&lt;td&gt;
[[Image:Uc_phpmyadmin_2.gif]]
[[Image:Uc_phpmyadmin_2.gif]]
</td>
&lt;/td&gt;
</tr>
&lt;/tr&gt;
</table>
&lt;/table&gt;
'''''[[#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.
<table>
&lt;table&gt;
<tr>
&lt;tr&gt;
<td>
&lt;td&gt;
=== 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 97: Line 105:
'''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 "Do you really want to :" Click on '''Yes'''   
:'''B)''' Click on '''Drop''' (Delete) when challenged &quot;Do you really want to :&quot; 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 110: Line 118:
'''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.<br>When challenged "You are about to DESTROY a complete database.." Click on '''OK''' this takes you back to the home 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.   


'''''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!
</td>
&lt;/td&gt;
<td>
&lt;td&gt;
[[Image:Uc_phpmyadmin_4.gif]]
[[Image:Uc_phpmyadmin_4.gif]]
</td>
&lt;/td&gt;
</tr>
&lt;/tr&gt;
</table>
&lt;/table&gt;
'''''[[#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'''.  
<table>
&lt;table&gt;
<tr>
&lt;tr&gt;
<td>
&lt;td&gt;
''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).<br>This opens the User overview page.
'''4)''' Click on privileges (see above image creating databases).&lt;br&gt;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
</td>
&lt;/td&gt;
<td>
&lt;td&gt;
[[Image:uc_phpmyadmin_5.gif]]
[[Image:uc_phpmyadmin_5.gif]]
</td>
&lt;/td&gt;
</tr>
&lt;/tr&gt;
</table>
&lt;/table&gt;


=== Add new user page: ===
=== Add new user page: ===
<table>
&lt;table&gt;
<tr>
&lt;tr&gt;
<td valign="top">
&lt;td valign=&quot;top&quot;&gt;
:'''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 165: Line 173:


'''''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!  
</td>
&lt;/td&gt;
<td>
&lt;td&gt;
[[Image:uc_phpmyadmin_6.gif]]
[[Image:uc_phpmyadmin_6.gif]]
</td>
&lt;/td&gt;
</tr>
&lt;/tr&gt;
</table>
&lt;/table&gt;
'''''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 181: Line 189:
== 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.  
<table>
&lt;table&gt;
<tr>
&lt;tr&gt;
<td>
&lt;td&gt;
''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.
</td>
&lt;/td&gt;
<td>
&lt;td&gt;
[[Image:uc_phpmyadmin_7.gif]]
[[Image:uc_phpmyadmin_7.gif]]
</td>
&lt;/td&gt;
</tr>
&lt;/tr&gt;
</table>
&lt;/table&gt;
<table>
&lt;table&gt;
<tr>
&lt;tr&gt;
<td>
&lt;td&gt;
:'''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 208: Line 216:
''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<br> 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&lt;br&gt; 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.  
</td>
&lt;/td&gt;
<td>
&lt;td&gt;
[[Image:uc_phpmyadmin_8.gif]]
[[Image:uc_phpmyadmin_8.gif]]
</td>
&lt;/td&gt;
</tr>
&lt;/tr&gt;
</table>
&lt;/table&gt;


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


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:&lt;br&gt;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.

Revision as of 01:01, 24 November 2010



Under Construction! Please Visit Reserve Page. Page Will Be Available Shortly


CLICK HERE


MySQL: General information.

MySQL creating users and setting permissions using phpMyAdmin

MySQL is a multi-user database, you can set up several different accounts on the same server with many varied permissions given to each user. This ability is part of what makes MySQL so powerful. You can use either MySQL console or phpMyAdmin to create users and grant permissions. This page focuses on using phpMyAdmin.

MySQL Privileges

MySQL privileges allow each of the users to access and utilize only the areas they need to perform their tasks. This prevents a user from accidentally accessing an area where he or she should not have access this adds to the security of the MySQL server.

When you connect to a MySQL server, the host from which you connect and the user name you specify determines your identity. With this information, the server then grants privileges based upon this identity.

phpMyAdmin

phpMyAdmin is an extremely powerfully MySQL database management tool you use either locally or remotely to set up your MySQL database. Uniform Server 3.3 currently uses version 2.6.4-pl4 while 3.5-Apollo uses version 2.10.2 both have the capability of multi-user authentication.

Starting phpMyAdmin

<table> <tr> <td valign="top"> 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.<br>To start phpMyAdmin click on its link from the menu c).



  • Remotely via the Internet: I cover this in more detail on hosting 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 <nowiki>http://localhost/apanel/</nowiki> into your browser address bar.

Note: Menu links A) and B) Start and stop the MySQL server. </td> <td> </td> </tr> </table> Top

phpMyAdmin - Home

<table> <tr> <td> If you loose your bearings in phpMyAdmin click on the home link 1) at anytime, takes you back to the index (start) page. </td> <td> </td> </tr> </table> Top

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. <table> <tr> <td valign="top"> Starting from phpMyAdmin home page:

1) Enter mpg3_db into the create new database field.
2) Click on create this creates the database. Click Home to take you back to the index page.

Repeat the above steps create two more databases named mpg4_db and mpg5_db we will need these later. <table> <tr> <td> </td> <td valign="top"> Viewing database list:

  • A drop down menu displays all databases on your MySQL server.
  • To select a database simply click on its name.
  • Number of tables in a database is shown in brackets.

</td> </tr> </table> </td> <td> </td> </tr> </table> Top

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. <table> <tr> <td>

Uniform Server3.3

Starting from phpMyAdmin home page: - The little house image link

3) Click on databases (see above image). This opens the database page.

A) Check (tick) databases to delete.
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.

Privileges: To view privileges set for that database click the padlock icon to the right of each database name


Uniform Server 3.5-Apollo

Starting from phpMyAdmin home page: - The little house image link

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.
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! </td> <td> </td> </tr> </table> Top

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. <table> <tr> <td> Starting from phpMyAdmin home page: - The little house image link.

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

</td> <td> </td> </tr> </table>

Add new user page:

<table> <tr> <td valign="top">

B) Enter user name power_mad
C) From the drop down menu select Any host
  • Note: Inserts a % code.
D) Enter password mad123
E) Re-enter password mad123
  • An alternative to entering a password into D) and E) is to click on Generate Password:
  • This automatically generates a password for you and looks something like TRwAXKhwLLHfGX:G
  • Click on Copy this inserts the password into D) and E)
F) Scroll down and click on Check All selects all privileges note these are Global (never give a user this power)
G) Click GO to create user a conformation message is displayed.



Quick Check:

Starting from phpMyAdmin home page:- The little house image link.

  • 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 has access to all databases. (remember you can use your browser back button to viewthe previous page)


Note 1: This is a serious user with identical power as root think about the implication! </td> <td> </td> </tr> </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.

  • Letting other users to have global privileges is a recipe for disaster.
  • It is safer to un check all priveledges and set privaliges on specific databases see next section.

Note 3: After adding a new user an additional menu becomes accessible allowing you to set Database-specific privileges. This appears at the bottom of the global privileges selection form.

Top

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. <table> <tr> <td> Starting from phpMyAdmin home page: - The little house image link.

  • 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.
  • Scroll down the page to Database specific privileges form.

</td> <td> </td> </tr> </table> <table> <tr> <td>

H) From the drop down menu
I) select database mpg4_db (database you wish to assign to this user)
J) Click on Go this opens the edit privileges page for that database.
K) Select privileges to allow.
L) Click on Go to complete

Quick Check:

Starting from phpMyAdmin home page: - The little house image link.

  • 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<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. </td> <td> </td> </tr> </table>

Top

Refresh the MySQL grant tables

Information you have changed or created when executing the above statements is stored in a database called mysql. This database was automatically created during MySQL installation. It contains several tables that hold all of the information pertaining to each user, the databases they have access to, the hosts they can connect from, and the privileges allowed for each.


The only time MySQL reads data in these tables is when it first boots (runs). It does not read those tables again unless it is told to do so. Without this step, none of your newly created users or changes to existing users will take effect.


At the bottom of the User overview page

  • Click the little house image link.page
  • Click 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:<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.

That extra database

Have you discovered an extra database you cannot remove! This page explains why information_schema.

Back to MySQL User Accounts MySQL user accounts

Top


Ric