MySQL Bugs: #84244: Distinguish roles and plain users in mysql.user

Bug #84244	Distinguish roles and plain users in mysql.user
Submitted:	17 Dec 2016 11:27	Modified:	9 Feb 2017 12:54
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a role is created, a row is added to the mysql.user table.  But the row is not clearly distinguisable from a row describing a plain user.

How to repeat:
CREATE role myrole;
CREATE USER myuser;
GRANT myrole TO myuser;
SELECT * FROM mysql.user WHERE `host` = '%';

The only difference I see is the value of the account_locked column.  It is "Y" for the View and "N" for the Table. I dont know if this can reliably be used for distinguishing roles and plain users. I dont find any help in documentation. 

The server also does not distinguish here:

DROP USER myuser;
CREATE USER myuser;
GRANT myuser TO myrole; -- success; what I believe is wrong

Suggested fix:
Consider either

1) don't add roles to mysql.user but to a new table mysql.role
2) add a new column to mysql.user `ìs_role`.

I also think that this page https://dev.mysql.com/doc/refman/8.0/en/grant-tables.html needs an update about this.

Another example that confuses the server, as it cannot distinguish roles and plain users:

CREATE role someone;
CREATE USER someone; -- Error Code: 1396: Operation CREATE USER failed for 'someone'@'%'
DROP USER someone; -- success. Erhh??? The role was dropped as shown by
SELECT COUNT(*) FROM mysql.user WHERE `host` = '%'; -- returns "0"

In my first post please correct

It is "Y" for the View and "N" for the Table.
>>
It is "Y" for the role and "N" for the user.

Changin severity.

Hello Peter,

Thank you for the report.

Thanks,
Umesh

Thanks for verification.

I simply have a requirement to 
1) display a list of users that are not roles
2) display a list of roles that are not plain users
3) identify if a an entry in mysql.user represents a role or a plain user
-- and I don't think this is possible now.

MariaDB 10.2 got it right:

SHOW COLUMNS FROM mysql.user; -- note the "is_role" column in the result. 

FIELD                   TYPE                               NULL    KEY     DEFAULT   Extra   
----------------------  ---------------------------------  ------  ------  --------  --------
HOST                    CHAR(60)                           NO      PRI                       
USER                    CHAR(80)                           NO      PRI                       
PASSWORD                CHAR(41)                           NO                                
Select_priv             ENUM('N','Y')                      NO              N                 
Insert_priv             ENUM('N','Y')                      NO              N                 
Update_priv             ENUM('N','Y')                      NO              N                 
Delete_priv             ENUM('N','Y')                      NO              N                 
Create_priv             ENUM('N','Y')                      NO              N                 
Drop_priv               ENUM('N','Y')                      NO              N                 
Reload_priv             ENUM('N','Y')                      NO              N                 
Shutdown_priv           ENUM('N','Y')                      NO              N                 
Process_priv            ENUM('N','Y')                      NO              N                 
File_priv               ENUM('N','Y')                      NO              N                 
Grant_priv              ENUM('N','Y')                      NO              N                 
References_priv         ENUM('N','Y')                      NO              N                 
Index_priv              ENUM('N','Y')                      NO              N                 
Alter_priv              ENUM('N','Y')                      NO              N                 
Show_db_priv            ENUM('N','Y')                      NO              N                 
Super_priv              ENUM('N','Y')                      NO              N                 
Create_tmp_table_priv   ENUM('N','Y')                      NO              N                 
Lock_tables_priv        ENUM('N','Y')                      NO              N                 
Execute_priv            ENUM('N','Y')                      NO              N                 
Repl_slave_priv         ENUM('N','Y')                      NO              N                 
Repl_client_priv        ENUM('N','Y')                      NO              N                 
Create_view_priv        ENUM('N','Y')                      NO              N                 
Show_view_priv          ENUM('N','Y')                      NO              N                 
Create_routine_priv     ENUM('N','Y')                      NO              N                 
Alter_routine_priv      ENUM('N','Y')                      NO              N                 
Create_user_priv        ENUM('N','Y')                      NO              N                 
Event_priv              ENUM('N','Y')                      NO              N                 
Trigger_priv            ENUM('N','Y')                      NO              N                 
Create_tablespace_priv  ENUM('N','Y')                      NO              N                 
ssl_type                ENUM('','ANY','X509','SPECIFIED')  NO                                
ssl_cipher              BLOB                               NO              (NULL)            
x509_issuer             BLOB                               NO              (NULL)            
x509_subject            BLOB                               NO              (NULL)            
max_questions           INT(11) UNSIGNED                   NO              0                 
max_updates             INT(11) UNSIGNED                   NO              0                 
max_connections         INT(11) UNSIGNED                   NO              0                 
MAX_USER_CONNECTIONS    INT(11)                            NO              0                 
PLUGIN                  CHAR(64)                           NO                                
authentication_string   TEXT                               NO              (NULL)            
password_expired        ENUM('N','Y')                      NO              N                 
is_role                 ENUM('N','Y')                      NO              N                 
default_role            CHAR(80)                           NO                                
max_statement_time      DECIMAL(12,6)                      NO              0.000000

Both roles and users are in fact authorization identifiers and share the same namespace per design.

A role is functionally a role when it's present in role_edges. Any authorization identifier can be a role and any user can be a role (ie you can authenticate to any authorization identifier unless it's locked or otherwise restricted)

There's no artificial restriction embedded in the system to prevent this but you are free to do as you please.

As a DBA you might still have a personal intention that a specific authorization identifier must be a role, and then using a name scheme to communicate this intention is in fact not a bad choice. You can for example add a prefix "r_" to those authorization IDs which you intend to be roles and nothing else.