MS access link to Teradata subset of views that is granted from a role

UDA

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 xxx

If 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 3.06.00.06
5 REPLIES
Teradata Employee

Re: MS access link to Teradata subset of views that is granted from a role

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.

Re: MS access link to Teradata subset of views that is granted from a role

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

Re: MS access link to Teradata subset of views that is granted from a role

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 views
No HELP DATABASE
Enable Custom Catalog Mode for 2.x Applications

Re: MS access link to Teradata subset of views that is granted from a role

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

Re: MS access link to Teradata subset of views that is granted from a role

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.