4

Is there a way to use CREATE ROLE with MySQL?

It's possible to create roles with PostgreSQL but when I try with MySQL it returns this error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'role famille' at line 1

James Fenwick
  • 2,190
  • 1
  • 20
  • 30
Nadk
  • 173
  • 2
  • 3
  • 9
  • 1
    No mysql does not have roles.But there is http://www.databasejournal.com/features/mysql/assigning-roles-to-mysql-users.html – Mihai Oct 19 '14 at 17:10
  • Thanks for your super fast reply ^^ as he said in your link it's seems impossible with MYSQL, we have to use workbench or SecuRich ... thank you again – Nadk Oct 19 '14 at 17:22

4 Answers4

14

I note that there are people claiming that MySQL doesn't have a role implementation so I would like to add that MySQL 8.0 has a working role implementation:

Example:

CREATE ROLE r1;
GRANT r1 TO myuser@localhost;
GRANT SELECT, INSERT,UPDATE ON db1.* TO r1;

reference: https://dev.mysql.com/doc/refman/8.0/en/roles.html

You can download source code and tested binaries here: https://dev.mysql.com/downloads/mysql/

At this moment in time you have to choose "development release" and MySQL server 8.0.1

Hyperdrive
  • 301
  • 3
  • 5
  • 2
    MySQL 8 has introduced roles: https://dev.mysql.com/doc/refman/8.0/en/roles.html This answer should be marked as the best one. – bjmi Sep 29 '18 at 10:30
8

MySQL 5 has no ROLES (https://dev.mysql.com/doc/refman/5.7/en/faqs-security.html#faq-mysql-have-builtin-rbac).

If you would be looking for RDBMS that is compatible with MySQL that would be MariaDB. MariaDB has Role-based access control that MySQL lacks and is open-source.

James Fenwick
  • 2,190
  • 1
  • 20
  • 30
Developer Marius Žilėnas
  • 5,813
  • 1
  • 41
  • 70
2

I came here looking for the syntax for creating users in MySQL. Postgres, as of version 8.1, does not refer to users and groups anymore but combines both in roles. Never having used any older version of Postgres, roles are how I think of users in the database context.

So for me the suitable answer would’ve been that MySQL calls them users, and they can be created like so:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

as described under “Account Management Statements” in the MySQL manual.

I hope this is helpful for someone.

Dawn Drescher
  • 901
  • 11
  • 17
  • MySQL 8+ has roles. Both users and roles are authentication identifiers but they are not the same. CREATE ROLE sets the attributes of the authid such that you can't authenticate with it per default. CREATE USER and CREATE ROLE also have different privilege requirements which could be significant depending on your system security model. – Hyperdrive Sep 30 '18 at 11:59
1

You can try what is written in the following documentation:

http://dev.mysql.com/doc/workbench/en/wb-mysql-connections-navigator-management-users-and-privileges.html

http://ftp.nchu.edu.tw/MySQL/doc/workbench/en/wb-adding-roles.html

Matthew Sprindzuk
  • 21
  • 1
  • 5