MySQL Bugs: #100361: LDAP authorization with 1 user on 2 schemas is not possible

Bug #100361	LDAP authorization with 1 user on 2 schemas is not possible
Submitted:	29 Jul 2020 8:12	Modified:	29 Jul 2020 12:47
Reporter:	Oli Sennhauser	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Pluggable Authentication	Severity:	S4 (Feature request)
Version:	8.0.21	OS:	Linux (n.a.)
Assigned to:		CPU Architecture:	x86 (n.a.)
Tags:	authorization, LDAP, plugin

Description:
LDAP authorization with 1 user on 2 schemas (2 roles, 2 proxied user) is not possible.

Scenario: 1 MySQL instance with 2 schemas (different stages, e.g. DEV and QA). When I connect with my personal user I always will end up with the same privileges (provided by LDAP) and I have no chance to switch this later with my user.

Automatization: Granting the privileges from both schemas to one proxied user is not an option because this will be rolled-out automatized in a standardized way.

How to repeat:
SQL> SHOW SCHEMAS;
+---------------------+
| Database            |
+---------------------+
| App1_DEV            |
| App1_QA             |
+---------------------+

Users:

+------------------+-----------------------------------+----------------+----------------------------+
| user             | host                              | account_locked | plugin                     |
+------------------+-----------------------------------+----------------+----------------------------+
|                  | %                                 | N              | authentication_ldap_simple |
| App1_DEV_Admin   | %                                 | N              | mysql_no_login             |
| App1_DEV_Read    | %                                 | N              | mysql_no_login             |
| App1_DEV_Write   | %                                 | N              | mysql_no_login             |
| App1_QA_Admin    | %                                 | N              | mysql_no_login             |
| App1_QA_Read     | %                                 | N              | mysql_no_login             |
| App1_QA_Write    | %                                 | N              | mysql_no_login             |
+------------------+-----------------------------------+----------------+----------------------------+

2 Users:

CREATE USER ''@'%' IDENTIFIED WITH 'authentication_ldap_simple' AS '#  App1_DEV_Admin,App1_DEV_Write,App1_DEV_Read,App1_QA_Admin,App1_QA_Write,App1_QA_Read' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

CREATE USER 'App1_DEV_Read'@'%' IDENTIFIED WITH 'mysql_no_login' REQUIRE SSL PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
GRANT SELECT ON App1_DEV.* TO 'App1_DEV_Read'@'%';

CREATE USER 'App1_QA_Read'@'%' IDENTIFIED WITH 'mysql_no_login' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
GRANT SELECT ON App1_QA.* TO 'App1_QA_Read'@'%';

ldapsearch for user App_Read_User (get assigned groups): 
ldap_initialize( ldap://mizhwadi03:389 )
filter: (member=CN=App_Read_User,OU=Test,DC=base,DC=int)
requesting: cn 
# extended LDIF
#
# LDAPv3
# base <OU=Test,DC=base,DC=int> with scope subtree
# filter: (member=CN=App_Read_User,OU=Test,DC=base,DC=int)
# requesting: cn 
#

# App1_DEV_Read, Test, base.int
dn: CN=App1_DEV_Read,OU=Test,DC=base,DC=int
cn: App1_DEV_Read

# App1_QA_Read, Test, base.int
dn: CN=App1_QA_Read,OU=Test,DC=base,DC=int
cn: App1_QA_Read

# App2_DEV_Read, Test, base.int
dn: CN=App2_DEV_Read,OU=Test,DC=base,DC=int
cn: App2_DEV_Read

# App2_QA_Read, Test, base.int
dn: CN=App2_QA_Read,OU=Test,DC=base,DC=int
cn: App2_QA_Read

# search result
search: 2
result: 0 Success

# numResponses: 5
# numEntries: 4

mysql> select user(), current_user(), @@proxy_user;
+-------------------------+-----------------+--------------+
| user()                  | current_user()  | @@proxy_user |
+-------------------------+-----------------+--------------+
| App_Read_User@localhost | App1_DEV_Read@% | ''@'%'       |
+-------------------------+-----------------+--------------+

2020-07-28T09:03:47.388937Z 14 [Note] [MY-013338] [Server] Plugin authentication_ldap_simple reported: 'User group retrieval: Group object has user information'
2020-07-28T09:03:47.389015Z 14 [Note] [MY-011763] [Server] Plugin authentication_ldap_simple reported: 'Searched group name: App1_DEV_Read'
2020-07-28T09:03:47.389084Z 14 [Note] [MY-011763] [Server] Plugin authentication_ldap_simple reported: 'Searched group name: App1_QA_Read'
2020-07-28T09:03:47.389150Z 14 [Note] [MY-011763] [Server] Plugin authentication_ldap_simple reported: 'Searched group name: App2_DEV_Read'
2020-07-28T09:03:47.389215Z 14 [Note] [MY-011763] [Server] Plugin authentication_ldap_simple reported: 'Searched group name: App2_QA_Read'
2020-07-28T09:03:47.389241Z 14 [Note] [MY-013340] [Server] Plugin authentication_ldap_simple reported: 'Get user proxy'
2020-07-28T09:03:47.389270Z 14 [Note] [MY-013342] [Server] Plugin authentication_ldap_simple reported: 'Get user proxy: configured mapping info:   App1_DEV_Admin,App1_DEV_Write,App1_DEV_Read,App1_QA_Admin,App1_QA_Write,App1_QA_Read'
2020-07-28T09:03:47.389297Z 14 [Note] [MY-013344] [Server] Plugin authentication_ldap_simple reported: 'Process group proxy mapping'
2020-07-28T09:03:47.389323Z 14 [Note] [MY-013351] [Server] Plugin authentication_ldap_simple reported: 'Getting next mapping information'
2020-07-28T09:03:47.389349Z 14 [Note] [MY-013352] [Server] Plugin authentication_ldap_simple reported: 'Parsing mapping, current state: 0  delimiter char: = '
2020-07-28T09:03:47.389395Z 14 [Note] [MY-013355] [Server] Plugin authentication_ldap_simple reported: 'Trimming left spaces'
2020-07-28T09:03:47.389422Z 14 [Note] [MY-013356] [Server] Plugin authentication_ldap_simple reported: 'Checking if current characters is quote'
2020-07-28T09:03:47.389450Z 14 [Note] [MY-013352] [Server] Plugin authentication_ldap_simple reported: 'Parsing mapping, current state: 4  delimiter char: = '
2020-07-28T09:03:47.389475Z 14 [Note] [MY-013346] [Server] Plugin authentication_ldap_simple reported: 'Processing delimiter'
2020-07-28T09:03:47.389502Z 14 [Note] [MY-013347] [Server] Plugin authentication_ldap_simple reported: 'Processing delimiter, separator = not found, resetting position'
2020-07-28T09:03:47.389527Z 14 [Note] [MY-013348] [Server] Plugin authentication_ldap_simple reported: '"Processing delimiter, failed to get data for = separator try for separator ,."'
2020-07-28T09:03:47.389562Z 14 [Note] [MY-013353] [Server] Plugin authentication_ldap_simple reported: 'Parsing mapping info, LDAP group: App1_DEV_Admin MySQL proxy: '
2020-07-28T09:03:47.389591Z 14 [Note] [MY-013351] [Server] Plugin authentication_ldap_simple reported: 'Getting next mapping information'
2020-07-28T09:03:47.389617Z 14 [Note] [MY-013352] [Server] Plugin authentication_ldap_simple reported: 'Parsing mapping, current state: 0  delimiter char: = '
2020-07-28T09:03:47.389641Z 14 [Note] [MY-013355] [Server] Plugin authentication_ldap_simple reported: 'Trimming left spaces'
2020-07-28T09:03:47.389666Z 14 [Note] [MY-013356] [Server] Plugin authentication_ldap_simple reported: 'Checking if current characters is quote'
2020-07-28T09:03:47.389690Z 14 [Note] [MY-013352] [Server] Plugin authentication_ldap_simple reported: 'Parsing mapping, current state: 4  delimiter char: = '
2020-07-28T09:03:47.389714Z 14 [Note] [MY-013346] [Server] Plugin authentication_ldap_simple reported: 'Processing delimiter'
2020-07-28T09:03:47.389739Z 14 [Note] [MY-013347] [Server] Plugin authentication_ldap_simple reported: 'Processing delimiter, separator = not found, resetting position'
2020-07-28T09:03:47.389764Z 14 [Note] [MY-013348] [Server] Plugin authentication_ldap_simple reported: '"Processing delimiter, failed to get data for = separator try for separator ,."'
2020-07-28T09:03:47.389789Z 14 [Note] [MY-013353] [Server] Plugin authentication_ldap_simple reported: 'Parsing mapping info, LDAP group: App1_DEV_Write MySQL proxy: '
2020-07-28T09:03:47.389815Z 14 [Note] [MY-013351] [Server] Plugin authentication_ldap_simple reported: 'Getting next mapping information'
2020-07-28T09:03:47.389839Z 14 [Note] [MY-013352] [Server] Plugin authentication_ldap_simple reported: 'Parsing mapping, current state: 0  delimiter char: = '
2020-07-28T09:03:47.389863Z 14 [Note] [MY-013355] [Server] Plugin authentication_ldap_simple reported: 'Trimming left spaces'
2020-07-28T09:03:47.389887Z 14 [Note] [MY-013356] [Server] Plugin authentication_ldap_simple reported: 'Checking if current characters is quote'
2020-07-28T09:03:47.389912Z 14 [Note] [MY-013352] [Server] Plugin authentication_ldap_simple reported: 'Parsing mapping, current state: 4  delimiter char: = '
2020-07-28T09:03:47.389936Z 14 [Note] [MY-013346] [Server] Plugin authentication_ldap_simple reported: 'Processing delimiter'
2020-07-28T09:03:47.389960Z 14 [Note] [MY-013347] [Server] Plugin authentication_ldap_simple reported: 'Processing delimiter, separator = not found, resetting position'
2020-07-28T09:03:47.389990Z 14 [Note] [MY-013348] [Server] Plugin authentication_ldap_simple reported: '"Processing delimiter, failed to get data for = separator try for separator ,."'
2020-07-28T09:03:47.390016Z 14 [Note] [MY-013353] [Server] Plugin authentication_ldap_simple reported: 'Parsing mapping info, LDAP group: App1_DEV_Read MySQL proxy: '
2020-07-28T09:03:47.390065Z 14 [Note] [MY-011756] [Server] Plugin authentication_ldap_simple reported: 'User is authenticated as: App1_DEV_Read external user: App1_DEV_Read'

Suggested fix:
Some similar mechanism like:

SET DEFAULT ROLE admin TO 'App1_QA_Read@%';

Would be nice.

SET DEFAULT PROXIED USER TO 'App1_QA_Read@%';

Otherwise we have to ban more than one schema per instance.

Hello Oli,

Thank you for the feature request!

Thanks,
Umesh

User group mapping information is ordered (in which they appears grp1,grp2 ), may be we can do alter user and change LDAP group mapping order?

Best Regards
Yashwant

Hi Yashwant

Thanks for your suggestion. Who is "we"? If "we" is MySQL, I do not care how you solve the problem. But I do not think that arbitrarily changing the group mapping is a robust plan and will cause troubles sooner or later.

If "we" is customer: Think about production cloud systems. On massive rolled out systems you do not want to change Accounts just to make a personal user capable to access the data he is entitled to.

So the concept like MySQL uses in the ROLEs seems not too bad to me. So I as a user can influence it. And I do not need an admin for it.

In my very keen dreams I would like to connect to a database. LDAP tells me what group/roles I have. And I can choose which one I need/want now.

So integrating it into MySQL ROLEs or a similar concept would not be the worst.

Oli, I meant "We" as customer or any other user who has similar setup.
And this is a just a suggestion if it can be useful.

Any update about this feature request? Some rough time estimate until when a decision is to expect.