Home arrow PHP arrow Page 6 - Doing More With phpMyAdmin (Part 1)

The Privileged Few - PHP

You might not know this, but you can do a lot more with phpMyAdmin than just create tables and insert records. This first in a two-part series takes a look at some of the other features hidden under the hood of this popular PHP application, explaining how it can be used to secure access to the MySQL server, manage multiple servers, manipulate user privileges, view reports on server activity, and export MySQL data into different formats.

  1. Doing More With phpMyAdmin (Part 1)
  2. Start Me Up
  3. Locking the Doors
  4. The More the Merrier
  5. A Perfect State
  6. The Privileged Few
  7. In and Out
  8. Mood Ring
By: Harish Kamath, (c) Melonfire
Rating: starstarstarstarstar / 148
October 27, 2003

print this article


You may or may not know this, but the entire MySQL security system consists of five tables (the so-called "grant tables").

| columns_priv
| db
| host
| tables_priv
| user

Each of these has a different role to play in deciding whether a user has access to a specific database, table or table column. Access rules may be set up on the basis of username, connecting host, or database requested.

When a user requests a connection to the database server from a specific host, MySQL will first check whether there is an entry for the user in the grant tables, if the user's password is correct, and if the user is allowed to connect from that specific host. If the check is successful, a connection will be allowed to the server.

Once a connection is allowed, every subsequent request to the server - SELECT, DELETE, UPDATE and other queries - will first be vetted to ensure that the user has the security privileges necessary to perform the corresponding action. A number of different levels of access are possible - some users may only have the ability to SELECT from the tables, while others may have INSERT and UPDATE capabilities, but not DELETE capabilities.

Normally, changes to the grant tables are made using the special GRANT and REVOKE commands (you've already seen these in action when creating the "admin" user a few pages back). However, if you don't really want to use command-line SQL to accomplish user privilege changes, phpMyAdmin includes a Web-based interface for the same that is both friendly and easy to use.

This privilege module is accessible from the main application page, via the "Privileges" link. By default, this module provides a report of all the users MySQL currently knows about, together with a list of the privileges assigned to each one.

Adding a new user is accomplished via the "Add a new user" link, which allows you to create a new user record in the "users" table and simultaneously assign privileges to that user. When setting user privileges, phpMyAdmin allows you to set privileges for a specific user, or create generic rules that apply to any user. Example.

The privileges that may be assigned to a user are broken down into three categories: data manipulation privileges like SELECT and INSERT, data creation privileges like CREATE and DROP, and administrative privileges like RELOAD and LOCK TABLES. Assigning privileges is as simple as checking boxes on a Web form - no complex syntax needed.

Also new in MySQL 4.x is the ability to assign resource limits - the maximum number of queries, connections and changes per hour - per user; phpMyAdmin supports this as well.Example.

For existing users, the phpMyAdmin privilege module allows you to change user passwords, alter privileges and resource limits, assign privileges on a per-database basis and (I really, really like this feature!) copy an existing user's profile to create a new user with identical privileges but a different name.

As you can imagine, this can come in very handy when you need to quickly create a user "like joe", but don't want to go through the hassle of checking Joe's privileges and manually assigning them to a new user.

>>> More PHP Articles          >>> More By Harish Kamath, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: