SQL考点之账户访问控制、帐户管理、mysql注释写法

如题：2019年10月

 分析：估计猜也能猜对。主要是对授权语法过一遍，体会下SQL描述性语言，简单之处。

语法结构：

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 ...]
 
常用用法：
GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]

 详解：

grant(授权)+什么权限（priv_type)【具体又分为表权限、列权限、数据库权限：可选具体给表中的哪列，如给的权限是select 查询，后面跟列，则只可查询哪列】详见：扩展---授权分类列表：

on（在）+ 哪个数据库、存储过程、表 【object_type】+ 级别（其实就是指操作整个数据库，整个表 +...+还是里面列（注：表和视图属于同一个级别）

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE
 
priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

to(给谁）+后面肯定就是跟的用户了user_specification（用户规范，其实就是具体的用户描述）

用户规范：

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

identified:确定的 identified by :指通过什么来确定（其实就是指密码），书上的例子不错，如下：

代码解析为：不存在用户liming、huang,创建两个用户，并设置口令，授权在数据库mysql_test的表customers上拥有select和update权限。

 

下面的ssl可选，了解即可。   

ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

with子句：权限转移

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count   #每小时最大访问次数
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

with grant option:是指具用将自已拥有权限转移给其他用户权利，而无论其他用户有没有这项权限。见书上的用法：

代码解析：创建一个zhou用户，并将在customers表上的select、update 权限给它，并给他授权，可以将自已权限给其他用户。

扩展：1、授权分类列表：

权限类型	作用域	简介
SELECT	全局，库，表，列	使用户能使用SELECT访问特定表
INSERT	全局，库，表，列	使用户能使用INSERT在特定表中添加行
UPDATE	全局，库，表，列	使用户能使用UPDATE修改特定表中的值
DELETE	全局，库，表	使用户能使用DELETE删除特定表中的行
CREATE	全局，库，表	使用户能使用CREATE创建数据库及表
ALTER	全局，库，表	使用户能使用ALTEER TABLE修改数据库中特定表
DROP	全局，库，表	使用户能使用DROP删除库，表及视图
INDEX	全局，库，表	使用户具有在表上创建，删除索引的能力
REFERENCES	全局，库，表	使用户具有在表上创建外键的能力
ALL [ALL PRIVILEGES]	全局，库，表，过程，proxy	所有权限名的缩写（除了GRANT OPTION）
CREATE VIEW	全局，库，表	使用户具有创建和修改视图的权利
EXECUTE	全局，库，表	使用户具有执行存储过程的权利
GRANT OPTION	全局，库，表，过程，proxy	使用户具有超级权限（可对其他用户授权或回收权利）
SHOW VIEW	全局，库，表	使用户具有SHOW CREATE VIEW的权限
TRIGGER	全局，库，表	使用户具有操作trigger的所有权限
ALTER ROUTINE	全局，库，过程	使用户具有修改和删除特定数据库中存储过程及存储函数的能力
CREATE ROUTINE	全局，库	使用户具有在特定数据库中新建存储过程及存储函数的能力
CREATE TABLESPACE	全局	使用户能够创建，修改，删除tablespaces and log file groups
CREATE TEMPORARY TABLES	全局，库	使用户能够CREATE TEMPORARY TABLE
CREATE USER	全局	使用户能够CREATE,RENAME,DROP USER及REVOKE ALL PRIVILEGES
EVENT	全局，库	使用户能够使用事件调度
FILE	全局	使用户能够触发数据库读或写文件
LOCK TABLES	全局，库	使用户在有SELECT权限的表上有锁表权限
PROCESS	全局	使用户能够使用SHOW PROCESSLIST查看所有线程
PROXY	用户到用户	使用户能够使用代理
RELOAD	全局	使用户能够进行FLUSH操作
REPLICATION CLIENT	全局	使用户能够查找主从服务器
REPLICATION SLAVE	全局	使从机能够从主机读取binlog
SHOW DATABASES	全局	使用SHOW DATABASES查看所有数据库
SHUTDOWN	全局	使用户能够使用mysqladmin shutdown停止数据库服务
SUPER	全局	允许使用其他管理命令，如： CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, 及mysqladmin debug
USAGE		no privileges 的同义词

 2、权限撤销：

语法格式：

revoke
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    from user [, user] ...

 用户授予权限时没有使用with grant option，直接revoke即可。使用with grant option的话，回收完权限还要grant option 如下：

revoke select on *.* from select_no_grant1@'%';#没有使用with grant option
 
revoke select on *.* from select_grant1@'%';
revoke grant option on *.* from select_grant1@'%';#使用了，还要再回收grant option

使用revoke，必须拥有mysql数据库的全局create user或update权限。

3、mysql三种注释写法：

#在mysql中如何写注释
SELECT 6 + 6; #这是单行注释
SELECT 8 + 8; -- 这是单行注释 (注意:--后面要加一个空格，一定要记住)
SELECT 20 + 6;
SELECT 15  /* 这是一个在行中间的注释 */ + 2;
/*
这是多行注释
这是多行注释
*/
 
/*
总结:
如果要使用单行注释的话，我个人推荐使用#，不建议用--
*/

4、查看当前用户权限：show grants(后面是带s).

      查看指定用户权限：show grants for user

     另一种方法：  

mysql> select * from mysql.user where user='user_all' \G
*************************** 1. row ***************************
                  Host: %
                  User: user_all
              Password: *F02C912C68B67B7097BAEC8FE76BA6F50357895C
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: 
 authentication_string: NULL

5、账户管理

所有mysql用户都被放在user表中，可通过命令 select user from mysql.user;#查看当前数据管理系统的用户。

创建账户：语法

CREATE USER user_specification [, user_specification] ...
 
user_specification:
    user
    [
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
        IDENTIFIED BY [PASSWORD] 'password'
    ]

如：

如果用户名或主机名包含特殊字符，必须在其特殊符号前后使用单引号隔开，这里指定主机名，就是限定用户在连接mysql数据库时所使用的主机名。

create user 'remote'@'%' identified by 'remote';

 主机位 %，代表没有指定主机名，表示允许所有外部主机连接到mysql数据库，创建的用户名为’remote‘,密码为remote

若主机为localhost,表示只允许本地用户连到mysql数据库。

create user local@localhost identified by 'local';

创建一个用户后相当在mysql.user表中插入一行记录

insert into mysql.user (Host, User, Password) values ('localhost', 'simple', password('simple'));

 

删除账户：

语法：

DROP USER user [, user] ...

注：这里的主机名，一定要明确指出是localhost.因为是删除本地的user表中数据。

删除一个或多个账户，并消除其权限，但不会删除此账户所创建的表、萦引等对象，原因是创建时没记录谁创建了这些表。

实例：

drop user remote1;
drop user simple@'localhost';
drop user simple@'10.186.18.%';

 

修改用户名：语法

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

很好理解吧，果然是描述性语言，一个例子就足以说明一切：

rename user 'remote'@'%' to 'remote1'@'%';
rename user 'simple' to 'simple'@'10.186.18.%';

    注：这条语句只能修改用户名字或主机名，不能修改用户密码。  实例1修改了用户名，实例2修改了主机名。

用户必须是存在的用户，若用户不存在或新名已经存在，都会出错。

 

修改用户密码：语法

SET PASSWORD [FOR user] =
    {
        PASSWORD('cleartext password')
      | OLD_PASSWORD('cleartext password')
      | 'encrypted password'  #encrypted 加密的
    }

注：password()函数，加密，返回明文的散列值（即哈希加密）。

如：

set password for remote1 = password('remote1');
set password for simple@'localhost' = password('simple1');

若不加for,表示修改当前用户密码。