代做毕设网站可信么,客村网站建设,二手房网站怎么做才能接到电话,新的数据新闻目录 MySQL用户授权
一#xff0c;密码策略
1#xff0c;查看临时密码
2#xff0c;查看数据库当前密码策略#xff1a;
二#xff0c; 用户授权和撤销授权
1、创建用户
2#xff0c;删除用户
3#xff0c;授权和回收权限 MySQL用户授权
一#xff0c;密码策略…目录 MySQL用户授权
一密码策略
1查看临时密码
2查看数据库当前密码策略
二 用户授权和撤销授权
1、创建用户
2删除用户
3授权和回收权限 MySQL用户授权
一密码策略
mysql刚安装时临时密码会存储在 /var/log/mysqld.log 1查看临时密码
方法一直接给出密码
[rootlocalhost ~]# awk /temporary password/ {print $NF} /var/log/mysqld.log*2D):irrJ_!_ 方法二查到的语句的最后为密码
[rootlocalhost ~]# grep password /var/log/mysqld.log2023-02-07T13:07:17.842453Z 6 [Note] [MY-010454] [Server] A temporary password is generated for rootlocalhost: *2D):irrJ_!_ 2查看数据库当前密码策略
mysql8.0.30 [(none)]show VARIABLES like%password%; 二 用户授权和撤销授权
MySql8有新的安全要求不能像之前的版本那样一次性创建用户并授权。需要先创建用户再进行授权操作。
mysql8.0.30 [(none)]grant all privileges on *.* to xiaoming%;
ERROR 1410 (42000): You are not allowed to create a user with GRANT 1、创建用户 创建新用户语法create user usernamehost identified by password; 说明username为自定义的用户名host为客户端的域名或者IP如果host为%时表示为任意IPpassword为密码。 1创建一个用户名为xiaoming客户端的ip为任意密码为Guest123!的新用户
mysql8.0.30 [(none)]create user xiaoming% identified by Guest123!;Query OK, 0 rows affected (0.01 sec) 2查看刚创建的的用户信息
mysql8.0.30 [(none)]select user,host from mysql.user;-----------------------------| user | host |-----------------------------| root | % || xiaoming | % || mysql.infoschema | localhost || mysql.session | localhost || mysql.sys | localhost |-----------------------------5 rows in set (0.00 sec) 3使用xiaoming用户登录一下数据库
[rootlocalhost ~]# mysql -uxiaoming -pGuest123!;mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 12Server version: 8.0.32 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or \h for help. Type \c to clear the current input statement. 4使用xiaoming用户查看所有的数据库
mysql8.0.30 [(none)]show databases;--------------------| Database |--------------------| information_schema || performance_schema |--------------------2 rows in set (0.01 sec) 5也可以在windows端登录xiaoming用户 2删除用户
1删除用户名为xiaoming客户端的ip为任意密码为Guest123!的用户
mysql8.0.30 [(none)]drop user xiaoming%;Query OK, 0 rows affected (0.01 sec) 2删除后的user表中已经没了xiaoming的信息
mysql8.0.30 [(none)]select user,host from mysql.user;-----------------------------| user | host |-----------------------------| root | % || mysql.infoschema | localhost || mysql.session | localhost || mysql.sys | localhost |-----------------------------4 rows in set (0.00 sec) 注意如果删除用户时显示如下提示 mysql8.0[mysql]drop user xiaoming; ERROR 1227(42000): Access denied; you need (atleast oneof) the SYSTEM_USER privilege(s) forthis operation 需要执行该语句 【mysql8.0 [(none)]grant system_user on *.* to root%;】 3授权和回收权限 授予权限的原则
1只授予能满足需要的最小权限 防止用户干坏事。比如用户只是需要查 询那就只给 select 权限就可以了不要给用户赋予update 、 insert 或 者 delete 权限
2创建用户的时候限制用户的登录主机 一般是限制成指定 IP 或者内网 IP 段。
3为每个用户设置满足密码复杂度的密码 。
4定期清理不需要的用户 回收权限或者删除用户。 授权语法grant 权限列表 on 库名.表名 to 用户名主机 [with GRANT option]; 没有with GRANT option,被授权者无法授权于下一个人用户 mysql用户常用权限列表 说明 all 或者all privileges 授予用户所有权限 create 授予用户创建新数据库和表的权限 drop 授予用户删除数据库和表的权限 delete 授予用户删除表中的行的权限 alter 授予用户修改表结构的权限 insert 授予用户在表中插入行add的权限 select 授予用户运行select命令以从表中读取数据的权限 update 授予用户更新表中的数据的权限 查看授予用户的权限的四种方法
1 查看root用户的权限方法一
mysql8.0.30 [(none)]show grants;--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Grants for root% |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO root% WITH GRANT OPTION |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec) 2查看xiaoming用户的权限方法二
mysql8.0.30 [(none)]show grants for xiaoming%;--------------------------------------| Grants for xiaoming% |--------------------------------------| GRANT USAGE ON *.* TO xiaoming% |--------------------------------------1 row in set (0.00 sec) 3mysql8.0[mysql]select* from mysql.user;方法三 4方法四 授权操作
不具有授予下一个人权限的权利的授权
1此时xiaoming用户可以访问以下两个数据库须授予其他数据库的访问权限才能对其 它数据库进行操作
mysql8.0.30 [(none)]show databases;--------------------| Database |--------------------| information_schema || performance_schema |--------------------2 rows in set (0.01 sec) 2给xiaoming用户授予所有权限
*.*中第一个*表示所有数据库第二个*表示所有数据表
mysql8.0.30 [(none)]grant all privileges on *.* to xiaoming%;Query OK, 0 rows affected (0.01 sec) 3此时xiaoming用户拥有和root用户一样的权限可以访问其它数据库的权限
mysql8.0.30 [(none)]show databases;--------------------| Database |--------------------| chap03 || information_schema || mysql || performance_schema || sys |--------------------5 rows in set (0.00 sec) 4此时的xiaoming用户没有赋予下一个人权限的权利
mysql8.0.30 [(none)]grant all privileges on *.* to xiaohei%;ERROR 1045 (28000): Access denied for user xiaoming% (using password: YES)mysql8.0.30 [(none)] 具有授予下一个人权限的权利的授权
1重新授予xiaoming用户授予其他用户权限的权利
mysql8.0.30 [(none)]grant all privileges on *.* to xiaoming% with GRANT option;Query OK, 0 rows affected (0.00 sec) 2使用xiaoming用户为xiaohei 用户授权
mysql8.0.30 [(none)]grant all privileges on *.* to xiaohei%;Query OK, 0 rows affected (0.01 sec) 3此时xiaohei用户拥有访问所有数据库的权利
mysql8.0.30 [(none)]select user(); 查看当前的用户-------------------| user() |-------------------| xiaoheilocalhost |-------------------1 row in set (0.00 sec)mysql8.0.30 [(none)]show databases; 此用户可以访问以下数据库的权利--------------------| Database |--------------------| chap03 || information_schema || mysql || performance_schema || sys |--------------------5 rows in set (0.00 sec) 收回权限 收回权限(不包含赋权权限) REVOKE ALL PRIVILEGES ON *.* FROM username; 收回赋权权限 REVOKE GRANT OPTION ON *.* FROM username; 收回赋权权限
1收回xiaoming用户授予其他用户权限的权利
mysql8.0.30 [(none)]revoke grant option on *.* from xiaoming;Query OK, 0 rows affected (0.00 sec) 2此时xiaoming用户不在拥有授予其他用户的权限
mysql8.0.30 [(none)]grant all privileges on *.* to xiaohei%;ERROR 1045 (28000): Access denied for user xiaoming% (using password: YES)mysql8.0.30 [(none)] 收回权限(不包含赋权权限)
1收回xiaoming用户的所有权限
mysql8.0.30 [(none)]revoke all privileges on *.* from xiaoming%;Query OK, 0 rows affected (0.00 sec) 2此时xiaoming用户不在拥有访问其它数据库的权限
mysql8.0.30 [(none)]show databases;--------------------| Database |--------------------| information_schema || performance_schema |--------------------2 rows in set (0.00 sec)
