A good Security Implementation means that each user access what he is authorized to and no more. It is well known that a bad security implementation within SAS Metadata Security might potentially be disastrous causing major problems for the organization. Effective security reporting is necessary and can be very helpful to:
- Document the security within SAS Metadata security plan.
- Evaluate the impact of changing the specific permission.
- Resolve potential permissions conflict.
- Detect bad implementation of security within Metadata Repository.
- Understand why the specific user or group does not have permission for a specific Metadata object.
- Understand different impact regarding Access Control implementation and their inherited permission settings …
In this blog we will look at an example of SAS (9.4) Security Reports to track permissions within Metadata SAS Security using the power of the SAS Stored Processes that I developed for one of my previous Client.
SAS SECURITY REPORT
SAS Security Reporting focused mainly on the authorization settings and effective permissions that are set on the objects in the SAS Metadata Server. This Report aims to answer the following key questions:
- Who have access to what and what permissions do they have?
- What objects the specific user or group have access to and what permissions do they have on them?
Any Security Reporting project starts with data collection. SAS provide several Macros (shipped whit SAS/Foundation) that use the DATA step functions to extract, filter, and format permissions information into SAS data sets regarding authorization settings and effective permissions for a specified set of identities, permissions, and objects stored in the SAS Metadata Repository. For our Windows platform those Macros are located on the following :
In this example, we use mainly the %MDSECDS autocall macro, which is the standard approach to build authorization data sets, to create the Reports.
For full documentation about Security Report Macros, see Chapter “Security Report Macros” in the SAS® 9.3 Intelligence Platform: Security Administration Guide.
The MDSECDS macro takes the following form:
All of the parameters are optional, which means that if no parameters are specified, the macro will build authorization data sets about all folders and their members. This can easily lead to huge resources consumption as the Metadata Repository can contains large amount of Metadata.
The last generated data set output is the view work.mdsecds that we considere here as base name. The execution of this macro generate four Data sets with underscore and a suffix appended to the base name which are xxx_OBJS, xxx_PERMSL, xxx_PCONDS, xxx_PERMSW and xxx_JOIN. Here are the description of those data Sets:
The following describe the macro parameters that define the scope of the extraction:
AUTHORIZATION DATA SET
We focus here on the main SAS view work.mdsecds_join that is the primary table for reporting which combines the object data with the permission data. Here is the output structure of this view:
Permission Columns (ReadMetadata, WriteMetadata, Administer, CheckInMetadata, Create, Delete, Read and Write) contains a value indicating the state of the permission as it pertains to the user on this object. Possible permission values and their appearance in the Authorization tab are as follows:
Let have a look now at how the Report is build.
SECURITY REPORT ON DEMAND
As the Report should be on demand, the Report is build using the SAS Stored Processes. The above Data set needed for the Report (which is the SAS view mdsecds_join) is generated within the SAS Stored Process as well as the HTML Report. This gives the advantage to display the Report with a variety of clients such as:
- SAS Stored Process Web Application
- SAS Information Delivery Portal
- SAS BI Dashboard
- SAS Web Report Studio
- SAS Visual Analytics
The Report was designed to gives the possibility to track permissions according to the different %MDSECDS Macro parameters discussed earlier. Behind the screen, the left HTML input Form (figure bellow) will generate macro variables that will be used with the %MDSECDS macro when submitting the report via the View Report Button. The Rest Button is used to clear the Form. The following is the print screen when the first page of the report is loaded:
The user can choose first parameters (via the Cascading Dropdown List) within the left HTML Form, according to what he need to track:
- Folder permissions,
- Group permissions
- or User permissions.
If the Folder permissions is selected, the second Dropdown List will display automatically the list of all Metadata folders where the reporting should start, if not (i.e. Group or User is selected), the second Dropdown will display Groups or Users in the Metadata repository and then the user can select the needed User or Group for which he need to check permissions.
For example, if the user select Folders and then choose Exploration Room\AD HOC as folder, the Report will use the following code to generate data:
%mdsecds(folder=”\Exploration Room\AD HOC”)
As example, for User sasdemo and Group PUBLIC the following code is generated for each of them:
The user have also the possibility to filter output result according to several options (using Radio Buttons and Checkboxes) within the left HTML input Form that we explain in the following.
GRANTED OR DENIED PERMISSIONS
The User can choose to Include Granted read Metadata, Denied read Metadata or all permissions within this radio regarding the object selected in the Cascading Dropdown List.
The macro variable generated by this Radio (that have the Granted or Denied value) will be used in the where clause to subset the SAS view work.mdsecds_join by keeping ReadMetadata with Granted or Denied permissions (see permission values in table above).
REPORT DETAILS LEVEL
This checkbox used to filter out Subfolders, table components, Cube components and Secured tables:
Each value checked in the above checkbox will add the corresponding macro parameter to the call of the MDSECDS (see parameters that define the scope of the extraction above).
If “Include Subfolders” is unchecked, the call of the macro will contains INCLUDESUBFOLDERS=NO, otherwise no need to add INCLUDESUBFOLDERS=YES because this value is the default. The same logic applied for INCLUDETABLECOMPONENTS (=NO if Include Table Components is unchecked), INCLUDECUBECOMPONENTS (=NO if Include Cube Components is unchecked) and INCLUDESECUREDTABLES (=NO if Include Secured Tables is unchecked).
For example, if the user select Folders and then select \Shared Data\Sales folder and check only include Subfolders, the Report will use the following code to generate data:
%mdsecds(folder=”\Shared Data\Sales” ,
INCLUDETABLECOMPONENTS=NO, INCLUDECUBECOMPONENTS =NO, NCLUDESECUREDTABLES =NO);
This checkbox used to filter out MEMBERTYPES (see parameters that define the scope of the extraction above) to display the public object types for the folder members that should be included in the report. Each object types checked will be added to the MEMBERTYPES list within the MDSECDS macro.
MEMBERTYPES =”Library, Job, StoredProcess, Dashboard, Report, INFOMAP, CUBE, Project …”.
If the user check ALL in the checkbox, which correspond to MEMBERTYPES=”*” in the macro code, the Report will return all types.
Object types are documented in the System Administration Guide and can be browsed in SAS Management Console under Folders > System > Types.
PERMISSIONS TO INCLUDE
In this checkbox, the user can choose permissions columns to include in the output Report. ReadMetadata and WriteMetadata are always displayed.
Macro variable generated by this checkbox will contains permissions to display in the Report.
IDENTITY TO INCLUDE
Here the macro variable will gives the identity type to include in the Report. Possible values are Person (User in the checkbox), IdentityGroup (Group in the checkbox), or Role.
INCLUDE SAS TECHNICAL USERS/GROUPS
Finally the last checkbox will filter some technical users and groups such as sasdemo, sasadm, SASUSERS, PUBLIC, …
EXAMPLE OF REPORT
The following is an example of generated report when selecting a folder with the default parameters in the Form when the page is loaded (as shown before) :
A weakness within SAS Metadata security implementation is a big risk in all organizations. This post can serve as good start points for the security administrator who have the responsibility for Security within SAS Metadata Repository. SAS Management Console can be used to check permissions for specific Metadata Object, this can become tedious tasks as the Metadata can contains large number of Metadata objects.
Armed with this kind of report, the security administrator can save lot of time and energy to correct and improve security implementation as as well as documenting the SAS Metadata security.
If you need the SAS Stored Procedure code used here, please let me know.
Your comments and suggestions are most welcome!