User privileges error on server maria db Create trigger Drop trigger 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 &lt;&gt; 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
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