ODBC viewing the table information
Hi community,
I have a rights problem when giving a user permission to perform some select queries on particular tables. So I have created a user with the following rights.
.png)
So this all works well. No problem so far. But the customer is using a program where you can easily build visually the query by selecting the table, choose the right colums etc. So the problem we have is when I give the user the Role %All the tables are shown.
.png)
And the user is allowed to query all tables. But we only need to see the granted tables granted, so role %All is not valid but is the only Role, Permission I have tried so far that shows the table information correctly.
So what do I need to do for only showing table information (schema information) for the granted tables.
Comments
Have you tried just giving Read rights?
The customer is able to use one the tables with the select right when they manually query it. But with query design tools and bi tools you are able to drag drop tables on the canvas and make your query.
Other databases with other odbc drivers are working as expected.
Tried to give INFORMATION_SCHEMA enough rights but this did not solve the issue.
I'm a bit confused here - granting the user SELECT privileges on those tables didn't allow your customer's ODBC client to work? It sounds like %ALL is working as designed (if you have %ALL you own the system and can therefore see everything). What does the client tool look like when you grant just SELECT access to certain tables or schemas? Can you try a different ODBC client to see how that behaves (I like WinSQL)?
ODBC log and maybe Audit log can contain additional information.
Enable ODBC log. Maybe there would be errors pointing to the root of the issue?
Maybe something to do with INFORMATION_SCHEMA.