mysql用户与授权
视图
create view 视图名 as 查询语句；
MariaDB [hellodb]> create view view_left as select name from students; Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> create view view_students as select stuid,name from students;Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> select * from view_students; +-------+---------------+ | stuid | name | +-------+---------------+ | 1 | Shi Zhongyu | | 2 | Shi Potian | | 3 | Xie Yanke | | 4 | Ding Dian | | 5 | Yu Yutong | | 6 | Shi Qing | | 7 | Xi Ren | | 8 | Lin Daiyu | | 9 | Ren Yingying | | 10 | Yue Lingshan | | 11 | Yuan Chengzhi | | 12 | Wen Qingqing | | 13 | Tian Boguang | | 14 | Lu Wushuang | | 15 | Duan Yu | | 16 | Xu Zhu | | 17 | Lin Chong | | 18 | Hua Rong | | 19 | Xue Baochai | | 20 | Diao Chan | | 21 | Huang Yueying | | 22 | Xiao Qiao | | 23 | Ma Chao | | 24 | Xu Xian | | 25 | Sun Dasheng | +-------+---------------+ 25 rows in set (0.00 sec)
用户管理
1、用户账号
用户的账号由用户名和HOST俩部分组成（'USERNAME'@'HOST'）
HOST的表示：
-
主机名
- 具体IP地址
-
网段/掩码
可以使用通配符表示，%和_；192.168.%即表示这个网段的所有主机
2、增加删除账号
主要：在数据库中修改了用户信息需要执行
FLUSH PRIVILEGES;来刷新授权表使其生效
- 创建
create user '用户名'@'主机名' identified by '密码';
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 | +-------+-----------+-------------------------------------------+
- 删除
drop user '用户名'@'主机名';
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 all on 库名.标名(*表示所有) to 用户名@'主机名(%所有)' identified by '密码';
授权时如果用户不存在则创建，所以我们一般不会单独去创建一个用户，而是授权创建一块完成。
- 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字段的查询权限
