topic Re: Options to Prefilter dataset in SAS in SAS Enterprise Guide

Options to Prefilter dataset in SAS

ykk — Tue, 09 Dec 2014 19:29:54 GMT

Please find my requirement below

We have a centralized data set which contains the information belonging to all the users.I want to know the options available to pre filter the data set before a user opens it in such a way that I can show only the records which belongs to Him.I dont want to use Filter and sort or Query builder as they will create another dataset.

I want to show dataset with filtered data as I want to capture the updates done by the user on that dataset.

Re: Options to Prefilter dataset in SAS

Reeza — Tue, 09 Dec 2014 19:46:46 GMT

Have you looked into a view?

Re: Options to Prefilter dataset in SAS

ykk — Tue, 09 Dec 2014 19:48:55 GMT

I tried but I want not able to update the View.so I thought of adding filter to the dataset itself

Re: Options to Prefilter dataset in SAS

Reeza — Tue, 09 Dec 2014 19:54:32 GMT

ykk wrote:

I tried but I want not able to update the View.

Not sure what that means

Re: Options to Prefilter dataset in SAS

ykk — Tue, 09 Dec 2014 20:01:11 GMT

I went through the option of view creation. I was not able to update the view .As my goal was to update the view I thought of accessing the data set directly with some pre filters on it.

Re: Options to Prefilter dataset in SAS

Reeza — Tue, 09 Dec 2014 20:16:30 GMT

You generally don't update views, you update the data behind the view.

Since views don't have data behind them its relatively trivial to recreate a view as well.

Re: Options to Prefilter dataset in SAS

ykk — Tue, 09 Dec 2014 20:35:41 GMT

Got your point.

But in my use case user will do the updates directly by opening the view not through update statements.

When he tries to open the view for doing updates the view will be read only he cannot do any updates.

Re: Options to Prefilter dataset in SAS

DaveBirch — Wed, 10 Dec 2014 03:25:33 GMT

How do you propose that the users access this 'update facility'?

Through Enterprise Guide by clicking on a Task Node or a Table/View Node?

Or some other way?

What do you want to happen if a user tries to change the value of User_Id for a row in his view? (Note, it is because of issues like this that many products don't allow update via Views.)

Are there any other Edit/Validation checks that may need to be applied?

How many users may require this ability to update 'their' data?

Is concurrent updating required? (SAS/SHARE needs to be licensed to enable this - fortunately it is usually part of a BI Server bundle.)

Re: Options to Prefilter dataset in SAS

ChrisHemedinger — Wed, 10 Dec 2014 17:42:36 GMT

You could use Metadata-bound libraries to achieve this. By putting your SAS-based data files into a secured library, you can use SAS metadata permissions to grant access to just the SAS users that need them. Because this access is completely controlled by the SAS Metadata Server, even a savvy SAS programmer can't gain access with a well-formed LIBNAME statement or a modified DATA step view.

More information can be found here:

Closing the "LIBNAME loophole" with metadata-bound libraries

And in this paper, which features a section titled, "HOW DO YOU RESTRICT A USER’S ACCESS TO SPECIFIC ROWS AND COLUMNS?"

http://support.sas.com/resources/papers/proceedings14/SAS118-2014.pdf

Chris

Re: Options to Prefilter dataset in SAS

AndreasMenrath — Thu, 11 Dec 2014 12:25:45 GMT

Wow, amazing security concept. Putting passwords inside of a view!

Has anyone considered that a malicious user could just run a

PROC SQL;

DESCRIBE VIEW <view_with_passwords_inside>;

QUIT;

and steal the passwords inside to bypass all your security layers to view all rows?

My experience on row-level security in a nutshell: it can only be achieved if you can ensure that your end users cannot run custom code on the application servers.

Re: Options to Prefilter dataset in SAS

ChrisHemedinger — Thu, 11 Dec 2014 13:42:58 GMT

Andreas,

I'm pretty sure that metadata-bound libraries have been "battle-tested" on the security front. As I noted in my article, the password value is not a security loophole or backdoor that can grant access to anyone who knows it. It's simply like a PIN for the administrator to later make changes to the contents of the secured library or its properties. Still, admins should guard the password and keep track of it.

The row-level security implementation requires some planning and setup, but once implemented there isn't a way for a user to gain access to the data if the metadata permissions won't allow it. Metadata-bound libraries -- which were introduced fully in SAS 9.4 -- are designed specifically as a security layer that can be used even when the end users have access and ability to run custom SAS code.

Chris

Re: Options to Prefilter dataset in SAS

AndreasMenrath — Wed, 17 Dec 2014 21:15:49 GMT

Hi Chris,

thank you for your valuable feedback.

I finally found some time to play around with this feature and the settings. My findings are also based on SAS(R) 9.4 Guide to Metadata-Bound Libraries, Second Edition.

1) To my former post: DESCRIBE VIEW requires ALTER priviliges in the metadata for the view. Even if these rights are granted the user won't see the password as clear text in the log. Instead the log contains some information like that:

select * from LIB.TABLE(read=XXX write=XXX alter=XXX);

Chris@SAS wrote:

As I noted in my article, the password value is not a security loophole or backdoor that can grant access to anyone who knows it. It's simply like a PIN for the administrator to later make changes to the contents of the secured library or its properties. Still, admins should guard the password and keep track of it.

I highly disagree. These passwords are the keys to the kingdom. Users who have metadata read rights on the secured library can use it to bypass all security layers. That's exactly what the row-level security concept does; therefore it is necessary to include the passwords in the view, because the user who will execute the view will not have metadata read and select rights on the secured table with all rows.

I used this code for some tests on a copy of the sashelp.prdsale table:

libname sample 'C:\SAS\SomeSampleData\RowLevelSecurityTest';

proc authlib library=sample;

create

securedlibrary='sample'

securedfolder='RowlevelSecTest'

pw=secret;

quit;

proc sql;

create view sample.prdsale_subset as

select *

from sample.prdsale(pw=secret)

where _METADATA_AUTHENTICATED_USERID_ = 'SASNOBODY@SAS94'

and country = "GERMANY" ;

quit;

Then i used SMC to grant a test user only access to the prdsale_subset table.

When i access the view as the test user i find some warnings in the log:

WARNING: The secured table object information for data set SAMPLE.PRDSALE.DATA could not be obtained from the metadata server or

has invalid data.

WARNING: The metadata server does not have a secured table object with the external identity "67AB2A08-XXXX-XXXX-XXXX-XXXXXXXXXX"

or you do not have ReadMetadata permission to see it.

When this test user knows the password to the library then the security can be completely bypassed and the user can access all tables and rows inside the secured library:

proc sql;

select *

from sample.prdsale(pw=secret);

quit;

3) at last it is confusing that this test user which has only basic rights can see all passwords associated with the secured library:

When the metadata object is exported i can see that the password "secret" was stored as "{SAS014}60AF7C50405EC46419AAAAA8" inside the metadata.

I hope that this password cannot be reversed as easily as the other {SASxxx} encoded passwords and are really good "battle-tested".

Best Regards,

Andreas

Re: Options to Prefilter dataset in SAS

JackS_Wallace — Fri, 19 Dec 2014 17:07:26 GMT

Andreas -

If someone were to crack the SAS014 encryption and obtain the decrypted value from the {SAS014} encoded passwords, they would get a one way hash of the original clear text password which could not be used in SAS code to open the data set.

You are correct that a brute force attack on the eight character alphanumeric passwords is an exposure. That’s why the administrator can set 3 different values for each to greatly change the number of possible clear text passwords from 27*(37**7) to (27**3)*(37**21).

But perhaps we need to re-examine the documentation and clearly state that the MBL passwords could be used to gain access to the data so that the administrator is more careful in his/her choice of passwords and in guarding knowledge of them.