topic How do I Hide select tables in a sas database library in SAS EG 8.x ? in Administration and Deployment

How do I Hide select tables in a sas database library in SAS EG 8.x ?

shoin — Thu, 01 Jun 2023 15:43:46 GMT

SAS 9.4 TS1M7, WIN x64 server

Issue: How do I stop some tables to appear in EG (8.x) for a pre-assigned MS SQLSRVR Database library?

Details:

1. A service ID is used to access a SQL DB, a sas sqlsrvr SAS library is created with a group (db-access). Only db-access group member access/view the said library.

2. Library is pre-assigned.

3. Library has a mix of tables and views

4. Currently only few views are 'registered' in metadata (out of 200 views and tables by design)

5. All views and tables appear in SAS EG session for all members of db-access group, we want only views to appear in the EG session.

6. The db-access group has explicit RM and appropriate grants and 'deny' for all other in metadata > 'Authorization'

Any idea how to set this up? My own thought is that since the access is via a service ID, we could ask DBA to remove READ GRANTS for the tables for the service ID. I wanted to get your opinion to see any simpler method exists.

Thank you in advance (TIA)

-S

Re: How do I Hide select tables in a sas database library in SAS EG 8.x ?

SASKiwi — Thu, 01 Jun 2023 21:45:19 GMT

Controlling access via user account permissions is easiest and bombproof. You can control access via metadata permissions but that can be bypassed unless you lockdown all SAS libraries so I wouldn't recommend it.

Re: How do I Hide select tables in a sas database library in SAS EG 8.x ?

Kurt_Bremser — Fri, 02 Jun 2023 05:18:15 GMT

For the views to work, the underlying tables must also be accessible to the SAS session, so you cannot "hide" them.

Re: How do I Hide select tables in a sas database library in SAS EG 8.x ?

shoin — Fri, 09 Jun 2023 19:20:14 GMT

These are Synapse views.

Re: How do I Hide select tables in a sas database library in SAS EG 8.x ?

Kurt_Bremser — Fri, 09 Jun 2023 19:28:56 GMT

SQL views are executed when used, in the context of the calling process. So this process needs to have access to the tables used in the view.

If you need to restrict access to certain parts of data, create separate tables, not views