3

I want to make multiple users and allow them to change procedures,functions and triggers in same DB. There must be user devel for developers and admin for admin, both can change developers DB, but admin is not root, i.e. he can't change everything, only some databases. User admin have following permissions:

GRANT ALL PRIVILEGES ON some_db_prefix%.* TO 'admin'@'%' WITH GRANT OPTION

All fine except definers in triggers and procedures. If I set definer in database to devel@'%', i can't edit procedures as admin, but root user can, of course. How can I allow to modify triggers,functions and procedures to multiple users?

UPD: I can't edit procedures as admin even when definer is `%`@`%`.

UPD2: MySQL version: Server version: 5.6.23-ndb-7.4.5-cluster-gpl-log MySQL Cluster Community Server (GPL)

UPD 3: (moved to new question Allow multiple users to see procedures and functions )

UPD 4: Manuals say that only way to change body of procedure is to drop it and create new. So, I can do it, and my question is incorrect, I want not only change functions/procedures using different users, but also see body (code) of funtions/procedures. That's why I have created new question.

  • Did you use GRANT ALL PRIVILEGES ON some_db_prefix%.* TO 'admin'@'%';. GRANT ALL PRIVILEGES ON some_db_prefix%.* TO 'devel'@'%';. FLUSH PRIVILEGES; ? – oNare Jul 16 '15 at 14:02
  • Seems like this doesn't work. I can change triggers, but not functions and procedures. I will check later in other version of mysql. – ckorzhik Jul 16 '15 at 14:35
1

As MySQL's documentation says:

The ALL or ALL PRIVILEGES privilege specifier is shorthand. It stands for “all privileges available at a given privilege level” (except GRANT OPTION). For example, granting ALL at the global or table level grants all global privileges or all table-level privileges.

Just use:

GRANT ALL ON test.* TO 'admin'@'%';
FLUSH PRIVILEGES;

Test:

mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'devel1'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test.* TO 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test.* TO 'devel1'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'devel1'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for devel1@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'devel1'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'devel1'@'%'                                                      |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'admin'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'%'                                                      |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
  • I can't write answer in comment because it too long, so I have updated question. Thanks, but as for me it doesn't work. – ckorzhik Jul 17 '15 at 7:45
  • Actually, it works (I can drop procedures and create new), but I can't see them. Developers (user devel) can't just get code and edit it if someone will create/update function as admin. – ckorzhik Jul 17 '15 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.