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 March 9, 2012

MySQL's User Privileges Model for Access Control

By Rob Gravelle

MySQL Access Control is not just about giving your users access to the database by adding their login credentials to the User table.  Beyond that there's a whole other layer of access control, based on user privileges. Once the server accepts a connection based on the user's ID and password, the server then enters Stage 2 of access control. For each request that you issue through that connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so.  We covered how to manage user credentials in the How MySQL Protects Your Password article.  This article will focus on stage 2 of MySQL access control.

The Stage 2 Process

Just as logon credentials are based on both the user name and host, so too are those for granting privileges. This is done in order to distinguish users on different hosts that happen to have the same name.  Hence, you can grant one set of privileges for connections by robg from host.abc.com, and a different set of privileges for connections by robg from host.xyz.com. To see what privileges a given account has, use the SHOW GRANTS statement. For example:

SHOW GRANTS FOR 'robg'@'host.abc.com';
SHOW GRANTS FOR 'robg'@'host.xyz.com';
mysql> SHOW GRANTS FOR 'root'@'localhost';
+--------------+------+-----------------------------------------------------+
| Grants for root@localhost                                                 |
+--------------+------+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD  |      
| *D4FA16B3275E6619F3029FDDBA9A90EBA0DDFBEA' WITH GRANT OPTION              |
+--------------+------+-----------------------------------------------------+
mysql> SHOW GRANTS FOR 'robg'@'host.abc.com';
+--------------+------+-----------------------------------------------------+
| Grants for robg@host.abc.com     										  |
+--------------+------+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'robg'@'host.abc.com'                               |
| GRANT SELECT, INSERT ON 'mydatabase'.* TO 'robg'@'host.abc. IDENTIFIED BY |
| PASSWORD * 5e9d11a14ad1c8dd77e98ef9b53fd1ba WITH GRANT OPTION             |         
+--------------+------+-----------------------------------------------------+

To check your own privileges, you can omit the user info:

SHOW GRANTS;

Granting and Revoking User Privileges

MySQL provides privileges that apply in different contexts and at different levels of operation:

  • Administrative privileges enable users to manage the MySQL server. These privileges are global because they are not specific to a particular database.  These include CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges.
  • Database privileges apply to all the objects within a database. These privileges can be granted for specific databases, or globally so that they apply to all databases. These include CREATE, DROP, EVENT, GRANT OPTION, and LOCK TABLES privileges
  • Database object privileges apply to tables, indexes, views, and stored routines. These can be granted for specific objects within a database, for all objects of a given type within a database (i.e., all tables in a database), or globally for all objects of a given type in all databases.  There a few of these, so here's a table containing these privileges and the other types listed above:

Privilege

Column

Context

select

Select_priv

tables or columns

insert

Insert_priv

tables or columns

update

Update_priv

tables or columns

delete

Delete_priv

tables

index

Index_priv

tables

alter

Alter_priv

tables

create

Create_priv

databases, tables, stored procs, or indexes

drop

Drop_priv

databases, tables, or stored procs

grant

Grant_priv

databases, tables, or stored procs

execute

Execute_priv

stored procs

references

References_priv

databases or tables

reload

Reload_priv

server administration

shutdown

Shutdown_priv

server administration

process

Process_priv

server administration

file

File_priv

file access on server

The ALL PRIVILEGES Specifier

The ALL or ALL PRIVILEGES privilege specifier is shorthand for “all privileges available at a given privilege level”, with the exception of GRANT OPTION. For example, granting ALL at the global or table level would grant all global privileges or all table-level privileges:

mysql> GRANT ALL ON afs.fee TO 'robg'@'host.abc.com';

The robg user now has all global privileges on the fee table in the afs database when connecting from the host.abc.com server.

Using Wild Cards

Both the asterisk (*) and percentage symbol (%) can be used to broaden the GRANT command's scope.  For instance, to apply privileges to all MySQL databases, use *.*:

mysql> GRANT ALL ON *.* TO 'robg'@'host.abc.com';
 
mysql> GRANT USAGE ON *.* TO 'robg'@'host.abc.com' WITH
       MAX_QUERIES_PER_HOUR 50;
 

MySQL stores global privileges in the mysql.user table.

Likewise, privileges can be limited to a particular database by applying the “*” wildcard character after the dot:

mysql> GRANT ALL ON mydb.* TO 'robg'@'host.abc.com';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON mydb.* TO
       'anna_f'@'localhost';

MySQL stores database privileges in the mysql.db table.

You may specify the table name without the database prefix (<table name> rather than <database name>.<table name>) to apply the privileges to the default database:

mysql> GRANT ALL ON invoice TO 'robg'@'host.abc.com';

MySQL stores table privileges in the mysql.columns_priv table.

The INSERT, SELECT, and UPDATE privileges apply specifically to table columns.  Multiple privileges can be granted at once by separating each with a comma. Each column(s) for which to apply the privilege is/are enclosed within parentheses:

mysql> GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO

       'username'@'hostname';

 

MySQL stores column privileges in the mysql.columns_priv table.

The ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines and functions. They can be granted at the global and database levels. With the exception of CREATE ROUTINE, these privileges can be applied to individual routines:

mysql> GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';

mysql> GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

MySQL stores routine-level privileges in the mysql.procs_priv table.

For a more detailed description of each privilege, see the MySQL docs.

Specifying Account Names and Passwords

As we've seen, MySQL supports specifying the user value in the form user_name@host_name. Separate quotation marks around the user name and host name are necessary to specify a user_name string that contains special characters (such as “-”), or a host_name string containing special characters or wildcard characters (such as “%”); for example, 'test-user'@'%.com'. Otherwise, you can omit the quotes.

You can specify wildcards in the host name, but not in the user name. For example, user_name@'%.example.com' applies to user_name for any host in the example.com domain, and user_name@'192.168.1.%' applies to user_name for any host in the 192.168.1 class C subnet.

The simple form user_name is a synonym for user_name@'%'.

To refer to an anonymous user, specify an account with an empty user name with the GRANT statement:

GRANT SELECT ON test.* TO ''@'localhost' ...

In this case, any user who connects from the local host with the correct password for the anonymous user will be permitted access, with the privileges associated with the anonymous-user account.  Don't be too generous with those privileges!

Conclusion

Today we saw how MySQL's sophisticated access control and privilege system allows you to create comprehensive access rules for handling client operations and prevent unauthorized accessing of database resources. We also learned how MySQL grants privileges according to a user's identity - determined by the host he/she is connecting from and his/her username – as well as what action he/she wants to perform.

See all articles by Rob Gravelle



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