| only for RuBoard - do not distribute or recompile | |
Database security controls access to MySQL data via the MySQL database engine. It does not address access to that data through direct access to the database files; system security is responsible for protecting the files.
To MySQL, a user is any connection authenticated to the database engine. In a development environment, MySQL users will likely correspond to developers. In other words, each developer has a personal user ID and password for MySQL authentication during development. This MySQL user has no inherent relationship to the host system user. In other words, MySQL does not use your Unix ID; it uses its own internal user list.[1]
[1] Some MySQL tools, such as the mysql command-line tool, do use your operating system user ID by default if you fail to specify one. If your Unix user ID is not the same as your user ID in MySQL, however, MySQL will deny you access.
In a production environment, a user is likely to be an application. The DBA creates a user ID and password to support the application, and database security controls how that application is allowed to interact with MySQL. The application can then pass on its rights to individual users of the application by acting on their behalves to access MySQL.
For example, you might have a database storing the family CD library with a web interface. A single MySQL user—the application—has access to the read, add, delete, and update records in that database. The application may have its own internal security structure for differentiating family members who can make changes and friends who can just read. It cannot, however, give users powers it does not have, such as creating new tables. MySQL knows nothing about these application-specific users—they exist in the realm of application security.
As the DBA, your job is to create new users and assign them access rights. MySQL has an access hierarchy that controls privileges at certain levels of MySQL operations. You can control the ability to connect to a server, to use a particular database, to make changes to the structure of that database, or to modify the data inside the database.
The security responsibilities of the DBA basically amount to managing MySQL users and their privileges. Just as operating systems offer an administrative or "root" user for managing other users, MySQL offers a special user called root who can create other MySQL user accounts and grant them privileges. When you use administrative commands such as mysql or mysqladmin to manage users, make yourself the MySQL root user through the -u option:
$ mysql -u root
There is no relationship between the Unix system's root and MySQL's root. Anyone can issue the preceding command—and for that reason, it's critical for you to assign a password to the MySQL root user as soon as you install MySQL. The following command assigns the string P?:2002:My? as the root password:
$ mysqladmin -u root password 'P?:2002:My?'
After you execute this command, anyone who attempts to administer MySQL as the root user has to use a -p option and enter the password:
$ mysql -u root -p Enter password: P?:2002:My?
Later in the chapter, we show you how to create other MySQL users through SQL GRANT commands. Always assign passwords to these users when you issue the GRANT commands.
While the bulk of your time will revolve around managing privileges, you will still need to put some thought into how you manage your users. In general, you will need to support three kinds of users:
Individuals
Applications
Roles
Individual users are people who use MySQL to develop against it and support it. You should never have MySQL manage the users of the applications run against it; this is the job of the applications themselves. If, for example, you have a MySQL installation supporting several applications in which MySQL must manage the users, you would have a user namespace nightmare; each username would have to be unique across all applications. Furthermore, security issues with one application could potentially compromise the entire MySQL installation. We cover application security in more detail later in the chapter.
Application users are MySQL users that represent specific applications to MySQL. If, for example, you had two web sites using the same MySQL installation to store their data, you might create two separate users to represent those applications. You can use these two separate user IDs to protect each application from the other.
Many database engines support roles . A person who sometimes does DBA work, sometimes does development work, and sometimes migrates applications between environments could have a single user ID with three different roles. When that person connects to the database, she logs in using that single user ID and password, and additionally, specifies a role. As long as that user is connected under the specified role, she can act using only the permissions assigned to that role.
MySQL, however, has no concept of role. Each user has individualized permissions. You therefore have three choices for managing users who play multiple roles on a MySQL server:
Give them single user IDs and assign those user IDs the permissions associated with all of their roles.
Create role-based users, and have different people share the same user ID for a given role.
Create multiple user IDs for each role played by each user; e.g., Andy might have andy-arch for work as an architect and andy-dev for work as a developer.
The advantage of the first approach is that it is the only single sign on solution. In other words, no matter what work you do in a MySQL environment, you need to remember only a single user ID/password combination. Unfortunately, it leaves open too much risk of wreaking havoc accidentally. An innocent DELETE FROM MYTABLE executed in one context could, for example, end up deleting data from a table in a different context simply because you got your contexts confused.
The second approach stinks on all levels, but it is to some degree the default for MySQL. Your clean install comes with a default DBA user called "root." If you simply share this one user with all DBAs on a system, you run several security risks. First, if you want to take DBA access away from one of the DBAs, you need to change the password and communicate that change to all the other DBAs. Second, you have very few ways of tracking which DBA performed which action. Finally, you still have to remember different passwords for each role. The one advantage of this approach is you need to connect as a DBA only when doing DBA work. When doing development work, you can connect as a developer and not risk accidentally executing a statement in a developer context that should be run only in a DBA context.
The third approach marries many of the advantages of the first and second approaches. Each user role you create for an individual can share the same password. The user specifies the same username plus his role whenever he connects to the database. Users cannot accidentally perform actions outside the contexts in which they connected. You can then perform operations on all users with a specific role or all roles belonging to a specific user using wildcards:
UPDATE db SET Select_priv = 'Y' WHERE Db = 'Library' AND User = 'dvl\_%'
The approach you choose really depends on the particulars of your environment. If you are the only one managing your MySQL server, it may work just fine if you have the default "root" user plus a "developer" user. In a complex environment with many different users and normal turnover, the third approach probably makes the most sense. Table 6-1 lists roles common to MySQL installations. We will cover what the different privileges mean later in the chapter.
|
Role |
Environments |
Privileges |
Description |
|---|---|---|---|
|
Development |
DELETE, INSERT, SELECT, UPDATE |
Developers write the application code. Their privileges should apply only to the applications they are developing. |
|
|
Development |
ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE |
Architects design the database structure for specific applications. Their privileges should apply only to the applications they are designing. |
|
|
Testing |
DELETE, INSERT, SELECT, UPDATE |
QAs are responsible for testing whether an application behaves properly. They need the same privileges as the application user that will support the application in the production environment. |
|
|
Development, Testing, Staging |
SELECT |
Emigrators pull the database schema from one environment so that it may be migrated to another environment. |
|
|
Testing, Staging, Production |
ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE |
Immigrators update the structure of the database to reflect changes made during development. |
|
|
All |
All |
DBAs manage the running of the MySQL installation. Technically, DBAs should not modify the data in application databases; you could therefore get by without granting them the rights to modify the data in application databases. It would, however, be very difficult to explain this to a DBA. |
Table 6-1 talks about different environments. A proper software development process includes the separation of software into separate environments. The number of environments generally depends on the number of developers, the number of applications, and the uptime requirements of the applications. Though cost often causes project teams using Oracle or Sybase to compromise on this issue by forcing teams to reduce the number of environments to save on licensing costs, an advantage of MySQL is that cost should never be a factor.
To ensure a smooth path from development through deployment, a project team needs the following environments and processes to support the migration of code through these environments:
Development is where all coding happens. MySQL is actually well suited to giving developers their own private development environments on their desktops. By having development separate from all other environments, developers never accidentally cause applications to become unusable or create phantom trouble tickets for the QA team.[2]
[2] A phantom trouble ticket is a bug reported by QA that does not really exist. It signifies that, in executing the test, the QA analyst encountered an error simply because a developer was in the middle of changing something.
Once the development team reaches the point where they are ready to test an application, they move the entire application state to a separate testing environment.
Staging exists solely for validating that the full application is correctly configured from end to end with the proper data. It ensures that any configuration issues are discovered prior to moving the application into production.
Only code and data serving end users run in the production environment. No developers, testers, or architects should have access to this environment. No changes should ever be made in this environment.
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.
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:
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.
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.
This enables the user to delete rows from a table. It does not grant the ability to drop tables or databases.
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.
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.
This enables a user to manage table indexes. With it, a user can create, alter, and drop indexes.
This enables a user to insert new rows into tables. In particular, it grants a user the ability to execute the INSERT statement.
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.
This does nothing under MySQL. It does, however, provide compatibility with ANSI SQL scripts written for servers such as Oracle that support foreign keys.
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.
This allows a user to read data from a table using the SELECT statement.
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.
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.
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.
|
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:
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.
[3] You must use quotes when using wildcards or any other nonalphanumeric characters.
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.
|
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.
|
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.
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:
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.
This table houses privileges relevant to individual databases.
This table enables you to manage privileges based on location.
This table contains the table-level privileges for the tables in MySQL's databases.
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.
|
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]
[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.
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:
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.
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:
root/localhost
andy/localhost
[blank]/localhost
andy/"%"
tim/"%.imaginary.com"
randy/"%"
Table 6-4 shows how MySQL matches different user connections to these values.
|
User ID |
Hostname |
Row matched |
Explanation |
|---|---|---|---|
|
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.
|
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.
|
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.
|
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.
|
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.
If you garble your GRANT commands or forget passwords and find that you don't have access to the critical mysql table—even as the root user—don't panic. Become the superuser on the operating system (we're talking now about the Unix root, not the MySQL root) and kill the MySQL process. On a RedHat Linux or other SVR4-type systems, you might be able to end MySQL through the command:
/etc/rc.d/init.d/mysql stop
Otherwise, find all MySQL processes and kill them explicitly as root:
$ ps ax | grep mysql 2498 pts/1 S 0:00 /bin/sh bin/safe_mysqld 2514 pts/1 S 0:00 /usr/local/src/mysql/bin/mysqld --defaults-extra-file 2516 pts/1 S 0:00 /usr/local/src/mysql/bin/mysqld --defaults-extra-file 2517 pts/1 S 0:00 /usr/local/src/mysql/bin/mysqld --defaults-extra-file $ kill 2498 2514 2516 2517
Now start up MySQL again, bypassing the grant tables:
$ safe_mysqld --skip-grant-tables
Make sure you can now get access to the mysql database:
$ mysql mysql mysql> quit
Assign a password once again to the MySQL root user:
$ mysqladmin -u root password 'new_password'
Terminate the MySQL server and restart it in the usual way. Make any necessary changes to the privileges through GRANT commands, running mysql as the root user.
| only for RuBoard - do not distribute or recompile | |