User privileges error on server maria db Create trigger Drop trigger

Question

User privileges error on server maria db

• User : user1
• DB : db1

Error

15:00:46 DROP TRIGGER IF EXISTS audit_trigger Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) 0.266 sec

Script

DELIMITER $$  
DROP TRIGGER IF EXISTS audit_trigger$$ 
CREATE TRIGGER `audit_trigger` BEFORE UPDATE ON `table` FOR EACH ROW
BEGIN
    IF OLD.dateModify <> NEW.dateModify THEN 
        INSERT INTO  table SELECT * FROM thistable where id = NEW.id ;  
    END IF ; 
END $$ 
DELIMITER ;

Is it possible to give super privileges for user1 to only db1 i dont want this user to have access to other tables on the server

Answer 1

Believe it or not, I wrote about this back on Jun 20, 2014. If you look in my post MySQL user without SUPER privilege, I clearly explain how running command like

GRANT ALL PRIVILEGES ON db1.table TO 'user1'@'%';
GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%';

never gives away SUPER privilege

If you run this query

SELECT table_name FROM information_schema.columns
WHERE table_schema='mysql' AND column_name='super_priv';

it only comes back with user.

+------------+
| table_name |
+------------+
| user       |
+------------+

Here is something interesting

• In MySQL 5.6, running DROP TRIGGER requires the TRIGGER privilege. Same is true for MySQL 5.7, MySQL 5.5, and MySQL 5.1
• In MySQL 5.0, running DROP TRIGGER requires the SUPER privilege

Running this query

SELECT table_name FROM information_schema.columns
WHERE table_schema='mysql' AND column_name='trigger_priv';

comes back with

+------------+
| table_name |
+------------+
| db         |
| user       |
+------------+

This shows you that globally and at the database level you can grant the TRIGGER privilege, provided the user can access the specified schema. You cannot give away the TRIGGER privilege to a user just for a single table.

Thus, if a user has full access to database db1 and you want that user to drop triggers in another database (such as db2), then simply run

GRANT TRIGGER ON db2.* TO user1@'%';

Be very careful how you assign individual grants like this

In your particular case, DROP TRIGGER requires TRIGGER privilege in MariaDB

Answer 2

According comment on this page, https://mariadb.com/kb/en/mariadb/drop-trigger/: It appears that there is a bug in MySQL which makes TRIGGER privilege not enough to create/drop triggers. Unfortunately it's also present in MariaDB 10.1.11

See: - stack-overflow comment - https://stackoverflow.com/questions/1627383/cant-create-mysql-trigger-with-trigger-privilege-on-5-1-32 - MySQL bug ticket - http://bugs.mysql.com/bug.php?id=39489

Answer 3

As a workaround, one may set the mysterious parameter ‘log_bin_trust_function_creators’ to 1, that prevents the database from complaining against simple and inoffensive triggers. Though, setting this parameter requires

1. Open the “Parameter Groups” tab.
2. Create a new Parameter Group. On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm.
3. Select the just created Parameter Group and issue “Edit Parameters”.
4. Look for the parameter ‘log_bin_trust_function_creators’ and set its value to ‘1’.
5. Save the changes.
6. Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
7. Select the just created Parameter Group and enable “Apply Immediately”.
8. Click on “Continue” and confirm the changes.
9. Again, open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
10. Dont forget: Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Reboot”.

Answer 4

You could specify the table name in the GRANT statement like this:

 GRANT ALL PRIVILEGES ON db1.table TO 'user1'@'%';

Or for all tables of db1 like this:

 GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%';