phpMyAdmin Exercise 3

MySQL User Administration Using phpMyAdmin

 

  1. The main page is helpful in setting up privileges on tables and databases. This is primarily used if you were an administrator for a hosting company or Auburn University for example. You only want to allow certain users access to certain databases, and maybe even certain tables in a particular database.
  2. We will add one user that has access to our pet_store database and give him enough privileges to read data from the tables, but not alter the information stored in the tables or to alter the structure of our tables. This is for security reasons. When we begin to write our php code, we don't want anybody who looks at our source code to know our root password, so we'll create a user that has access only to one database.
  3. Click on the word localhost at the top of any page to return to the main screen. Then click on the privileges link on the left side near the bottom.
  4. Click on the add new user link in the middle of the page to begin adding a new user.
  5. Enter your new users name - in my case, I will call him joe.
  6. On the host: field, click on the drop down box and choose local (assuming your php pages are being hosted on your local machine).
  7. Then choose a password for your new user named joe and re-type it as required. Do NOT check any of the boxes (they are for global permissions which we don't want joe to have!)
  8. Then click on the Go button at the bottom of that screen.
  9. Next, you are ready to change joes privileges on the pet_store database. In the middle of the screen select the database pet_store under the Database-specific privileges header and click the Go button.
  10. Put a check mark beside all four options in the Data column of the table. This will allow joe to SELECT, INSERT, UPDATE, & DELETE data from ALL tables in the pet_store database. Be sure to hit the Go button to save your changes. If we wanted to only allow him certain options on certain tables in the pet_store database, we would click on the table specific privileges and update that screen. Figure 5.8 shows this screen.


Figure 5.8

 

11.   So now, in our php code, when we connect to the database, we will connect using joe as our username and Joe's password to login. Joe will only have access to the pet_store database. If someone steals my password, they can only mess up my data. Actually if I don't allow joe the DELETE or INSERT options, they can do even less damage. We will definitely need the SELECT and UPDATE options in any online store or php driven database.

 

 

On Your Own Exercise 3

 

Add another user with a username other than joe and give the same privileges as Joe (i.e. SELECT, INSERT, UPDATE, DELETE on pet_store database). Print out the privileges on your MySQL server by click on the localhost link at the top of any page and then click on privileges. Click on the edit link beside your new user (that you created for this On Your Own Exercise), and print out that screen as well. A sample output is show below.

 


On You Own Exercise 3 - Sample Answer