The program contained within the Full Code tab demonstrates a technique for copying user and group identity metadata from one SAS^® Metadata Repository to another. This is useful when moving from a SAS^® 9.1.3 environment to a SAS^® 9.2 environment, or when deploying a new SAS^® 9.2 Metadata Server in which you want the same users and groups as were defined in the original SAS Metadata Repository.

This sample was adapted from the Sample Code for User Synchronization, SAS(R) 9.2 Intelligence Platform: Security Administration Guide, Appendix 2, page 219.

This sample uses the logic described in the Sample Code for User Synchronization. A major difference between these two programs is that this program's initial extraction for the "master" or "source" table is performed against a SAS Metadata Repository rather than an Active Directory or LDAP server. This is useful to move identities from one SAS Metadata Repository to another. This program only extracts and loads SAS Metadata identities. It does not set authorizations.

The first identity extraction is run against the "old" SAS Metadata Repository and is referred to throughout the sample as the "source" data. The second identity extraction is run against the "new" SAS Metadata Repository and is referred to throughout the sample as the "target" data.

Comparison and validation occurs the same as in the User Synchronization sample. Since there will certainly be violations in integrity constraints, this sample includes the generation and use of an Exceptions List to omit certain identities from the comparison and update.

You must modify this sample code to run in your environment.

Each major step is described by a commented section. Read the comments and understand the steps before running the program. At a minimum, you will need to modify SAS Metadata Server connection options and library paths to appropriate values for your environment. You will need to provide keyword parameter values in some macro calls.

If both your source and target SAS Metadata Servers are available AND both the source SAS Metadata Repository and the target SAS Metadata Repository are running under the same version of SAS, you can run this as a single program.

However, if both of the SAS Metadata Repositories are not concurrently available or they are running under different versions of SAS, you will need to run Step 1 as a stand–alone program under the same version of SAS as the source repository. Complete Steps 2–5 under the same version of SAS as the target repository.

---

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.

---

/*----------------------------------------------------------------------
* SAMPLE code for copying user and group identity metadata from one
* repository to another. This is useful when moving from a 9.1.3
* environment to a 9.2 environment, or when deploying a new 9.2
* metadata server in which you want the same users and groups as
* were defined in the outgoing repository.
*
* This sample was adapted from the Sample Code for User Synchronization
*  
* SAS(R) 9.2 Intelligence Platform: Security Administration Guide,
*   Appendix 2, page 219.
*   http://support.sas.com/documentation/cdl/en/bisecag/61133/PDF/default/bisecag.pdf
*
* 
* This sample uses the logic described in the Sample Code for User
* Synchronization. A major difference is that the initial extraction for
* the "master" table is performed against a metadata repository rather
* than an Active Directory or LDAP server. This is useful to move
* identities from one metadata repository to another. This program only
* extracts and loads metadata identities. It does not set authorizations.
* 
* The first identity extraction is run against the "old" repository and
* is referred to throughout the sample as the "source" data. The second
* identity extraction is run against the "new" repository and is
* referred to throughout the sample as the "target" data.
* 
* Comparison and validation occurs the same as in the User
* Synchronization sample. Since there will certainly be violations in
* integrity constraints, this sample includes the generation and use of
* an Exceptions List to omit certain identities from the comparison and
* update.
* 
* You must modify this sample code to run in your environment.
* 
* Each major step is described by a commented section. Read the comments
* and understand the steps before running the program. At a minimum, you
* will need to modify metadata server connection options and library paths
* to appropriate values for your environment. You will need to provide
* keyword parameter values in some macro calls.
* 
* If both your source and target metadata servers are available AND both
* the source repository and the target repository are running under the
* same version of SAS, you can run this as a single program.
* 
* However, if both repositories are not concurrently available or 
* they are running under different versions of SAS, you will need
* to run Step 1 as a stand-alone program under the same version of SAS
* as the source repository. Complete Steps 2-5 under the version of
* SAS as the target repository.
* 
*
* SAS INSTITUTE INC. IS PROVIDING YOU WITH THE COMPUTER SOFTWARE CODE
* INCLUDED WITH THIS AGREEMENT ("CODE") ON AN "AS IS" BASIS, AND
* AUTHORIZES YOU TO USE THE CODE SUBJECT TO THE TERMS HEREOF.  BY USING
* THE CODE, YOU AGREE TO THESE TERMS.  YOUR USE OF THE CODE IS AT YOUR
* OWN RISK.  SAS INSTITUTE INC. MAKES NO REPRESENTATION OR WARRANTY,
* EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NONINFRINGEMENT AND
* TITLE, WITH RESPECT TO THE CODE.
* 
* The Code is intended to be used solely as part of a product
* ("Software") you currently have licensed from SAS Institute Inc. or
* one of its subsidiaries or authorized agents ("SAS"). The Code is
* designed to either correct an error in the Software or to add
* functionality to the Software, but has not necessarily been tested. 
* Accordingly, SAS makes no representation or warranty that the Code
* will operate error-free.  SAS is under no obligation to maintain or
* support the Code.
* 
* Neither SAS nor its licensors shall be liable to you or any third
* party for any general, special, direct, indirect, consequential,
* incidental or other damages whatsoever arising out of or related to
* your use or inability to use the Code, even if SAS has been advised of
* the possibility of such damages.
* 
* Except as otherwise provided above, the Code is governed by the same
* agreement that governs the Software.  If you do not have an existing
* agreement with SAS governing the Software, you may not use the Code.
*
* REV: 2/1/2011
*----------------------------------------------------------------------*/


/*----------------------------------------------------------------------
* Step 1. 
*
* Define metadata server connection options and extraction library
* for storing identity information from the source (old) metatdata
* repository.
*
* Execute %MDUEXTR to extract user information from the metadata
* server into a modified form of the canonical tables. Store them 
* in MD1 library. 
*----------------------------------------------------------------------*/

/*--------------------------------------------------------------
* Define connection options for the source metadata server 
*-------------------------------------------------------------*/
options metaserver=sasbi-1.demo.sas.com 
        metaport=8561
        metauser="sasadm@saspw"  
        metapass="Orion123"  
        metaprotocol=bridge
        metarepository="Foundation";                

/*--------------------------------------------------------------
* Define the library where the extracted metadata information
* will be stored from the source repository. 
*-------------------------------------------------------------*/
libname MD1 'C:\Admin\MD1extract';

/*--------------------------------------------------------------
* Extract identity information from the source metadata. 
*-------------------------------------------------------------*/
%mduextr(libref=MD1);

/*--------------------------------------------------------------
* If your source repository is SAS 9.1.3 and your target 
* repository is 9.2, uncomment the following block of code.
* This adds the variable DISPLAYNAME to the source data, as it
* is an expected column in the 9.2 identity model. This code
* should NOT be run if your souce repository is 9.2, as the
* DISPLAYNAME column already exists.
*-------------------------------------------------------------*/

data MD1.PERSON;
  retain Name Title DisplayName keyid description objid externalkey;
  set MD1.PERSON;
  ATTRIB DisplayName LENGTH=$256 FORMAT=$256. INFORMAT=$256. LABEL='Person DisplayName';
run;

data MD1.IDGRPS;
  retain Name DisplayName keyid description grpType objid externalkey;
  set MD1.IDGRPS;
  ATTRIB DisplayName LENGTH=$256 FORMAT=$256. INFORMAT=$256. LABEL='Person DisplayName';
run;


/*----------------------------------------------------------------------
* Step 2. 
*
* Define metadata server connection options and extraction library
* for storing identity information from the target (new) metatdata
* repository.
*
* Execute %MDUEXTR to extract user information from the metadata
* server into a modified form of the canonical tables. Store them 
* in MD2 library. 
*----------------------------------------------------------------------*/

/*--------------------------------------------------------------
* Define system options for the target metadata server 
*-------------------------------------------------------------*/
options metaserver=sasbi-2.demo.sas.com 
        metaport=8561
        metauser="sasadm@saspw"  
        metapass="Orion123"  
        metaprotocol=bridge
        metarepository="Foundation";                

/*--------------------------------------------------------------
* Define the library where the extracted metadata information
* will be stored from the target repository. 
*-------------------------------------------------------------*/
libname MD2 'C:\Admin\MD2extract';

/*--------------------------------------------------------------
* Extract identity information from the target metadata.
* For a new repository, this data will contain the default
* users, groups, and roles. For example, your installation
* might contain the following users, groups, and roles:
*
*   USERS:
*   SAS Administrator
*   SAS Trusted User
*   SAS Demo User
*  
*   GROUPS/ROLES:
*   SAS Administrators
*   SAS System Services
*   SAS General Servers
*   Metadata Server: Unrestricted
*   Metadata Server: User Administration
*   Metadata Server: Operation
*-------------------------------------------------------------*/
%mduextr(libref=MD2);


/*----------------------------------------------------------------------
* Step 3.
*
* Compare identity data from the source repository with the target
* repository, and create a set of changes/updates to be made
* to the target repository. 
*  
* Optionally, create and specify an Exception List in this step to
* prevent deletion of identities manually added to the target repository,
* and to prevent comparison of default identities (users, groups, roles).
*
* Execute the %MDUCMP comparison macro with the flag that specifies 
* to compare all information. This allows comparison of identities
* which were added manually, as well as automatically via a
* bulk-load process. The distinguishing attribute is the presence
* of an association to an ExternalIdentity object.
*-----------------------------------------------------------------------*/

/*--------------------------------------------------------------
* Define the library for the comparison output (change tables).  
*-------------------------------------------------------------*/
libname update "C:\Admin\MDupdates"; 


/*--------------------------------------------------------------
* Optionally, define an exceptions table. Each observation you
* define declares a canonical table name (tablename) and WHERE
* expression (filter) that protects the target repository
* identities from being updated/deleted or causing violations
* in server integrity contstraints.
* 
* This example shows many of the default users, groups, and
* roles in a new SAS 9.2 repository.
*-------------------------------------------------------------*/
data update.exceptions;
  length tablename $ 30 filter $ 200;
  
  tablename='Person';
  filter='upcase(name)="SASADM"';
  output;  
  tablename='Person';
  filter='upcase(name)="SASTRUST"';
  output;
  tablename='Person';
  filter='upcase(name)="SASDEMO"';
  output;
    
  tablename='IdGrps';
  filter='upcase(name)="SASUSERS"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="PUBLIC"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="SASADMINISTRATORS"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="META: UNRESTRICTED USERS ROLE"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="META: USER AND GROUP ADMINISTRATORS ROLE"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="META: OPERATORS ROLE"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="SAS SYSTEM SERVICES"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="SAS GENERAL SERVERS"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="TSADMINS"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="ADD-IN FOR MICROSOFT OFFICE: ADVANCED"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="ADD-IN FOR MICROSOFT OFFICE: OLAP"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="ADD-IN FOR MICROSOFT OFFICE: ANALYSIS"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="ENTERPRISE GUIDE: ADVANCED"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="ENTERPRISE GUIDE: PROGRAMMING"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="ENTERPRISE GUIDE: OLAP"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="ENTERPRISE GUIDE: ANALYSIS"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="MANAGEMENT CONSOLE: ADVANCED"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="MANAGEMENT CONSOLE: CONTENT MANAGEMENT"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="BI WEB SERVICES USERS"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="WEB REPORT STUDIO: REPORT VIEWING"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="WEB REPORT STUDIO: REPORT CREATION"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="WEB REPORT STUDIO: ADVANCED"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="BI DASHBOARD ADMINISTRATORS"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="BI DASHBOARD: ADMINISTRATION"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="BI DASHBOARD USERS"';
  output;
  tablename='IdGrps';
  filter='upcase(name)="ESRI USERS"';
  output;
run;

/*--------------------------------------------------------------
* Execute the %MDUCMP comparison macro.
* 
* If you are using an exceptions table, remove the first macro
* call below and uncomment the second macro call that is using
* the exceptions= keyword parameter.
*-------------------------------------------------------------*/
%mducmp(master=MD1, target=MD2, change=update, externonly=0);
*%mducmp(master=MD1, target=MD2, change=update, externonly=0, exceptions=update.exceptions);


/*----------------------------------------------------------------------
* Step 4. 
*
* Validate the changes for integrity contraint violations and correct
* any validation errors.
*
* The %MDUCHGV macro checks the change tables created by the
* %MDUCMP macro and reports violations in server integrity constraints
* in the data table UPDATE.MDUCHGVERRORS. Any data written to
* UPDATE.MDUCHGVERRORS prevents the metadata load from executing.
*
* Note that if you do not create and use an Exceptions List, at least two
* integrity constraints will be found; one each for SASUSERS and PUBLIC.
*-----------------------------------------------------------------------*/
%mduchgv(change=update, target=MD2, temp=work, 
         errorsds=update.mduchgverrors);

/*--------------------------------------------------------------
* Evaluate the contents written to UPDATE.MDUCHGVERRORS and
* correct the problems before attempting to load changes. The
* results are written to the .lst file or Output window.
*-------------------------------------------------------------*/
%macro eval_mduchgv;
   %if (&MDUCHGV_ERRORS ^= 0) %then %do;
      %put NOTE: See output listing for validation errors detected by %nrstr(%mduchgv).;
        proc sql;
        select * from update.mduchgverrors;
        quit;
   %end;
%mend eval_mduchgv;
%eval_mduchgv;

/*--------------------------------------------------------------
* Note that the UPDATE.MDUCHGVERRORS data set is structured
* such that it can be used as an exceptions table.
* 
* If the errors written to UPDATE.MDUCHGVERRORS are acceptable
* exceptions, then you can combine your UPDATE.EXCEPTIONS table
* with the UPDATE.MDUCHGVERRORS table to quickly create a
* complete list of exceptions. Then, rerun the %MDUCMP macro to
* recreate the change tables without the offending changes.
* 
* Assign SUBMIT=1 on the call to %RERUN_MDUCMP to combine the
* exceptions and rerun the comparison and validation.
*-------------------------------------------------------------*/
%macro rerun_mducmp(submit=0);
   %if (&MDUCHGV_ERRORS ^= 0 and &SUBMIT = 1) %then %do;
      %put NOTE: Adding MDUCHGVERRORS conditions as exceptions.;
      %put NOTE: Rerunning comparison and integrity constraint checking.;
        data update.allexceptions;
          set UPDATE.MDUCHGVERRORS %if %sysfunc(exist(update.exceptions))
          %then update.exceptions; ;
		    run;
        %mducmp(master=MD1, target=MD2, change=update, externonly=0, exceptions=update.allexceptions);
		    %mduchgv(change=update, target=MD2, temp=work, errorsds=update.mduchgverrors2);
   %end;
%mend rerun_mducmp;
%rerun_mducmp(submit=0);


/*----------------------------------------------------------------------
* Step 5.
*
* Load the changes into the target metadata repository.
*
* Note that identities which did NOT have an ExternalIdentity association
* in the source repository will be created in the target repository also
* without an ExternalIdentity association.
*-----------------------------------------------------------------------*/
%macro exec_mduchgl;
   %if (&MDUCHGV_ERRORS ^= 0) %then %do;
      %put ERROR: Validation errors detected by %nrstr(%mduchgv). Load not attempted.;
      %return;
      %end;
   %mduchgl(change=update, submit=1);
%mend;
%exec_mduchgl;

---

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.

Type:

Sample

Date Modified:	2011-04-13 10:30:55
Date Created:	2011-02-01 14:35:47

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	SAS Metadata Server	Microsoft Windows Server 2008 for x64
		Microsoft Windows Server 2008
		Microsoft Windows Server 2003 for x64
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows NT Workstation
		Microsoft Windows 2000 Professional
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 95/98
		Microsoft® Windows® for x64
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		z/OS
		Microsoft Windows XP 64-bit Edition
		Microsoft Windows XP Professional
		Windows 7 Enterprise 32 bit
		Windows 7 Enterprise x64
		Windows 7 Home Premium 32 bit
		Windows 7 Home Premium x64
		Windows 7 Professional 32 bit
		Windows 7 Professional x64
		Windows 7 Ultimate 32 bit
		Windows 7 Ultimate x64
		Windows Millennium Edition (Me)
		Windows Vista
		Windows Vista for x64
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		HP-UX IPF
		Linux
		Linux for x64
		Linux on Itanium
		Solaris for x64