mysql8账户和权限管理
mysql8的账户和权限管理
账户
登录
mysql -h host -u user -p[password] -P 3306 dbname -e “...”
#-p直接加密码不能空格，可不直接加密码
#-h 不写默认localhost
#-P端口,dbname数据库名可最后指定
# -e登录后执行-e后的语句并退出
创建用户
可使用 create user、grant语句或操作授权表创建新用户
#create user 创建的用户无任何权限
CREATE USER 'u1'@'localhost' IDENTIFIED BY 'xxx';
CREATE USER 'u1'@'%' IDENTIFIED BY 'xxx';
mysql8移除password()获取哈希值，可使用md5() sha()
select MD5("a");
CREATE USER 'u1'@'%' IDENTIFIED BY MD5 '...'
CREATE USER 'u4'@'%' IDENTIFIED WITH mysql_native_password BY 'aaa';
mysql8不支持直接用grant直接创建用户，grant只能修改用户
直接操作user表
查看用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
SELECT User, Host FROM mysql.user;
删除用户
删除用户前应收回用户所有权限
drop user 'u1'@'localhost';
#不会自动删除用户创建的数据库和对象
# drop无法删除权限较高的用户？未解决
delete from mysql.user where host='%' and user='u5';
#delete可以删除权限较高的用户
更改root用户密码
use admin; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
使用mysqladmin修改
./mysqladmin -u root -p password "xxx"
使用set命令修改
set password="a_123456";
修改user表
root密码丢失
win登录
mysqld --skip-grant-tables
mysqld-nt --skip-grant-tables
linux登录
mysqld_safe --skip-grant-tables user=xxx
mysql start-mysqld --skip-grant-tables
登录成功后使用update或mysqladmin重置密码
更改普通用户密码
set password for 'test3'@'%' ="abcabc123";
# 权限较高的用户无法修改
使用update
update mysql.user set password=password("xxx") where user='test03' and host='%';
#mysql8 移除password表头和password函数
使用grant
grant usage on *.* to 'test3'@'%' IDENTIFIED by "xxx";
# 报错，
普通用户修改密码
set password="xxx";
账户权限
查看权限
总共五层权限
1查看用户全局权限
SELECT * FROM mysql.user WHERE user='u1'\G
2查看用户对库的权限
SELECT * FROM mysql.db WHERE user='u1'\G
3查看用户对表的权限
SELECT * FROM mysql.mysql.tables_priv
4查看用户对列的权限
SELECT * FROM mysql.mysql.columns_priv
5查看用户程序权限
SELECT * FROM mysql.mysql.
常用查看方法
show grants for 'u1'@'%';
#查看设置语句
查看自己权限
show grants;
收回权限
取消用户所有权限
revoke all privileges ,grant option from 'u1'@'%';
取消部分权限
revoke update on *.* from 'u1'@'%'
设置权限
语法
grant 权限 ON db.table TO 'user'@'host'
权限说明
privileges 权限指定符权限允许的操作
alter 修改表和索引
create 创建数据库和表
delete 删除表中已有的记录
drop 抛弃（删除）数据库和表
index 创建或抛弃索引
insert 向表中插入新行
reference 未用
select 检索表中的记录
update 修改现存表记录
file 读或写服务器上的文件
process 查看服务器中执行的线程信息或杀死线程
reload 重载授权表或清空日志、主机缓存或表缓存。
shutdown 关闭服务器
all 所有；
all privileges同义词
usage 特殊的“无权限”权限
设置全局权限
grant select,insert on *.* to test@'%'
grant all privileges on *.* to 'user1'@'%';
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’127.0.0.1’ WITH GRANT OPTION;
设置库权限
grant select,insert on db1.* to test@'%'
针对表设置权限
grant all privilegeson MyDB.kkk to test@'%'
针对列设置权限
grant select(id,num) on db1.ta1 to dba@localhost;
#对id,num列设置权限 ，用户将只能操作这两列
程序级权限
grant execute on procedure MyDB.PRC_TEST to test@'%'
grant execute on procedure testdb.pr_add to ‘dba’@'localhost’
grant execute on function testdb.fn_add to ‘dba’@'localhost’
设置读写权限
grant SELECT,UPDATE on db01.table01 to 'user1'@'%';
grant select,insert,update,delete on student.* to test2@192.168.2.2 identified by “123456″;
其他权限
如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“
grant 创建、修改、删除 MySQL 数据表结构权限。
grant create on testdb.* to user01@’192.168.0.%’;
grant alter on testdb.* to user01@’192.168.0.%’;
grant drop on testdb.* to user01@’192.168.0.%’;
grant 操作 MySQL 外键权限。
grant references on testdb.* to user01@’192.168.0.%’;
grant 操作 MySQL 临时表权限。
grant create temporary tables on testdb.* to user01@’192.168.0.%’;
grant 操作 MySQL 索引权限。
grant index on testdb.* to user01@’192.168.0.%’;
grant 操作 MySQL 视图、查看视图源代码 权限。
grant create view on testdb.* to user01@’192.168.0.%’;
grant show view on testdb.* to user01@’192.168.0.%’;
grant 操作 MySQL 存储过程、函数 权限。
grant create routine on testdb.* to user01@’192.168.0.%’; — now, can show procedure status
grant alter routine on testdb.* to user01@’192.168.0.%’; — now, you can drop a procedure
grant execute on testdb.* to user01@’192.168.0.%’;
刷新，权限才生效
flush privileges;
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
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,
ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,
REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,
SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION
mysqlpump权限
grant lock tables,reload,process,replication client,super,select,event,trigger,show view on power.* to 'u1'@'%';
grant reload,process,replication client,super,select,event,trigger,show view on *.* to 'u1'@'%';
#不给锁权限也行
grant lock tables,reload,process,replication client,super,select,event on *.* to 'u1'@'%';
#也能备份，但是有报错
