Appearance
MySQL DCL
Note：DCL (Data Control Language)。
1. 创建用户
SQL
CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
DEFAULT ROLE role [, role ] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
[COMMENT 'comment_string' | ATTRIBUTE 'json_object']
user:
(see Section 8.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY 'auth_string' [AND 2fa_auth_option]
| IDENTIFIED BY RANDOM PASSWORD [AND 2fa_auth_option]
| IDENTIFIED WITH auth_plugin [AND 2fa_auth_option]
| IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 2fa_auth_option]
| IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 2fa_auth_option]
| IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 2fa_auth_option]
| IDENTIFIED WITH auth_plugin [initial_auth_option]
}
2fa_auth_option: {
IDENTIFIED BY 'auth_string' [AND 3fa_auth_option]
| IDENTIFIED BY RANDOM PASSWORD [AND 3fa_auth_option]
| IDENTIFIED WITH auth_plugin [AND 3fa_auth_option]
| IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 3fa_auth_option]
| IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 3fa_auth_option]
| IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 3fa_auth_option]
}
3fa_auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY RANDOM PASSWORD
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
| IDENTIFIED WITH auth_plugin AS 'auth_string'
}
initial_auth_option: {
INITIAL AUTHENTICATION IDENTIFIED BY {RANDOM PASSWORD | 'auth_string'}
| INITIAL AUTHENTICATION 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
}
password_option: {
PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
| PASSWORD HISTORY {DEFAULT | N}
| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
| FAILED_LOGIN_ATTEMPTS N
| PASSWORD_LOCK_TIME {N | UNBOUNDED}
}
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}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
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
Note：MySQL 帐户名称由用户名和主机名组成，语法
'user_name'@'host_name'。
SQL
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';SQL
CREATE USER 'joe'@'10.0.0.1' DEFAULT ROLE administrator, developer;2. 修改用户
SQL
ALTER USER [IF EXISTS]
user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
[COMMENT 'comment_string' | ATTRIBUTE 'json_object']
ALTER USER [IF EXISTS]
USER() user_func_auth_option
ALTER USER [IF EXISTS]
user [registration_option]
ALTER USER [IF EXISTS]
USER() [registration_option]
ALTER USER [IF EXISTS]
user DEFAULT ROLE
{NONE | ALL | role [, role ] ...}
user:
(see Section 8.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY 'auth_string'
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| IDENTIFIED BY RANDOM PASSWORD
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| IDENTIFIED WITH auth_plugin AS 'auth_string'
| DISCARD OLD PASSWORD
| ADD factor factor_auth_option [ADD factor factor_auth_option]
| MODIFY factor factor_auth_option [MODIFY factor factor_auth_option]
| DROP factor [DROP factor]
}
user_func_auth_option: {
IDENTIFIED BY 'auth_string'
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
| DISCARD OLD PASSWORD
}
factor_auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY RANDOM PASSWORD
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
| IDENTIFIED WITH auth_plugin AS 'auth_string'
}
registration_option: {
factor INITIATE REGISTRATION
| factor FINISH REGISTRATION SET CHALLENGE_RESPONSE AS 'auth_string'
| factor UNREGISTER
}
factor: {2 | 3} FACTOR
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
}
password_option: {
PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
| PASSWORD HISTORY {DEFAULT | N}
| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
| FAILED_LOGIN_ATTEMPTS N
| PASSWORD_LOCK_TIME {N | UNBOUNDED}
}
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}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
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
SQL
ALTER USER USER() IDENTIFIED BY 'auth_string';
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password';SQL
ALTER USER 'joe'@'10.0.0.1' DEFAULT ROLE administrator, developer;SQL
ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;3. 删除用户
SQL
DROP USER [IF EXISTS] user [, user] ...SQL
DROP USER 'jeffrey'@'localhost';4. 查询用户权限
SQL
SHOW GRANTS
[FOR user_or_role
[USING role [, role] ...]]
user_or_role: {
user (see Section 8.2.4, “Specifying Account Names”)
| role (see Section 8.2.5, “Specifying Role Names”.
}1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
Bash
mysql> SHOW GRANTS FOR 'jeffrey'@'localhost';
+------------------------------------------------------------------+
| Grants for jeffrey@localhost |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jeffrey`@`localhost` |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` |
+------------------------------------------------------------------+5. 授予用户权限
SQL
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_or_role [, user_or_role] ...
[WITH GRANT OPTION]
[AS user
[WITH ROLE
DEFAULT
| NONE
| ALL
| ALL EXCEPT role [, role ] ...
| role [, role ] ...
]
]
}
GRANT PROXY ON user_or_role
TO user_or_role [, user_or_role] ...
[WITH GRANT OPTION]
GRANT role [, role] ...
TO user_or_role [, user_or_role] ...
[WITH ADMIN OPTION]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
user_or_role: {
user (see Section 8.2.4, “Specifying Account Names”)
| role (see Section 8.2.5, “Specifying Role Names”)
}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
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
SQL
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';6. 撤销用户权限
SQL
REVOKE [IF EXISTS]
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user_or_role [, user_or_role] ...
[IGNORE UNKNOWN USER]
REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION
FROM user_or_role [, user_or_role] ...
[IGNORE UNKNOWN USER]
REVOKE [IF EXISTS] PROXY ON user_or_role
FROM user_or_role [, user_or_role] ...
[IGNORE UNKNOWN USER]
REVOKE [IF EXISTS] role [, role ] ...
FROM user_or_role [, user_or_role ] ...
[IGNORE UNKNOWN USER]
user_or_role: {
user (see Section 8.2.4, “Specifying Account Names”)
| role (see Section 8.2.5, “Specifying Role Names”
}1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
REVOKE SELECT ON world.* FROM 'role3';