GNU Info
(mysql.info)GRANT
`GRANT' and `REVOKE' Syntax
---------------------------
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
`GRANT' is implemented in MySQL Version 3.22.11 or later. For earlier
MySQL versions, the `GRANT' statement does nothing.
The `GRANT' and `REVOKE' commands allow system administrators to create
users and grant and revoke rights to MySQL users at four privilege
levels:
*Global level*
Global privileges apply to all databases on a given server. These
privileges are stored in the `mysql.user' table.
*Database level*
Database privileges apply to all tables in a given database. These
privileges are stored in the `mysql.db' and `mysql.host' tables.
*Table level*
Table privileges apply to all columns in a given table. These
privileges are stored in the `mysql.tables_priv' table.
*Column level*
Column privileges apply to single columns in a given table. These
privileges are stored in the `mysql.columns_priv' table.
If you give a grant for a users that doesn't exists, that user is
created. For examples of how `GRANT' works, see Note: Adding users.
For the `GRANT' and `REVOKE' statements, `priv_type' may be specified
as any of the following:
ALL PRIVILEGES FILE RELOAD
ALTER INDEX SELECT
CREATE INSERT SHUTDOWN
DELETE PROCESS UPDATE
DROP REFERENCES USAGE
`ALL' is a synonym for `ALL PRIVILEGES'. `REFERENCES' is not yet
implemented. `USAGE' is currently a synonym for "no privileges." It
can be used when you want to create a user that has no privileges.
To revoke the *grant* privilege from a user, use a `priv_type' value of
`GRANT OPTION':
REVOKE GRANT OPTION ON ... FROM ...;
The only `priv_type' values you can specify for a table are `SELECT',
`INSERT', `UPDATE', `DELETE', `CREATE', `DROP', `GRANT', `INDEX', and
`ALTER'.
The only `priv_type' values you can specify for a column (that is, when
you use a `column_list' clause) are `SELECT', `INSERT', and `UPDATE'.
You can set global privileges by using `ON *.*' syntax. You can set
database privileges by using `ON db_name.*' syntax. If you specify `ON
*' and you have a current database, you will set the privileges for
that database. (*WARNING:* If you specify `ON *' and you _don't_ have
a current database, you will affect the global privileges!)
In order to accommodate granting rights to users from arbitrary hosts,
MySQL supports specifying the `user_name' value in the form
`user@host'. If you want to specify a `user' string containing special
characters (such as `-'), or a `host' string containing special
characters or wild-card characters (such as `%'), you can quote the
user or host name (for example, `'test-user'@'test-hostname'').
You can specify wild cards in the hostname. For example,
`user@"%.loc.gov"' applies to `user' for any host in the `loc.gov'
domain, and `user@"144.155.166.%"' applies to `user' for any host in
the `144.155.166' class C subnet.
The simple form `user' is a synonym for `user@"%"'. *NOTE:* If you
allow anonymous users to connect to the MySQL server (which is the
default), you should also add all local users as `user@localhost'
because otherwise the anonymous user entry for the local host in the
`mysql.user' table will be used when the user tries to log into the
MySQL server from the local machine! Anonymous users are defined by
inserting entries with `User=''' into the `mysql.user' table. You can
verify if this applies to you by executing this query:
mysql> SELECT Host,User FROM mysql.user WHERE User='';
For the moment, `GRANT' only supports host, table, database, and column
names up to 60 characters long. A user name can be up to 16 characters.
The privileges for a table or column are formed from the logical OR of
the privileges at each of the four privilege levels. For example, if
the `mysql.user' table specifies that a user has a global *select*
privilege, this can't be denied by an entry at the database, table, or
column level.
The privileges for a column can be calculated as follows:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
In most cases, you grant rights to a user at only one of the privilege
levels, so life isn't normally as complicated as above. The details of
the privilege-checking procedure are presented in Note: Privilege
system.
If you grant privileges for a user/hostname combination that does not
exist in the `mysql.user' table, an entry is added and remains there
until deleted with a `DELETE' command. In other words, `GRANT' may
create `user' table entries, but `REVOKE' will not remove them; you
must do that explicitly using `DELETE'.
In MySQL Version 3.22.12 or later, if a new user is created or if you
have global grant privileges, the user's password will be set to the
password specified by the `IDENTIFIED BY' clause, if one is given. If
the user already had a password, it is replaced by the new one.
*WARNING:* If you create a new user but do not specify an `IDENTIFIED
BY' clause, the user has no password. This is insecure.
Passwords can also be set with the `SET PASSWORD' command. Note: `SET
OPTION'.
If you grant privileges for a database, an entry in the `mysql.db'
table is created if needed. When all privileges for the database have
been removed with `REVOKE', this entry is deleted.
If a user doesn't have any privileges on a table, the table is not
displayed when the user requests a list of tables (for example, with a
`SHOW TABLES' statement).
The `WITH GRANT OPTION' clause gives the user the ability to give to
other users any privileges the user has at the specified privilege
level. You should be careful to whom you give the *grant* privilege,
as two users with different privileges may be able to join privileges!
You cannot grant another user a privilege you don't have yourself; the
*grant* privilege allows you to give away only those privileges you
possess.
Be aware that when you grant a user the *grant* privilege at a
particular privilege level, any privileges the user already possesses
(or is given in the future!) at that level are also grantable by that
user. Suppose you grant a user the *insert* privilege on a database.
If you then grant the *select* privilege on the database and specify
`WITH GRANT OPTION', the user can give away not only the *select*
privilege, but also *insert*. If you then grant the *update* privilege
to the user on the database, the user can give away the *insert*,
*select* and *update*.
You should not grant *alter* privileges to a normal user. If you do
that, the user can try to subvert the privilege system by renaming
tables!
Note that if you are using table or column privileges for even one
user, the server examines table and column privileges for all users and
this will slow down MySQL a bit.
When `mysqld' starts, all privileges are read into memory. Database,
table, and column privileges take effect at once, and user-level
privileges take effect the next time the user connects. Modifications
to the grant tables that you perform using `GRANT' or `REVOKE' are
noticed by the server immediately. If you modify the grant tables
manually (using `INSERT', `UPDATE', etc.), you should execute a `FLUSH
PRIVILEGES' statement or run `mysqladmin flush-privileges' to tell the
server to reload the grant tables. Note: Privilege changes.
The biggest differences between the ANSI SQL and MySQL versions of
`GRANT' are:
* In MySQL privileges are given for an username + hostname
combination and not only for an username.
* ANSI SQL doesn't have global or database-level privileges, and
ANSI SQL doesn't support all privilege types that MySQL supports.
MySQL doesn't support the ANSI SQL `TRIGGER', `EXECUTE' or `UNDER'
privileges.
* ANSI SQL privileges are structured in a hierarchal manner. If you
remove an user, all privileges the user has granted are revoked. In
MySQL the granted privileges are not automatically revoked, but
you have to revoke these yourself if needed.
* If you in MySQL have the `INSERT' grant on only part of the
columns in a table, you can execute `INSERT' statements on the
table; The columns for which you don't have the `INSERT' privilege
will set to their default values. ANSI SQL requires you to have the
`INSERT' privilege on all columns.
* When you drop a table in ANSI SQL, all privileges for the table
are revoked. If you revoke a privilege in ANSI SQL, all
privileges that were granted based on this privilege are also
revoked. In MySQL, privileges can be dropped only with explicit
`REVOKE' commands or by manipulating the MySQL grant tables.
automatically generated by info2www version 1.2.2.9