All Forums UDA
Ron Fleck 3 posts Joined 05/09
29 May 2009
MS access link to Teradata subset of views that is granted from a role

We are using MS Access as a front end to Teradata. We are trying to move to granting ROLES to users so that they can see only a certain number of views within the DB. But when we specify that DB as the default in the ODBC default database, we get error 3524 - The user does not have any access to database xxxIf we specify a Default DB in the ODBC connection wherein the user has access to ALL the views within that DB (either via ROLE or profile access), then MS Access connects and shows us all those views.Is there a way to have an MS Access link show the user ONLY that subset of views based on a role that is granted to just a few views in the Teradata database?Using ODBC driver

Fred 1096 posts Joined 08/04
30 May 2009

That's what the "Use X Views" driver option is for; those views restrict the metadata APIs to return only objects for which the user has some access. You will likely need to set the "No HELP DATABASE" option also, to ensure the driver does not use a shortcut method for the special case of enumerating all objects in a database.

Ron Fleck 3 posts Joined 05/09
01 Jun 2009

I already had the Xviews set.When I checked No help database, I could connect, but could not see any of the views in that database. I further tried connecting to a non-existent database, and it allowed me to connect to Teradata, but of course there was nothing visible.I then tried re-linking to the DB where I did have access to all views (and that were previously visible) but nothing visible this time. Unchecking the No Help for this database once again made all the views visible but the one with partial access granted by a role once again gave the 3524 error

Ron Fleck 3 posts Joined 05/09
10 Jun 2009

I did further experimenting and found that within the ODBC driver configuration I also had to have the "Enable Custom Catalog Mode for 2.x Applications" checked under the Advanced tab. So what allowed this to work was having ALL the following checked in the ODBC driver setup:Use X viewsNo HELP DATABASEEnable Custom Catalog Mode for 2.x Applications

ca_simm 1 post Joined 05/10
21 May 2010

Thanks for taking the time to post this solution Ron, you just saved a lot of people, including me a lot of wasted time.


Pearl0811 1 post Joined 06/13
05 Jun 2013

I tried setting suggested 3 parameters in ODBC drivers - Use X views, No HELP DATABASE and Enable Custom Catalog Mode for 2.x Applications and still all views are visible to us instead of subset of views based on a role.
Please suggest us what other setting is required to avoid this situation.

You must sign in to leave a comment.