注意

想法及时记录，实现可以待做。

SQL语句主要可以分为DDL、DML、DCL几大类。

DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

账户管理语句

修改用户语句

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] ...

ALTER USER [IF EXISTS]
    USER() IDENTIFIED BY 'auth_string'

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'
}

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
}

以上语句用来修改MySQL的账号,如果要修改用户权限，那就需要有创建用户的权限，否则就会报错。

ALTER USER概述

示例：更改帐户密码并使其过期。结果，用户必须使用指定的密码进行连接，并在下一次连接时选择一个新密码：

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

示例：修改帐户以使用sha256_password身份验证插件和给定的密码。要求每180天选择一个新密码：

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

示例：锁定或解锁帐户：

ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;

示例：要求一个帐户使用SSL进行连接并建立每小时20个连接的限制：

ALTER USER 'jeffrey'@'localhost'
  REQUIRE SSL WITH MAX_CONNECTIONS_PER_HOUR 20;

示例：更改多个帐户，并指定一些每个帐户属性和一些全局属性：

ALTER USER
  'jeffrey'@'localhost' IDENTIFIED BY 'new_password',
  'jeanne'@'localhost'
  REQUIRE SSL WITH MAX_USER_CONNECTIONS 2;

ALTER USER身份验证选项

示例：将密码指定为明文；使用默认插件：

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

示例：指定身份验证插件以及明文密码值：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password
             BY 'password';

示例：指定身份验证插件以及哈希密码值：

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password
             AS '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

ALTER USER资源限制选项

示例：

ALTER USER 'jeffrey'@'localhost'
  WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

ALTER USER密码管理选项

PASSWORD EXPIRE 立即将该语句命名的所有帐户的密码标记为过期。
```
  ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
```
PASSWORD EXPIRE DEFAULT 设置该语句命名的所有帐户，以便应用全局过期策略，如default_password_lifetime系统变量所指定。
```
  ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
```
PASSWORD EXPIRE NEVER 该到期选项将覆盖该语句命名的所有帐户的全局策略。对于每个密码，它都会禁用密码过期，以便密码永不过期。
```
  ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
```
PASSWORD EXPIRE INTERVAL N DAY 该到期选项将覆盖该语句命名的所有帐户的全局策略。对于每个密码，它将密码有效期设置为N天。以下声明要求每180天更改一次密码：
```
  ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
```

创建用户语句

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
}

CREATE USER概述

示例：创建一个使用默认身份验证插件和给定密码的帐户。将密码标记为过期，以便用户在与服务器的第一次连接时必须选择一个新密码：

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

示例：创建一个使用sha256_password身份验证插件和给定密码的帐户。要求每180天选择一个新密码：

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;

CREATE USER身份验证选项

示例：将密码指定为明文；使用默认插件：

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

示例：指定身份验证插件以及明文密码值：

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

CREATE USER资源限制选项

示例：

CREATE USER 'jeffrey'@'localhost'
  WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

CREATE USER密码管理选项

PASSWORD EXPIRE 立即将该语句命名的所有帐户的密码标记为过期。
```
  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
```
PASSWORD EXPIRE DEFAULT 设置该语句命名的所有帐户，以便应用全局过期策略，如default_password_lifetime系统变量所指定。
```
  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
```
PASSWORD EXPIRE NEVER 该到期选项将覆盖该语句命名的所有帐户的全局策略。对于每个密码，它都会禁用密码过期，以便密码永不过期。
```
  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
```
PASSWORD EXPIRE INTERVAL N DAY 该到期选项将覆盖该语句命名的所有帐户的全局策略。对于每个密码，它将密码有效期设置为N天。以下声明要求每180天更改一次密码：
```
  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
```

删除用户语句

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

示例：删除用户：

DROP USER 'jeffrey'@'localhost';

赋权语句

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 概览

示例：给用户授权

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

MySQL支持的权限

权限	含义和授予级别
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 source or replica servers are. Level: Global.
REPLICATION SLAVE	Enable replicas to read binary log events from the source. 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”

全局权限

GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

数据库权限

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

表的权限

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

字段权限

GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';

其他的特点

WITH GRANT OPTION子句使用户能够将指定级别的用户特权授予其他用户。要将GRANT OPTION特权授予帐户，而又不更改其特权，请执行以下操作：

GRANT USAGE ON *.* TO 'someuser'@'somehost' WITH GRANT OPTION;

用户重命名语句

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

重命名账户，一定是存在的账户，否则会报错。

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

REVOKE 语句

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] ...

REVOKE PROXY ON user
    FROM user [, user] ...

这个语句，允许管理员撤销账户的权限。比如，撤销写入权限：

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

要使用第一种REVOKE语法，您必须具有GRANT OPTION特权，并且必须具有要撤销的权限。要撤消所有权限，请使用第二种语法，该语法将删除一个或多个指定用户的所有全局，数据库，表，列特权：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

MySQL常用语句DCL

经常使用的MyQL数据注意事项

注意