首頁 / CompScience / Database / MySQL / 免費揭露MySQL DROP TRIGGER的秘密

免費揭露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 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 USERDROP USERRENAME 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 TOKILLPURGE BINARY LOGSSET 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 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…等等的權限選項,希望你可以善用今天提供的資訊在觸發程序的測驗上可以得心應手

馬上成為工作達人的Fans

About ㄚ琪

工作達人Fun Taiwan的創辦者及總編,可以在這裡更認識他。

發表迴響

你的電子郵件位址並不會被公開。 Required fields are marked *

*

Scroll To Top

 好吃、好玩、旅遊、試吃、試用、邀稿、SEO

歡迎洽詢:flylinux@gmail.com

➨ 國外美食旅遊  日本東京自由行  美國猶他加州自由行  韓國釜山首爾慶州自由行 │ 泰國馬來西亞新加坡自由行  澳門廣東香港自由行日本九州奈良京阪自由行 │澳洲墨爾本坎培拉雪梨自由行 │中國北京自由行 

紅屋頂加級大阪難波飯店 (Red Roof Plus Osaka Namba) 日本大阪紅屋頂加級大阪難波飯店 (Red Roof Plus Osaka Namba)激推