目录
一、用户管理
1、用户账号
用户的账号由用户名和host俩部分组成（’username’@’host’）
host的表示：
- 主机名
- 具体ip地址
- 网段/掩码
可以使用通配符表示，%和_；192.168.%即表示这个网段的所有主机
2、增加删除账号
主要：在数据库中修改了用户信息需要执行flush privileges;来刷新授权表使其生效
创建
mariadb [mysql]> create user 'user1'@'192.168.%'; mariadb [mysql]> create user 'user2'@'192.168.%' identified by 'your_password'; mariadb [mysql]> select user,host,password from user; +-------+-----------+-------------------------------------------+ | user | host | password | +-------+-----------+-------------------------------------------+ | root | localhost | *4a54c3f37c03c7fbace31591d6a8c546f93df5c5 | | root | centos7 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | centos7 | | | user1 | 192.168.% | | | user2 | 192.168.% | *9e72259ba9214f692a85b240647c4d95b0f2e08b | +-------+-----------+-------------------------------------------+
删除
mariadb [mysql]> drop user user2@'192.168.%'; mariadb [mysql]> select user,host,password from user; +-------+-----------+-------------------------------------------+ | user | host | password | +-------+-----------+-------------------------------------------+ | root | localhost | *4a54c3f37c03c7fbace31591d6a8c546f93df5c5 | | root | centos7 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | centos7 | | | user1 | 192.168.% | | +-------+-----------+-------------------------------------------+
重命名
mariadb [mysql]> rename user user1@'192.168.%' to testuser@'%'; mariadb [mysql]> select user,host,password from mysql.user; +----------+-----------+-------------------------------------------+ | user | host | password | +----------+-----------+-------------------------------------------+ | root | localhost | *4a54c3f37c03c7fbace31591d6a8c546f93df5c5 | | root | centos7 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | centos7 | | | testuser | % | | +----------+-----------+-------------------------------------------+
修改密码
mariadb [mysql]> set password for testuser@'%' =password('testpass');
mariadb [mysql]> select user,host,password from mysql.user;
+----------+-----------+-------------------------------------------+
| user | host | password |
+----------+-----------+-------------------------------------------+
| root | localhost | *4a54c3f37c03c7fbace31591d6a8c546f93df5c5 |
| root | centos7 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | centos7 | |
| testuser | % | *00e247ac5f9af26ae0194b41e1e769dee1429a29 |
+----------+-----------+-------------------------------------------+
其他修改密码的方法：
update user set password=password('testpass') where user='testuser';
# mysqladmin -uroot -poldpass password 'newpass'
3、破解管理账号密码
空数据库的情况下恢复密码
# systemctl stop mariadb # rm -rf /var/lib/mysql/* #删库跑路 # systemctl start mariadb
有数据的情况下恢复密码
1）在/etc/my.cnf配置文件的[mydqld]下添加skip-grant-tables和skip-networking参数
2）# systemctl restart mariadb 重启服务
3）执行mysql登录到数据库
4）mariadb [(none)]> update mysql.user set password=password(‘newpassword’) where user=’root’ and host=’localhost’; #更新密码
5）mariadb [(none)]> flush privileges; #刷新授权表
6）退出，修改配置文件，删除skip-grant-tables和skip-networking参数，重启服务
也可以在启动mysqld进程时，为其使用如下选项：
–skip-grant-tables
–skip-networking
二、授权管理
1、授权
语法：grant priv_type on [object_type] priv_level to user@’%’ [identified by ‘password’] [with grant option];
授权时如果用户不存在则创建，所以我们一般不会单独去创建一个用户，而是授权创建一块完成。
priv_type 授权类型
– select
– insert
– update
– delete
– create
– drop
– index
– alter
– show databases
– create temporary tables
– lock tables
– create view
– show view
– create user
– all privileges 或 all
object_type 授权对象
– table
– function
– procedure
priv_level 授权级别
– *或*.* 表示所有库
– db_name.* 表示指定库中的所有表
– db_name.tbl_name 指定库中的指定表
– tbl_name 表示当前库的表
– db_name.routine_name 表示指定库的函数,存储过程,触发器
with grant option
– max_queries_per_hour count
– max_updates_per_hour count
– max_connections_per_hour count
– max_user_connections count
mariadb [school]> grant select(stuid,name) on table school.students to admin@'%' identified by 'admin'; #把students表的stuid和name字段的查询权限授权于admin@'%'用户 mariadb [school]> flush privileges; #刷新授权表
2、查询授权
mariadb [school]> show grants for admin@'%'\g #查看指定用户的权限 *************************** 1. row *************************** grants for admin@%: grant usage on *.* to 'admin'@'%' identified by password '*4acfe3202a5ff5cf467898fc58aab1d615029441' *************************** 2. row *************************** grants for admin@%: grant select (stuid, name) on `school`.`students` to 'admin'@'%'
[root@working ~]# mysql -uadmin -padmin -h192.168.0.7 mariadb [(none)]> show grants for current_user()\g #查询自己的权限 *************************** 1. row *************************** grants for admin@%: grant usage on *.* to 'admin'@'%' identified by password '*4acfe3202a5ff5cf467898fc58aab1d615029441' *************************** 2. row *************************** grants for admin@%: grant select (stuid, name) on `school`.`students` to 'admin'@'%'
3、收回授权
mariadb [school]> revoke select(stuid) on school.students from admin@'%'; #收回admin@'%'用户对stuid字段的查询权限
总结
到此这篇关于mysql用户与授权的文章就介绍到这了,更多相关mysql用户与授权内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！