Oracle 5.0, Reference Manual

Page: 1175 / 3232

Account Management Statements
1155
MySQL stores column privileges in the
mysql.columns_priv
table.
Stored Routine Privileges
The
ALTER ROUTINE
[576] ,
CREATE ROUTINE
[576] ,
EXECUTE
[577] , and
GRANT OPTION
[577]
privileges apply to stored routines (procedures and functions). They can be granted at the global and
database levels. Except for
CREATE ROUTINE
[576] , these privileges can be granted at the routine
level for individual routines.
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
The permissible
priv_type
values at the routine level are
ALTER ROUTINE
[576] ,
EXECUTE
[577] ,
and
GRANT OPTION
[577] .
CREATE ROUTINE
[576] is not a routine-level privilege because you must
have this privilege to create a routine in the first place.
MySQL stores routine-level privileges in the
mysql.procs_priv
table.
For the global, database, table, and routine levels,
GRANT ALL
assigns only the privileges that exist at
the level you are granting. For example,
GRANT ALL ON db_name.*
is a database-level statement,
so it does not grant any global-only privileges such as
FILE
[577] .
The
object_type
clause was added in MySQL 5.0.6. If present, it should be specified as
TABLE
,
FUNCTION
, or
PROCEDURE
when the following object is a table, a stored function, or a stored
procedure.
The privileges for a database, table, column, or routine are formed additively as the logical
OR
[881] of
the privileges at each of the privilege levels. For example, if a user has a global
SELECT
[578] privilege,
the privilege cannot be denied by an absence of the privilege at the database, table, or column level.
Details of the privilege-checking procedure are presented in
Section 6.2.5, “Access Control, Stage 2:
Request Verification” .
MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the
privileges to be granted must include the
CREATE
[576] privilege. This behavior is by design, and is
intended to enable the database administrator to prepare user accounts and privileges for databases or
tables that are to be created at a later time.
Important
MySQL does not automatically revoke any privileges when you drop a database
or table. However, if you drop a routine, any routine-level privileges granted for
that routine are revoked.
Account Names and Passwords
The
user
value indicates the MySQL account to which the
GRANT
statement applies. To accommodate
granting rights to users from arbitrary hosts, MySQL supports specifying the
user
value in the form
user_name@host_name
. If a
user_name
or
host_name
value is legal as an unquoted identifier, you
need not quote it. However, quotation marks are necessary to specify a
user_name
string containing
special characters (such as “
-
”), or a
host_name
string containing special characters or wildcard
characters (such as “
%
”); for example,
'test-user'@'%.com'
. Quote the user name and host name
separately.
You can specify wildcards in the host name. For example,
user_name@'%.example.com'
applies to
user_name
for any host in the
example.com
domain, and
user_name@'192.168.1.%'
applies to
user_name
for any host in the
192.168.1
class C subnet.
The simple form
user_name
is a synonym for
user_name@'%'
.
MySQL does not support wildcards in user names. To refer to an anonymous user, specify an account
with an empty user name with the
GRANT
statement: