Oracle 5.0, Reference Manual

Page: 603 / 3232

Specifying Account Names
583
In the
user
,
db
, and
host
tables, each privilege is listed in a separate column that is declared as
ENUM('N','Y') DEFAULT 'N'
. In other words, each privilege can be disabled or enabled, with the
default being disabled.
In the
tables_priv
,
columns_priv
, and
procs_priv
tables, the privilege columns are declared as
SET
columns. Values in these columns can contain any combination of the privileges controlled by the
table. Only those privileges listed in the column value are enabled.
Table 6.7. Set-Type Privilege Column Values
Table Name Column Name Possible Set Elements
tables_priv Table_priv 'Select', 'Insert', 'Update',
'Delete', 'Create', 'Drop', 'Grant',
'References', 'Index', 'Alter',
'Create View', 'Show view'
tables_priv Column_priv 'Select', 'Insert', 'Update',
'References'
columns_priv Column_priv 'Select', 'Insert', 'Update',
'References'
procs_priv Proc_priv 'Execute', 'Alter Routine', 'Grant'
Administrative privileges (such as
RELOAD
[577] or
SHUTDOWN
[578] ) are specified only in the
user
table. Administrative operations are operations on the server itself and are not database-specific, so
there is no reason to list these privileges in the other grant tables. Consequently, to determine whether
you can perform an administrative operation, the server need consult only the
user
table.
The
FILE
[577] privilege also is specified only in the
user
table. It is not an administrative privilege
as such, but your ability to read or write files on the server host is independent of the database you are
accessing.
The
mysqld
server reads the contents of the grant tables into memory when it starts. You can tell it to
reload the tables by issuing a
FLUSH PRIVILEGES
statement or executing a
mysqladmin flush-
privileges
or
mysqladmin reload
command. Changes to the grant tables take effect as indicated
in Section 6.2.6, “When Privilege Changes Take Effect” .
When you modify an account's privileges, it is a good idea to verify that the changes set up privileges
the way you want. To check the privileges for a given account, use the
SHOW GRANTS
statement
(see Section 13.7.5.17, “
SHOW GRANTS
Syntax” ). For example, to determine the privileges that are
granted to an account with user name and host name values of
bob
and
pc84.example.com
, use
this statement:
SHOW GRANTS FOR 'bob'@'pc84.example.com';
6.2.3. Specifying Account Names
MySQL account names consist of a user name and a host name. This enables creation of accounts for
users with the same name who can connect from different hosts. This section describes how to write
account names, including special values and wildcard rules.
In SQL statements such as
CREATE USER
,
GRANT
, and
SET PASSWORD
, write account names using
the following rules:
• Syntax for account names is
'user_name'@'host_name'
.
• An account name consisting only of a user name is equivalent to
'user_name'@'%'
. For example,
'me'
is equivalent to
'me'@'%'
.
• The user name and host name need not be quoted if they are legal as unquoted identifiers. Quotes
are necessary to specify a
user_name
string containing special characters (such as “
-
”), or a