免費揭露MySQL DROP TRIGGER的秘密

2013-08-14_090101

這是MySQL 5實力養成暨評量裡的8-47.『刪除觸發程序使用者需要有下列何項權限?』

答案:(C) Super

有關Trigger的使用在MySQL裡相當的詭異,今天我們要免費為你揭露鮮為人知的秘密,告訴你Trigger在MySQL中的權限是如何操作的。當然你可能較熟的是drop這個只是刪除的指令而已,但是如果你有看過MySQL GRANT及REVOKE語法的話,你應該會懷疑GRANT不是有drop的選項嗎?沒錯,在那篇文章中有提到,但是意義是『允許使用DROP TABLE』。

這就是我搞混的地方。

因為如果你眼尖的話,去看MySQL 5.1 Reference Manual :: 13 SQL Statement Syntax :: 13.7 Database Administration Statements :: 13.7.1 Account Management Statements :: 13.7.1.3 GRANT Syntax

5.1版的英文手冊這樣寫

Table 13.1. Permissible Privileges for GRANT and 

PrivilegeMeaning
ALL [PRIVILEGES]Grant all privileges at specified access level except GRANT OPTION
ALTEREnable use of ALTER TABLE
ALTER ROUTINEEnable stored routines to be altered or dropped
CREATEEnable database and table creation
CREATE ROUTINEEnable stored routine creation
CREATE TEMPORARY TABLESEnable use of CREATE TEMPORARY TABLE
CREATE USEREnable use of CREATE USERDROP USERRENAME USER, and REVOKE ALL PRIVILEGES
CREATE VIEWEnable views to be created or altered
DELETEEnable use of DELETE
DROPEnable databases, tables, and views to be dropped
EVENTEnable use of events for the Event Scheduler
EXECUTEEnable the user to execute stored routines
FILEEnable the user to cause the server to read or write files
GRANT OPTIONEnable privileges to be granted to or removed from other accounts
INDEXEnable indexes to be created or dropped
INSERTEnable use of INSERT
LOCK TABLESEnable use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESSEnable the user to see all processes with SHOW PROCESSLIST
REFERENCESNot implemented
RELOADEnable use of FLUSH operations
REPLICATION CLIENTEnable the user to ask where master or slave servers are
REPLICATION SLAVEEnable replication slaves to read binary log events from the master
SELECTEnable use of SELECT
SHOW DATABASESEnable SHOW DATABASES to show all databases
SHOW VIEWEnable use of SHOW CREATE VIEW
SHUTDOWNEnable use of mysqladmin shutdown
SUPEREnable use of other administrative operations such as CHANGE MASTER TOKILLPURGE BINARY LOGSSET GLOBAL, and mysqladmin debug command
TRIGGEREnable trigger operations
UPDATEEnable use of UPDATE
USAGESynonym for “no privileges”

The EVENT and TRIGGER privileges were added in MySQL 5.1.6. A trigger is associated with a table, so to create or drop a trigger, you must have the TRIGGER privilege for the table, not the trigger. ( MySQL 5.1.6, theSUPER privilege was required to create or drop triggers.)

用中文來講就是,MySQL 5.1.6以後,要刪除觸發程序必須有該資料表的TRIGGER權限,但是在這版本之前則需要有SUPER權限來建構或刪除觸發程序。

所以在MySQL 5.1 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.30 DROP TRIGGER Syntax

也這樣提到

This statement drops a trigger. The schema (database) name is optional. If the schema is omitted, the trigger is dropped from the schema. DROP TRIGGER requires the TRIGGER privilege for the table associated with the trigger. (This statement requires the SUPER privilege prior to MySQL 5.1.6.)

要再一次清楚的說明(D) GRANT是開權限的指令,它下面會有DELETE、DROP、SUPER…等等的權限選項,希望你可以善用今天提供的資訊在觸發程序的測驗上可以得心應手

Print Friendly, PDF & Email

感謝你看到這裡,很快就可以離開了,但最好的獎勵行動就是按一下幫我分享,感恩喔~

點我分享到Facebook

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料