Permission for CRUD vs. DDL operations

[JayDZimmerman]

We have a library with a number of tables registered in metadata.  I want users to be able to perform CRUD operations (insert/update/delete) without being able to perform DDL operations (add/alter/remove).  Playing with authorizations on the SAS metadata side of things, I haven't had much luck differentiating between the two:  without granting "Delete" the user can't delete rows, but if I do grant "Delete" they can also delete the table itself.  Anything specific I should look at?

[SASKiwi]

Where are these tables? Are they SAS datasets or in an external database?

How are you trying to change them? Can you post example code?

[LinusH]

As @SASKiwi implies, the data need to reside in SAS for you to able to really enforce permissions.

Also, you need to allow access only using metadata engine. And to really be sure none is bypassing metadata permissions, metadata bound libraries might be the way to go.

Also, please describe how you test the permission, and what is actually happens.

[JayDZimmerman]

This is a BASE Library with explicitly registered tables (no pre-assignment).  The users are accessing it via Enterprise Guide, typically via Query Builders but also with a code node written for them.  The intent is that they be able to insert records into, update existing records, and delete records from these tables.  However, I do not want them to do anything to the table structures themselves, since I created the tables with indexes and check constraints.

As an example, I have a table named "INDICATORS" (structure not important for this exercise).  The authorizations on the metadata folder containing the library and tables allow RM,R,W for the user and deny all other rights.  When trying to run the query below, the resulting error is "ERROR: The Delete permission is required to delete data from table/view QOAUFL.INDICATORS.DATA.".

PROC SQL;
    DELETE FROM QOAUFL.INDICATORS;
QUIT;

Accordingly I add the "Delete" authorization to the metadata folder and the above statement runs as desired.  However, if I run the next statement, the table is deleted, which I do not want to be permitted.

PROC SQL;
    DROP TABLE QOAUFL.INDICATORS;
QUIT;

I'm unsure of how to set the authorizations to allow deletion of data within a table without allowing the deletion of the table itself.

[PaulHomes · Accepted solution]

The permissions required/enforced depend on where the data resides and the manner in which it is accessed. Consideration needs to be given to permissions at multiple layers: the metadata layer (for the metadata objects that describe the physical objects), permissions at the data layer and permissions at the storage layer - this could be 3rd party database, file system, etc.

Some examples/options:

1) When using a 3rd party database using a SAS/ACCESS engine: metadata permissions on folder, table and library objects govern who can see and manipulate the metadata "view" of those external tables but not the content of those tables. Permissions at the database authorization layer will determine who can do CRUD and DDL operations.

2) When using the SAS BASE engine WITHOUT the Metadata Libname Engine (MLE): metadata permissions on folder, table and library objects govern who can see and manipulate the metadata "view" of those SAS datasets but not the content of those SAS datasets. Metadata permissions RM, WM/WMM, CM will control who can register and unregister the data (the data permissions R,W,C,D will be ignored). File system access controls will determine who can manipulate the SAS dataset files (where it's effectively read-only or read/write).

3) When using the SAS BASE engine WITH the Metadata Libname Engine: metadata permissions on folder, table and library objects govern who can see and manipulate the metadata "view" of those SAS datasets and the content of those SAS datasets - i.e. in addition to metadata RM, WM/WMM, CM permissions, the data permissions R,W,C,D will also be enforced (but only when accessing through the MLE). File system access controls will still determine who can manipulate the physical SAS dataset files. The main thing to beware of here is that whilst you may present an MLE view, savvy users with file system write permissions will be able to bypass it by coding their own libname statements ... if this is a concern then Metadata-Bound Libraries may be of interest.

4) For more control you can secure the underlying storage for a BASE engine library by converting it to a Metadata Bound Library (MBL). This protects the SAS datasets and contents with more traditional DB-style permissions(S, I, U, D, AT, CT, DT), maintained in metadata and always enforced. A savvy SAS user will not be able to bypass the metadata layer permissions by coding their own libname statement. You may however still need to consider file system access for users that can access the op-sys and do file system level operations (rm, mv etc).

There is also the potential of adding in mediated-access for file-system access control but this is already a long response so I'll just add it as a additional point of interest that may be worth looking into 🙂

From your description I imagine that option 4 (MBL), or possibly option 1 (DB), will give you the level of control you need (as others have suggested) but there are always pros/cons to each approach that need to be considered against requirements so your situation may warrant something different or a hybrid. If you need more help in planning/testing this then I'd definitely recommend getting SAS Professional Services or a local SAS Partner in to advise further.

[JayDZimmerman]

Metadata Bound Libraries are not something I had looked into before, but after some light reading/testing, they do seem to be the best bet for accomplishing what I need.  Never a fan of additional administrative overhead, but they provide the fine-level control I can't seem to get from the standard metadata authorizations or file system.  And the ability to control permissions for manually issued libname statements is an added bonus.

Thanks for the insight.