MySQL用户操作相关语法

2019-11-09  本文已影响0人  nzdxwl

创建用户

以下为数据库版本 5.7语法。

语法
CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD '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
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}
示例

Each account name uses the format described in Section 6.2.4, “Specifying Account Names”. For example:

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

The host name part of the account name, if omitted, defaults to '%'.

CREATE USER 'jeffrey'@'localhost'  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

CREATE USER 'jeffrey'@'localhost'   IDENTIFIED WITH sha256_password BY 'new_password' PASSWORD EXPIRE INTERVAL 180 DAY;

CREATE USER
  'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
                                   BY 'new_password1',
  'jeanne'@'localhost' IDENTIFIED WITH sha256_password
                                  BY 'new_password2'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  ACCOUNT LOCK;

在auth_option这部分：
auth_plugin names an authentication plugin. The plugin name can be a quoted string literal or an unquoted name. Plugin names are stored in the plugin column of the mysql.user system table.

For auth_option syntax that does not specify an authentication plugin, the default plugin is indicated by the value of the default_authentication_plugin system variable. For descriptions of each plugin, see Section 6.4.1, “Authentication Plugins”.

在使用MySQL8时，容易碰到类似以下的问题：
The server requested authentication method unknown to the client [caching_sha2_password] in Standard input code on line 22
可以创建使用本地认证方式的用户：
create user 'someone' IDENTIFIED WITH 'mysql_native_password' BY 'plaintxt_password'
也可以通过设置数据库变量 default-authentication-plugin=mysql_native_password 来实现
在数据库中可用以下命令查看当前设置值： show variables like 'default_authentication_plugin'

Each auth_option value (IDENTIFIED WITH ... BY in this case) applies only to the account named immediately preceding it, so each account uses the immediately following authentication plugin and password.

The remaining properties apply globally to all accounts named in the statement, so for both accounts:

账户名称

MySQL account names consist of a user name and a host name, which enables creation of distinct accounts for users with the same user name who can connect from different hosts.

删除用户

语法：

DROP USER [IF EXISTS] user [, user] ...

Important

DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed. Once the session is closed, the user is dropped, and that user's next attempt to log in will fail. This is by design.

查看用户

For each account, CREATE USER creates a new row in the mysql.user system table. The account row reflects the properties specified in the statement.
对于每个用户，create user操作创建新的一行记录到mysql.user系统表中。

use mysql;
select host, user from user;

授权

An account when first created has no privileges. To assign privileges, use the GRANT statement.
新创建的账户没有任何权限，需要使用grant语句进行授权。

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 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD '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 ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

Table 13.8 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”

A trigger is associated with a table. To create or drop a trigger, you must have the TRIGGER privilege for the table, not the trigger.

查看用户权限

示例：

show grants for 'root'@'localhost'
上一篇下一篇

猜你喜欢

热点阅读