How do I implement Column Level Security in the SMC?

[AndrewJones]

Hello,

I want to stop a group of users being able to access some columns on a table.  How do I do this?

The scenario is that the users access the library via EG.  The library in question in registered in the metadata via the SMC.  In the 'Data Library Manager', I have registered the table.  I then selected the table, right clicked on 'Properties' and found the columns in the Columns tab. I right clicked on the columns that I wanted to restrict and chose 'Authorization'.  For the group , I marked 'Deny' for 'ReadMetadata' and 'Read'.  However, the users in the group can still see the columns.  Why isn't this working?  What have I done wrong?  Is this the wrong way to go about restricting access?  SASUSERS also has 'Deny' for everything and the users are not in another group that would give them permission.

The version of SAS is 9.4.  I have read documentation that says that it is possible to do this, but I haven't found anything that tells me how.

Thanks for any help

Andrew

[SASKiwi]

My understanding is that for tables registered in metadata, access can only be restricted at the table level, not by column. To restrict by column create a view of the original table, dropping the columns you don't want to show, and change the metadata permissions on that.

Please bear in mind that SAS users can write a LIBNAME statement pointing at this table/view and completely bypass any metadata permissions. The only way to lock down permissions completely is to create a metadata-bound library.

[AndrewJones]

Thanks for the reply.

In that case, I must have misunderstood the comment on https://documentation.sas.com/?docsetId=lrmeta&docsetTarget=p0rr01uaxmiih0n1fdvxeutblr76.htm&docsetV... where it says: "Security on a Column object is not enforced in the metadata engine. Therefore, the metadata engine cannot prevent a user from viewing the contents of a specific column in a table. To hide a column in a table from a user, the user’s ReadMetadata permission on the column must be Deny. The ReadMetadata permission is enforced by the SAS Metadata Server."

If I create a view in the same library as the original table, how do I stop the users from seeing the original table?

Thanks

Andrew

PS  I'm am aware on metadata-bound library, but for reason that I didn't understand, my IT dept was reluctant to implement them.  If we can't do what we want with the metadata permissions, then we will have to revisit this decision. 

[SASKiwi]

@AndrewJones  - Sorry, but I'm not following that description. My understanding is that when you register a table in metadata you store the whole table definition in a particular metadata folder and its the permissions on that folder you set to restrict access. So I don't see how you can set permissions at any level apart from the table. I don't see any options at the column level.

Metadata-bound libraries incur extra setup and support overheads so there has to be a good justification for their use.

[AndrewJones]

@SASKiwi Where do you find that access is only on the table, and not at column level?  I have only read that column-level permissions are possible.

In "SAS® 9.4 Intelligence Platform: Overview", found here: https://documentation.sas.com/?docsetId=biov&docsetTarget=p19wxrbc0rjdeqn1w5jpodblcdkt.htm&docsetVer..., it says "Resource-level controls manage access to a specific item such as a report, an information map, a stored process, a table, a column, a cube, or a folder. The controls can be defined individually (as explicit settings) or in patterns (by using access control templates)." (emphasis mine).  This is also mentioned in the "SAS® 9.4 Intelligence Platform Security Administration Guide" on page 40.  And a similar reference is here: http://support.sas.com/documentation/onlinedoc/guide/EG43MetaLibraries.pdf, though this is SAS 9.2.

Maybe I am misunderstanding all these references, or they don't apply to my situation.  In the SMC, there is the ability to put Authorization on a column, though I have yet to get this to work.  Can you give me a reference that shows that column-level security is not possible?

Thanks

Andrew

[SASKiwi]

@AndrewJones  - I'm going by what I see in SMC rather than the documentation. When you register a table in SAS metadata it prompts you for a metadata folder to store it in:

In the case of this screenshot the metadata folder is /Shared Data. In the Properties of this folder in the Authorization tab you can define the users or user groups access permissions. Note the WHOLE table is stored in this folder so by definition changing the authorizations affects the whole table.

If you have found another way to set permissions at the column level in SMC then I'd be very interested to know. I haven't yet found it! It can be frustrating trying to line up the documentation with the product. I'd agree that a report, stored process, table or folder would work fine as all of these are stored as metadata folder entries. I've never seen a column entry though.

Afterthought - maybe columns come into it with Information Maps. I've never played with these much but I do know you can select columns. Perhaps you could make several Info Maps on the same table with different column selections but store thenm in different metadata folders to apply different permissions. Pretty similar to using data views really.

[AndrewJones · Accepted solution]

@SASKiwi 

Thanks for the reply.  I get your frustration of matching documentation to the actual product.

Below is a screenshot of how I thought that I could apply column level security to a table via SMC.  As you can see, it would seem that I am applying some sort of authorization to the column, but I cannot work out what.

If I am not applying column level security, that what I am doing when I do the above?

Thanks

Andrew

[AndrewJones]

@SASKiwi 

The above method works!  The reason that it didn't work for me was that when I set up the library, I hadn't select 'By metadata library engine' as the 'Pre-Assignement Type'.  Once I had done that, the columns were no longer visible to the users.

Thanks for your replies

Andrew

[SASKiwi]

@AndrewJones  - Great it now works. Yes I now see what you are doing. I was working in the Folders tab rather than the Plug-ins tab. That makes a world of difference and you've taught me something - thanks!