1

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...

1

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

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.