授權(quán)案例總結(jié)

host 列: %,localhost,127.0.0.1,::11)授權(quán)普通用戶,具有查詢、插入、更新、刪除 itpux 這個數(shù)據(jù)庫所有表數(shù)據(jù)的權(quán)限。 grant select, insert, update, delete on itpux.* to itpux1@'%';flush privileges; show grants for itpux1@'%'2)開發(fā)人員授權(quán)：創(chuàng)建/刪除/修改-表/索引/視圖/存儲過程/函數(shù)等權(quán)限 create user dev@'%' identified by 'dev'; grant create,alter,drop on itpux1.* to dev@'%'; flush privileges; show grants for dev@'%';create user dev@'10.0.0.%' identified by 'dev'; grant create,alter,drop on itpux1.* to dev@'10.0.0.%'; flush privileges; show grant for dev@'10.0.0.%';3)操作mysql外鍵的權(quán)限 grant references on itpux1.* to dev@'%'; flush privileges;4)操作mysql臨時表的權(quán)限 grant create temporary tables on itpux1.* to dev@'%'; flush privileges; 5)操作mysql索引的權(quán)限 grant index on itpux1.* to dev@'%'; flush privileges;6)操作mysql視圖的權(quán)限 grant create view,show view on itpux1.* to dev@'%'; flush privileges; 7)操作mysql存儲過程/函數(shù)的權(quán)限 grant create routine,alter routine,execute on itpux1.* to dev@'%';8)授權(quán)一個普通的dba用戶，可以管理某一個數(shù)據(jù)庫 grant all privileges on itpux to itpuxdba@'localhost' identified by 'itpuxdba'; flush privileges;9)授權(quán)一個高級的dba用戶，可以管理所有的數(shù)據(jù)庫 grant all privileges on *.* to alldba@'localhost' identified by 'alldba'; flush privileges;10)授權(quán)針對所有的數(shù)據(jù)庫 grant all privileges on *.* to alldba@'localhost'; flush privileges；11)授權(quán)針對單個的數(shù)據(jù)庫 grant all privileges on itpux to alldbae'LocaLhost'i grant select on itpux.* to alldba@'localhost'; flush privileges;12)授權(quán)針對所有的數(shù)據(jù)庫 grant select on itpux.* to alldba@'locaLhost'; flush privileges;13)授權(quán)針對單個列 grant select(deptno,dname) on itpux.dept to alldba@'localhost'; flush privileges;14)授權(quán)針對存儲過程和函數(shù) grant execute on procedure 庫名.過程名 to alldba@'localhost'; grant execute on function 庫名.函數(shù)名 to alldba@'localhost'; flush privileges;

revoke 語法

1)授權(quán)針對所有的數(shù)據(jù)庫 revoke revoke all privileges on *.* from alldba@'localhost'; flush privileges；2)授權(quán)針對單個的數(shù)據(jù)庫 revoke all privileges on itpux.* from alldba@'locaLhost'; revoke select on itpux.* from alldba@'localhost'; flush privileges;3)授權(quán)針對所有的數(shù)據(jù)庫 revoke select on itpux.* from alldba@'locaLhost'; flush privileges;4)授權(quán)針對單個列 revoke select(deptno,dname) on itpux.dept from alldba@'localhost'; flush privileges;5)授權(quán)針對存儲過程和函數(shù) revoke execute on procedure 庫名.過程名 from alldba@'localhost'; revoke execute on function 庫名.函數(shù)名 from alldba@'localhost'; flush privileges;show grants for alldba@'localhost' show grants for itpux1@'localhost' 另類方法：直接修改授權(quán)表回收授權(quán)-不建議使用 select user,host from mysql.user delete from mysql.user where user='alldba' and host='localhost'; commit； flush privileges; select user,host from mysql.user;

刪除用戶及重命名用戶

select user,host from mysql.user; drop user dev; drop user dev@'%'; drop user dev@'192.168.1.%'; select user,host from mysql.user where user like 'dev%'; select user,host from mysql.user; rename user 'itpuxdba'@'localhost' to 'itpuxdba1'@'localhost';

顯示授權(quán)

show grants for itpux; show grants for itpux@localhost; select * from mysql.user where user='itpux1' and host='localhost';select CURRENT_USER();show grants; show grants for current_user;mysql root@localhost:mysql> show grants for current_user\G ***************************[ 1. row ]*************************** Grants for root@localhost | GRANT 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, EVENT, TRIGGER,
CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION ***************************[ 2. row ]*************************** Grants for root@localhost | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,
GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,
SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION ***************************[ 3. row ]*************************** Grants for root@localhost | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

總結(jié)

以上是生活随笔為你收集整理的2.3.3 mysql 权限系统介绍的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。