latte Studio

mysql用户管理与权限管理

 运维/数据库  2170  5 分钟  

用户管理

用户格式

mysql的用户格式：用户名@'允许该用户可以用来远程登陆的主机ip或主机名'

如：

root@'localhost' 允许root用户本机登陆
root@'192.168.80.%' 允许root用户在80网段的任务ip的主机上，发起登陆请求
root@'%' 允许root用户在任何地址的主机上登陆

# 一般root用户要禁止远程ip登陆，只允许本机登陆，为了安全
# root是mysql实例的管理员用户，对于各个业务库一般都会建立业务用的用户
# 对于各个业务库才针对需要放行特定的ip和用户名登陆；

权限管理

相关表

mysql库是mysql进行权限管理的元数据库；
其中user表是用户信息相关的表
其中用户名，主机，加密后的密码字符串，为创建用户时需要指定的3个字段；
以下默认的值表示，只有root用户存在，且root用户只能在本机登陆，

MariaDB [mysql]> select user,host,authentication_string from user;
+------+-----------+-----------------------+
| user | host      | authentication_string |
+------+-----------+-----------------------+
| root | localhost |                       |
| root | 127.0.0.1 |                       |
| root | ::1       |                       |
+------+-----------+-----------------------+
3 rows in set (0.00 sec)

创建用户

1、创建用户

MariaDB [mysql]> create user user1;
Query OK, 0 rows affected (0.00 sec)

2、给用户授权，

MariaDB [mysql]> grant all on *.* to user1@'192.168.80.%' identified by 'linux';
Query OK, 0 rows affected (0.00 sec)
# 授权给user1用户所有库的所有表的（*.*）所有权限(all)，并且user1用户在80网段的主机上可以登陆，密码是linux

MariaDB [mysql]> select user,host,authentication_string from user;
+-------+--------------+-----------------------+
| user  | host         | authentication_string |
+-------+--------------+-----------------------+
| root  | localhost    |                       |
| user1 | 192.168.80.% |                       |
| root  | 127.0.0.1    |                       |
| root  | ::1          |                       |
| user1 | %            |                       |
+-------+--------------+-----------------------+

3、访问登陆

# 在102主机上通过网络登陆访问；

[root@host3 mariadb-10.2.25]# mysql -uuser1 -h192.168.80.101 -plinux
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.25-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

4、创建用户授权语句合并；

​ 先建用户再授权，和建立授权合并的，在user表中数据不一致，一般采用后者；

MariaDB [mysql]> grant all on *.* to user2@'192.168.80.%' identified by 'linux';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *13FDE0EB3CE09F9A02F5F02C7FF07FD6F27B0687 |
| user1 | 192.168.80.% | *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| root  | 127.0.0.1    | *13FDE0EB3CE09F9A02F5F02C7FF07FD6F27B0687 |
| root  | ::1          | *13FDE0EB3CE09F9A02F5F02C7FF07FD6F27B0687 |
| user1 | %            |                                           |
| user2 | 192.168.80.% | *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
+-------+--------------+-------------------------------------------+
6 rows in set (0.00 sec)

修改密码3种方法

1、set password命令：sql命令行

MariaDB [mysql]> set password for user2@'192.168.80.%' = password('wang');
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *13FDE0EB3CE09F9A02F5F02C7FF07FD6F27B0687 |
| user1 | 192.168.80.% | *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| root  | 127.0.0.1    | *13FDE0EB3CE09F9A02F5F02C7FF07FD6F27B0687 |
| root  | ::1          | *13FDE0EB3CE09F9A02F5F02C7FF07FD6F27B0687 |
| user1 | %            |                                           |
| user2 | 192.168.80.% | *4DB9D7B9A1EE2F9646C3A6A1CC9A37942588BFBF |
+-------+--------------+-------------------------------------------+

2、update命令修改权限表中对应的字段值！sql命令行

MariaDB [mysql]> update mysql.user set password=password('shuai') where user='user2' and host='192.168.80.%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> select user,host,password from user;
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *13FDE0EB3CE09F9A02F5F02C7FF07FD6F27B0687 |
| user1 | 192.168.80.% | *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| root  | 127.0.0.1    | *13FDE0EB3CE09F9A02F5F02C7FF07FD6F27B0687 |
| root  | ::1          | *13FDE0EB3CE09F9A02F5F02C7FF07FD6F27B0687 |
| user1 | %            |                                           |
| user2 | 192.168.80.% | *FAD7DBEB59388700AB165A70781CC75F3C75A91B |
+-------+--------------+-------------------------------------------+
6 rows in set (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec

3、mysqladmin命令：shell命令行

[root@host3 mariadb-10.2.25]# mysqladmin -h192.168.80.101 -uuser2 -pwang password 'shuai'
# mysqladmin -hIP可以修改远程的mysql密码 -u要修改的用户名 -p现在用户的密码 password '要修改成的新密码'

[root@host3 mariadb-10.2.25]# mysql -uuser2 -h192.168.80.101 -pshuai
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.2.25-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

mysql忘记root管理员密码

1、修改配置文件

[root@host2 ~]# mysql -uroot -pwfasdf
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
# 此时假设忘记密码

# 添加如下2个参数；然后重启；
[root@host2 ~]# vim /etc/my.cnf
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-grant-tables=1
# 跳过权限表的检查，即可以免密登陆
skip-networking=1
# 由于免密登陆，所以加上此项，禁止网段的连接，此时不可以从网络连接到数据库

[root@host2 ~]# service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]

2、重启后，本机免密进入，修改为新密码

[root@host2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.25-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> set password for 'root'@'localhost' = password('yes');
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

MariaDB [(none)]> update mysql.user set password=password('yes') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 此时直接免密登陆，仅限本机
# 此时set password命令不可以用，可以用update命令
# 修改后，刷新权限，使之生效

3、去除第一步添加的配置选项，重启，继续使用

# 注释掉刚2个参数，重启
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
#skip-grant-tables=1
#skip-networking=1

[root@host2 ~]# service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]

# 此时不可以免密登陆，远程也可以继续登陆
[root@host2 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# 密码，已经改为新密码
[root@host2 ~]# mysql -uroot -pyes
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.25-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

授权

https://mariadb.com/kb/en/grant/

MariaDB [mysql]> help grant;
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]
...

priv_type时权限类型，如update insert select等，all代表所有类型的操作权限；
column_list 表示的是权限加到哪些列上，不加即为所有列
object_type有三种，table，procedure，function
priv_level 即权限的授予范围，如*.* 表示所有库的所有表，db1.* db1库下的所有表，db1.tb1 db1库下的tb1表；

回收权限

URL: https://mariadb.com/kb/en/revoke/

MariaDB [mysql]> help revoke;
Name: 'REVOKE'
Description:
Syntax:
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...
和grant相反

查看某用户授权

# 查看user2@‘192.168.80.%’用户的授权
MariaDB [mysql]> show grants for user2@'192.168.80.%';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.80.%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user2'@'192.168.80.%' IDENTIFIED BY PASSWORD '*FAD7DBEB59388700AB165A70781CC75F3C75A91B' |
+--------------------------------------------------------------------------------------------------------------------------+

# 利用内置函数，查看当前用户授权信息
MariaDB [mysql]> show grants for current_user;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*293C17197B3150952CF285A2AD56017E97CA787C' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+

# mysqld进程启动时会加载所有权限相关的表；
# 修改权限后，一般要flush privileges重载权限表，使之生效！

权限类别

priv_types代表权限类别，all代表所有类型的权限，也可以精确的只授权需要的类型，只授权创建表授权，只授权查看表的权限等；

管理类

  • create user
  • create tempprary tables
  • show databases
  • shutdown
  • replication slave
  • replication client
  • ...

程序类

  • create
  • alter
  • drop
  • excute

库和表

  • create
  • alter
  • drop
  • index
  • show view
  • grant option，自己有权限的相关对象，可以在自己的权限范围内再授予他人

字段操作

  • select
  • update
  • drop
  • delete
updatedupdated2021-03-092021-03-09

相关文章：

加载评论