topic Re: Read only Permission to Redshift table via SAS DI studio in Administration and Deployment

Read only Permission to Redshift table via SAS DI studio

YNWA1 — Mon, 27 May 2024 01:36:28 GMT

Hi,

I am new to SAS Admin. I am looking to give read-only permission to Redshift tables in SAS DI Studio for a specific SAS user group in PROD environment.

Example:

CDP SAS Users Group - I have assigned it to Redshift Read Only Users

I have 2 set of auth domains I have created for 2 groups

I have a library to Redshift access using CDP_Redshift_Prod as the write access to Redshift. See below

LIBNAME ROCOUR SASIORST DSN=prod_cdp_ods_sas SCHEMA=xxxx AUTHDOMAIN="CDP_Redshift_Prod" ;

My issue is, I only want to give users of the CDP SAS Users to have read-only metadata and read only data to the redshift tables. How can I achieve this as I cannot change the configuration of the existing library to use CDP_Redshift_Prod_RO.

I tried using the Redshift Write Users group to CDP SAS Users and giving only read only via ACT but I can still write to the Redshift table.

Any help you be more than welcome.

Re: Read only Permission to Redshift table via SAS DI studio

PaulHomes — Mon, 27 May 2024 02:06:49 GMT

I think your primary issue relates to understanding how the Read/Write/Created/Delete/... metadata permissions are implemented. ReadMetadata, WriteMetadata and CheckInMetadata are used to control the ability to view and update SAS metadata for the library and Read, Write, Create and Delete can be used to control the ability to view and update the data behind the library, but only in certain circumstances. In order for Read, Write, Create and Delete to be enforced, the library has to be assigned using an engine that supports and enforces them, such as the SAS Metadata Libname Engine. You also need to consider the possibility of users attempting to bypass the use of that library engine. The simplest and most robust thing to do is to instead enforce those data level permissions in the underlying data source itself, in this case Redshift. Since you are already using 2 possible shared logins, one for read/write access and one for read-only access, I would suggest making sure those logins in Redshift only have that level of access to the data. That way the data level permissions are enforced there and in SAS metadata you just need to make sure that users only have access to one appropriate shared login. Use a single Redshift authentication domain and put the shared read/write access login on one group (Redshift Write Users) and the shared read access login on the other group (Redshift Read Only Users). Finally, ensure a user is only a member of one group or the other (either via direct or appropriate nested membership) depending on the access they should have.

Re: Read only Permission to Redshift table via SAS DI studio

Patrick — Mon, 27 May 2024 03:45:52 GMT

Below superseded by Paul Holmes advice.

On a metadate level this should just be about group memberships.

On a physical level: DB access is granted by the credentials stored in the authdomain so everyone with read access to this authdomain will have the same access (use the same credentials). Anyone can also issue their own libname statement using this authdomain so the only way to secure this is via read/no read to the authdomain metadata object.

If your library is pre-assigned (=DIS won't generate a libname statement) then a "hack" to get what you want could be:

1. Create another authdomain CDP_Redshift_Prod_RO with a user that got only read access to the DB

2. Only give users with write access to Redshift metadata read access to authdomain CDP_Redshift_Prod

3. In the autoexec check metadata membership of the user under which the process executes. If this user isn't in the group for write access to Redshift but is in the group for read access to Redshift then issue libname statement LIBNAME ROCOUR SASIORST DSN=prod_cdp_ods_sas SCHEMA=xxxx AUTHDOMAIN="CDP_Redshift_Prod_RO " ; which then will overwrite the pre-assigned one.

This "overwrite" libname statement must be in a usermods autoexec that gets processed both by batch and online sessions (like in the usermods under the App server).

This approach is not perfect and you need to ensure to keep this coded libname statement fully in sync with the metadata definition of the pre-assigned version.

Re: Read only Permission to Redshift table via SAS DI studio

SASKiwi — Mon, 27 May 2024 02:22:45 GMT

It looks like you are using a service account to access Redshift. I suggest you switch the current service account for one that has only read-only access, but leave the rest of the Authentication Domain the same. Note, you can't enforce read-only access via metadata permissions alone, as these can always be bypassed by assigning a LIBNAME in code if the user has access to a write-capable account.

Re: Read only Permission to Redshift table via SAS DI studio

Patrick — Mon, 27 May 2024 02:23:53 GMT

Use a single Redshift authentication domain and put the shared read/write access login on one group (Redshift Write Users) and the shared read access login on the other group (Redshift Read Only Users). Finally, ensure a user is only a member of one group or the other (either via direct or appropriate nested membership) depending on the access they should have.

@PaulHomes So with a single pre-assigned library that uses an authdomain how does this work? Can you define two user ids/pw for the same authdomain and then secure who gets access/uses which one?

Re: Read only Permission to Redshift table via SAS DI studio

PaulHomes — Mon, 27 May 2024 03:29:11 GMT

@Patrick Yes, you can have 2 different logins in the same auth domain on 2 different groups and let SAS automatically pick the right one for you. There is nothing special that has to be done to secure them - you only have access to logins on your own identity or on any group within your identity hierarchy (unless you are unrestricted in which case you can see all logins but have no access to their passwords). When SAS (9.2 onwards) needs a login it will walk your identity hierarchy and find the login for the required auth domain that is closest to you in your identity hierarchy i.e. on your identity, then your direct groups memberships , then second level via nested groups etc. I just verified this with 2 different users in 2 different groups where those groups have different logins in the same auth domain. I created a made up ODBC library for a made up ODBC data server tagged with that same auth domain. When the libname is not pre-assigned, so you can use Display Libname Statement in SAS MC Data Library Manager, those users see their correct shared logins.

When the library is pre-assigned, and used in a normally configured SAS Workspace Server, it should use the metadata identity of the launch user to find the appropriate login in the same way. I don't have a readily available SAS/Access accessible ODBC database to hand to double check this all the way through to the database itself but that is my understanding.

Re: Read only Permission to Redshift table via SAS DI studio

PaulHomes — Mon, 27 May 2024 03:38:00 GMT

@YNWA1 - I also forgot to mention that, being a new SAS Admin, I would recommend you avoid denying permissions to any groups other than PUBLIC or SASUSERS otherwise you can end up with conflicts that can be hard to understand and troubleshoot. I would thoroughly recommend reading up on best practices for SAS metadata security such as https://communities.sas.com/t5/SAS-Communities-Library/Golden-Rules-for-Security-Model-Design/ta-p/373542