Home arrow MySQL arrow Working with the MySQL Access Privilege System

Working with the MySQL Access Privilege System

If you need to administer MySQL, this article gets you off to a good start. In this section, we continue our discussion of security issues with the MySQL access privilege system. The third of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

TABLE OF CONTENTS:
  1. Working with the MySQL Access Privilege System
  2. 4.4.3 Privileges Provided by MySQL
  3. 4.4.4 Connecting to the MySQL Server
  4. 4.4.5 Access Control, Stage 1: Connection Verification
  5. 4.4.6 Access Control, Stage 2: Request Verification
  6. 4.4.7 When Privilege Changes Take Effect
  7. 4.4.9 Password Hashing in MySQL 4.1
By: Sams Publishing
Rating: starstarstarstarstar / 6
June 08, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

4.4 The MySQL Access Privilege System

MySQL has an advanced but non-standard security/privilege system. This section describes how it works.

4.4.1 What the Privilege System Does

The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE.

Additional functionality includes the ability to have an anonymous user and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

4.4.2 How the Privilege System Works

The MySQL privilege system ensures that all users may perform only the operations allowed to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the username you specify. The system grants privileges according to your identity and what you want to do.

MySQL considers both your hostname and username in identifying you because there is little reason to assume that a given username belongs to the same person everywhere on the Internet. For example, the user joe who connects from office.com need not be the same person as the user joe who connects from elsewhere.com. MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: You can grant joe one set of privileges for connections from office.com, and a different set of privileges for connections from elsewhere.com.

MySQL access control involves two stages:

  • Stage 1: The server checks whether you are even allowed to connect.

  • Stage 2: Assuming that you can connect, the server checks each statement you issue to see whether you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server verifies that you have the SELECT privilege for the table or the DROP privilege for the database.

If your privileges are changed (either by yourself or someone else) while you are connected, those changes will not necessarily take effect immediately for the next statement you issue. See Section 4.4.7, "When Privilege Changes Take Effect," for details.

The server stores privilege information in the grant tables of the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables into memory when it starts and re-reads them under the circumstances indicated in Section 4.4.7, "When Privilege Changes Take Effect." Access-control decisions are based on the in-memory copies of the grant tables.

Normally, you manipulate the contents of the grant tables indirectly by using the GRANT and REVOKE statements to set up accounts and control the privileges available to each one. The discussion here describes the underlying structure of the grant tables and how the server uses their contents when interacting with clients.

The server uses the user, db, and host tables in the mysql database at both stages of access control. The columns in these grant tables are shown here:

Table Name

user

db

host

Scope columns

 

Host

Host

Host

 

User

Db

Db

 

Password

User

 

Privilege columns

 

Select_priv

Select_priv

Select_priv

 

Insert_priv

Insert_priv

Insert_priv

 

Update_priv

Update_priv

Update_priv

 

Delete_priv

Delete_priv

Delete_priv

 

Index_priv

Index_priv

Index_priv

 

Alter_priv

Alter_priv

Alter_priv

 

Create_priv

Create_priv

Create_priv

 

Drop_priv

Drop_priv

Drop_priv

 

Grant_priv

Grant_priv

Grant_priv

 

References_priv

References_priv

References_priv

 

Reload_priv

 

 

 

Shutdown_priv

 

 

 

Process_priv

 

 

 

File_priv

 

 

 

Show_db_priv

 

 

 

Super_priv

 

 

 

Create_tmp_table_priv

Create_tmp_table_priv

Create_tmp_table_priv

 

Lock_tables_priv

Lock_tables_priv

Lock_tables_priv

 

Execute_priv

 

 

 

Repl_slave_priv

 

 

 

Repl_client_priv

 

 

 

ssl_type

 

 

 

ssl_cipher

 

 

 

x509_issuer

 

 

 

x509_subject

 

 

 

max_questions

 

 

 

max_updates

 

 

 

max_connections

 

 


During the second stage of access control (request verification), the server may, if the request involves tables, additionally consult the tables_priv and columns_priv tables that provide finer control at the table and column levels. The columns in these tables are shown here:

Table Name

tables_priv

columns_priv

Scope columns

 

 

 

Host

Host

 

Db

Db

 

User

User

 

Table_name

Table_name

 

 

Column_name

Privilege columns

 

 

 

Table_priv

Column_priv

 

Column_priv

 

Other columns

 

 

 

Timestamp

Timestamp

 

Grantor

 


The Timestamp and Grantor columns currently are unused and are discussed no further here.

Each grant table contains scope columns and privilege columns:

  • Scope columns determine the scope of each entry (row) in the table; that is, the context in which the entry applies. For example, a user table entry with Host and User values of 'thomas.loc.gov' and 'bob' would be used for authenticating connections made to the server from the host thomas.loc.gov by a client that specifies a username of bob. Similarly, a db table entry with Host, User, and Db column values of 'thomas.loc.gov', 'bob' and 'reports' would be used when bob connects from the host thomas.loc.gov to access the reports database. The tables_priv and columns_priv tables contain scope columns indicating tables or table/column combinations to which each entry applies.

  • Privilege columns indicate the privileges granted by a table entry; that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. The rules used to do this are described in Section 4.4.6, "Access Control, Stage 2: Request Verification."

Scope columns contain strings. They are declared as shown here; the default value for each is the empty string:

Column Name

Type

Host

CHAR(60)

User

CHAR(16)

Password

CHAR(16)

Db

CHAR(64)

Table_name

CHAR(60)

Column_name

CHAR(60)


Before MySQL 3.23, the Db column is CHAR(32) in some tables and CHAR(60) in others.

For access-checking purposes, comparisons of Host values are case-insensitive. User, Password, Db, and Table_name values are case-sensitive. Column_name values are case-insensitive in MySQL 3.22.12 or later.

In the user, db, and host tables, each privilege is listed in a separate column that is declared as ENUM('N','Y') DEFAULT 'N'. In other words, each privilege can be disabled or enabled, with the default being disabled.

In the tables_priv and columns_priv tables, the privilege columns are declared as SET columns. Values in these columns can contain any combination of the privileges controlled by the table:

Table Name

Column Name

Possible Set Elements

tables_priv

Table_priv

-'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'

tables_priv

Column_priv

'Select', 'Insert', 'Update', 'References'

columns_priv

Column_priv

'Select', 'Insert', 'Update', 'References'


Briefly, the server uses the grant tables as follows:

  • The user table scope columns determine whether to reject or allow incoming connections. For allowed connections, any privileges granted in the user table indicate the user's global (superuser) privileges. These privileges apply to all databases on the server.

  • The db table scope columns determine which users can access which databases from which hosts. The privilege columns determine which operations are allowed. A privilege granted at the database level applies to the database and to all its tables.

  • The host table is used in conjunction with the db table when you want a given db table entry to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave the Host value empty in the user's db table entry, then populate the host table with an entry for each of those hosts. This mechanism is described in more detail in Section 4.4.6, "Access Control, Stage 2: Request Verification."

    Note: The host table is not affected by the GRANT and REVOKE statements. Most MySQL installations need not use this table at all.

  • The tables_priv and columns_priv tables are similar to the db table, but are more fine-grained: They apply at the table and column levels rather than at the database level. A privilege granted at the table level applies to the table and to all its columns. A privilege granted at the column level applies only to a specific column.

Administrative privileges (such as RELOAD or SHUTDOWN) are specified only in the user table. This is because administrative operations are operations on the server itself and are not database-specific, so there is no reason to list these privileges in the other grant tables. In fact, to determine whether you can perform an administrative operation, the server need consult only the user table.

The FILE privilege also is specified only in the user table. It is not an administrative privilege as such, but your ability to read or write files on the server host is independent of the database you are accessing.

The mysqld server reads the contents of the grant tables into memory when it starts. You can tell it to re-read the tables by issuing a FLUSH PRIVILEGES statement or executing a mysqladmin flush-privileges or mysqladmin reload command. Changes to the grant tables take effect as indicated in Section 4.4.7, "When Privilege Changes Take Effect."

When you modify the contents of the grant tables, it is a good idea to make sure that your changes set up privileges the way you want. One way to check the privileges for a given account is to use the SHOW GRANTS statement. For example, to determine the privileges that are granted to an account with Host and User values of pc84.example.com and bob, issue this statement:

mysql> SHOW GRANTS FOR 'bob'@'pc84.example.com';

A useful diagnostic tool is the mysqlaccess script, which Yves Carlier has provided for the MySQL distribution. Invoke mysqlaccess with the --help option to find out how it works. Note that mysqlaccess checks access using only the user, db, and host tables. It does not check table or column privileges specified in the tables_priv or columns_priv tables.

For additional help in diagnosing privilege-related problems, see Section 4.4.8, "Causes of Access denied Errors." For general advice on security issues, see Section 4.3, "General Security Issues."



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: