msyql SQL语言-创建用户/授权/取消授权

一、创建用户

1、查看帮助文档

mysql> help create
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE FUNCTION UDF
   CREATE INDEX
   CREATE LOGFILE GROUP
   CREATE PROCEDURE
   CREATE SERVER
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE USER
   CREATE VIEW
   SHOW
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PROCEDURE
   SHOW CREATE TABLE
   SPATIAL

mysql> help create user

2、根据帮助文档分析

2.1 语法

Name: 'CREATE USER'
Description:
Syntax:
CREATE USER user_specification
    [, user_specification] ...

user_specification:
    user
    [
        IDENTIFIED BY [PASSWORD] 'password'
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
    ]

2.2 使用create user 的条件

原文：The CREATE USER statement creates new MySQL accounts. To use it, you
must have the global CREATE USER privilege or the INSERT privilege for
the mysql database. For each account, CREATE USER creates a new row in
the mysql.user table and assigns the account no privileges. An error
occurs if the account already exists.

使用该语法的账号必须是具有创建用户权限的。

2.3 创建用户

2.3.1 创建一个当前主机登录并使用密码的用户

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

2.3.2 创建一个当前主机不使用密码的用户

CREATE USER 'jeffrey'@'localhost';

2.3.3 创建一个所有主机登录并使用密码的用户

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

二、用户授权

1、查看帮助文档

mysql> help grant

2、分析帮助文档

2.1语法

	GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

2.2 创建用户并授权

2.2.1 创建用户并授权命令

命令：grant all privileges on dbname.* to username@localhost identified by 'password'

2.2.2 案例1：创建shanTest用户使用密码’123456’并具有shan_study库的所有权限，允许从localhost主机登录

mysql> grant all privileges on shan_study.* to 'shanTest'@'localhost' identified by '123456'; 
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.2.3 查看授权

mysql> show grants for shanTest;
ERROR 1141 (42000): There is no such grant defined for user 'shanTest' on host '%'
mysql> show grants for shanTest@localhost;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for shanTest@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'shanTest'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `shan_study`.* TO 'shanTest'@'localhost'                                                |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

2.2.4 收回权限

语法：
	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] 

2.2.5 收回权限命令

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

2.2.6 案例

mysql> revoke all privileges, grant option from 'shanTest'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grant from 'shanTest'@'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grant from 'shanTest'@'localhost'' at line 1