MySQL权限操作

一、MySQL的权限

二、权限用法
1.创建用户create user 'admin'@'%' identified by 'your_password';

可通过navicat加密方式登录
2.创建用户create user 'admin'@'%' identified with mysql_native_password by 'your_password';

3.查看用户权限
show grants for 'admin'@'%';

4.MySQL权限对象列表

授权操作

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_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON . TO root@% WITH GRANT OPTION

grant select,update,insert,delete on database.* to 'admin'@'%';
flush privileges;

4.删除权限

REVOKE ALL PRIVILEGES ON database.* FROM 'admin'@'%';
FLUSH PRIVILEGES;

5.删除用户
drop user 'admin'@'%';

6.修改用户名
rename user 'admin'@'%' to 'admin_new'@'%';

7.修改密码
a. set password for 'admin'@'%'= password('111111');
b. use mysql;
update user set password=password('1111111') where user='admin';
update user set authentication_string=password('111111') where user='admin';
c. mysqldmin -uroot -p yourpassword password 111111