这是MySQL 5实力养成暨评量里的8-47.‘删除触发程序使用者需要有下列何项权限？’
答案：(C) Super
[adsense][/adsense]
有关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…等等的权限选项，希望你可以善用今天提供的资讯在触发程序的测验上可以得心应手