MySQL數(shù)據(jù)庫權(quán)限操作

操作對象：

線上和測試所有MySQL Plus數(shù)據(jù)庫

創(chuàng)建用戶：

create user 'testname'@'%' identified by '123456';

查看testname用戶的權(quán)限

show grants for testname;

testname用戶授權(quán)：

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX,ALTER,SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'testnam'@'%';

#創(chuàng)建用戶并授權(quán)

GRANT ALL PRIVILEGES ON *.* TO 'testname'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;

testname用戶原有權(quán)限：

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX,ALTER,SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

查看用戶的授權(quán)權(quán)限：

select * from mysql.user where User='testname' and Host='%'\G;

修改用戶的授權(quán)權(quán)限：

update mysql.user set Grant_priv='Y' where User='testname' and Host='%';

flush privileges;

回收權(quán)限：

CREATE,DROP,ALTER

revoke CREATE,DROP,ALTER on *.* from testname@'%';

線上授權(quán)testname1：

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'testname1'@'%';

mysqladmin -u testname1 -h2.1.1.1 password C123456 "c123456"

修改密碼：

set password = password 'C123456';