暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL 用户与权限

MySQL 用户与权限

程序员35 2020-02-24
452

学习 MySQL 的用户管理与权限管理。

MySQL 用户

-- 创建用户 'test'@'%'
CREATE USER test IDENTIFIED BY '123456';
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
-- 创建用户 'test'@'localhost'
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
flush privileges;

-- 删除用户 'test'@'%'
drop user 'test'@'%';
-- 删除用户 'test'@'localhost'
drop user 'test'@'localhost';
flush privileges;

MySQL 权限

-- 权限汇总
-- 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

-- 授权/取消，单个权限
GRANT SELECT ON mydbname.mytbname TO 'test'@'%';
REVOKE SELECT ON mydbname.mytbname FROM 'test'@'%';
GRANT SELECT ON mydbname.* TO 'test'@'%';
REVOKE SELECT ON mydbname.* FROM 'test'@'%';
flush privileges;

-- 授权/取消，多个权限
GRANT SELECT, INSERT ON mydbname.mytbname TO 'test'@'%';
REVOKE SELECT, INSERT ON mydbname.mytbname FROM 'test'@'%';
flush privileges;

-- 授权/取消，所有权限
GRANT ALL PRIVILEGES ON mydbname.mytbname TO 'test'@'%';
REVOKE ALL PRIVILEGES ON mydbname.mytbname FROM 'test'@'%';
flush privileges;

-- 查看已授权的权限
show grants for 'test'@'%';
show grants for 'test'@'localhost';

权限说明
all
alter
alter routine使用alter procedure和drop procedure
create
create routine使用create  procedure
create temporary tables使用create temporary table
create user
create view
delete
drop
execute使用call和存储过程
file使用select into outfile和load data infile
grant option使用grant和revoke
index使用create index和drop index
insert
lock tables锁表
process使用show full processlist
reload使用flush
replication client服务器位置访问
replication slave由复制从属使用
select
show databases
show view
shutdown使用mysqladmin shutdown来关闭mysql
super
update
usage无访问权限

扫码关注微信公众号 「程序员35」 ，获取最新技术干货，畅聊 #程序员的35，35的程序员# 。独立站点：https://cxy35.com

mysql
文章转载自程序员35，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论