grant用户权限 mysql mysql账户权限

MySQL账户和权限管理

Cmd方式进入mysql >> mysql\bin目录 >> mysql -u root -p

 

1.MySQL权限设计

在MySQL最新的版本中，将权限分为：全局级别 > 数据库级别 > 表级别 > 列级别。在用户发起操作数据库请求的时候会鉴别数据库权限，从最大级别往下搜索。

1.1user表

User表示MySQL中最重要的表之一，这个表的权限是全局级别的，只要授权用户对应的权限就是可以对整个MySQL数据库进行操作。User表的属性如下，其中分类用户列，权限列，安全列，资源列。

 

用户列：Host、User、authentication_string分别表示主机IP、用户名称、密码。当用户登录的时候只有这3个值匹配才允许用户连接。

 

权限列：该列用于控制用户的全局级别权限，表示允许用户对MySQL和数据的操作。包括查询、删除、修改、插入、创建表、重启数据库等。权限列的值为ENUM类型，只能输入Y/N。默认都是N就是用户没有全局的任何权限。但是默认情况下root用户是拥有所有权限的，同时root默认是没有密码。

 

安全列：ssl用于加密，x509标准可用于标志用户。其中plugin字段可以用来验证用户身份，如果为空，那么服务器就会使用内部的授权验证机制验证用户身份。show variables like 'have_openssl';语句可以用来查询是否已经开启了ssl。

 

资源列：主要用于限制用户使用的资源。具体作用如下：

1. max_questions：用户每小时允许执行的查询操作次数。
2. max_updates：用户每小时允许执行连接操作次数。
3. max_connections：用户每小时允许执行的连接操作次数。
4. max_user_connections：用户允许同时建立的连接次数。

如果用户的查询或者连接超过次数就会被锁定，等待下一个小时才会释放。

 

1.2db表

Db表的权限是属于数据库级别的，主要是存储用户对某个数据库的权限操作，如果在该表赋值用户权限，那么用户就拥有整个数据库对应的权限。例如赋值用户select_priv权限，那么用户就拥有整个数据库所有表的查询权限。Host、db、user3个字段属于用户列，记录允许那台主机的用户对那个数据库的操作权限，其中权限列就是对应的权限。

 

1.3tables_priv表和columns_priv表

Tables_priv表是表级别的权限控制，如果只期望用户都数据库的某个表有权限，那么只需要在这个表赋值对应的表权限即可。表结构如下：

 

Host、db、user是用户列和db表的介绍一样这里不累赘。Table_name是数据库某个表的名称，table_priv类型是set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')类型，表示用户对这个数据库表的操作权限有哪些。Column_priv类型是set('Select','Insert','Update','References')类型，表示用户对这个表的列的操作权限。

 

Columns_priv表是列级别的权限控制，如果期望用户只对数据库表的某些列有操作权限，那么可以使用该表。这里的字段意思和tables_priv一样就不累赘讲解。

 

1.4procs_priv表

Procs_priv表是针对存储过程和存储函数的权限管理，host、db、user和上面一样的意思，routine_name是存储过程或存储函数的名称，proc_priv是权限一样是set类型。

 

2.账户管理

2.1登录和退出

这里介绍安装了mysql后如何使用cmd命令登录数据库和退出。在操作之前需要了解几个基本的命令：

参数	作用
-h	主机名，默认是localhost
-u	用户名
-p	密码
-P	端口号，默认是3306
-e	执行SQL语句

 

进入mysql安装的bin目录下，执行mysql -u root -p命令就会使用出现Enter password提示，如果没有密码直接回车就可以登录。就可以看到下面的文字提示信息，我们已经进入了mysql的执行命令面板，需要注意mysql命令以“;”表示语句的结束。

 

如果mysql语句执行完毕需要关闭连接，可以输入exit命令。

 

Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.26 MySQL Community Server (GPL)   Copyright (c) 2000, 2019, 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>

 

2.2创建用户

Mysql提供了3种方式创建用户，建议使用grant语句的方式创建，这样比较精确已经错误比较少。

 

创建用户时候的密码在数据库存储是加密的，但是如果用户拥有日志文件的权限，那么就可以通过历史文件查询到用户的密码明文信息。

2.2.1create user语句

Create user详细的官方说明可以看附录一。使用create user创建用户的时候，会修改对应的权限表更改用户的权限。

 

Create user基本语法如下：

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

 

创建一个用户test1，绑定IP为localhost，同时使用IDENTIFIED BY指定密码为test1。创建的这个用户暂时没有任何分配权限。如果创建的用户没有密码，那么可以省略

IDENTIFIED BY 'test1'。

CREATE USER 'test1'@'localhost' IDENTIFIED BY 'test1';

 

在上面的创建语句中，密码是使用明文的方式，如果知道密文也可以使用密文创建密码。由于mysql默认使用password(str)函数加密密码，所以可以使用select password(str);语句查询先获取加密的密文在使用创建语句。

SELECT PASSWORD('test1'); 得到结果：*06C0BF5B64ECE2F648B5F048A71903906BA08E5C 使用密文语法创建用户：CREATE USER 'test1'@'localhost' IDENTIFIED BY PASSWORD '*06C0BF5B64ECE2F648B5F048A71903906BA08E5C';

2.2.2grant语句

Create user语句创建用户实际是在user表添加一条新的记录，但是但是用户是没有任何权限的，需要另外操作给用户授权。Grant语句不仅可以创建用户，同时还可以给用户授权。

 

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

 

创建一个用户test1，绑定用户登录主机IP为localhost，并且赋予select/insert/update/delete权限。用户只能操作permission_test.test1表。

 

如果需要允许用户在不用主机登录localhost替换为%，允许用户操作整个数据库的所有表使用*.*替换permission_test.test1。permission_test.*表示允许用户操作permission_test库的所有表。

GRANT SELECT,INSERT,UPDATE,DELETE ON permission_test.test1 TO 'test1'@'localhost' IDENTIFIED BY 'test1';

2.2.3直接操作mysql表

直接操作mysql表的方式就不多讲解了，只需要在user/db/tables_priv/columns_priv插入对应的记录，授予那种级别的权限就插入到那个表即可。

2.3删除用户

Mysql中使用drop user语句删除用户，也可以通过delete语句从user表中删除用户。但是drop user的方式同时可以删除权限表的数据，数据级联删除。Delete只能删除user表的数据，其他权限表的数据需要单独处理。

 

2.3.1drop user语句删除

删除用户名是test1 绑定的主机是localhost的用户和所有权限。

DROP USER 'test1'@'localhost';

 

Drop user不能关闭已经打开的会话窗口。如果用户在登录状态，那么该命令不会立即生效会等待用户关闭会话后才生效。一旦用户关闭后再次打开就会失败。

2.3.2delete语句删除

Delete语句的删除这里不多介绍，和普通的delete语句是一样的，虽然只删除user表的记录即可，但是最好同时删除其他权限表的记录避免脏数据。

2.4修改密码

2.4.1修改自己密码

Root用户是mysql中权限级别最高的，对于root用户修改密码这里介绍几种常见的方式。

2.4.1.1mysqladmin命令

-h 参数默认是localhost，执行该命令后，mysql会提示输入旧的密码才可以完成。

mysqladmin -u username -h localhost -p password ‘newpassword’

2.4.1.2修改user表

Mysql的密码保存在user表的authentication_string(不同版本字段名称不一样)，可以通过update语句修改改字段，但是需要注意的是密文才可以。密码对应的密文可以使用语句SELECT PASSWORD('test1'); 获取加密后的密文。执行了update后，需要执行FLUSH PRIVILEGES;语句重新加载用户权限。

2.4.1.3set语句修改

Set语句默认就是修改当前用户的密码，而且通用，即使不同版本之间密码保存的字段不一样都可以兼容。执行后，需要执行FLUSH PRIVILEGES;语句重新加载用户权限。

SET PASSWORD = PASSWORD('test1pwd');

2.4.2修改别人密码

2.4.2.1set语句修改

Set语句和上面是一样的，只是修改别人密码的时候需要使用for指定用户和主机。

SET PASSWORD FOR 'test1'@'localhost' = PASSWORD('test1');

 

2.4.2.2修改user表

这个和上面一样，请看2.4.1.2介绍

2.4.2.3grant语句修改密码

Grant usage语句(*.*)指定某个用户的密码而不影响账户当前的权限。建议使用该方法修改密码。

 

修改test1用户主机IP是localhost的密码为test1pwd，这里不需要密文，因为会自动加密。

GRANT USAGE ON *.* TO 'test1'@'localhost' IDENTIFIED BY 'test1pwd';

2.4.3找回root密码

对于mysql如果其他用户密码忘记可以使用root重置密码找回，但是如果root密码也忘记了，那么就比较麻烦，我们可以通过mysql的日志文件找到但是比较麻烦，接下来将介绍一种方法找回root的密码。

 

--skip-grant-tables参数启动mysql的时候不会加载权限判断，任何用户都可以访问数据库。

 

在windows中可以使用mysqld命令启动mysql服务。如果mysql已经添加了环境变量，可以直接双击myslqd.exe文件启动。

 

Cmd命令下，切换到mysql\bin目录

停止服务：net stop mysql

Mysqld命令：mysqld --skip-grant-tables

命令执行后无法输入指令，需要重新找一个打开一个cmd窗口登录root账户。不使用密码：mysql -u root

这时候已经登录进去了，可以使用上面2.4(mysqladmin或者修改user表方式改变密码，不支持set方式)教程修改密码。推荐修改user表方式。

UPDATE mysql.user SET authentication_string = PASSWORD('root') WHERE `User` = 'root' AND `Host` = 'localhost';

然后按正常的模式启动mysql或者先使用FLUSH PRIVILEGES;刷新权限后重启。如果mysql启动不了，那就先停止mysqld命令的进程。

2.5权限管理

2.5.1授权和回收

全局层级：grant all on *.*和revoke all on *.*只授予和撤销全局权限

 

数据库层级：grant all on db_name.*和revoke all on db_name.*只授予和撤销数据库权限

 

表层级：grant all on db_name.tb1_name和revoke all on db_name.tb1_name只授予和撤销表权限

 

列层级：

 

子程序层级：create routine、alter routine、execute和grant权限适用于已存储的子程序。

 

2.5.2查看权限

Show grants for ‘user’@’localhost’;

附录一

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)   auth_option: {     IDENTIFIED BY 'auth_string'   | IDENTIFIED WITH auth_plugin   | IDENTIFIED WITH auth_plugin BY 'auth_string'   | IDENTIFIED WITH auth_plugin AS 'hash_string'   | IDENTIFIED BY PASSWORD 'hash_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 }   The CREATE USER statement creates new MySQL accounts. It enables authentication, SSL/TLS, resource-limit, and password-management properties to be established for new accounts, and controls whether accounts are initially locked or unlocked.   To use CREATE USER, you must have the global CREATE USER privilege, or the INSERT privilege for the mysql system database. When the read_only system variable is enabled, CREATE USER additionally requires the SUPER privilege.   An error occurs if you try to create an account that already exists. If the IF NOT EXISTS clause is given, the statement produces a warning for each named account that already exists, rather than an error.   URL: http://dev.mysql.com/doc/refman/5.7/en/create-user.html

附录二

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 http://dev.mysql.com/doc/refman/5.7/en/account-names.html)   auth_option: {     IDENTIFIED BY 'auth_string'   | IDENTIFIED WITH auth_plugin   | IDENTIFIED WITH auth_plugin BY 'auth_string'   | IDENTIFIED WITH auth_plugin AS 'hash_string'   | IDENTIFIED BY PASSWORD 'hash_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 }   The GRANT statement grants privileges to MySQL user accounts.   To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. When the read_only system variable is enabled, GRANT additionally requires the SUPER privilege.   The REVOKE statement is related to GRANT and enables administrators to remove account privileges. See [HELP REVOKE].   Each account name uses the format described in http://dev.mysql.com/doc/refman/5.7/en/account-names.html. For example:   GRANT ALL ON db1.* TO 'jeffrey'@'localhost';   The host name part of the account, if omitted, defaults to '%'.   Normally, a database administrator first uses CREATE USER to create an account and define its nonprivilege characteristics such as its password, whether it uses secure connections, and limits on access to server resources, then uses GRANT to define its privileges. ALTER USER may be used to change the nonprivilege characteristics of existing accounts. For example:   CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password'; 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;   *Note*:   Examples shown here include no IDENTIFIED clause. It is assumed that you establish passwords with CREATE USER at account-creation time to avoid creating insecure accounts.   *Note*:   If an account named in a GRANT statement does not already exist, GRANT may create it under the conditions described later in the discussion of the NO_AUTO_CREATE_USER SQL mode. It is also possible to use GRANT to specify nonprivilege account characteristics such as whether it uses secure connections and limits on access to server resources.   However, use of GRANT to create accounts or define nonprivilege characteristics is deprecated as of MySQL 5.7.6. Instead, perform these tasks using CREATE USER or ALTER USER.   From the mysql program, GRANT responds with Query OK, 0 rows affected when executed successfully. To determine what privileges result from the operation, use SHOW GRANTS. See [HELP SHOW GRANTS].