|
|
|
|
| |
Managing MySQL User Accounts
The MySQL administrator should know how to set up MySQL user accounts by specifying which users can connect to the server, where they can connect from, and what they can do while connected. This information is stored in the grant tables in the mysql database and is managed primarily by means of two statements:
GRANT creates MySQL accounts and specifies their privileges.
REVOKE removes privileges from existing MySQL accounts.
These statements were introduced in MySQL 3.22.11 to make it easier to manage user accounts. Prior to 3.22.11, it was necessary to manipulate the contents of the grant tables directly by issuing SQL statements such as INSERT and UPDATE. GRANT and REVOKE act as a front end to the grant tables. They are more convenient to work with conceptually because you describe the permissions you want to allow, and the server maps your requests onto the proper grant table modifications automatically. Nevertheless, although it's much easier to use GRANT and REVOKE than to modify the grant tables directly, I advise that you supplement the material in this chapter by reading Chapter 12. That chapter discusses the grant tables in more detail, to help you understand how they work beyond the level of the GRANT and REVOKE statements. It also contains a section on setting up accounts without using GRANT, which is how you'll need to set up privileges if your server is older than 3.22.11.
You may also want to consider using the mysqlaccess and mysql_setpermission scripts, which are part of the MySQL distribution. These are Perl scripts that provide an alternative to the GRANT statement for setting up user accounts. mysql_setpermission requires that you have DBI support installed.
The GRANT and REVOKE statements affect four tables:
There is a fifth grant table named host, but it is not affected by GRANT or REVOKE and is not discussed here. For information on how it works, see Chapter 12.
When you issue a GRANT statement for an account, an entry is created for that account in the user table. If the statement specifies any global privileges (administrative privileges or privileges that apply to all databases), those are recorded in the user table, too. If you specify privileges that are specific to a given database, table, or table column, they are recorded in the db, tables_priv, and columns_priv tables.
The rest of this section describes how to set up MySQL user accounts and grant privileges, how to revoke privileges and remove users from the grant tables entirely, and how to change passwords or reset lost passwords.
Creating New Users and Granting Privileges
The syntax for the GRANT statement looks like this:
GRANT privileges (columns)
ON what
TO account IDENTIFIED BY 'password'
REQUIRE encryption requirements
WITH grant or resource management options;
Several of these clauses are optional and need not be specified at all. In general, you'll most commonly fill in the following parts:
privileges
The privileges to assign to the account. For example, the SELECT privilege allows a user to issue SELECT statements and the SHUTDOWN privilege allows the user to shut down the server.
columns
The columns the privileges apply to. This is optional, and you use it only to set up column-specific privileges. If you want to list more than one column, separate their names by commas.
what
The level at which the privileges apply. The most powerful level is the global level for which any given privilege applies to all databases and all tables. Global privileges can be thought of as superuser privileges. Privileges also can be made database-specific, table-specific, or (if you specify a columns clause) column-specific.
|
|
|
|
|
|
| Link Partners: Asia florist, Flowers to India, Hong kong flowers, Site submit, Cheap web hosting, China florist, Japan florist |
|
