這是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 REVOKE
| Privilege | Meaning |
|---|---|
ALL [PRIVILEGES] |
Grant all privileges at specified access level except GRANT OPTION |
ALTER |
Enable use of ALTER TABLE |
ALTER ROUTINE |
Enable stored routines to be altered or dropped |
CREATE |
Enable database and table creation |
CREATE ROUTINE |
Enable stored routine creation |
CREATE TEMPORARY TABLES |
Enable use of CREATE TEMPORARY TABLE |
CREATE USER |
Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES |
CREATE VIEW |
Enable views to be created or altered |
DELETE |
Enable use of DELETE |
DROP |
Enable databases, tables, and views to be dropped |
EVENT |
Enable use of events for the Event Scheduler |
EXECUTE |
Enable the user to execute stored routines |
FILE |
Enable the user to cause the server to read or write files |
GRANT OPTION |
Enable privileges to be granted to or removed from other accounts |
INDEX |
Enable indexes to be created or dropped |
INSERT |
Enable use of INSERT |
LOCK TABLES |
Enable use of LOCK TABLES on tables for which you have the SELECT privilege |
PROCESS |
Enable the user to see all processes with SHOW PROCESSLIST |
REFERENCES |
Not implemented |
RELOAD |
Enable use of FLUSH operations |
REPLICATION CLIENT |
Enable the user to ask where master or slave servers are |
REPLICATION SLAVE |
Enable replication slaves to read binary log events from the master |
SELECT |
Enable use of SELECT |
SHOW DATABASES |
Enable SHOW DATABASES to show all databases |
SHOW VIEW |
Enable use of SHOW CREATE VIEW |
SHUTDOWN |
Enable use of mysqladmin shutdown |
SUPER |
Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command |
TRIGGER |
Enable trigger operations |
UPDATE |
Enable use of UPDATE |
USAGE |
Synonym 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. (Before 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 default 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…等等的權限選項，希望你可以善用今天提供的資訊在觸發程序的測驗上可以得心應手