用户和权限的管理

最新推荐文章于 2024-05-01 12:19:18 发布

miner_k

最新推荐文章于 2024-05-01 12:19:18 发布

阅读量712

点赞数

分类专栏： ……【mysql】 MySQL

本文链接：https://blog.csdn.net/miner_k/article/details/80866092

版权

……【mysql】同时被 2 个专栏收录

17 篇文章 0 订阅

订阅专栏

MySQL

17 篇文章 0 订阅

订阅专栏

MySQL的用户

用户：MySQL的服务的用户类似于VsFTPD的用户，只属于MySQL服务，不能用MySQL用户登录服务器，也不能使用服务器的用户登录MySQL
密码：是由MySQL自有的加密函数password()加密

组成：用户名@主机
作用：数据库登录认证

用户账户

用户名@主机名

用户名：16字符以内
主机：
主机名：www.miner-k.com
IP:172.16.10.177
网络地址：
172.16.0.0/255.255.0.0
通配符：%
172.16.%.%

—skip-name-resolve 启动服务的时候设置不需要解析。

用户权限管理

涉及到数据的的表

表名	作用
user	用户账号、全局权限
db	库级别的权限
tables_priv	表级别权限
colums_priv	字段（列）级别权限
procs_priv	存储过程和存储函数相关的权限
proxies_priv	代理用户权限

Privilege	Column	Context（作用范围）
ALL [PRIVILEGES]	Synonym for “all privileges”	Server administration
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables 临时表默认是内存中。内存表的存储空间是有限的16M.
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines 存储函数、存储过程
FILE	File_priv	File access on server host 将表中的数据备份到文件中，或者将文件中的数据导入表中。
GRANT OPTION	Grant_priv	Databases, tables, or stored routines 一个用户获取授权之后能不能再将授权分配给其他用户。
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables 触发器，
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for “no privileges”	Server administration

创建用户

方法一：CREATE USER

语法格式：

CREATE USER
    username@host  [IDENTIFIED BY PASSWORD]

创建用户的实例

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

测试创建用户的权限

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.01 sec)

mysql> create database abc;
ERROR 1044 (42000): Access denied for user 'tom'@'localhost' to database 'abc'

方法二：GRANT

mysql> grant all privileges on student.* to 'jim'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

方法三： INSERT INTO mysql.user

用户授权以及测试

语法格式：

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

priv_type：{
    ALL PRIVILEGES  所有权限
    SUPER  用户管理权限
    CREATE 创建数据库、表等权限

}
object_type: {
    TABLE       表
  | FUNCTION   存储函数
  | PROCEDURE   存储过程
}

priv_level: {
    *   所有的数据库
  | *.*   所有数据库的所有表、存储过程、函数
  | db_name.*   指定数据库下的所有表、存储过程、函数
  | db_name.tbl_name  指定数据库的指定的表。
  | tbl_name  特定的表
  | db_name.routine_name  指定数据库的存储过程。
如果存储过程、存储函数、表名重复可以通过object_type指定是对表还是存储函数、存储过程进行限制
}

mysql> create user 'zabbix'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

查看创建用户的的授权信息

mysql> show grants for 'zabbix'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for zabbix@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zabbix'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

测试：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.04 sec)

mysql> create database testdb;
ERROR 1044 (42000): Access denied for user 'zabbix'@'%' to database 'testdb'

总结。只有查看基本数据库的权限，create user 命令其实类似于grant命令授权usage权限。

授权用户创建表、库的权限

mysql> grant create on zabbix.* to 'zabbix'@'%';
Query OK, 0 rows affected (0.00 sec)

测试：


mysql> create database zabbix;
Query OK, 1 row affected (0.03 sec)

mysql> use zabbix;
Database changed
mysql> create table tb1 (id int,name char(30),age tinyint);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tb1 values(1,'jim',23);
ERROR 1142 (42000): INSERT command denied to user 'zabbix'@'localhost' for table 'tb1'

总结，只能创建授权的库以及库中的表，不能直接插入语句。

授权插入数据的权限

mysql> grant insert on zabbix.* to 'zabbix'@'%';
Query OK, 0 rows affected (0.00 sec)

测试;

mysql> insert into tb1 values(1,'jim',23);
Query OK, 1 row affected (0.03 sec)

总结，测试的时候需要重新连接会话，否则会插入数据失败。

显示所有的插入数据

mysql> grant select  on zabbix.* to 'zabbix'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'zabbix'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for zabbix@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zabbix'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, CREATE ON `zabbix`.* TO 'zabbix'@'%'                                            |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

测试

mysql> select * from tb1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | jim  |   23 |
+------+------+------+
1 row in set (0.00 sec)

授权用户修改字段的权限

mysql> grant update (age)  on zabbix.tb1 to 'zabbix'@'%';
Query OK, 0 rows affected (0.00 sec)

测试

mysql> update tb1 set age=33 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

显示数据库中所有的用户

mysql> select User,Host,Password from mysql.user;
+--------+-----------------------+-------------------------------------------+
| User   | Host                  | Password                                  |
+--------+-----------------------+-------------------------------------------+
| root   | localhost             | *565B1B47FD7BC0488435D2B707071F5EF873197B |
| root   | localhost.localdomain | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root   | 127.0.0.1             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root   | ::1                   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| tom    | localhost             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| jim    | %                     | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| zabbix | %                     | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+--------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)

显示当前用户的授权情况

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*565B1B47FD7BC0488435D2B707071F5EF873197B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

显示指定用户的授权情况

mysql> show grants for 'jim'@'%';
+----------------------------------------------------------------------------------------------------+
| Grants for jim@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT ALL PRIVILEGES ON `student`.* TO 'jim'@'%'                                                   |
| GRANT ALL PRIVILEGES ON `students`.* TO 'jim'@'%'                                                  |
+----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

删除用户

mysql> drop user tom@localhost;
Query OK, 0 rows affected (0.00 sec)

修改用户名

mysql> rename user 'jim'@'%' to 'jack'@'%';
Query OK, 0 rows affected (0.00 sec)

取消授权

语法格式：

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

取消select权限

mysql> show grants for 'zabbix'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for zabbix@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zabbix'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, CREATE ON `zabbix`.* TO 'zabbix'@'%'                                            |
| GRANT UPDATE (age) ON `zabbix`.`tb1` TO 'zabbix'@'%'                                                  |
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> revoke select on zabbix.* from 'zabbix'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'zabbix'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for zabbix@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zabbix'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT INSERT, CREATE ON `zabbix`.* TO 'zabbix'@'%'                                                    |
| GRANT UPDATE (age) ON `zabbix`.`tb1` TO 'zabbix'@'%'                                                  |
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

修改root用户的密码

方法一：修改mysql的启动脚本

关闭数据库
修改启动脚本
```
vim /etc/init.d/mysqld
```
–skip-grant-tables 跳过密码
–skip-networking 禁止网络连接
重启启动mysqld

修改密码

mysql> update mysql.user  set password= password('12345') where user='root';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

修改配置文件，重启mysqld

方法二：修改配置文件/etc/my.cnf

参考链接

mysql官网的权限管理

miner_k

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
用户和权限的管理

MySQL的用户用户：MySQL的服务的用户类似于VsFTPD的用户，只属于MySQL服务，不能用MySQL用户登录服务器，也不能使用服务器的用户登录MySQL 密码：是由MySQL自有的加密函数password()加密组成：用户名@主机作用：数据库登录认证用户账户用户名@主机名用户名：16字符以内主机：主机名：www.miner-k.com IP:172....
复制链接

扫一扫