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.