Mastering phpMyAdmin 3.3.x for Effective MySQL Management
A complete guide to get started with phpMyAdmin 3.3 and master its features
- The best introduction to phpMyAdmin available
- Written by the project leader of phpMyAdmin, and improved over several editions
- A step-by-step tutorial for manipulating data with phpMyAdmin
- Learn to do things with your MySQL database and phpMyAdmin that you didn’t know were possible!
Managing users and their privileges
The Privileges subpage (visible only if we are logged in as a privileged user) contains dialogs to manage MySQL user accounts. It also contains dialogs to manage privileges on the global, database, and table levels. This subpage is hierarchical. For example, when editing a user’s privileges, we can see the global privileges as well as the database-specific privileges. We can then go deeper to see the table-specific privileges for this database-user combination.
The user overview
The first page displayed when we enter the Privileges subpage is called User verview. This shows all user accounts and a summary of their global privileges, as shown in the next screenshot:
From this page, we can:
- Edit a user’s privileges, via the Edit link for this user
- Use the checkboxes to remove users, via the Remove selected users dialog
- Access the page when the Add a new User dialog is available
The displayed users’ list has columns with the following characteristics:
Privileges reload
At the bottom of User Overview, the following message is displayed:
Note: 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.
Here, the text reload the privileges is clickable. The effective privileges (the ones against which the server bases its access decisions) are the privileges that are located in the server’s memory. Privilege modifications that are made from the User overview page are made both in memory and on disk, in the mysql database. Modifications made directly to the mysql database do not have immediate effect. The reload the privileges operation reads the privileges from the database and makes them effective in memory.
Adding a user
The Add a new User link opens a dialog for user account creation. First, we see the panel where we’ll describe the account itself:
The second part of the Add a new User dialog is where we’ll specify the user’s global privileges, which apply to the server as a whole.
Entering the username
The User name menu offers two choices. Firstly, we can choose Use text field and enter a username in the box, or we can choose Any user to create an anonymous user (the blank user). Let’s choose Use text field and enter bill.
Assigning a host value
By default, this menu is set to Any host, with % as the host value. The Local choice means “localhost”. The Use host table choice (which creates a blank value in the host field) means to look in the mysql.hosts table for database-specific privileges. Choosing Use text field allows us to enter the exact host value we want. Let’s choose Local.
Setting passwords
Even though it’s possible to create a user without a password (by selecting the No password option), it’s best to have a password. We have to enter it twice (as we cannot see what is entered) to confirm the intended password. A secure password should have more than eight characters, and should contain a mixture of uppercase and lowercase characters, digits, and special characters. Therefore, it’s recommended to have phpMyAdmin generate a password—this is possible in JavaScript-enabled browsers. In the Generate Password dialog, clicking on Generate enters a random password (in clear text) on the screen and fills the Password and Re-type input fields with the generated password. At this point, we should note the password so that we can pass it on to the user.
Understanding rights for database creation
A frequent convention is to assign a user the rights to a database having the same name as this user. To accomplish this, the Database for user section offers the checkbox Create database with same name and grant all privileges. Selecting this checkbox automates the process by creating both the database (if it does not already exist) and the corresponding rights. Please note that, with this method, each user would be limited to one database (user bill, database bill).
Another possibility is to allow users to create databases that have the same prefix as their usernames. Therefore, the other choice, Grant all privileges on wildcard name (username_%), performs this function by assigning a wildcard privilege. With this in place, user bill could create the databases bill_test, bill_2, bill_payroll, and so on; phpMyAdmin does not pre-create the databases in this case.
Assigning global privileges
Global privileges determine the user’s access to all databases. Hence, these are sometimes known as “superuser privileges”. A normal user should not have any of these privileges unless there is a good reason for this.
Of course, if we are really creating a superuser, we will select every global privilege that he or she needs. These privileges are further divided into Data, Structure, and Administration groups.
In our example, bill will not have any global privileges.
Limiting the resources used
We can limit the resources used by this user on this server (for example, the maximum queries per hour). Zero means no limit. We will not impose any resource limits on bill.
The following screenshot shows the status of the screen just before hitting Go to create this user’s definition (with the remaining fields being set to default):
Editing a user profile
The page used to edit a user’s profile appears after a user’s creation, or whenever we click on Edit for a user in the User overview page. There are four sections on this page, each with its own Go button. Hence, each section is operated independently and has a distinct purpose.
Editing privileges
The section for editing the user’s privileges has the same look as the Add a new User dialog, and is used to view and change global privileges.
Assigning database-specific privileges
In this section, we define the databases to which our user has access, and his or her exact privileges on these databases.
As shown in the previous screenshot, we see None because we haven’t defined any privileges yet. There are two ways of defining database privileges. First, we can choose one of the existing databases from the drop-down menu:
This assigns privileges only for the chosen database. We can also choose Use text field and enter a database name. We could enter a non-existent database name, so that the user can create it later (provided that we give him or her the CREATE privilege in the next panel). We can also use special characters, such as the underscore and the percent sign, for wildcards.
For example, entering bill here would enable him to create a bill database, and entering bill% would enable him to create a database with any name that starts with bill. For our example, we will enter bill and then click on Go.
The next screen is used to set bill‘s privileges on the bill database, and create table-specific privileges.
To learn more about the meaning of a specific privilege, we can move the mouse over a privilege name (which is always in English), and an explanation about this privilege appears in the current language. We give SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, and DROP privileges to bill on this database. We then click on Go.
After the privileges have been assigned, the interface stays at the same place, so that we can refine these privileges further. We cannot assign table-specific privileges for the moment, as the database does not yet exist.
To go back to the general privileges page of bill, click on the ‘bill’@’localhost’ title.
This brings us back to the following, familiar page, except for a change in one section:
We see the existing privileges (which we can Edit or Revoke) on the bill database for user bill, and we can add privileges for bill on another database. We can also see that bill has no table-specific privileges on the bill database.
Changing the password
The Change password dialog is part of the Edit user page, and we can use it either to change bill’s password or to remove it. Removing the password will enable bill to login without a password. The dialog offers a choice of password hashing options, and it’s recommended to keep the default of MySQL 4.1+ hashing. For more details about hashing, please visit http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html
