mysql用户管理(account management)创建/删除/权限配置

本文将比较全面详细的介绍mysql数据库上面关于user的各种设置.

一: 查看当前所有用户list以及状态

mysql> select user from mysql.user;+----------------+| user()         |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)

查看具体某个用户的权限:

mysql> select * from user where user = 'root';+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       || ubuntu    | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       || 127.0.0.1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       || ::1       | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+4 rows in set (0.00 sec)

二: 添加新用户
方法一: 利用insert into user方式添加
step1 添加

mysql>use mysql;mysql> insert into user(host,user,password) values("localhost","test1",password("1234"));Query OK, 1 row affected, 3 warnings (0.00 sec)或者mysql> insert into **mysql.**user(host,user,password) values("localhost","test1",password("1234"));Query OK, 1 row affected, 3 warnings (0.00 sec)

注意：此处的”localhost”，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将”localhost”改为”%”，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录,例如某个网段192.168.1.%.

step2 刷新系统权限表

mysql> FLUSH PRIVILEGES;

注意：在注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。否则无法使用新创建的用户来连接mysql服务器，除非你重启mysql服务器。

step3 查看

mysql> select host,user,password from user where user = 'test1';+-----------+-------+-------------------------------------------+| host      | user  | password                                  |+-----------+-------+-------------------------------------------+| localhost | test1 | *A4B6157319038724E3560894F7F932C8886EBFCF |+-----------+-------+-------------------------------------------+1 row in set (0.00 sec)

注意: 在添加用户时，请注意使用MySQL提供的 PASSWORD() 函数来对密码进行加密。 你可以在以上实例看到用户密码加密后为：那一个长串.

step4 登录

mysql> exitBye# mysql -utest1 -p1234Welcome to the MySQL monitor.  Commands end with ; or \g.

注意: 此时该用户仅能在本机(localhost)上使用密码登录, 无法远程登录. 并且很多其他的权限也是N的状态, 需要后面用grant来添加权限.

remote login failed$ mysql -h myhostip -u root -pEnter password: ERROR 1045 (28000): Access denied for user 'test1'@'romote server' (using password: YES)mysql> select * from user where user = 'test1';+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+| Host      | User  | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+| localhost | test1 | *A4B6157319038724E3560894F7F932C8886EBFCF | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+1 row in set (0.00 sec)

方法二: 利用grant的方法添加

mysql> grant all privileges on *.* to test2@'%' identified by '1234';Query OK, 0 rows affected (0.00 sec)mysql> select * from user where user = 'test2';+------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+| Host | User  | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |+------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+| %    | test2 | *A4B6157319038724E3560894F7F932C8886EBFCF | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |+------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+1 row in set (0.00 sec)

注意:
对所有库的所有表赋予了全部权限,不需要使用flush privilege刷新系统权限表，改用户立即生效。
但是我后面发现虽然赋予了权限, 可是我在其他的ubuntu上面依然无法连接本机的数据库.

$ mysql -h myhostip -u test2 -pEnter password: ERROR 2003 (HY000): Can't connect to MySQL server on 'myhostip' (111)

后面发现了问题所在, 我需要my.cnf文件进行修改. Grant remote access privileges
https://stackoverflow.com/questions/1420839/cant-connect-to-mysql-server-error-111

< #skip-external-locking---> skip-external-locking47,48c47< #bind-address     = 127.0.0.1< #bind-address           = 0.0.0.0---> bind-address      = 127.0.0.1

修改过后重启mysql, 更新配置. 现在就可以了.

方法三: 利用create user来创建

mysql> create user 'test3'@'localhost' identified by '1234';Query OK, 0 rows affected (0.00 sec)

这种方法创建出来的用户的权限和方法一中的是一样的.

grant all privileges on *.* to test3@'localhost' identified by '1234';

后面发现如果你想更改用户权限,用grant的时候要注意test3@’localhost’ identified by ‘1234’;
的部分钥匙和原来的一样的,否则你会发现你创建了两个名叫test3的不同权限的用户.

三: 用户的权限添加与删除
usefull reference：
https://dev.mysql.com/doc/refman/5.5/en/grant.html 官方文件关于各个权限的列表.
所有权限列表 Permissible Privileges for GRANT and REVOKE
所有权限列表 Permissible Privileges for GRANT and REVOKE

Privilege Privilege 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, procedure. 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, table. 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, procedure, 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 master or slave servers are. Level: Global. REPLICATION SLAVE Enable replication slaves to read binary log events from the master. 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”

关于用户权限的添加有很多种, 如下我就按照官方文件的分类来解释:

Global Privilegesgrant all on *.* to 'someuser'@'somehost';授权test用户拥有所有数据库的所有权限.grant select,delete,update,create,drop on *.* to 'someuser'@'somehost';test用户对所有数据库都有select,delete,update,create,drop 权限。Database PrivilegesGRANT ALL ON mydb.* TO 'someuser'@'somehost';授权test用户拥有testDB数据库的所有权限GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';授权test用户拥有testDB数据库的某些权限下面的以此类推: Table PrivilegesGRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';Column PrivilegesGRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';Stored Routine PrivilegesGRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';Proxy User PrivilegesGRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';

至于取消用户的权限的话, grant和 revoke的格式是相同的, 只需要把原来句式中的grant换成revoke就好了.

四: 用户密码重置

CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password;SET old_passwords = 0;SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');

五: 用户重命名

rename user  'test'@'localhost' to 'testnew'@'hostname';

六: 删除用户名

方法一:drop user 'test4'@'localhost';mysql> select * from user where user = 'test4';Empty set (0.00 sec)方法二:delete from user where user='test4' and host='localhost' ;