mysql用户管理与权限管理

用户管理

用户格式

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
加载评论