The Privileged Few - PHP

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