1. 首页
  2. 文章中心
  3. Mysql相关

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

96 阅读 0 评论
我是靠谱客的博主 缓慢戒指，这篇文章主要介绍mysql用户管理(account management)创建/删除/权限配置，现在分享给大家，希望可以做个参考。

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

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

复制代码
1
2
3
4
5
6
7
mysql> select user from mysql.user;
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

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

复制代码
1
2
3
4
5
6
7
8
9
10
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 添加

复制代码
1
2
3
4
5
6
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 刷新系统权限表

复制代码
1
mysql> FLUSH PRIVILEGES;

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

step3 查看

复制代码
1
2
3
4
5
6
7
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 登录

复制代码
1
2
3
4
mysql> exit
Bye
# mysql -utest1 -p1234
Welcome to the MySQL monitor.  Commands end with ; or g.

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

复制代码
1
2
3
4
5
6
7
8
9
10
11
remote login failed
$ mysql -h myhostip -u root -p
Enter 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的方法添加

复制代码
1
2
3
4
5
6
7
8
9
10
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上面依然无法连接本机的数据库.

复制代码
1
2
3
$ mysql -h myhostip -u test2 -p
Enter 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

复制代码
1
2
3
4
5
6
7
8
< #skip-external-locking
---
> skip-external-locking
47,48c47
< #bind-address     = 127.0.0.1
< #bind-address           = 0.0.0.0
---
> bind-address      = 127.0.0.1

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

方法三: 利用create user来创建

复制代码
1
2
mysql> create user 'test3'@'localhost' identified by '1234';
Query OK, 0 rows affected (0.00 sec)

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

复制代码
1
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

PrivilegePrivilege
ALL [PRIVILEGES]Grant all privileges at specified access level except GRANT OPTION and PROXY.
ALTEREnable use of ALTER TABLE. Levels: Global, database, table.
ALTER ROUTINEEnable stored routines to be altered or dropped. Levels: Global, database, procedure.
CREATEEnable database and table creation. Levels: Global, database, table.
CREATE ROUTINEEnable stored routine creation. Levels: Global, database.
CREATE TABLESPACEEnable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
CREATE TEMPORARY TABLESEnable use of CREATE TEMPORARY TABLE. Levels: Global, database.
CREATE USEREnable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global.
CREATE VIEWEnable views to be created or altered. Levels: Global, database, table.
DELETEEnable use of DELETE. Level: Global, database, table.
DROPEnable databases, tables, and views to be dropped. Levels: Global, database, table.
EVENTEnable use of events for the Event Scheduler. Levels: Global, database.
EXECUTEEnable the user to execute stored routines. Levels: Global, database, table.
FILEEnable the user to cause the server to read or write files. Level: Global.
GRANT OPTIONEnable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure, proxy.
INDEXEnable indexes to be created or dropped. Levels: Global, database, table.
INSERTEnable use of INSERT. Levels: Global, database, table, column.
LOCK TABLESEnable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database.
PROCESSEnable the user to see all processes with SHOW PROCESSLIST. Level: Global.
PROXYEnable user proxying. Level: From user to user.
REFERENCESEnable foreign key creation. Levels: Global, database, table, column.
RELOADEnable use of FLUSH operations. Level: Global.
REPLICATION CLIENTEnable the user to ask where master or slave servers are. Level: Global.
REPLICATION SLAVEEnable replication slaves to read binary log events from the master. Level: Global.
SELECTEnable use of SELECT. Levels: Global, database, table, column.
SHOW DATABASESEnable SHOW DATABASES to show all databases. Level: Global.
SHOW VIEWEnable use of SHOW CREATE VIEW. Levels: Global, database, table.
SHUTDOWNEnable use of mysqladmin shutdown. Level: Global.
SUPEREnable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global.
TRIGGEREnable trigger operations. Levels: Global, database, table.
UPDATEEnable use of UPDATE. Levels: Global, database, table, column.
USAGESynonym for “no privileges”

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

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Global Privileges
grant all on *.* to 'someuser'@'somehost';
授权test用户拥有所有数据库的所有权限.
grant select,delete,update,create,drop on *.* to 'someuser'@'somehost';
test用户对所有数据库都有select,delete,update,create,drop 权限。

Database Privileges
GRANT ALL ON mydb.* TO 'someuser'@'somehost';
授权test用户拥有testDB数据库的所有权限
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
授权test用户拥有testDB数据库的某些权限

下面的以此类推: 
Table Privileges
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
Column Privileges
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

Stored Routine Privileges
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
Proxy User Privileges
GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';

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

四: 用户密码重置

复制代码
1
2
3
CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password;
SET old_passwords = 0;
SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');

五: 用户重命名

复制代码
1
rename user  'test'@'localhost' to 'testnew'@'hostname';

六: 删除用户名

复制代码
1
2
3
4
5
6
方法一:
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' ;

最后

以上就是缓慢戒指最近收集整理的关于mysql用户管理(account management)创建/删除/权限配置的全部内容，更多相关mysql用户管理(account内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(64)

相关文章

User Account Management User Account Management
User Account Management User Account Management
IAM最佳实践
IAM最佳实践
springboot整合springsecurity+oauth2.0密码授权模式springboot整合springsecurity+oauth2.0
springboot整合springsecurity+oauth2.0密码授权模式springboot整合springsecurity+oauth2.0
保护特权用户帐户的九个最佳实践
保护特权用户帐户的九个最佳实践
mysql用户管理(account management)创建/删除/权限配置
mysql用户管理(account management)创建/删除/权限配置
JAVA类和对象简单练习之用户密码管理
JAVA类和对象简单练习之用户密码管理
18.Spring Boot 2.x 最佳实践之 Java Mail Sender集成
18.Spring Boot 2.x 最佳实践之 Java Mail Sender集成
如何查看服务器账户密码信息文件,如何查询服务器的数据库账号密码
如何查看服务器账户密码信息文件,如何查询服务器的数据库账号密码

评论列表共有 0 条评论

立即
投稿
返回
顶部