Mediated Access in SAS Enterprise Guide

[nrose]

Hi all,

Great enhancements in SAS EG 5.1 . However, I am dissapointed about some features that have not been improved in regards to mediated access of data.

Mediated access is something our organisation will depend on to secure data, as we have hundreds of users (many who use SAS EG), and dozens of data sources all with potentially identifiable information. It is my understanding, that the limitations of SAS in regards to metadata security (i.e. that users with OS access to data can bypass it) can be overcome by using mediated access. This sounds like the ideal solution for us as it allows us to totally control column and row level access to sensitive data. The aim is to provide just a few levels of OS permissions which are only accessible by Information Maps, and deny all OS permission to users so they are forced to access data via Information Maps. Information maps are great as they provide an ideal interface to our SQL server, pushing database processing in a controlled way, and allowing us to apply formats and other filters.

Sounds great?

Then why does SAS EG still limit this mediated access by providing an error when information maps are used where users do not have OS permission to the data? Why not just have a NOTE come up in the log rather than an unsightly error message.

It also by default tries to open up maps using the workspace server rather than the pooled workspace server. Why not have the option to set EG to open up maps with the workspace server?

SAS EG is a great interface to the BI server, but it opens up gaping holes in security which maps can, for the most part, overcome. Can I suggest that improvements are made to EG so that it handles information maps in a more elegant way?

Thanks,

Nick

[TomKari]

Hi, Nick

I think I see where you're trying to go. How is your sensitive data stored? Is it in flat files, SAS datasets, a SQL database, or something else?

I assume that your users connect to SAS Metadata Server as individuals, but how would you like to implement your mediated access? Is your information map intended to access the data using different credentials from the user?

This is a very interesting question. I'm sure someone out there will have some ideas that will help you.

Tom

[nrose]

Thanks Tom and Art for your reply.

The data will be in SQL, and so we have to implement OS security at both the metadata level and OS/database level is we were to use a persons individual credentials. Administratore will know that you can put all the metadata security in the world, but if a user has OS permissions, they can write a libname statement straight to the data and do what they want. By using information maps, which can access the data directly via a limited number of service accounts, we make sure that the information map has full access to the data, but only give users access to the information maps, not the data underneath. This works great if you access information maps using web report studio, as by default, this accesses data using the pooled workspace server. However, by default, SAS EG access information maps using the workspace server, and if the user does not have OS permission to the data, then, and only then, tries using the pooled workspace server.

For a user who does not have OS/database permissions to the SQL server (which is what we intend) it means that SAS EG throws up an error, but still is able to process the information map. The reason in throws up an error is that it first tries to use the persons credentials and when it fails, an error ensues. This is unsightly for the user, and it also makes it an inefficient way of access information maps.

For more info, see usage note 38948

So, whilst mediated access is a recommended approach by SAS to help overcome the limitations of metadata security, and it works, it is not ideal in  SAS EG in that processing of information maps is slow and an error is thrown, even though it works. It would be nice, given that SAS BI is metadata driven, that mediated access is more elegantly managed in SAS EG.

Nick

[art297]

I'm not an EG user, but came across the following article.  http://support.sas.com/documentation/cdl/en/bisecag/61133/HTML/default/viewer.htm#a003273932.htm

Is it of any help in addressing the issues you raised?

[nrose]

In case anyone wants to prevent users from bypassing metadata security in SAS EG here is a solution, albeit one that is not particularly efficient for the SAS EG user.

• Block off all host level access to data.
• Only allow access to data via information maps (apart for developers).
• Include, in all information maps, stored processes to access the RDBMS data.
• within the stored process code, explicitly write RDBMS connection details.
• Hide the code in your stored process using compiled macros.
• Remove all metadata permissions to libraries referencing your RDBMS data to ordinary users, making it difficult to write rogue information maps. No one can see RDBMS details.
• Only install Infromation Map studio to trusted developers and regularly audit metadata for rogue information maps.
• For really sensitive data, encrypt field values stored in the RDBMS and use decryption algorithms within the information map used for access.

Upside - all security is now via metadata, and it is really hard to bypass.

Downside - Opening up such an information map in SAS EG introduces a significant performance hit.

Would be interested what Paul Holmes and/or Chris think about this approach to enforcing metatadata security for EG users.

Nick

[ChrisHemedinger]

Nick,

All of your guidelines can be effective to help to obscure, and in some cases secure, the data for the enterprise while still allowing EG users to be productive...to an extent.

However, SAS programs traditionally operate on physical entities, and not just metadata.  EG is an extension of the SAS programming metaphor, but it's also a hybrid: it brings the world of metadata into your programmer/analyst workbench.  The measures that you outline here may prevent unauthorized access to data, but they also make it more difficult for power-user analysts to do their work.

I have opinions on this, and I'll admit that my opinions are informed only by my knowledge of the technology capabilities and limitations.  I realize that my suggestions might not always pass muster for IT governance.

I favor the use of database and OS permissions to restrict access to sensitive data.  That is most effective in keeping data secure. 

I also favor the use of clear policies to end users.  A policy that says "of the data that you can access, this is what you may do with it, and this is what you cannot do".  (For example, "you may not export personally identifiable information from our customer data into an Excel file".)

Policies should be backed up by active monitoring.  Use SAS log facilities, Windows event logs, and desktop file scanners to detect when your policies might have been violated.  It can be a considerable amount of work to set up, but if your end users must have access to this data to accomplish work, then you need a "trust but verify" strategy.

If you cannot trust the end users, don't supply them with a tool like EG.  Instead, restrict them to Web Report Studio or similar tool.  It's less flexible, but less risky.

Chris

[nrose]

Thanks Chris,

Our needs are perhaps unique in that we have data from various sources that are made available to a wide range of users, and where our SAS administrators are a separate department from the IT department. In particular, many users need to carry out ad-hoc analysis, but, as administrators of the system, we would like to implement row and column level security in a way that does not necessitate coordination with our database administrator for the creation of accounts and database views each time. We need the ability to provide and revoke access in a timely and efficient manner, and trying to coordinate both metadata and host level security is complicated and prone to error.

As for trusting our users, a signed statement is not often enough for custodians of sensitive data, where, it is not just the problem of inappropriate distribution of data, but inappropriate viewing.  Denying a tool such as EG is really a drastic step when all we want to do is restrict access to data.

I don’t think the system of mediated access makes it more difficult for the power user – indeed, by allowing access to what is a combination of complex and sensitive data in a mediated way – we allow those users the ability to carry out important analysis, whilst satisfying the requirements of the data custodians for security. Here there is a distinction between the developer, who requires host level permissions to create the content for other users, and the analyst, who requires access to data to then analyse. A mediated approach simply uses metadata objects such as Information Maps as the access point to data – from then on, the user can work in the ‘physical’ world. The disadvantage from the way SAS EG is set up is that, if they use information maps where a user does not have host permissions to the underlying data, they get that annoying error, as well as a much slower access time. Furthermore, they get to see all the libraries, connection details and table definitions that make up those Information Maps, even though, they don’t physically have access to them. My suggestion is a way around this.

It seems that, from your email, that there is little emphasis in future releases of EG to encourage the use of mediated access, which, although understandable in regards to development effort required, is disappointing. I would be interested if other users have a similar issue where, they want finer control of data access for their analysts using SAS Metadata security, or that they find that SAS EG just allows to easy an access to their data to be useful.

I would also like to know if anyone can see any security holes around the suggested mediated access approach.

Thanks again Chris for your response.

Nick

[ChrisHemedinger]

Nick,

There are ongoing efforts at SAS to make "mediated access" a better experience, but they aren't being implemented at the client level (such as in EG).  Rather, these mechanisms have to be implemented at a lower level, such as at the library engine level, to coordinate proper access according to the rules that you've set up in SAS metadata.

The client tier is too high in the stack to enforce these rules.  After all, with SAS' plethora of APIs for Windows and Java clients, any motivated developer can build a client application.  We cannot expect all client applications to enforce business rules in the same way (even if we strive to achieve that with the SAS-provided clients).

I am aware of internal development projects to make the library engines more aware of metadata permissions, such that you'll get the control you desire without sacrificing performance.  I think it's too early for me to share details of those here, but SAS R&D is aware of the need and is working on it.

Chris