What happens when you test it yourself? Are your users reporting any specific error messages?

Default database – Can you not set it in the client?

There's no method AFAIK to set a default database ala SQL Server but you could do it with init_connect…

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_init_connect

You could call a stored procedure here to compare the username against a table containing (username, default_database). I'd be careful not to override anything intentional the user wants to do.

Rhys

From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Karthick Subramanian
Sent: 09 February 2017 13:44
To: Maria Discuss <maria-discuss@lists.launchpad.net>
Subject: [Maria-discuss] ROLE and DEFAULT DATABASE

Hi,

Can anyone assist me on this below:

I have a database called appdb. I need to grant access to this db to different users say: api_user, app_user, portal_user. 

So I created a ROLE as APP_ROLE and GRANT as below:

GRANT SELECT, INSERT, UPDATE, DELETE ON APPDB.* TO APP_ROLE;

Later I assign the Role to user:

GRANT APP_ROLE TO API_USER@'%' IDENTIFIED BY 'YOU';

SET DEFAULT ROLE APP_ROLE TO API_USER@'%';

But the problem now is:

Application team want to choose the database when they establish connection. But they couldn't use APPDB. 

So they couldn't see  any db objects listed in APPDB. Is there a way we can assign a default database also for user.