6.2.2. Privilege System Grant Tables
Normally, you manipulate the contents of the grant tables in the mysql
database indirectly by using statements such as GRANT
and REVOKE
to set up accounts and control the privileges available to each one. SeeSection 13.7.1, “Account Management Statements”. The discussion here describes the underlying structure of the grant tables and how the server uses their contents when interacting with clients.
These mysql
database tables contain grant information:
user
: Contains user accounts, global privileges, and other non-privilege columns.db
: Contains database-level privileges.host
: Obsolete.tables_priv
: Contains table-level privileges.columns_priv
: Contains column-level privileges.procs_priv
: Contains stored procedure and function privileges.
Other tables in the mysql
database do not hold grant information and are discussed elsewhere:
event
: Contains information about Event Scheduler events: See Section 19.4, “Using the Event Scheduler”.func
: Contains information about user-defined functions: See Section 22.3, “Adding New Functions to MySQL”.help_
: These tables are used for server-side help: See Section 5.1.9, “Server-Side Help”.xxx
plugin
: Contains information about server plugins: See Section 5.1.8.1, “Installing and Uninstalling Plugins”, andSection 22.2, “The MySQL Plugin API”.proc
: Contains information about stored procedures and functions: See Section 19.2, “Using Stored Routines (Procedures and Functions)”.servers
: Used by theFEDERATED
storage engine: See Section 14.11.2.2, “Creating aFEDERATED
Table UsingCREATE SERVER
”.time_zone_
: These tables contain time zone information: See Section 10.6, “MySQL Server Time Zone Support”.xxx
Tables with
_log
in their name are used for logging: See Section 5.2, “MySQL Server Logs”.
Modifications to tables in the mysql
database normally are made by the server in response to statements such asCREATE USER
, GRANT
, or CREATE PROCEDURE
. Direct modification of these tables using statements such asINSERT
, UPDATE
, or DELETE
is not encouraged. The server is free to ignore rows that become malformed as a result of such modifications.
Each grant table contains scope columns and privilege columns:
Scope columns determine the scope of each row (entry) in the tables; that is, the context in which the row applies. For example, a
user
table row withHost
andUser
values of'thomas.loc.gov'
and'bob'
would be used for authenticating connections made to the server from the hostthomas.loc.gov
by a client that specifies a user name ofbob
. Similarly, adb
table row withHost
,User
, andDb
column values of'thomas.loc.gov'
,'bob'
and'reports'
would be used whenbob
connects from the hostthomas.loc.gov
to access thereports
database. Thetables_priv
andcolumns_priv
tables contain scope columns indicating tables or table/column combinations to which each row applies. Theprocs_priv
scope columns indicate the stored routine to which each row applies.Privilege columns indicate which privileges are granted by a table row; that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. Section 6.2.5, “Access Control, Stage 2: Request Verification”, describes the rules that are used to do this.
The server uses the grant tables in the following manner:
The
user
table scope columns determine whether to reject or permit incoming connections. For permitted connections, any privileges granted in theuser
table indicate the user's global privileges. Any privilege granted in this table applies to all databases on the server.NoteBecause any global privilege is considered a privilege for all databases, any global privilege enables a user to see all database names with
SHOW DATABASES
or by examining theSCHEMATA
table ofINFORMATION_SCHEMA
.The
db
table scope columns determine which users can access which databases from which hosts. The privilege columns determine which operations are permitted. A privilege granted at the database level applies to the database and to all objects in the database, such as tables and stored programs.The
host
table is used in conjunction with thedb
table when you want a givendb
table row to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave theHost
value empty in the user'sdb
table row, then populate thehost
table with a row for each of those hosts. This mechanism is described more detail in Section 6.2.5, “Access Control, Stage 2: Request Verification”.The
tables_priv
andcolumns_priv
tables are similar to thedb
table, but are more fine-grained: They apply at the table and column levels rather than at the database level. A privilege granted at the table level applies to the table and to all its columns. A privilege granted at the column level applies only to a specific column.The
procs_priv
table applies to stored routines. A privilege granted at the routine level applies only to a single routine.
The server uses the user
, db
, and host
tables in the mysql
database at both the first and second stages of access control (see Section 6.2, “The MySQL Access Privilege System”). The columns in the user
and db
tables are shown here. The host
table is similar to the db
table but has a specialized use as described in Section 6.2.5, “Access Control, Stage 2: Request Verification”.
Table 6.3. user
and db
Table Columns
Table Name | user | db |
---|---|---|
Scope columns | Host | Host |
User | Db | |
Password | User | |
Privilege columns | Select_priv | Select_priv |
Insert_priv | Insert_priv | |
Update_priv | Update_priv | |
Delete_priv | Delete_priv | |
Index_priv | Index_priv | |
Alter_priv | Alter_priv | |
Create_priv | Create_priv | |
Drop_priv | Drop_priv | |
Grant_priv | Grant_priv | |
Create_view_priv | Create_view_priv | |
Show_view_priv | Show_view_priv | |
Create_routine_priv | Create_routine_priv | |
Alter_routine_priv | Alter_routine_priv | |
Execute_priv | Execute_priv | |
Trigger_priv | Trigger_priv | |
Event_priv | Event_priv | |
Create_tmp_table_priv | Create_tmp_table_priv | |
Lock_tables_priv | Lock_tables_priv | |
References_priv | References_priv | |
Reload_priv | ||
Shutdown_priv | ||
Process_priv | ||
File_priv | ||
Show_db_priv | ||
Super_priv | ||
Repl_slave_priv | ||
Repl_client_priv | ||
Create_user_priv | ||
Security columns | ssl_type | |
ssl_cipher | ||
x509_issuer | ||
x509_subject | ||
Resource control columns | max_questions | |
max_updates | ||
max_connections | ||
max_user_connections |
The Event_priv
and Trigger_priv
columns were added in MySQL 5.1.6.
During the second stage of access control, the server performs request verification to make sure that each client has sufficient privileges for each request that it issues. In addition to the user
, db
, and host
grant tables, the server may also consult the tables_priv
and columns_priv
tables for requests that involve tables. The latter tables provide finer privilege control at the table and column levels. They have the columns shown in the following table.
Table 6.4. tables_priv
and columns_priv
Table Columns
Table Name | tables_priv | columns_priv |
---|---|---|
Scope columns | Host | Host |
Db | Db | |
User | User | |
Table_name | Table_name | |
Column_name | ||
Privilege columns | Table_priv | Column_priv |
Column_priv | ||
Other columns | Timestamp | Timestamp |
Grantor |
The Timestamp
and Grantor
columns currently are unused and are discussed no further here.
For verification of requests that involve stored routines, the server may consult the procs_priv
table, which has the columns shown in the following table.
Table 6.5. procs_priv
Table Columns
Table Name | procs_priv |
---|---|
Scope columns | Host |
Db | |
User | |
Routine_name | |
Routine_type | |
Privilege columns | Proc_priv |
Other columns | Timestamp |
Grantor |
The Routine_type
column is an ENUM
column with values of 'FUNCTION'
or 'PROCEDURE'
to indicate the type of routine the row refers to. This column enables privileges to be granted separately for a function and a procedure with the same name.
The Timestamp
and Grantor
columns are set to the current timestamp and the CURRENT_USER
value, respectively. However, they are unused and are discussed no further here.
Scope columns in the grant tables contain strings. They are declared as shown here; the default value for each is the empty string.
Table 6.6. Grant Table Scope Column Types
Column Name | Type |
---|---|
Host | CHAR(60) |
User | CHAR(16) |
Password | CHAR(41) |
Db | CHAR(64) |
Table_name | CHAR(64) |
Column_name | CHAR(64) |
Routine_name | CHAR(64) |
For access-checking purposes, comparisons of User
, Password
, Db
, and Table_name
values are case sensitive. Comparisons of Host
, Column_name
, and Routine_name
values are not case sensitive.
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', 'Trigger' |
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
or SHUTDOWN
) 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
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.22, “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';
출처 - http://dev.mysql.com/doc/refman/5.1/en/grant-table-structure.html
'DB > MySQL' 카테고리의 다른 글
mysql - innodb 설정 (0) | 2013.11.06 |
---|---|
mysql - innodb ibdata1 archetecter (0) | 2013.11.06 |
mysql - 한글 정렬 (0) | 2013.05.14 |
mysql 클러스터(cluster) (0) | 2013.03.08 |
mysql root password 초기화 (0) | 2013.01.03 |