mysql1215_mysql 第15章 用户、权限管理

2015-10-24

目录

1.CREATE USER(创建用户)

Syntax:

CREATE USER user_specification

[, user_specification] ...

user_specification:

user [IDENTIFIED BY [PASSWORD] 'password']

Examples:

CREATE USER 'jeffrey'@'localhost';

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

CREATE USER 'jeffrey'@'localhost'

IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';

2.DROP USER(删除用户)

Syntax:

DROP USER user [, user] ...

Examples:

DROP USER 'jeffrey'@'localhost';

3.GRANT(授权用户)

Syntax:

GRANT

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

TO user_specification [, user_specification] ...

object_type:

TABLE

| FUNCTION

| PROCEDURE

priv_level:

*

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name

user_specification:

user [IDENTIFIED BY [PASSWORD] 'password']

Examples:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

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;

4.RENAME USER(重命名用户)

Syntax:

RENAME USER old_user TO new_user

[, old_user TO new_user] ...

Examples:

RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';

5.REVOKE(收回授权)

Syntax:

REVOKE

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION

FROM user [, user] ...

Examples:

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

6.SET PASSWORD(设置密码)

Syntax:

SET PASSWORD [FOR user] =

{

PASSWORD('cleartext password')

| OLD_PASSWORD('cleartext password')

| 'encrypted password'

}

Examples:

SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');

UPDATE mysql.user SET Password=PASSWORD('cleartext password')

WHERE User='bob' AND Host='%.example.org';

FLUSH PRIVILEGES;

GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';

参考资料