Database Reference
In-Depth Information
Here,
account
names the account to be granted the privileges,
privileges
indicates
what they are, and
scope
indicates the privilege scope, or level at which they apply. The
privileges
value can be
ALL
(or
ALL
PRIVILEGES
) to specify all privileges available at
the given level, or a comma-separated list of one or more privilege names such as
SELECT
or
CREATE
. (For a full discussion of available privileges and
GRANT
syntax not shown here,
see the
MySQL Reference Manual.
)
The following examples illustrate the syntax for granting privileges at each level.
• Granting privileges globally enables the account to perform administrative opera‐
tions or operations on any database:
GRANT
FILE
ON
*
.
*
TO
'user1'
@
'localhost'
;
GRANT
CREATE
TEMPORARY
TABLES
,
LOCK
TABLES
ON
*
.
*
TO
'user1'
@
'localhost'
;
• Granting privileges at the database level enables the account to perform operations
on objects within the named database:
GRANT
ALL
ON
cookbook
.
*
TO
'user1'
@
'localhost'
;
• Granting privileges at the table level enables the account to perform operations on
the named table:
GRANT
SELECT
ON
mysql
.
user
TO
'user1'
@
'localhost'
;
• Granting privileges at the column level enables the account to perform operations
on the named table column:
GRANT
SELECT
(
User
,
Host
),
UPDATE
(
password_expired
)
ON
mysql
.
user
TO
'user1'
@
'localhost'
;
• Granting privileges at the procedure level enables the account to perform operations
on the named stored procedure:
GRANT
EXECUTE
ON
PROCEDURE
cookbook
.
exec_stmt
TO
'user1'
@
'localhost'
;
Use
FUNCTION
rather than
PROCEDURE
if the routine is a stored function.
To verify the privilege assignments, use
SHOW
GRANTS
:
mysql>
SHOW GRANTS FOR 'user1'@'localhost';
+--------------------------------------------------------------------------+
| Grants for user1@localhost |
+--------------------------------------------------------------------------+
| GRANT FILE, CREATE TEMPORARY TABLES, LOCK TABLES |
| ON *.* TO 'user1'@'localhost' |
| GRANT ALL PRIVILEGES ON `cookbook`.* TO 'user1'@'localhost' |
| GRANT SELECT, SELECT (User, Host), UPDATE (password_expired) |
| ON `mysql`.`user` TO 'user1'@'localhost' |
| GRANT EXECUTE ON PROCEDURE `cookbook`.`exec_stmt` TO 'user1'@'localhost' |
+--------------------------------------------------------------------------+
To see your own privileges, omit the
FOR
clause.
