MySQL – How to Set Up Privileges for Different Schemas

MySQLpermissionsschema

How do I set up 2 schemas with a total of 5 users?

Schemas:

  • hp
  • hpc

Users:

  • dba – has full access to both schemas (hp, hpc)
  • hp_dbo – owns all objects in hp schema
  • hpc_fbo – owns all objects in hpc schema
  • hp_application_user – has select, create, drop, alter, execute privilege in hp schema
  • hpc_application_user – has select, create, drop, alter, execute privilege in hp schema

How do I realize this privilege structure? I was playing around with MySQL-Workbench but I ended up setting wrong permissions and losing access to my database for my application…

Best Answer

You can always have root as user t access when all fails, so don't touch the user and give a string password.

Besides that you can always recover whwn you log in as su or administrator see manual

CREATE USER 'dba'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hp. * TO 'dba '@'localhost';
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hpc. * TO 'dba '@'localhost';
CREATE USER 'hp_dbo '@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hp. * TO 'hp_dbo'@'localhost';
CREATE USER 'hp_application_user  '@'localhost' IDENTIFIED BY 'mypassword';
GRANT SELECT,CREATE,DROP,EXECUTE  ON hp. * TO 'hp_application_user '@'localhost';
CREATE USER 'hpc_dbo '@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hpc.* TO 'hpc_dbo'@'localhost';
CREATE USER 'hpc_application_user'@'localhost' IDENTIFIED BY 'mypassword';
GRANT SELECT,CREATE,DROP,EXECUTE  ON hp. * TO 'hpc_application_user`@'localhost';

If you need more privileges for the user hpc_application_user or hpc_application_user you can use the same command with the privilege you want to add or revoke

If you have multiple user with the same privileges you can define a role

Related Question