-
Notifications
You must be signed in to change notification settings - Fork 14
/
mysqlGrant.md
235 lines (207 loc) · 9.74 KB
/
mysqlGrant.md
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
# GRANT Statement
```
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]
GRANT PROXY ON user
TO user [, user] ...
[WITH GRANT OPTION]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
user:
(see Section 6.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY [PASSWORD] 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin AS 'auth_string'
}
tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: {
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
```
## GRANT General Overview
For example:
```
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
```
The host name part of the account name, if omitted, defaults to `'%'`.
Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example:
```
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
```
## Object Quoting Guidelines
To specify quoted values:
- Quote database, table, column, and routine names as identifiers.
- Quote user names and host names as identifiers or as strings.
- Quote passwords as strings.
## Account Names and Passwords
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:
```
GRANT ALL ON test.* TO ''@'localhost' ...;
```
## Privileges Supported by MySQL
Permissible Privileges for GRANT and REVOKE
Privilege | Meaning and Grantable Levels
---|-----
`ALL [PRIVILEGES]` | Grant all privileges at specified access level except `GRANT OPTION` and `PROXY`.
`ALTER` | Enable use of `ALTER TABLE`. Levels: Global, database, table.
`ALTER ROUTINE` | Enable stored routines to be altered or dropped. Levels: Global, database, routine.
`CREATE` | Enable database and table creation. Levels: Global, database, table.
`CREATE ROUTINE` | Enable stored routine creation. Levels: Global, database.
`CREATE TABLESPACE` | Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
`CREATE TEMPORARY TABLES` | Enable use of `CREATE TEMPORARY TABLE`. Levels: Global, database.
`CREATE USER` | Enable use of `CREATE USER`, `DROP USER`, `RENAME USER`, and `REVOKE ALL PRIVILEGES`. Level: Global.
`CREATE VIEW` | Enable views to be created or altered. Levels: Global, database, table.
`DELETE` | Enable use of `DELETE`. Level: Global, database, table.
`DROP` | Enable databases, tables, and views to be dropped. Levels: Global, database, table.
`EVENT` | Enable use of events for the Event Scheduler. Levels: Global, database.
`EXECUTE` | Enable the user to execute stored routines. Levels: Global, database, routine.
`FILE` | Enable the user to cause the server to read or write files. Level: Global.
`GRANT OPTION` | Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, routine, proxy.
`INDEX` | Enable indexes to be created or dropped. Levels: Global, database, table.
`INSERT` | Enable use of `INSERT`. Levels: Global, database, table, column.
`LOCK TABLES` | Enable use of `LOCK TABLES` on tables for which you have the `SELECT` privilege. Levels: Global, database.
`PROCESS` | Enable the user to see all processes with `SHOW PROCESSLIST`. Level: Global.
`PROXY` | Enable user proxying. Level: From user to user.
`REFERENCES` | Enable foreign key creation. Levels: Global, database, table, column.
`RELOAD` | Enable use of `FLUSH` operations. Level: Global.
`REPLICATION CLIENT` | Enable the user to ask where master or slave servers are. Level: Global.
`REPLICATION SLAVE` | Enable replication slaves to read binary log events from the master. Level: Global.
`SELECT` | Enable use of `SELECT`. Levels: Global, database, table, column.
`SHOW DATABASES` | Enable `SHOW DATABASES` to show all databases. Level: Global.
`SHOW VIEW` | Enable use of `SHOW CREATE VIEW`. Levels: Global, database, table.
`SHUTDOWN` | Enable use of `mysqladmin shutdown`. Level: Global.
`SUPER` | Enable use of other administrative operations such as `CHANGE MASTER TO`, `KILL`, `PURGE BINARY LOGS`, `SET GLOBAL`, and `mysqladmin debug` command. Level: Global.
`TRIGGER` | Enable trigger operations. Levels: Global, database, table.
`UPDATE` | Enable use of `UPDATE`. Levels: Global, database, table, column.
`USAGE` | Synonym for “no privileges”
The privileges that a user holds for a database, table, column, or routine are formed additively as the logical `OR` of the account privileges at each of the privilege levels, including the global level. It is not possible to deny a privilege granted at a higher level by absence of that privilege at a lower level. For example, this statement grants the `SELECT` and `INSERT` privileges globally:
```
GRANT SELECT, INSERT ON *.* TO u1;
```
## Global Privileges
Global privileges are administrative or apply to all databases on a given server. To assign global privileges, use `ON *.*` syntax:
```
GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';
```
## Database Privileges
Database privileges apply to all objects in a given database. To assign database-level privileges, use `ON db_name.*` syntax:
```
GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
```
## Table Privileges
Table privileges apply to all columns in a given table. To assign table-level privileges, use `ON db_name.tbl_name` syntax:
```
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
```
## Column Privileges
Column privileges apply to single columns in a given table. Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses.
```
GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';
```
## Stored Routine Privileges
The `ALTER ROUTINE`, `CREATE ROUTINE`, `EXECUTE`, and `GRANT OPTION` privileges apply to stored routines (procedures and functions). They can be granted at the global and database levels. Except for `CREATE ROUTINE`, 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';
```
## Proxy User Privileges
The `PROXY` privilege enables one user to be a proxy for another. The proxy user impersonates or takes the identity of the proxied user; that is, it assumes the privileges of the proxied user.
```
GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';
```
## Implicit Account Creation
If an account named in a `GRANT` statement does not exist, the action taken depends on the `NO_AUTO_CREATE_USER` SQL mode:
- If `NO_AUTO_CREATE_USER` is not enabled, `GRANT` creates the account. This is very insecure unless you specify a nonempty password using `IDENTIFIED BY`.
- If `NO_AUTO_CREATE_USER` is enabled, `GRANT` fails and does not create the account, unless you specify a nonempty password using `IDENTIFIED BY` or name an authentication plugin using `IDENTIFIED WITH`.
As of MySQL 5.6.12, if the account already exists, `IDENTIFIED` WITH is prohibited because it is intended only for use when creating new accounts.
## Other Account Characteristics
`GRANT` permits these `tls_option` values:
- `NONE`
```
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE NONE;
```
- `SSL`
```
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE SSL;
```
- `X509`
```
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE X509;
```
- `ISSUER 'issuer'`
```
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL/CN=CA/emailAddress=ca@example.com';
```
- `SUBJECT 'subject'`
```
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL demo client certificate/
CN=client/emailAddress=client@example.com';
```
- `CIPHER 'cipher'`
```
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
```
The `SUBJECT`, `ISSUER`, and `CIPHER` options can be combined in the `REQUIRE` clause like this:
```
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL demo client certificate/
CN=client/emailAddress=client@example.com'
AND ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL/CN=CA/emailAddress=ca@example.com'
AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
```
To grant the `GRANT OPTION` privilege to an account without otherwise changing its privileges, do this:
```
GRANT USAGE ON *.* TO 'someuser'@'somehost' WITH GRANT OPTION;
```
GRANT permits these resource_option values:
- `MAX_QUERIES_PER_HOUR count`, `MAX_UPDATES_PER_HOUR count`, `MAX_CONNECTIONS_PER_HOUR count`
- `MAX_USER_CONNECTIONS count`
To specify resource limits for an existing user without affecting existing privileges, use `GRANT USAGE` at the global level (`ON *.*`) and name the limits to be changed. For example:
```
GRANT USAGE ON *.* TO ...
WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
```
## References
- [13.7.1.4 GRANT Statement](https://dev.mysql.com/doc/refman/5.6/en/grant.html)