0

Role:DB Admin

I have recently installed MySQL and also created the basic user as scott but my doubt is regarding the roles. I don't understand different roles available for the user which I have created.

I want the user to simply create tables, must be able to perform delete and update operations but restrict him from able to change the password and view the complete database available (all the records). Based on my above requirement which kind of role should be given to scott?

  • Roles are groupings of privileges.You grant SELECT,INSERT, blah privilegs to a role and then assign this role to a user.But I didnt know that Mysql has roles. – Mihai Jul 11 '14 at 7:30
  • Are you serious you didn't know about it? I guess based on the roles only we are granting permissions to the user – user285oo6 Jul 11 '14 at 9:16
  • @Mihai MySQL Server does not support the concept of roles or groups. This is a client-only -Workbench- implementation (but a nice one): "To aid in assigning privileges to MySQL Server users, MySQL Workbench introduces the concept of Administrative Roles." dev.mysql.com/doc/workbench/en/… – jynus Jul 11 '14 at 9:59
3

Roles and groups are not supported by MySQL Server. Groups are sets of users. The concept of roles is a group of permissions that can be assigned to a group or a user.

Workbench emulates roles by handling them at client side: Adding a role on workbench. You can access this tab on the Users and privileges tab.

I want the user to simply create tables, must be able to perform delete and update operations but restrict him from able to change the password and view the complete database available (all the records).

In order to do what you want, you can add a new role on the previous mentioned tab with no administrative privileges and "all object rights" and "DDL rights" for a particular schema (Add entry...). You can fine-grain it more by not adding INSERT rights or DROP right, as you see it conveniently. If you restrict it to a single schema, it won't be able to see or modify any other table from others, except the administrative schema information_schema.

If you feel like creating the user by using SQL (when connected as an administrator):

CREATE USER <username>@<host> IDENTIFIED BY '<password>';
GRANT ALL ON <schema>.* TO <username>@<host>;

Obviously, change <username>, <host>, <password> and <schema> for the right values.

Please note that a user in MySQL can always change its own password, but not that of others (unless it is an administrator).

| improve this answer | |
  • 1
    Damn, you know Mysql is left behind when even clients seem to get ahead. – Mihai Jul 11 '14 at 12:00

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.