MySQL phpMyAdmin

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

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. To start phpMyAdmin click on its link from the menu c). Directly: To directly start phpMyAdmin type the following into your browser: http://localhost/apanel/phpMyAdmin/ Remotely via the Internet: I cover this in more detail on hosting MySQL accounts page. For security a centeral copy of phpMyAdmin is run from the root directory. To start phpMyAdming they type their domain name into abrowser, for example http://their_domain.com/phpMyAdmin. They will be challenged for a name and password to gain access to phpMyAdmin.. Tip: You can re-run apanel by typing http://localhost/apanel/ into your browser address bar. Note: Menu links A) and B) Start and stop the MySQL server.

Top

phpMyAdmin - Home

If you loose your bearings in phpMyAdmin click on the home link 1) at anytime, takes you back to the index (start) page.

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.

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

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.

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. 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!

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.

Starting from phpMyAdmin home page: - The little house image link. 4) Click on privileges (see above image creating databases). This opens the User overview page. A) Click on Add a new User this displays the add new user page

Add new user page:

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!

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.

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.

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

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:
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