Privilege Management

MySQL stores information about who has which privileges in special tables in the system database mysql. It then consults these tables when determining whether to allow certain operations. Because MySQL privilege information is stored as regular database data, you can manage privileges using the SQL you already know. We will cover the structure of these tables later in the chapter. First, however, we will go into the preferred method of managing privileges: ANSI SQL’s GRANT and REVOKE statements.

GRANT and REVOKE

Privilege management includes granting privileges to users and taking them away. ANSI SQL provides two database-independent statements that support these operations. By learning these two statements, you can manage access privileges for MySQL and any other database without knowing the details of how the database actually stores privilege information.

The GRANT statement is the preferred method for adding new users and granting them access to MySQL objects. It has the following syntax: 

GRANT privilege  [(column)] [, privilege [(columns)], ...]
ON table1, table2, ..., tablen
TO user [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password'], ...]
[WITH GRANT OPTION]

The simplest form of this statement looks like the following SQL statement: 

GRANT SELECT ON Book to andy;

This statement gives the user andy the ability to read data from the table Book. The GRANT statement has three basic components: the privilege, the object, and the user.

The privilege is a keyword that describes the operation the user is being granted. MySQL supports the following privileges:

ALTER

This provides the ability to alter the structure of an existing table. In particular, it enables the user with this privilege to execute SQL’s ALTER statement as long as that statement does not affect indexes.

CREATE

This allows users to create new tables and databases. In particular, it enables a user to execute the CREATE statement as long as the user is not creating new indexes.

DELETE

This enables the user to delete rows from a table. It does not grant the ability to drop tables or databases.

DROP

This provides the ability to drop tables and databases, but not indexes. Though this privilege does not specifically enable a user to delete data from a table, a user with this privilege can simply drop the entire table and thus delete all table data.

FILE

This enables a user to access files on the server machine with the same privileges as the MySQL server process. This privilege is useful for executing the LOAD DATA INFILE and SELECT INTO OUTFILE statements that read from and write to server-side files. This privilege, however, can be abused as a backdoor around operating system security and thus should be granted sparingly.

INDEX

This enables a user to manage table indexes. With it, a user can create, alter, and drop indexes.

INSERT

This enables a user to insert new rows into tables. In particular, it grants a user the ability to execute the INSERT statement.

PROCESS

Like FILE, this is a privilege that a user may use to circumvent operating system security. It specifically grants a user access to the MySQL process threads, including the ability to kill them. In particular, it provides the ability to execute the SHOW PROCESSLIST and KILL SQL statements.

REFERENCES

This does nothing under MySQL. It does, however, provide compatibility with ANSI SQL scripts written for servers such as Oracle that support foreign keys.

RELOAD

This enables a user to force MySQL to reload data it usually keeps cached, such as user permissions. In particular, it enables a user to execute the FLUSH statement.

SELECT

This allows a user to read data from a table using the SELECT statement.

SHUTDOWN

This enables a user to shut down the MySQL server. A user with PROCESS privileges but not SHUTDOWN privileges can accomplish the same thing, however, by killing the MySQL server thread.

UPDATE

This enables a user to modify existing data in a table using the UPDATE statement. It does not grant the ability to delete data or add new data.

USAGE

This enables a user to simply connect to the MySQL server. A user with only USAGE privileges can do nothing except establish a connection.

There is also a special privilege: ALL PRIVILEGES . ALL PRIVILEGES does not, however, grant all privileges. Though it does grant full control over all the databases and tables running on the server, it does not automatically grant the more dangerous FILE, PROCESS, RELOAD, and SHUTDOWN privileges. You must grant those privileges explicitly. You can use the synonym ALL in place of ALL PRIVILEGES.

A DBA may further grant the ability to a user to extend his privileges to other users. The optional WITH GRANT OPTION empowers the targeted user with this ability. The ability to grant privileges should be given only to trusted users, generally other DBAs. Their ability to grant is not limited to the privileges in the GRANT statement, but to any privileges they are granted at any time.

The object of GRANT is the database object --column, table, database, etc.—to which the privilege applies. Certain privileges, however, make sense only when applied to particular objects. For example, it makes no sense to grant SHUTDOWN privileges on a column. Table 6-2 identifies the objects to which different privileges may apply.

Table 6-2. Privileges and the objects to which they apply

Privilege

Column

Table

Database

Server

ALTER

X

X

CREATE

X

X

DELETE

X

X

DROP

X

X

FILE

X

INDEX

X

INSERT

X

X

PROCESS

X

RELOAD

X

SELECT

X

X

SHUTDOWN

X

UPDATE

X

X

For table, database, and server privileges, you specify the object in the ON clause of the GRANT statement. MySQL provides several different ways of naming tables in the ON clause:

table name

The simplest way to specify a table is to name it. You may grant access to any table outside the current database—the database to which you are connected—by fully qualifying the table name using the database . table_name notation.

*

This syntax names every table in the current database. You can also reference all tables in a database other than the current database using database .*. You will find yourself using this syntax the most often.

*.*

This syntax references every table in every database. You should generally reserve the use of this syntax when granting server-wide privileges such as SHUTDOWN.

The ON clause does not address privileges targeting columns. When applying a privilege to a column, you still specify the table in the ON clause, but you specify the column right after the name of the privilege: 

GRANT SELECT ( title, authorID ) ON Library.Book TO andy;

In this case, we have granted andy the ability to execute queries limited to the title and authorID from the Book table in the database Library.

The final component of the GRANT statement specifies who is being granted the privilege. The simplest form identifies users without indicating where they are connecting from: 

GRANT ALL ON Library.* TO andy, tim, randy, george;

In reality, however, identifying users to MySQL is a little more complex than specifying usernames. MySQL identifies a user by both name and client host. In other words, when I connect from www.imaginary.com, I am a different user in the eyes of MySQL from when I connect from www.mysql.com. So far, we have not specified a location in any of our examples. When that happens, MySQL assumes you mean any andy, tim, randy, or george—without respect to the client hostname.

Specify the a specific user with a username and a hostname separated by the @ symbol: 

[email protected]

A valid MySQL username is any 16 characters or less. These characters do not need to be ASCII characters, but we recommend ASCII characters since some clients are not able to handle alternative character sets. If a username does consist of characters other than ASCII alphanumeric characters, you must enclose it in either single or double quotes. Usernames are case insensitive. In other words, MySQL treats fred, Fred, and fReD as the same user.

A location is a DNS host name (www.imaginary.com) or an IP address (192.168.2.5). You may also use the SQL wildcards '%' and '_' to specify a range of addresses.[3] "%.imaginary.com", for example, matches all hosts in the imaginary.com domain while "192.168.2.%" matches all hosts in the 192.168.2.0/24 subnet.

You can alternatively specify that same subnet using a netmask: "192.168.2.0/255.255.255.0". Failure to specify a location is the same as specifying user @"%". If you specify a host name, you should make sure it resolves via your host’s configuration file or DNS.

Any user that does not exist when you issue the GRANT statement will be created for you. The user will have a blank password unless you specify one through the IDENTIFIED BY clause. IDENTIFIED BY names the password that identifies the user as authentic. The password may be up to 16 characters of any kind. MySQL will encrypt this password before storing it in the database. If you specify an IDENTIFIED BY clause for an existing user, you will change their password.

Tip

As a general rule, you should always provide passwords for new users. Blank passwords are huge security holes for the database.

In addition to the default DBA user root, a clean MySQL installation defines default privileges for any user on the localhost. These default privileges are limited to USAGE. In other words, any person with shell access to the machine on which the server is running can connect to the server, but they cannot access any database or data. Remote users cannot even connect unless granted a user ID in MySQL.

Tip

The default root user has complete control over every aspect of MySQL. Because a clean MySQL installation provides a root user with no password set, the very first thing you should do once you have installed MySQL is change the root password! As an added layer of security, you can go into the MySQL security tables described later in this chapter and change the name of the DBA user from root to something else.

The opposite of GRANT is REVOKE . It has a structure that is virtually identical to GRANT : 

REVOKE privilege [(columns)] [, privilege [(columns)] ...]
ON table1, table2, ..., tablen
FROM user1, user2, ..., usern

Only a few elements of the REVOKE statement differ from the GRANT statement:

  • Revoking the GRANT option is more straightforward—you simply specify GRANT as the privilege name.

  • The ALL privilege actually means all privileges possessed by the specified users. All the user will have left is USAGE privileges.

  • REVOKE does not have an IDENTIFIED BY clause.

The security tables

The GRANT and REVOKE statements provide complete access to the MySQL security infrastructure without you having to know the details about how that infrastructure works. At times, however, you may find it necessary to fine-tune security settings by going directly to the security tables that store user privileges.

MySQL uses five tables to store privilege information:

user

This table is the main privilege table that contains the user ID, location, and global privileges. In addition, MySQL stores all metadata rights (including the ability to start and stop the server and grant rights to others) in this table.

db

This table houses privileges relevant to individual databases.

host

This table enables you to manage privileges based on location.

tables_priv

This table contains the table-level privileges for the tables in MySQL’s databases.

columns_priv

This table manages the column-level privileges for specific columns.

MySQL consults these tables for two distinct events: the initial connection and the execution of any statement. During the initial connection, MySQL consults the user table described in Table 6-3.

Table 6-3. The schema for the user table

Field name

Data type

Default

Host (PK)

VARCHAR(60) BINARY

User (PK)

VARCHAR(16) BINARY

Password

VARCHAR(16) BINARY

Select_priv

ENUM('N','Y')

'N'

Insert_priv

ENUM('N','Y')

'N'

Update_priv

ENUM('N','Y')

'N'

Delete_priv

ENUM('N','Y')

'N'

Create_priv

ENUM('N','Y')

'N'

Drop_priv

ENUM('N','Y')

'N'

Reload_priv

ENUM('N','Y')

'N'

Shutdown_priv

ENUM('N','Y')

'N'

Process_priv

ENUM('N','Y')

'N'

File_priv

ENUM('N','Y')

'N'

Grant_priv

ENUM('N','Y')

'N'

References_priv

ENUM('N','Y')

'N'

Index_priv

ENUM('N','Y')

'N'

Alter_priv

ENUM('N','Y')

'N'

ssl_type

ENUM('NONE', 'ANY', 'X509', 'SPECIFIED')

'NONE'

ssl_cipher

BLOB

x509_issuer

BLOB

x509_subject

BLOB

The primary key of the user table is a joint key of the Host and User fields. In other words, MySQL uniquely identifies a user by the username used to connect to MySQL and the name of the host from which the connection comes. The user randy connecting from the local machine is different from the user randy connecting from www.mysql.com. The Host field may contain wildcards to indicate multiple hosts.

The user table is also the place where MySQL stores the passwords that authenticate users. MySQL expects the passwords in the Password column to be scrambled using the PASSWORD( ) function. When you create a user with the GRANT command, MySQL automatically scrambles the password you specify in the required fashion.[4]

The most basic way to add a user to MySQL is: 

INSERT INTO user ( User, Host, Password )
VALUES ( 'randy', 'www.mysql.com', PASSWORD('randyspass') );

This new user will not be able to do anything with MySQL since you have not provided the user with any privileges. The xxx _priv columns contain the privileges assigned to individual users. The values of these columns can be either 'Y' or 'N'.

The final four columns are new to MySQL 4.0. They exist to support SSL and X.509 certificates.

Whenever a client attempts to connect to a MySQL server, it sends MySQL a username and password. The client can grab the username and password by prompting a user or pulling the information from a configuration file. MySQL then consults the user table to determine whether the user can connect. The connecting user must specifically have matching User and Host values. Because both the User and Host tables may contain wildcards, it is possible that more than one row will match a connected user. For example, when andy connects to the server from www.mysql.com, the row with andy and "%" for User and Host as well as the row with "%" and "www.mysql.com" match his connection. MySQL, however, will use only one of those rows to determine the user’s access rights using the following algorithm:

  1. MySQL considers more-specific Host values before less-specific values. In other words, MySQL first considers values with no wildcards followed by mixed values and, finally, pure wildcards. MySQL views subnets to be less specific than individual hosts. MySQL considers the value "%" last.

  2. MySQL examines rows with the same Host value according to the specificity of their User values. MySQL considers blank User values last. A blank User value therefore defines the default access rights for a given host. MySQL ships with default access rights for the localhost.

Consider the following User/Host values from the user table:

  1. root/localhost

  2. andy/localhost

  3. [blank]/localhost

  4. andy/"%"

  5. tim/"%.imaginary.com"

  6. randy/"%"

Table 6-4 shows how MySQL matches different user connections to these values.

Table 6-4. Resolution of various User/Host combinations

User ID

Hostname

Row matched

Explanation

root

localhost

1

Both User and Host are specific matches.

andy

localhost

2

Both User and Host are specific matches.

george

localhost

3

george has no entry, so the default values for localhost are used.

andy

www.imaginary.com

4

No specific host matches with the user andy, but the unspecific "%" does have andy as a user.

randy

localhost

3

Both 3 and 6 match, but 3 has the more specific host. One of the most common mistakes with MySQL security is to think that this one matches 6 because of the specific user match.

root

www.imaginary.com

NONE

No Host value matches www.imaginary.com for the user root. The connection is denied.

When MySQL finds no match in the User table for a connection, it rejects the connection. When it does find a match, it checks the password provided by the connection against the value in the Password column. If there is a match, the connection is allowed. Otherwise, the connection is denied.

Once a client connection is allowed, MySQL performs security checks for every SQL statement executed by the query. These security checks require all the security tables.

During query execution, MySQL first consults the user table using the row matched when the user connected. This row contains the user’s global rights. In other words, if a user has a 'Y' value for a given privilege in this table, no further security checks are made—the operation is allowed. You should therefore be extremely cautious when setting privileges in the user table.

Should the user table not provide access to a specific resource, MySQL consults the database-level privileges in the db table with the schema described in Table 6-5.

Table 6-5. The schema for the db table

Field name

Data type

Default

Host (PK)

CHAR(60) BINARY

Db (PK)

CHAR(64) BINARY

User (PK)

CHAR(16) BINARY

Select_priv

ENUM('N','Y')

'N'

Insert_priv

ENUM('N','Y')

'N'

Update_priv

ENUM('N','Y')

'N'

Delete_priv

ENUM('N','Y')

'N'

Create_priv

ENUM('N','Y')

'N'

Drop_priv

ENUM('N','Y')

'N'

Grant_priv

ENUM('N','Y')

'N'

References_priv

ENUM('N','Y')

'N'

Index_priv

ENUM('N','Y')

'N'

Alter_priv

ENUM('N','Y')

'N'

The primary key of the db table includes not only the Host and User columns, but also the Db column. Both the Host and Db columns can contain wildcards. The privilege columns in this table have the same semantics as the privilege columns in the user table. It has, however, fewer privileges than the user table to reflect the fact that some privileges make no sense when applied to a database.

MySQL performs matches in this table using rules similar to those used for the user table. Specifically, MySQL looks for an exact match on User, Host, and Db. If it finds no exact match, it searches for a row with a User and Db match but a "%" for Host. If it finds such a row, it looks in the host table for a match. The host table has the schema described in Table 6-6.

Table 6-6. The schema for the host table

Field name

Data type

Default

Host (PK)

CHAR(60) BINARY

Db (PK)

CHAR(64) BINARY

Select_priv

ENUM('N','Y')

'N'

Insert_priv

ENUM('N','Y')

'N'

Update_priv

ENUM('N','Y')

'N'

Delete_priv

ENUM('N','Y')

'N'

Create_priv

ENUM('N','Y')

'N'

Drop_priv

ENUM('N','Y')

'N'

Grant_priv

ENUM('N','Y')

'N'

References_priv

ENUM('N','Y')

'N'

Index_priv

ENUM('N','Y')

'N'

Alter_priv

ENUM('N','Y')

'N'

The primary key of this table is the combination of the Host and Db columns—the username is not involved. The privileges again share the same semantics as the user and db tables

This table is basically an extension of the db table. It provides default database access privileges for specific hosts where requests originate. MySQL checks the host table for matching Host and Db values. When it finds such a row, it uses the privileges defined in that row combined with the values from the matching row in the db table to determine whether to allow the user’s statement to execute. The fact that the privilege must be positive in both tables is critical. This feature enables you to define a privilege granted to most people but selectively denied for specific hosts.

If MySQL still has not found positive permissions, and the object of the statement is the database, or the operation is a DROP, MySQL denies the operation. If the target of the query is a table or a column, then MySQL checks with the tables_priv table. It has the schema described in Table 6-7.

Table 6-7. The schema of the tables_priv table

Field name

Data type

Default

Host (PK)

CHAR(60) BINARY

Db (PK)

CHAR(64) BINARY

User (PK)

CHAR(16) BINARY

Table_name (PK)

CHAR(60) BINARY

Grantor

CHAR(77)

Timestamp

TIMESTAMP(14)

NULL

Table_priv

SET('Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter')

Column_priv

SET('Select', 'Insert', 'Update', 'References')

The primary key of this table is a combination of four columns: Host, Db, User, and Table_name. As with other privilege tables, the Host and Db columns may contain SQL wildcards. The Table_name column may contain the special character "*" to indicate all tables in the database.

The remaining fields are new to this table. The Grantor field stores the name of the user who granted the particular privilege, and the Timestamp field indicates when the privilege was granted or modified. The final two columns, Table_priv and Column_priv, contain set values. For the Table_priv column, the values indicate the privileges that apply to the table as a whole. The Column_priv values, on the other hand, indicate the privileges applicable to individual columns.

MySQL again uses the “most-specific first” rule to match the statement to a specific row. If it finds a match, and a positive value exists for the privilege in question, the operation is allowed. If the privilege is negative, MySQL checks with the Column_priv value. If that value is negative, the operation is denied. If it is positive, MySQL moves on to the columns_priv table with the schema described in Table 6-8.

Table 6-8. The schema of the columns_priv table

Field name

Data type

Default

Host (PK)

CHAR(60) BINARY

Db (PK)

CHAR(64) BINARY

User (PK)

CHAR(16) BINARY

Table_name (PK)

CHAR(64) BINARY

Column_name (PK)

CHAR(64) BINARY

Timestamp

TIMESTAMP(14)

NULL

Column_priv

SET('Select', 'Insert', 'Update', 'References')

The primary key of this table is a joint key containing the Host, Db, User, Table_name, and Column_name columns. The Host and Db columns may contain wildcards, and the Table_name field may contain the "*" character to indicate all tables.

When MySQL consults the columns_priv table, it checks against each of the columns accessed by the statement. This table must have a match for each column, and the permission must be positive for the privilege for each of the columns.


[3] You must use quotes when using wildcards or any other nonalphanumeric characters.

[4] A determined attacker can easily defeat this encryption mechanism if given read access to the user table. Access to this table should therefore be restricted to DBAs.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset