mysql第一天

1、用户与权限管理

1.1.用户的创建/修改/删除

开启mysql服务

service mysql start/stop

查看mysql进程是否启动成功

ps -ef | grep 3309  

登录mysql服务器：

mysql -h hostname -P port -u username -p DatabaseName -e "SQL语句"；

查看用户名和主机

select host,user from mysql.user;#mysql数据管理系统安装后会自动内置一个mysql库，包含用户，密码管理等各种信息；

如果误删user表：MYSQL无备份情况下恢复误删除的user权限表 

第一种情况：mysql服务未关闭即进程是启动的，从从其他的运行好的数据库或官方文档找到mysql5.7版本user表结构，然后手动的进行创建，然后插入初始化数据库之后，系统默认的存在的用户。

第二种情况：MYSQL服务进程停掉了，无法重启成功。从3306端口的实例中拷贝故障实例的数据文件目录，几个user文件；复制成功后，重启mysql进程；

ll /data/mysql/mysql_3306/data/mysql/user*#查看3306端口的user实例
cp /data/mysql/mysql_3306/data/mysql/use* /data/mysql/mysql_3309/data/mysql/  #复制实例
ll /data/mysql/mysql_3309/data/mysql/user*#查看3309端口的user实例
chown mysql:mysql /data/mysql/mysql_3309/data/mysql/user* #修改文件的组和主
mysqld --datadir=/data/mysql/mysql_3309/data/ --socket=/tmp/mysql3309.sock --port=3309 --server_id=3309 --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/data/mysql/mysql_3309/log/slow.log --log-error=/data/mysql/mysql_3309/log/error.log --binlog_format=row --log-bin=/data/mysql/mysql_3309/log/mysql3309.bin --gtid-mode=ON --enforce-gtid-consistency=ON --log-slave-updates=ON & [1] 17339   #重启mysql进程

创建用户

create user 用户名 identified by '密码';
create user '用户名'@hostname' identified by '123';

修改用户

update mysql.user set user='用户名2' where user='用户名1'；
flush privileges;

删除用户

方法一：drop

drop user 用户名；#默认删除host为%的用户
drop user '用户名'@'localhost';

方法2：delete

delete from mysql.user where host='主机名' and user='用户名'；
flush privileges;

1.2密码设置/修改,密码过期，密码重用

root用户不仅可以修改自己的密码，还可以修改其他用户的密码

密码字段名是authentication_string值显示英文，其实是做了加密处理；

方法一：alter

alter user '用户名'@'主机名'identified by '新密码'；

方法二：set

set password = password('新密码');   #修改当前用户密码，旧版本，mysql5.7和8.0测试有效

set password for '用户名'@'主机名'='新密码';  #修改普通用户密码

方法三：update (不推荐)

update mysql.user set authentication_string=password('新密码') where user='用户名' and host='主机名';

密码过期策略，密码过期后还可以进行登录，但是不能进行查询等操作；

方法一：alter user '用户名'@'主机名' password expire;   #手动设置密码过期
        alter user 用户名 password expire; 
方法二：set password default_password_lifetime =180;   #密码生存周期必须是整数，超过则密码过期
方法三：在my.cnf中添加语句 default_password_lifetime=180

手动设置密码过期：

create user '用户名'@'主机名' password expire interval by 90 day;#设置该用户账号密码每90天过期
alter user '用户名'@'主机名' password expire interval by 90 day;
create user '用户名'@'主机名' password expire never;    #设置永不过期
alter user '用户名'@'主机名' password expire interval by never;
create user '用户名'@'主机名' password expire default;  #延用全局密码过期策略
alter user '用户名'@'主机名' password expire default;

限制密码重用：

方法一：
set persist password_history = 6;  #设置不能使用最近使用的6个密码
set persist password_reuse_interval = 6; #设置不能选择最近6天的密码
方法二：
修改my.cnf配置文件
password_history =6
password_reuse_interval =6

1.3权限与访问控制

> show privileges; 查看权限

权限分布	可以设置的权限
表权限	select/insert/updata/delete/create/drop/grant/reference/index/alter
列权限	select/insert/update/references
过程权限	execute/alter routine/grant

2、角色

创建角色，给角色赋予权限，查看角色权限，回收角色权限，删除角色，给用户赋予角色，激活角色，撤销用户角色，设置强制角色；

应用：

1）授予用户最小的权限，只给select,防止用户修改或者干坏事；

2）限制指定IP或者内网IP段，公司里限制用户的登录主机；例如：知网查看论文

3）为每个用户设置满足密码复杂度的密码（接上述密码管理），保证安全性；

4）定期清理不需要的用户，回收权限或者删除用户；

2.1授予权限

一是，直接给用户权限；二是，将权限封装在创建的角色中；

> show grants；#查看登录用户的权限
> show grants for 'user'@'主机地址';

 第一条是登录权限，第二天是用户zhang3对数据库的查看和更新全权限；

给新用户zhang3赋予查看和更新dbtest数据库的所有表的权限；

> grant select,update on dbtest1.* to 'zhang3'@'%';

给用户li4赋予所有数据库的所有表的所有权限：

> grant all privileges on *.* to 'li4'@'%';

> grant all privileges on *.* to joe@'%' identified by '123';

但是，用户li4和root用户还是有区别的；

 2.2 收回权限

取消已经赋予的权限，在一定程度上保证系统的安全性，使用rewoke取消，用户的账户记录从db/host/tabkes_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存；

即可以登录，但是无法查看数据库中的表，查看时会报error 1142。

注意：在user表中删除用户时，应该收回相应用户的所有权限。

> revoke 权限1，……，权限n on 数据库名称.表名称 from 用户名@用户地址；# 收回权限命令
> revoke all privieges on *.* from joe@'%'；# 收回全库全表所有权限

   收回该用户所有所有权限时，可能会报错，需要给予root用户 SYSTEM_USER这个权限；     我们使用数据库时一般不使用root用户去访问数据库，root账号和密码放在代码里不安全，尽量使用创建的角色和用户机制来访问权限；

 2.3权限表

mysql服务器通过权限表控制用户对数据库的访问权限，权限表放在内置的mysql数据库中。mysql数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。内置mysql库中含有user表（用户账户及权限信息），db表（数据库层级单位权限），authentication_string表（密码表），table_priv表（动态全局授权，表的权限），column_priv表，proc_priv表，sl_*表（权限与加密）max_*表（每小时允许执行查询的次数/每小时允许连接的次数/用户重复连接的次数）。mysql启动时，服务器将数据库表中权限信息的内容读入内存。

2.4、访问控制

补充：> desc 表名；   #查看表名