Overview

In SAS Information Map Studio, you can define a row-level permission filter that is based on group membership using the SAS.IdentityGroups property, which is available starting with SAS 9.2. This filtering method can be useful when you need to subset results based on metadata group memberships for a user.

However, when you are using a security associations table, and a user is a member of more than one group listed in the table, then the filter results contain duplicate values. This sample explains how to use a stored process, instead of the SAS.IdentityGroups property, to subset the security associations table so that duplicates are eliminated from the final results.

About the sample data

This sample use two sample data sets: Furniture contains the data that you want to filter and Security contains the groups that are used in the identity-driven filter. In the Security table, the product Table is associated with both the North group and the South group.

This data structure means that users who are members of both North and South see duplicate cost values for Table.

This sample shows you how to eliminate the duplicates so that your results look like this.

Prerequisites

In addition to creating an information map, this sample assumes that you know how to submit SAS code, and how to register tables and stored processes using SAS^® Management Console.

This sample is intended for intermediate to advanced users.

Create the sample data

Submit the following code, substituting your site-specific values, and making sure that the tables are in two different SAS libraries.

/* Sample Data */
/* ********************************************************************
*  Description:  This code creates two tables that are used in the sample
*
*  Additional Steps:  Open the SAS Management Console and register the tables
*                     in separate libraries. The BIOUT library is used to
*                     redirect the Security table to the Work area. For this
*                     reason, separate libraries are needed for the example.                    
*
*********************************************************************** */

/* Add the location where you want to store the data */
%let location1 = c:\orion\data\biout;
%let location2 = c:\orion\data;

LIBNAME biout "&location1";
LIBNAME mylib "&location2";

data biout.security;

   input group $ product $;
   cards;
   North Table
   North Couch
   North Desk
   South Table
   East Couch
   West Desk
   
   ;
   run;

   data mylib.furniture;
   input product $ cost;
   cards;
   Table 5
   Couch 5
   Desk  5
   ;
   run;

Register the tables

In SAS^® Management Console, register both the Security table and the Furniture table that you created previously.

Note: For help, see Establishing Connectivity to a Library of SAS Data Sets.

Create the stored process

The following stored process subsets the security associations table to remove duplicates. This is the most important part of the sample. Save the following code on the machine that is hosting your SAS Workspace server. Name the program MyGroups.sas.

*Processbody;

/* ********************************************************* 
* Program: mygroups.sas
*
* Description:  This code gets a list of groups that the current id is  
*               a member of.  Those groups are stored in a macro call &groups. 
*               The Security table is filtered by &groups.  Duplicates are then 
*               removed from the table
*
*********************************************************** */

%stpbegin;

%let groups=;
data _null_;
  length uri group groupuri group_names $256;
  n=1;
  uri='';

     /*Return the metadata information for this user*/

  nobj=metadata_getnobj("omsobj:Person?@Name = '"||"&;_METAPERSON"||"'",n,uri);
  put nobj=;
  if nobj=-1 then put 'Unable to connect to metadata server';
  else if nobj=-4 then put 'Argument "n" in METADATA_GETNOBJ function is out of range';
  else if nobj=-3 then do;
    put "&_METAPERSON was not found.";
	call symput('Groups', "PUBLIC");
  end;

  else do;
	    /***Get the group association information for this user***/
	 a=1;
	 grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri);
	 if grpassn in (-3,-4) then call symput('Groups', "SASUSERS");

	    /***If the user is a member of any groups, retrieve the name
	        of each group and append it to a macro variable***/
	 else do while (grpassn > 0);
		rc2=metadata_getattr(groupuri, "Name", group);
		a+1;
		if group_names=' ' then group_names="'" || trim(group) || "'";
		else group_names=trim(group_names)||", '"||trim(group) || "'";
		call symput('Groups', group_names);
        grpassn=metadata_getnasn(uri,"IdentityGroups",a,groupuri);
     end;
  end;
run;

%put &_METAPERSON is in these groups: &GROUPS;


/* Subset the security table */

LIBNAME temp "c:\orion\data\biout";
LIBNAME BIOUT  (work);

data biout.security;
   set temp.security (where=(group in (&groups)));
run;

/* ****************************************************** */
/* remove duplicate products by using the nodupkey option */
/* ****************************************************** */
proc sort data = biout.security nodupkey;
by product;
run;



%stpend;

Register the stored process

1. In SAS Management Console, click the Folders tab.
2. Select SAS Folders ► Shared Data ► StoredProcesses (or another shared location).
3. Right-click on StoredProcesses and select New Stored Process to launch the New Stored Process wizard.
4. Name the stored process MyGroups. Click Next.
5. The second wizard page is the most important. On this page, you provide the following information:
1. For the SAS Server, select the SAS Workspace server on which to run the stored process.
2. For the Source code repository, select from the existing list, or click Manage to select the physical location where you stored the sample stored process (for example, c:/storedprocesses).
3. For the Source file, enter MyGroups.sas.
4. Do not select any output type.
6. Click Next and then Finish to exit the wizard.

Note: If you are creating the stored process in SAS 9.3, then, in SAS Management Console, right-click on the stored process and select Make Compatible. Using the Make Compatible option is the easiest way to ensure that stored processes created with SAS 9.3 can be used with information maps.

Create the sample user groups

1. In SAS Management Console, click the Plug-ins tab.
2. Right-click on User Manager, and select New ► Group.
3. Enter North and then click OK.
4. Repeat step 3 to add the South, East, and West groups.
5. Open the properties of the North and South groups and add SAS Demo User to the Members tab. If you do not have a SAS Demo User account, then you can use another account.

Create and test the filter in the information map

1. Log in to SAS Information Map Studio using the SAS Demo User account (or whatever account that you added as a member of the North and South groups).
2. Include the BIOUT.Security table.
3. Include the MyGroups.sas stored process.
4. Select Tools ► Run a Test Query. You should see the following result:
   
   
5. Insert the Furniture table, and then join the Security table and the Furniture table on Product.







6. Perform another test query. Notice that there is only one row where Product is equal to "Table".
   



7. Save the information map.
8. Select Edit ► Properties ► Information Map, and click the Required Tables tab. Add the security table, and click OK.

Additional Documentation

For more information about stored processes, see SAS 9.2 Stored Processes: Developer's Guide.

For more information about SAS Web Report Studio, see the SAS Web Report Studio documentation page.

For more information about SAS Information Map Studio, see the SAS Information Map Studio documentation page.

For more information about SAS Management Console, see the SAS Management Console documentation page.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Operating System and Release Information