1

By default we have role as locked accounts

Here we create a demo role account -

CREATE ROLE role1;
GRANT SELECT ON *.* TO role1;
FLUSH PRIVILEGES;

Now, all role accounts by default are locked accounts. But we can unlock a role account.

SELECT account_locked FROM mysql.user
    -> WHERE user = "role1";
+----------------+
| account_locked |
+----------------+
| Y              |
+----------------+

ALTER USER role1
    -> ACCOUNT UNLOCK;

SELECT account_locked FROM mysql.user
    -> WHERE user = "role1";
+----------------+
| account_locked |
+----------------+
| N              |
+----------------+

Now, we create user user1 and grant role role1 to it -

CREATE USER user1 IDENTIFIED BY "********";
GRANT role1 TO user1;
FLUSH PRIVILEGES;


SHOW GRANTS FOR user1;
+-----------------------------------+
| Grants for user1@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%` |
| GRANT `role1`@`%` TO `user1`@`%`  |
+-----------------------------------+

Now user1 logs in -

mysql -u user1 -p
Enter password: 

SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+

SET ROLE ALL;

SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `role1`@`%`    |
+----------------+

SHOW DATABASES;
+---------------------+
| Database            |
+---------------------+
| database1           |
| database2           |
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+

USE database1;

+------------+
| DATABASE() |
+------------+
| database1  |
+------------+

SHOW TABLES;
+-------------------+
| Tables_in_company |
+-------------------+
| table1            |
| table2            |
+-------------------+

DESCRIBE table1;
............

SELECT * FROM table1;
............

Everything seems to be working as it should.

So, is there any disadvantage if role account is unlocked?

Payel Senapati
  • 1,134
  • 1
  • 11
  • 27
  • Unlocking the account allows a user to login to that account. Roles are not normally intended to be used as ordinary login accounts, but I can't think of anything bad that could happen as a result. – Barmar Dec 15 '21 at 20:16
  • But when role accounts are created no password is specified. So how can a user login to role account? – Payel Senapati Dec 15 '21 at 20:21
  • You could also give it a password when you unlock it. – Barmar Dec 15 '21 at 20:24
  • Ok, a sort of interface analogy for Java I guess – Payel Senapati Dec 15 '21 at 20:28
  • Roles are basically just accounts with different default settings, and the ability to link other accounts to them. But you can alter them to be like user accounts. – Barmar Dec 15 '21 at 20:30
  • Yeah I mean like in Java interface is used for inheritance, similarly role has some inheritance concept. – Payel Senapati Dec 15 '21 at 20:34

0 Answers0