Mysql Grant权限管理

by · 2018年04月28日 · 2080 Words · ~5min reading time | Improve on


原文链接： Mysql Grant权限管理

删除用户

drop user root@'%';

创建数据库

CREATE DATABASE IF NOT EXISTStpcmsDEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE DATABASE IF NOT EXISTSguoanDEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

docker exec -i mariadb mysql -uroot -pXXXX -e "CREATE DATABASE IF NOT EXISTS ytjxc DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"
docker exec -i mariadb mysql -uroot -pXXXX -e "CREATE DATABASE IF NOT EXISTS ytjxc DEFAULT CHARACTER SET utf8 "

创建并授权用户

docker exec -i mariadb mysql -uroot -pXXXX -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'XXXX' WITH GRANT OPTION; FLUSH PRIVILEGES;"

docker 有个坑 内网ip不一定都是172.0.0.x 还有可能是192.0.0.x 所以 'user'@'1_2.%' 保险起见用 'user'@'%'

docker exec -i mariadb mysql -uroot -pXXXX -e "GRANT ALL PRIVILEGES ON *.* TO 'user'@'1_2.%' IDENTIFIED BY 'XXXX'; FLUSH PRIVILEGES;"

权限管理

创建用户并授权

//把所有用户的权限授予username@'hostname',密码是your_passwd
mysql>grant all on *.* to username@'hostname' indentified by [password]'your_passwd'

命令:GRANT privileges ON databasename.tablename TO 'username'@'host'
privileges - 用户的操作权限,如 SELECT INSERT UPDATE DELETE 等.如果要授予所的权限则使用ALL.;

            select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file

databasename - 数据库名,
tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用表示, 如.*

例子:
GRANT SELECT, INSERT ON test.user TO 'username'@'%';
GRANT ALL ON . TO 'username'@'%';

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'kyxx'@'123.132.%.%' IDENTIFIED BY '888888' WITH GRANT OPTION; FLUSH PRIVILEGES;
#root可从任何IP登陆，注意修改密码:'888888'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '888888' WITH GRANT OPTION;
#root可从指定IP登陆，注意修改密码:'888888'、IP:'192.168.1.188'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.188' IDENTIFIED BY '888888' WITH GRANT OPTION;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER ON . TO 'huijin'@'%' IDENTIFIED BY 'Huijin2017@!'; FLUSH PRIVILEGES;

创建用户: CREATE USER bench@% IDENTIFIED BY 'bench';

1. 系统级权限 等于root权限

GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create User, Create View, Delete, Drop, Event, Execute, File, Grant Option, Index, Insert, Lock Tables, Process, References, Reload, Replication Client, Replication Slave, Select, Show Databases, Show View, Shutdown, Super, Trigger, Update ON . TO root@% IDENTIFIED BY 'toor' WITH GRANT OPTION;

GRANT
SUPER
PROCESS
RELOAD
SHUTDOWN
SHOW DATABASES
LOCK TABLES
REFERENCES
REPLICATION CLIENT
REPLICATION SLAVE
CREATE USER

2. 数据库级别 权限授予

grant all privileges ON gocron.* TO bench@%; + GRANT Grant Option ON gocron.* TO bench@%;

grant all privileges ON test.* TO bench@%; # 同一个用户授权多个数据库
等价
GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create View, Delete, Drop, Event, Routine, Create Temporary Tables, Create View, Delete, Drop, Event, Execute, Grant Option, Index, Insert, Lock Tables, References, Select, Show View, Trigger, Update ON gocron.* TO bench@%;

3. 表级别 权限授予

GRANT Alter, Create, Create View, Delete, Drop, Grant Option, Index, Insert, References, Select, Show View, Trigger, Update ON TABLE guoan.dede_admin TO bench@%;

--master-data 权限

Couldn't execute 'FLUSH /*!40101 LOCAL */ TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

GRANT RELOAD,REPLICATION CLIENT ON *.* TO 'your_user'@'localhost';

4. prometheus 监控权限

GRANT REPLICATION CLIENT, PROCESS, SUPER, SELECT ON . TO 'pmm'@'%' IDENTIFIED BY 'percona2018';
GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO 'pmm'@'%';

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON . TO 'exporter'@'localhost';

5. replication 主从复制权限

grant REPLICATION SLAVE on *.* to 'repl'@'%' identified by '123456';

grant lock tables, reload, replication slave,replication client on *.* to 'repl'@'10.114.0.%' identified by '123456'; flush privileges;

权限

GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT
ON wechat.* TO 'wechat';

GRANT GRANT OPTION ON wechat.* TO 'wechat';

grant select, insert, update, delete, create, drop, references, index, alter,

    create temporary tables, lock tables, create view, show view, create routine,
    alter routine, execute, trigger

on Sample.* to 'acme-manager'@'%';

grant create,select,insert,update,create view,show view,event,alter,trigger,index,alter on fgg.* to 'allen2016'@'%' identified by
'allen2016soft*88' with grant option;
flush privileges;

获取命令帮助

help
grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* to common_user@’%’
grant insert on testdb.* to common_user@’%’
grant update on testdb.* to common_user@’%’
grant delete on testdb.* to common_user@’%’
或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@’%’

9>.grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。

grant 创建、修改、删除 MySQL 数据表结构权限。

grant create on testdb.* to developer@’192.168.0.%’;

grant alter on testdb.* to developer@’192.168.0.%’;

grant drop on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 外键权限。

grant references on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 临时表权限。

grant create temporary tables on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 索引权限。

grant index on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 视图、查看视图源代码 权限。

grant create view on testdb.* to developer@’192.168.0.%’;

grant show view on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 存储过程、函数 权限。

grant create routine on testdb.* to developer@’192.168.0.%’; -- now, can show procedure status

grant alter routine on testdb.* to developer@’192.168.0.%’; -- now, you can drop a procedure

grant execute on testdb.* to developer@’192.168.0.%’;

10>.grant 普通 DBA 管理某个 MySQL 数据库的权限。

grant all privileges on testdb to dba@’localhost’

其中，关键字 “privileges” 可以省略。

11>.grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* to dba@’localhost’

12>.MySQL grant 权限，分别可以作用在多个层次上。

1. grant 作用在整个 MySQL 服务器上：

grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。

grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库

2. grant 作用在单个数据库上：

grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。

3. grant 作用在单个数据表上：

grant select, insert, update, delete on testdb.orders to dba@localhost;

4. grant 作用在表中的列上：

grant select(id, se, rank) on testdb.apache_log to dba@localhost;

5. grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to ’dba’@’localhost’

grant execute on function testdb.fn_add to ’dba’@’localhost’
注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES。


权限表
权限  说明
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  服务器位置访问
replocation slave   由复制从属使用
select
show databases
show view
shutdown    使用mysqladmin shutdown 来关闭mysql
super
update
usage   无访问权限

Tags:

`