Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Feb 17, 2004

An introduction to MySQL permissions - Page 2

By Ian Gilfillan

A brief recap of the process

The order of precedence of the tables is as follows:
USER
|
DB/HOST
|
TABLES_PRIV
|
COLUMNS_PRIV

MySQL checks the user table first, if permission is not granted there, it will check the db and host tables, and, if further confirmation is required, the tables_priv and even the columns_priv tables. Be aware that excessive use of all these tables comes at a performance cost - if before every operation MySQL has to check permissions at a column level, it will be that much slower. Use what you need, no more, no less.

How to GRANT permissions

Hopefully the process has been easy to follow, but the burning question must be, how does anyone set these permissions! There are two ways - directly manipulating the tables, with INSERT, UPDATE and DELETE statements (which require MySQL to be reloaded, or the privileges flushed, for example with the FLUSH PRIVILEGES statement. Note also that if you add a record directly to the password field in the user table, you must use the PASSWORD() function. The alternative, more convenient in my opinion, is using a GRANT statement. The syntax of a GRANT statement is:
GRANT privilege ON table_or_database_name TO user@hostname IDENTIFIED BY 'password'.

The privileges are:

PrivilegeDescription
ALL/ALL PRIVILEGESAll the basic permissions
ALTERPermission to run ALTER statements
CREATEPermission to CREATE tables or databases
CREATE TEMPORARY TABLESPermission to run CREATE TEMPORARY TABLE statements
DELETEPermission to run DELETE statements
DROPPermission to DROP tables or databases
EXECUTEPermission to run stored procedures (in MySQL 5)
FILEPermission to read and write files (e.g. LOAD DATA INFILE statements)
GRANTPermission to GRANT available permissions to other users
INDEXPermission to create, change or drop indexes
INSERTPermission to run INSERT statements
LOCK TABLESPermission to LOCK tables which the user has SELECT access to
PROCESSPermission to view or kill MySQL processes
REFERENCESCurrently unused
RELOADPermission to reload the database (e.g. FLUSH statements)
REPLICATION CLIENTPermission to ask about replication
REPLICATION SLAVEPermission to replicate from the server
SHOW DATABASESPermission to see all databases
SELECTPermission to run SELECT statements
SHUTDOWNPermission to SHUTDOWN the MySQL server
SUPERPermission to connect, even if the number of connections is exceeded, and perform maintenance commands
UPDATEPermission to run UPDATE statements
USAGEPermission to connect and and perform basic commands only

Database and Table names in a GRANT statement

NameDescription
*.*All tables in a database
*All tables in the current database
dbname.*All tables in the named database
dbname.tbnameThe named table in the named database

So, some examples of GRANT in action:

mysql> GRANT SELECT ON *.* TO rushdi@localhost IDENTIFIED BY 'supa_password'
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'rushdi';
+-----------+--------+------------------+-------------+-------------+
| host      | user   | password         | select_priv | insert_priv |
+-----------+--------+------------------+-------------+-------------+
| localhost | rushdi | 0b3bcd316f1c8020 | Y           | N           |
+-----------+--------+------------------+-------------+-------------+

Note that the password has been automatically encrypted. The record also only appears in the user table, not the db table, as permission was granted to all databases. Another example:

mysql> GRANT INSERT ON mysql.* TO suretha@localhost IDENTIFIED BY 'supa_password2';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'Suretha';
+-----------+---------+------------------+-------------+-------------+
| host      | user    | password         | select_priv | insert_priv |
+-----------+---------+------------------+-------------+-------------+
| localhost | suretha | 30f59c271b923c47 | N           | N           |
+-----------+---------+------------------+-------------+-------------+

mysql> SELECT host,db,user,select_priv,insert_priv FROM db WHERE user='Suretha';
+-----------+-------+---------+-------------+-------------+
| host      | db    | user    | select_priv | insert_priv |
+-----------+-------+---------+-------------+-------------+
| localhost | mysql | suretha | N           | Y           |
+-----------+-------+---------+-------------+-------------+

Here records are added to both the user and db tables. To revoke permission, we use the REVOKE statement.

mysql> REVOKE SELECT ON *.* FROM rushdi@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'rushdi';
+-----------+--------+------------------+-------------+-------------+
| host      | user   | password         | select_priv | insert_priv |
+-----------+--------+------------------+-------------+-------------+
| localhost | rushdi | 0b3bcd316f1c8020 | N           | N           |
+-----------+--------+------------------+-------------+-------------+

Note that the record still appears in the table, so he can connect, but select_priv has been disabled.

mysql> REVOKE INSERT ON mysql.* FROM suretha@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,db,user,select_priv,insert_priv FROM db WHERE user='Suretha';
Empty set (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'Suretha';
+-----------+---------+------------------+-------------+-------------+
| host      | user    | password         | select_priv | insert_priv |
+-----------+---------+------------------+-------------+-------------+
| localhost | suretha | 30f59c271b923c47 | N           | N           |
+-----------+---------+------------------+-------------+-------------+

The record has been deleted from the db table, but still appears, with no permissions, in the user table.

Hopefully you're starting to find MySQL permissions flexible and easy to use. Although there is much more to explore, do not overuse the available options. You will make management more complex, and affect performance. Good luck!

» See All Articles by Columnist Ian Gilfillan



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date