What is the proper way to assign the privileges when a MySQL instance has multi dba?

Question

I have a MySQL instance offers services to multi-application, different application has different DBA who responsible to their own application schema.

For example:

Application A has schema DB_A,there are two type of MySQL account for A,one to satisfy the business requirement, and the other one is for administration purpose(performance tuning,DDL,and some other privilege need for administration), apparently ,the former is the subset of the latter. In addition, there is a super DBA (root), who is responsible to create such accounts I mentions above.

As new to MySQL, I would like to ask that how to execute the proper grant statement to satisfy my requirement?

Answer 1

Mysql offers different ways of creating user .

1. For application A you can create a user with specific required roles and its better to give IP from respective DBA operates instead of giving % which allows user to enter in mysql from anywhere. With this user you can check performance of your queries and also explain part.

   So you can use :

   Grant select, insert, update,delete to user@IP on db_a.* Identified​ by 'password';
   

2. For subset user you need to declare which admin rights you want to grant like version and all. Roles will depend on your requirement.

User can be created with same command as above.

Let me know if more clarity is needed