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字段的查询权限
版权声明：本文为yaun1498078591原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。