of {$slidecount} ½ {$title} ATZJG.NET {$author}

首页






MySQL 用户管理
MySQL 初步


Haifeng Xu


(hfxu@yzu.edu.cn)

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

and based on 刘增杰、张少军 《MySQL 5.5 从零开始学》

References: Michael Kofler, The Definitive Guide to MySQL 5

http://forums.mysql.com/

MySQL 5.1 Reference Manual

目录

MySQL 的安装

MySQL 的安装

安装中可能会产生的问题

安装中可能会产生的问题

mysql

mysql

mysql 只是一个小程序, 它将命令传至 MySQL server, 并显示结果. 它是与 MySQL server 交互的客户端程序.

登录 MySQL

登录 MySQL

Unix/Linux

mysql, mysqld, mysqladmin, mysqldump 等都位于目录 /usr/bin/ 下. 可在终端下直接输入这些命令登录.

Windows

mysql 和其他命令工具被存储于 C:\Programs\ MySQL\MySQL Server n.n\bin 目录.


mysql 的选项

mysql 命令一般需要两个选项 -u name -p 来于 server 连接. 下面所列的选项也适用于 mysqladmin, mysqldump.

例子

mysql -u root -p -h localhost --default-character-set=utf8 test_bar

建立新用户

建立新用户

类似于 Unixroot 帐户, 一般建议不使用 MySQL 的超级用户(root)来完成我们日常要完成的数据操作. 我们应该仅在必要的情况下才使用 root 帐户. 当使用 root 帐户时, 可能某些误操作会给我们的数据带来无法弥补的伤害.

现在我们来建立一个普通帐户, 使具有有限的权限.

mysql> CREATE USER haifeng@localhost IDENTIFIED BY '123456';

上面这条语句创建了一个新的用户, 名为 haifeng, 且设定其密码为 123456. 用户虽然创建了, 但是尚未具有任何权限.

mysql> GRANT ALL ON mydb.* to haifeng@localhost;
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON mydb.*
    -> TO 'haifeng'@'localhost'
    -> IDENTIFIED BY '123456';

查看 MySQL 中的用户

mysql> use mysql
mysql> select * from user\G;

another way to insert user

mysql> INSERT INTO user
    -> (host, user, password,
    ->  select_priv, insert_priv, update_priv)
    ->  VALUES ('localhost', 'haifeng',
    ->  PASSWORD('123456'), 'Y', 'Y', 'Y');
mysql> FLUSH PRIVILEGES;

这里使用 FLUSH PRIVILEGES 是告诉服务器重新载入授权表. 如果不这样做, 要使用新账户登录服务器, 只能在服务器重新启动之后才可以.

user 表中的其他权限

MySQL 5.7.14 中还有下面的几项

修改密码

修改密码

root 用户修改自己的密码

root 用户的安全对于保证 MySQL 的安全非常重要. 修改 root 用户的密码有很多种方法.

mysqladmin -u username -h hostname -p password newpasswd

实验

mysqladmin -u root -p password "654321"
Enter password:

这里在 Enter password: 处输入 root 原来的密码即可.

mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

使用 UPDATE 语句修改 user 表

使用 SET PASSWORD 语句

mysql>SET PASSWORD=PASSWORD("654321");

新密码必须用 PASSWORD() 函数进行加密.

SET PASSWORD 语句执行成功后, 为使更改生效, 需要重新启动 MySQL 或者执行 FLUSH PRIVILEGES; 语句刷新权限, 重新加载权限表.


root 用户修改普通用户的密码

SET PASSWORD FOR 'haifeng'@'localhost'=PASSWORD('newpasswd');

普通用户修改自己的密码

使用 SET PASSWORD 语句

mysql>SET PASSWORD=PASSWORD("654321");

几个常用命令

几个常用命令

我们以数据库 mysql 为例

mysql> show databases;
mysql> use mysql;
mysql> show tables;
mysql> show columns from user;   // 与 desc user 一致
mysql> show index from user;
mysql> SHOW TABLE STATUS LIKE '%user%'\G;
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 68
    Data_length: 548
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2012-11-03 15:14:11
    Update_time: 2014-11-11 10:09:47
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: Users and global privileges
1 row in set (0.00 sec)

Caution

小心别误删数据

mysql 的一些命令在使用时要特别小心, 比如

mysql> DELETE FROM tablename

将删除表中的所有记录, 而且无法恢复.

如果希望能避免这样的误操作, 则可以在登录 mysql 时使用 --i-am-a-dummy 选项.

mysql> delete from friends;
ERROR 1175 (HY000): You are using safe update mode and 
     you tried to update a table without a WHERE that uses a KEY column

此时如果要执行 UPDATEDELETE 语句, 则必须带有 WHERELIMIT 关键字.

最重要的几个 mysql 命令

最重要的几个 mysql 命令

简写 命令 解释
\c clear 取消输入命令
\h help 显示常用的一些命令
\q quitexit 退出 mysql, 在 Unix/Linux 中也可以使用 Ctrl+D
\s status 显示 MySQL 服务器的状态信息
\T [f] tee [filename] 将所有的输入输出自动记录到指定的文件中
\t notee 结束 tee 命令. 如果想继续记录命令, 则随时可以使用 tee 命令, 此时可不必指定文件名.
\u db use database 指定哪个数据库为当前的数据库.
\. fn source filename 执行保存在文件 filename 中的 SQL 命令. 命令之间必须使用分号隔开.

注意 \c 这个终止命令如果是在单引号或双引号之间的话, 则不会起作用. 同样的, \u \c 会发生错误.

MySQL 会保存之前的命令, 可以使用上下光标键提取之前的历史命令. 这些命令即使在系统关闭之后仍会被保存.

Unix/Linux 下使用 mysql 的一些技巧

字符集

目前大多 Linux 分发版默认使用的是 utf8 字符编码. 如果 mysql 与 MySQL 服务器之间的通讯并不是使用同一个字符集, 则 international special characters 将会被弄砸了. 这个问题可以通过下面两个方式来解决.

可以使用 \sstatus 命令来查看 mysql 与 MySQL 服务器之间的通讯使用的是哪种字符集.

以下是在 wampserver 默认安装下, 启动其中的 mysql 后的状态.

mysql> \s
--------------

Connection id:		19
Current database:	mysql
Current user:		root@localhost
SSL:			Not in use
Using delimiter:	;
Server version:		5.7.14 MySQL Community Server (GPL)
Protocol version:	10
Connection:		localhost via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	gbk
Conn.  characterset:	gbk
TCP port:		3306
Uptime:			29 min 40 sec

Threads: 1  Questions: 88  Slow queries: 0  Opens: 128  Flush tables: 1  Open tables: 121  Queries per second avg: 0.049
--------------

让 mysql 执行含 SQL 命令的文件

用户管理

MySQL 用户管理

MySQL 是一个多用户数据库, 具有功能强大的访问控制系统, 可以为不同用户指定允许的权限.

权限表

权限表

MySQL 服务器通过权限表来控制用户对数据库的访问, 权限表存放在 mysql 数据库中, 有 mysql_install_db 脚本初始化.

存储账号权限的信息表主要有:

user 表

user 表的结构

user 表的用户列包括 HostUserPassword, 分别表示主机名、用户名和密码. 其中 HostUser 是表 user 的联合主键.

当用户与服务器之间建立连接时, 输入的帐户信息(包含主机名、用户名称和密码)必须与user 表中的一致才能建立连接.

修改用户密码时, 实际上就是修改 user 表中的 Password 属性.

mysql> DESC user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.05 sec)
mysql> SELECT Host, User FROM user;
+-----------+---------+
| Host      | User    |
+-----------+---------+
| 127.0.0.1 | root    |
| ::1       | root    |
| localhost |         |
| localhost | haifeng |
| localhost | root    |
+-----------+---------+
5 rows in set (0.00 sec)

user 表

user 表的权限列


user 表

user 表的安全列

查询服务器是否支持 SSL 功能.

mysql> SHOW VARIABLES LIKE '%have_openssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
+---------------+----------+
1 row in set (0.00 sec)

user 表

user 表的权限列

资源控制列的字段用来限制用户使用的资源:

mysql> SELECT max_questions, max_updates, max_connections, max_user_connections FROM user WHERE User='root';
+---------------+-------------+-----------------+----------------------+
| max_questions | max_updates | max_connections | max_user_connections |
+---------------+-------------+-----------------+----------------------+
|             0 |           0 |               0 |                    0 |
|             0 |           0 |               0 |                    0 |
|             0 |           0 |               0 |                    0 |
+---------------+-------------+-----------------+----------------------+
3 rows in set (0.00 sec)

mysql> SELECT max_questions, max_updates, max_connections, max_user_connections FROM user WHERE User='haifeng';
+---------------+-------------+-----------------+----------------------+
| max_questions | max_updates | max_connections | max_user_connections |
+---------------+-------------+-----------------+----------------------+
|             0 |           0 |              30 |                    0 |
+---------------+-------------+-----------------+----------------------+
1 row in set (0.00 sec)

db 表

db 表的结构

db 表和host 表是 MySQL 数据库中非常重要的权限表.

db 表中存储了用户对某个数据库的操作权限, 决定用户能从哪个主机存取哪个数据库.

host 表中存储了某个主机对数据库的操作权限, 配合 db 权限表对给定主机上数据库级操作权限做更细致的控制. 这个权限表不受 GRANTREVOKE 语句的影响.

db 表比较常用, host 表一般很少使用. 现在新版本的 MySQL 都已经没有 host 表了.

db 表和host 表结构类似, 字段大致分为两类: 用户列和权限列.

mysql> DESC db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.04 sec)

host 表

host 表

MySQL 5.6.17 版本中没有 host 表.

mysql> SHOW TABLES LIKE '%host%';
Empty set (0.00 sec)
mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.6.17    |
+-----------+
1 row in set (0.00 sec)

db 表

db 表的用户列

db 表的用户列有三个属性, 分别为 HostUserDb. 它们组成了 db 表 的 PRIMARY KEY.


db 表

db 表的权限列

Create_routine_privAlter_routine_priv 这两个属性表明用户是否有创建和修改存储过程的权限.

user 表中的权限是针对所有数据库的, 如果希望用户只对某个数据库有操作权限, 那么需要将 user 表中对应的权限设置为 N, 然后在 db 表中设置对应数据库的操作权限.

mysql> SELECT * FROM db WHERE 1\G;
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
2 rows in set (0.00 sec)

ERROR:
No query specified

tables_priv 表

mysql> SHOW TABLES LIKE "%tables%";
+----------------------------+
| Tables_in_mysql (%tables%) |
+----------------------------+
| tables_priv                |
+----------------------------+
1 row in set (0.01 sec)
mysql> DESC tables_priv\G;
*************************** 1. row ***************************
  Field: Host
   Type: char(60)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 2. row ***************************
  Field: Db
   Type: char(64)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 3. row ***************************
  Field: User
   Type: char(16)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 4. row ***************************
  Field: Table_name
   Type: char(64)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 5. row ***************************
  Field: Grantor
   Type: char(77)
   Null: NO
    Key: MUL
Default:
  Extra:
*************************** 6. row ***************************
  Field: Timestamp
   Type: timestamp
   Null: NO
    Key:
Default: CURRENT_TIMESTAMP
  Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
  Field: Table_priv
   Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')
   Null: NO
    Key:
Default:
  Extra:
*************************** 8. row ***************************
  Field: Column_priv
   Type: set('Select','Insert','Update','References')
   Null: NO
    Key:
Default:
  Extra:
8 rows in set (0.03 sec)

tables_priv 表

tables_priv 表有 8 个属性, 分别是 HostDbUserTable_nameGrantorTimestampTable_privColumn_priv

mysql> SELECT GROUP_CONCAT( COLUMN_NAME )
    -> FROM information_schema.COLUMNS
    -> WHERE table_name='tables_priv'
    -> AND table_schema='mysql';
+------------------------------------------------------------------+
| GROUP_CONCAT( COLUMN_NAME )                                      |
+------------------------------------------------------------------+
| Host,Db,User,Table_name,Grantor,Timestamp,Table_priv,Column_priv |
+------------------------------------------------------------------+
1 row in set (0.02 sec)

columns_priv 表

columns_priv 表的结构

columns_priv 表只有 7 个属性, 分别是 HostDbUserTable_nameColumn_nameTimestampColumn_priv

mysql> SHOW TABLES LIKE "%columns%";
+-----------------------------+
| Tables_in_mysql (%columns%) |
+-----------------------------+
| columns_priv                |
+-----------------------------+
1 row in set (0.01 sec)
mysql> SELECT GROUP_CONCAT( COLUMN_NAME )
    -> FROM information_schema.COLUMNS
    -> WHERE table_name='columns_priv'
    -> AND table_schema='mysql';
+-----------------------------------------------------------+
| GROUP_CONCAT( COLUMN_NAME )                               |
+-----------------------------------------------------------+
| Host,Db,User,Table_name,Column_name,Timestamp,Column_priv |
+-----------------------------------------------------------+
1 row in set (0.02 sec)
mysql> DESC columns_priv\G;
*************************** 1. row ***************************
  Field: Host
   Type: char(60)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 2. row ***************************
  Field: Db
   Type: char(64)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 3. row ***************************
  Field: User
   Type: char(16)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 4. row ***************************
  Field: Table_name
   Type: char(64)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 5. row ***************************
  Field: Column_name
   Type: char(64)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 6. row ***************************
  Field: Timestamp
   Type: timestamp
   Null: NO
    Key:
Default: CURRENT_TIMESTAMP
  Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
  Field: Column_priv
   Type: set('Select','Insert','Update','References')
   Null: NO
    Key:
Default:
  Extra:
7 rows in set (0.04 sec)

DESC columns_priv\G; 等价于 SHOW FIELDS FROM columns_priv\G;SHOW COLUMNS FROM columns_priv\G;

也可以使用 SHOW FULL COLUMNS FROM table_name;SHOW FULL FIELDS FROM table_name;

mysql> SHOW FULL COLUMNS FROM columns_priv\G;
*************************** 1. row ***************************
     Field: Host
      Type: char(60)
 Collation: utf8_bin
      Null: NO
       Key: PRI
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: Db
      Type: char(64)
 Collation: utf8_bin
      Null: NO
       Key: PRI
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: User
      Type: char(16)
 Collation: utf8_bin
      Null: NO
       Key: PRI
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 4. row ***************************
     Field: Table_name
      Type: char(64)
 Collation: utf8_bin
      Null: NO
       Key: PRI
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 5. row ***************************
     Field: Column_name
      Type: char(64)
 Collation: utf8_bin
      Null: NO
       Key: PRI
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 6. row ***************************
     Field: Timestamp
      Type: timestamp
 Collation: NULL
      Null: NO
       Key:
   Default: CURRENT_TIMESTAMP
     Extra: on update CURRENT_TIMESTAMP
Privileges: select,insert,update,references
   Comment:
*************************** 7. row ***************************
     Field: Column_priv
      Type: set('Select','Insert','Update','References')
 Collation: utf8_general_ci
      Null: NO
       Key:
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
7 rows in set (0.01 sec)

procs_priv 表

procs_priv 表的结构

procs_priv 表可以对存储过程和函数设置操作权限.

mysql> SELECT GROUP_CONCAT( COLUMN_NAME )
    -> FROM information_schema.COLUMNS
    -> WHERE table_name='procs_priv'
    -> AND table_schema='mysql';
+--------------------------------------------------------------------+
| GROUP_CONCAT( COLUMN_NAME )                                        |
+--------------------------------------------------------------------+
| Host,Db,User,Routine_name,Routine_type,Grantor,Proc_priv,Timestamp |
+--------------------------------------------------------------------+
1 row in set (0.07 sec)
mysql> DESC procs_priv\G;
*************************** 1. row ***************************
  Field: Host
   Type: char(60)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 2. row ***************************
  Field: Db
   Type: char(64)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 3. row ***************************
  Field: User
   Type: char(16)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 4. row ***************************
  Field: Routine_name
   Type: char(64)
   Null: NO
    Key: PRI
Default:
  Extra:
*************************** 5. row ***************************
  Field: Routine_type
   Type: enum('FUNCTION','PROCEDURE')
   Null: NO
    Key: PRI
Default: NULL
  Extra:
*************************** 6. row ***************************
  Field: Grantor
   Type: char(77)
   Null: NO
    Key: MUL
Default:
  Extra:
*************************** 7. row ***************************
  Field: Proc_priv
   Type: set('Execute','Alter Routine','Grant')
   Null: NO
    Key:
Default:
  Extra:
*************************** 8. row ***************************
  Field: Timestamp
   Type: timestamp
   Null: NO
    Key:
Default: CURRENT_TIMESTAMP
  Extra: on update CURRENT_TIMESTAMP
8 rows in set (0.03 sec)

帐户管理

帐户管理

可以在登录系统时, 指定执行某个 SQL 语句, 使用 -e 选项, 比如 -e "SELECT * FROM table_name WHERE ..."

C:\Users\haifeng>mysql -u root -p test_zero -e "SHOW TABLES;"
Enter password: ******
+---------------------+
| Tables_in_test_zero |
+---------------------+
| account             |
| file_table          |
| tmp                 |
| tmp11               |
| tmp112              |
| tmp12               |
| tmp4                |
| tmp5                |
| tmp6                |
| tmp7                |
| tmp8                |
| tmp9                |
| worker              |
+---------------------+

新建普通用户

新建普通用户

创建新用户, 必须要有相应的权限.

有两种方式创建用户,


权限管理

权限管理

权限管理主要是指对于 MySQL 系统中的用户(即 mysql.user 表中的所有用户)进行权限验证.

所有用户的权限都存储在 MySQL 的权限表中. 权限表有

授权

全局权限适用于一个给定服务器中的所有数据库, 这些权限存储在 mysql.user 表中.

数据库权限适用于一个给定数据库中的所有目标(表、存储过程、函数、触发器、视图等等), 这些权限存储在 mysql.db 表中.

表权限适用于一个给定表中的所有属性(列), 这些权限存储在 mysql.tables_priv 表中.

属性(列)权限适用于一个给定表中的某个属性(列), 这些权限存储在 mysql.columns_priv 表中. 当使用 REVOKE 时, 必须指定与被授权属性(列)相同的属性(列).

CREATE ROUTINEALTER ROUTINEEXECUTEGRANT 权限适用于已存储的子程序. 这些权限可以被授予为全局层级和数据库层级. 而且除了 CREATE ROUTINE 之外, 这些权限可以被授予子程序层级, 并存储在 mysql.procs_priv 表中.

在 MySQL 中, 必须是拥有 GRANT 权限的用户才可以执行 GRANT 语句.

要使用 GRANTREVOKE, 必须拥有 GRANT OPTION 权限, 并且必须用于正在授予或撤销的权限.

GRANT 的语法如下:

GRANT priv_type

显示指定用户的权限

显示指定用户的权限

使用 SHOW GRANTS FOR 'root'@'localhost'; 显示指定用户的权限.

也可以直接在 user 表中查询.

mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2A26DC91ED99AAF2AF0256BC32CDA93B8298724C' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2A26DC91ED99AAF2AF0256BC32CDA93B8298724C' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+

mysql> SHOW GRANTS FOR `root`@`localhost`;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2A26DC91ED99AAF2AF0256BC32CDA93B8298724C' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW GRANTS FOR 'root'@'127.0.0.1';
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR root@127.0.0.1;
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR `root`@`127.0.0.1`;
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR "root"@"127.0.0.1";
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR haifeng@127.0.0.1;
ERROR 1141 (42000): There is no such grant defined for user 'haifeng' on host '127.0.0.1'
mysql> SHOW GRANTS FOR haifeng@localhost;
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for haifeng@localhost                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'haifeng'@'localhost' IDENTIFIED BY PASSWORD '*2A26DC91ED99AAF2AF0256BC32CDA93B8298724C' WITH MAX_CONNECTIONS_PER_HOUR 30 |
| GRANT SELECT, UPDATE (name, id) ON `test`.`table1` TO 'haifeng'@'localhost'                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User='haifeng'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: haifeng
              Password: *2A26DC91ED99AAF2AF0256BC32CDA93B8298724C
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 30
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User='root'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *2A26DC91ED99AAF2AF0256BC32CDA93B8298724C
           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: Y
       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:
      password_expired: N
*************************** 2. row ***************************
                  Host: 127.0.0.1
                  User: root
              Password:
           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: Y
       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:
      password_expired: N
*************************** 3. row ***************************
                  Host: ::1
                  User: root
              Password:
           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: Y
       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:
      password_expired: N
3 rows in set (0.00 sec)

访问控制

访问控制

MySQL 的访问控制分为两个阶段:

连接核实阶段

MySQL 使用 user 表中的三个属性(字段)(Host, UserPassword)执行身份检查.

服务器只有在 user 表记录的这三个属性值完全相同时才接受连接, 否则服务器完全拒绝访问.

请求核实阶段

建立连接后, 服务器进入访问控制的第二个阶段, 即请求核实阶段. 对此连接上进来的每个请求, 服务器检查用户要执行的操作, 然后检查是否有足够的权限来执行它. 此时, MySQL 会依次检查 user 表、db 表、tables_priv 表、columns_priv 表, 即依次检查全局权限、数据库级别的权限、数据表级别的权限、属性列级别的权限. 如果所有权限表都检查完毕, 都没有找到允许的操作权限, MySQL 将返回错误信息, 用户请求的操作不能执行.

MySQL 通过逐级向下检查权限表, 从 user 表到columns_priv 表, 但不是所有的权限都要执行这个检查过程. 例如, 一个用户登录到 MySQL 服务器之后只执行对 MySQL 的管理操作, 此时只涉及管理权限, 因此 MySQL 只检查 user 表. 另外, 如果请求的权限操作不被允许, MySQL 也不会继续检查下一层级的权限表.

匿名帐户

匿名帐户

匿名帐户是指 user 表中 User 属性列为空字符串的记录.

mysql> SELECT * FROM mysql.user WHERE User=''\G;
*************************** 1. row ***************************
                  Host: localhost
                  User:
              Password:
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              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
      password_expired: N
1 row in set (0.00 sec)

这可以使得任何用户都可以登录到 MySQL 系统, 不过其权限有限, 可以对 test 开头的数据库进行查询修改.

C:\Users\haifeng>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 637
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_bar           |
| test_db3           |
| test_fruits        |
| test_my_first_db   |
| test_products      |
| test_transaction   |
| test_zero          |
+--------------------+
8 rows in set (0.01 sec)

mysql> SELECT * FROM worker;
+----+---------+
| id | Name    |
+----+---------+
|  1 | Jimy    |
|  2 | Tom     |
|  3 | Kate    |
|  4 | Kavin   |
|  5 | Michael |
|  6 | Nick    |
+----+---------+
6 rows in set (0.01 sec)

mysql> DESC worker;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(30) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.04 sec)

mysql> INSERT INTO worker (Name) VALUES ("David");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM worker;
+----+---------+
| id | Name    |
+----+---------+
|  1 | Jimy    |
|  2 | Tom     |
|  3 | Kate    |
|  4 | Kavin   |
|  5 | Michael |
|  6 | Nick    |
|  7 | David   |
+----+---------+
7 rows in set (0.00 sec)

mysql> USE mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
mysql>

在生产环境中, 匿名帐户一定要删除.

DELETE FROM mysql.user WHERE User='';

普通用户忘记密码

为用户重置密码

如果用户 haifeng 忘记了登录密码, 而 root 用户的密码是知道的.

C:\Users\haifeng>mysql -uhaifeng -p
Enter password: *****
ERROR 1045 (28000): Access denied for user 'haifeng'@'localhost' (using password: YES)

使用 root 帐户登录系统

C:\Users\haifeng>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 642
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> SELECT Host, User, Password, plugin FROM user WHERE User='haifeng'\G;
*************************** 1. row ***************************
    Host: localhost
    User: haifeng
Password: *9DF4837AD74328106EA4528EEA6DC38ED2CA2AD8
  plugin: mysql_native_password
1 row in set (0.00 sec)

使用 SET PASSWORD FOR 'username'@'host'=PASSWORD('newpassword'); 为用户修改密码.

mysql> SET PASSWORD FOR haifeng@localhost=PASSWORD('123456');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host, User, Password, plugin FROM user WHERE User='haifeng'\G;
*************************** 1. row ***************************
    Host: localhost
    User: haifeng
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
  plugin: mysql_native_password
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> SELECT PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.00 sec)

通知用户 haifeng, 请他登录 MySQL 后自行修改密码

C:\Users\haifeng>mysql -uhaifeng -p
Enter password: [input: 123456]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 643
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> SET PASSWORD=PASSWORD('NewPassword');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT User, Host, Password FROM mysql.user WHERE User='haifeng'\G;
ERROR 1142 (42000): SELECT command denied to user 'haifeng'@'localhost' for table 'user'
ERROR:
No query specified

root 用户登录后可以查看

mysql> SELECT User, Host, Password FROM mysql.user WHERE User='haifeng'\G;
*************************** 1. row ***************************
    User: haifeng
    Host: localhost
Password: *EC760840CDAC6AF05321A7DDD05A84E18892AA97
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User='haifeng'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: haifeng
              Password: *EC760840CDAC6AF05321A7DDD05A84E18892AA97
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 30
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.db WHERE User='haifeng'\G;
Empty set (0.00 sec)
mysql> SELECT * FROM mysql.tables_priv WHERE User='haifeng'\G;
*************************** 1. row ***************************
       Host: localhost
         Db: test
       User: haifeng
 Table_name: table1
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select
Column_priv: Update
1 row in set (0.04 sec)
mysql> SELECT * FROM mysql.columns_priv WHERE User='haifeng'\G;
*************************** 1. row ***************************
       Host: localhost
         Db: test
       User: haifeng
 Table_name: table1
Column_name: id
  Timestamp: 0000-00-00 00:00:00
Column_priv: Update
*************************** 2. row ***************************
       Host: localhost
         Db: test
       User: haifeng
 Table_name: table1
Column_name: name
  Timestamp: 0000-00-00 00:00:00
Column_priv: Update
2 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.procs_priv WHERE User='haifeng'\G;
Empty set (0.00 sec)



End






Thanks very much!