MySQL5.7 - Question about Privileges for Triggers

[Zvoni]

Hi Folks,
in MySQL in INFORMATION_SCHEMA there are the Privilege-Tables.

Question:
For all intents and purposes let's say a MySQL-User has the GRANT for Triggers on a specific base-table in "MySchema"
Meaning: He can create and drop a Trigger for that table.
Does this User need schema-wide select/update/insert-GRANTS?
The MySQL-doc's say, to even read the "old"/"new"-values inside the trigger require at least SELECT-Grants, but those would have to be schema-wide, right?
or would those SELECT-Grants be for the Table, the trigger is attached to?
The same with the DEFINER-Attribute: I don't plan to use the definer-attribute during creation of the Trigger, so i know, that the trigger runs in context of the creator.

I'm trying to figure out, what GRANTS a User needs in what Scope to do the following:

1) Create a Table in a specified schema --> Right. This one's easy: At least Schema-wide CREATE TABLE
2) Create a Trigger (AFTER INSERT/UPDATE/DELETE) on a specified base-table --> At least TRIGGER-Grant for the Base-table
3) This trigger fires on changes to that table, and writes something into the table mentioned in Step 1 --> SELECT/UPDATE/INSERT in which Scope?
3.a) This User reads the inserted rows of the table from Step 1, sends it somewhere (not important to this discussion), then deletes the row --> SELECT/DELETE schema-wide
4) DROP the TRIGGER from Step 2 --> ??
5) Drop the Table from Step 1 --> DROP TABLE at least schema-wide

AS of Right now, i see the following needed Grants:
CREATE/DROP TRIGGER at least for the Table the Trigger is attached to
CREATE/DROP TABLE at least schema-wide
SELECT/INSERT/UPDATE/DELETE at least schema-wide (because of reading/deleting rows from the "temporary" table)

Any insight?
Theoretically this should also apply to all DBMS implementing information_schema (PG, MSSQL)

EDIT: No (new) Privileges are granted during the run of the program. and how would that be possible, if the User doesn't have the GRANT-privilege.
He cannot grant himself a privilege, if he doesn't have the GRANT-Privilege

[topshot]

Their doc says,

"DROP TRIGGER requires the TRIGGER privilege for the table associated with the trigger."

The Trigger privilege states

Enables trigger operations. You must have this privilege for a table to create, drop, execute, or display triggers for that table.

When a trigger is activated (by a user who has privileges to execute INSERT, UPDATE, or DELETE statements for the table associated with the trigger), trigger execution requires that the user who defined the trigger still have the TRIGGER privilege for the table.

That seems pretty clear so is something not working like they state it should?

Does this User need schema-wide select/update/insert-GRANTS?

On any table the trigger does CRUD on it seems but schema should work, too.

The MySQL-doc's say, to even read the "old"/"new"-values inside the trigger require at least SELECT-Grants, but those would have to be schema-wide, right?
or would those SELECT-Grants be for the Table, the trigger is attached to?

Same as above.
However, since you seem to need this trigger on a table that doesn't exist yet, it would seem schema-wide is the way to go in both cases. It seems like this would be very easy to test.

[Zvoni]

TS,

thx, your answer is basically the same as my assumption, and you're right: pretty easy to test.
Just wanted to make sure, i'm not forgetting something.

However, since you seem to need this trigger on a table that doesn't exist yet, it would seem schema-wide is the way to go in both cases. It seems like this would be very easy to test.

That's the thing: The Trigger is attached to an existing table. Changes in this table fire the trigger, but the trigger must write to a non-existing table, which is first created during/after login, before the trigger is created

The project i'm working on right now, needs the user to perform the actions described above
("user" as in the application-user of my program, but which must exist as a mysql-user on the server. "Hell, no!" to to using "root")

So, right now, my plan is, after User-login and successful connect, to probe the privileges in the following order:
Server-wide (a.k.a USER_PRIVILEGES)
Schema-wide (a.k.a. SCHEMA_PRIVILIGES)
Table (a.k.a TABLE_PRIVILEGES)
First point all needed privilieges are positive, i'm jumping to the next step, otherwise i have to return an error-message.

[Zvoni]

Well, i did the Tests, and i thought to share the results with you, curoius those results might be in my opinion.
If you just create a mysql.user, with nothing else, this user has the privilege USAGE.
Meaning: he can connect to the server, and that's it.
Side-Effect of this: this user can probe the "information_schema"

SELECT T1.UserName, SUM(T1.Privilege) AS Value FROM 
(SELECT REPLACE(SUBSTRING_INDEX(GRANTEE, '@', 1),'\'','') AS USERNAME, PRIVILEGE_TYPE, 
(CASE WHEN PRIVILEGE_TYPE='SELECT' THEN 1 ELSE 
(CASE WHEN PRIVILEGE_TYPE='TRIGGER' THEN 2 ELSE 0 END) END) AS PRIVILEGE 
FROM information_schema.TABLE_PRIVILEGES /*USE PARAMETERS*/ 
WHERE TABLE_NAME='sourcetable' /*SOURCE_TABLE*/ AND TABLE_SCHEMA='MyDatabase' /*SOURCE_DATABASE*/ 
UNION ALL 
SELECT REPLACE(SUBSTRING_INDEX(GRANTEE, '@', 1),'\'','') AS USERNAME, PRIVILEGE_TYPE, 
(CASE WHEN PRIVILEGE_TYPE='SELECT' THEN 4 ELSE 
(CASE WHEN PRIVILEGE_TYPE='INSERT' THEN 8 ELSE 
(CASE WHEN PRIVILEGE_TYPE='DELETE' THEN 16 ELSE 
(CASE WHEN PRIVILEGE_TYPE='CREATE' THEN 32 ELSE 
(CASE WHEN PRIVILEGE_TYPE='DROP' THEN 64 ELSE 0 END) END) END) END) END) AS PRIVILEGE 
FROM information_schema.TABLE_PRIVILEGES  /*USE PARAMETERS*/ 
WHERE TABLE_NAME='targettable' /*TARGET_TABLE*/ AND TABLE_SCHEMA='MyDatabase' /*SOURCE_DATABASE*/ 
UNION ALL 
SELECT REPLACE(SUBSTRING_INDEX(GRANTEE, '@', 1),'\'','') AS USERNAME, PRIVILEGE_TYPE, 
(CASE WHEN PRIVILEGE_TYPE='TRIGGER' THEN 2 ELSE 
(CASE WHEN PRIVILEGE_TYPE='SELECT' THEN 5 ELSE 
(CASE WHEN PRIVILEGE_TYPE='INSERT' THEN 8 ELSE 
(CASE WHEN PRIVILEGE_TYPE='DELETE' THEN 16 ELSE 
(CASE WHEN PRIVILEGE_TYPE='CREATE' THEN 32 ELSE 
(CASE WHEN PRIVILEGE_TYPE='DROP' THEN 64 ELSE 0 END) END) END) END) END) END) AS PRIVILEGE 
FROM information_schema.SCHEMA_PRIVILEGES  /*USE PARAMETERS*/  
WHERE TABLE_SCHEMA='MyDatabase' /*SOURCE_DATABASE*/ 
UNION ALL 
SELECT REPLACE(SUBSTRING_INDEX(GRANTEE, '@', 1),'\'','') AS USERNAME, PRIVILEGE_TYPE, 
(CASE WHEN PRIVILEGE_TYPE='TRIGGER' THEN 2 ELSE 
(CASE WHEN PRIVILEGE_TYPE='SELECT' THEN 5 ELSE 
(CASE WHEN PRIVILEGE_TYPE='INSERT' THEN 8 ELSE 
(CASE WHEN PRIVILEGE_TYPE='DELETE' THEN 16 ELSE 
(CASE WHEN PRIVILEGE_TYPE='CREATE' THEN 32 ELSE 
(CASE WHEN PRIVILEGE_TYPE='DROP' THEN 64 ELSE 0 END) END) END) END) END) END) AS PRIVILEGE 
FROM information_schema.USER_PRIVILEGES) AS T1  /*USE PARAMETERS*/ 
WHERE T1.UserName='Testuser' AND T1.PRIVILEGE_TYPE IN ('TRIGGER', 'SELECT', 'INSERT', 'DELETE', 'CREATE', 'DROP', 'USAGE') 
ORDER BY T1.PRIVILEGE;

That way, i only have to check the returnvalue for >=127