I have a user who I want to restrict to only seeing certain databases and tables.
Currently DBC.Tables and other associated views allow them to see table structures on most databases.
How do we lock them down to only seeing rows relevant to a small list of databases they are allowed to access?
Revoke the access rights for dbc.Tables etc. and tell the users to use the X-versions, e.g. there's dbc.Tables and dbc.TablesX.
dbc.Tables returns rows for all objects within the system, while dbc.TablesX only returns rows for objects the submitting user has any kind of access to.
But beware, the X-views are much more complex and will run slower.
Btw, you shouldn't use the old views anymore, better switch to TablesV and TablesXV instead.
agree on using tablesV and tablesXV.
But we have tools here (e.g. sas, bo) that generate queries that use tablesX and the as guys claims that there is no way they can change that.