MySQL資料庫許可權操作

標籤：ide   alt   ESS   database   replica   databases   操作   dmi   date   

操作對象：

線上和測試所有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,ALTERrevoke CREATE,DROP,ALTER on *.* from [email protected]‘%‘;

線上授權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 -h1.1.1.1 password C123456 "c123456"

修改密碼：

set password = password ‘C123456‘;

MySQL資料庫許可權操作