MySQL數據庫權限操作

操作對象：

線上和測試所有MySQL Plus數據庫

創建用戶：

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

查看testname用戶的權限

show grants for testname;

testname用戶授權：

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'@'%';

#創建用戶并授權

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

testname用戶原有權限：

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

查看用戶的授權權限：

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

修改用戶的授權權限：

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

flush privileges;

回收權限：

CREATE,DROP,ALTER

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

線上授權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';