First, let me thank @JuanS_OCS and @LinusH for your help. With your help, I was able to create a solution that works for my setup.
So, here is my setup. I work in Higher Education, so almost everything in my data maps to college codes and department codes. However, I did not have a data sources that tied individuals to a college/department...and I needed this, as we plan to use SAS' row-level security. In order to create this, I have several pieces...but, I wanted it all to work from Active Directory. So, I created the following:
Active Directory: I have an OU setup with 100 or so groups in it. Each group contains any number of users.
SQL Server: I have a job in SQL Server that is setup to run at the top of every hour. This job essentailly gets my group membership for all group in my OU. The last step of this job is to ETL the data to Oracle, which is where my crosswalk table exists.
Oracle: In Oracle, I have my crosswalk table. This crosswalk table maps each of my AD groups to colleges and departments. A group can map to one or more colleges and one or more departments. By have the crosswalk table at the group level, instead of having it at the user level, it is much easier to maintain.
SAS: I have a job setup in SAS that joins the crosswalk table and the membership table (taking into account some speacial groups that gives members access to everything) and then pushes this data to Hadoop and then to LASR. The result is a table that contains every user and every combination of college/department that he/she has access to.
Now, for my row-level security, as I mentioned, most (if not all) of my data contains columns for College Code and Department Code. So, this is what I based by row-level security on. By doing this, it means that I need a SAS Group for each College Code and each College Code || Department Code combination (as a department can belong to multiple colleges).
This is where my crosswalk table comes into play. My crosswalk table contains exactly what I need...every College Code and College Code || Department Code combination. I can base my SAS Groups off of this information...and my crosswalk table also contains the members for each group. Perfect! But, the problem was...how do I go from crosswalk table (which is a table that I have loaded into LASR) to SAS Groups and Users (by the way, my SAS enviroronment is setup to authenticate against Active Directory). So, I started asking question in this post and started building a script. I ended up with the below script setup as a Data Integration Studio job (with user written note). This allowed me to then schedule this script after HDFS and LASR have both loaded, as I run this process hourly.
So, the results are this...I can now maintain all of my users and groups via AD. I simply add a new user to an AD group, and the process with recognize that user and add him/her to SAS. It will add my person record and add him/her to the appropriate groups. The same thing goes for groups. This process will also take care of my updates and deletes...which is exactly what I was looking for. So, now for the script. Enjoy. I hope that this saves others some work.
A couple notes about the script:
- You will notice that I have created a new MDUCHGV macro. This is due to the following: http://support.sas.com/kb/49/002.html
- You will notice that in one of the proc sql steps below, I create a veiw. This is due to the above...I was trying to get around that warning without having to modify the macro. Since the macro has been modified, this could be cleaned up.
- In my DI Studio job, I have two notes...User Written (which contains the code below) and Return Code Check (which checks the return code of the User Written node and emails me the results - Outlook rule files away the success emails, and flags any failures).
- With my setup, I am only maintainging accounts/groups for my "general" users. All admin accounts and power users are being handled manually (as these users do not appear in my crosswalk). At some later time, I may modify the script to read info for my admins and power users directly from AD. I am using my exclusions for these accounts...so that the script skips over them.
If anyone has any suggestions on how to improve this script, please post. I am always looking for suggestions, as I am just getting stated in SAS.
_________________________________________________________
/*---- Start of User Written Code ----*/
/* Use the edited version of the %MDUCHGV macro */
/* to prevent PROC SQL truncation warnings, per */
/* SAS Note 49002. */
%include '/data/sasdata/admin/mduchgv.sas';
/* Get METAServer from automatic macro variables */
%let var_METAServer = &METASERVER;
/*-------------------------------------------------------
* Define system options to connct to the metadata server.
*-------------------------------------------------------*/
options metaserver=your.sas.server.here
metaport=8561
metauser="unrestricted_user_here@saspw"
metapass="password_for_above_user__should_encode_this"
metaprotocol=BRIDGE
metarepository=Foundation;
/* Define the library where the extracted crosswalk info will be stored. */
libname XWALK "/data/sasdata/admin/XWALKextract";
%rcSet(&syslibrc);
/* Define the library where the extracted metadata info will be stored. */
libname META "/data/sasdata/admin/METAextract";
%rcSet(&syslibrc);
/* Define the library where the change data sets will be stored. */
libname UPDATES "/data/sasdata/admin/METAupdates";
%rcSet(&syslibrc);
/* Define the library where the Exclusions data sets will be stored. */
libname EXCLUDE "/data/sasdata/admin/Exclusions";
%rcSet(&syslibrc);
/* Clean up if last execute left tables in libraries. */
proc datasets library=XWALK memtype=data kill;
proc datasets library=META memtype=data kill;
proc datasets library=UPDATES memtype=data kill;
proc datasets library=EXCLUDE memtype=data kill;
run;
quit;
/* Create canonical tables for XWALK. */
%MDUIMPC (LIBREF=XWALK, MAKETABLE=1);
%rcSet(&syserr);
/* Define the library where the crosswalk info resides. */
LIBNAME LASRLIB SASIOLA TAG=VAPUBLIC PORT=10031 HOST="your_sas_host_name_here";
%rcSet(&syslibrc);
/* Populate XWALK.person. */
proc sql;
INSERT INTO XWALK.person (keyid, name, displayname, description)
SELECT DISTINCT NETID AS keyid, NETID AS name, NETID AS displayname, 'my (RLS) - Auto-Synched User' AS description
FROM LASRLIB.my_SECURITY_CROSSWALK;
quit;
%rcSet(&sqlrc);
/* Populate XWALK.grpmems. */
proc sql;
INSERT INTO XWALK.grpmems (grpkeyid, memkeyid)
SELECT DISTINCT STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT)) AS grpkeyid, NETID AS memkeyid
FROM LASRLIB.my_SECURITY_CROSSWALK
WHERE COLL_CODE IS NOT NULL;
quit;
%rcSet(&sqlrc);
/* Populate XWALK.logins. */
proc sql;
INSERT INTO XWALK.logins (keyid, userid, authdomkeyid)
SELECT DISTINCT NETID AS keyid, NETID AS userid, 'DefaultAuth' as authdomkeyid
FROM LASRLIB.my_SECURITY_CROSSWALK;
quit;
%rcSet(&sqlrc);
/* Populate XWALK.idgrps. */
proc sql;
CREATE VIEW aview AS
SELECT DISTINCT STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT)) AS keyid, SUBSTR((STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT))), 1, MIN(60, LENGTH(STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT))))) LENGTH=60 AS name,
CASE
WHEN COLL_DEPT IS NULL THEN SUBSTR((LEFT('my - (RLS) - ')||STRIP(TRIM(COLL_DESC))), 1, MIN(255, LENGTH(LEFT('my - (RLS) - ')||STRIP(TRIM(COLL_DESC)))))
ELSE SUBSTR((LEFT('my - (RLS) - ')||STRIP(LEFT(COLL_DESC))||' - '||STRIP(LEFT(COLL_DEPT))), 1, MIN(255, LENGTH(LEFT('my - (RLS) - ')||STRIP(LEFT(COLL_DESC))||' - '||STRIP(LEFT(COLL_DEPT)))))
END LENGTH=255 as displayname,
CASE
WHEN COLL_DEPT IS NULL THEN 'College'
ELSE 'Department'
END as description
FROM LASRLIB.my_SECURITY_CROSSWALK
WHERE COLL_CODE IS NOT NULL;
INSERT INTO XWALK.idgrps (keyid, name, displayname, description)
SELECT DISTINCT keyid, name, displayname, description
FROM aview;
quit;
%rcSet(&sqlrc);
/* Populate XWALK.authdomain. */
proc sql;
INSERT INTO XWALK.authdomain (keyid, authdomname)
VALUES ('DefaultAuth', 'DefaultAuth');
quit;
%rcSet(&sqlrc);
/* Define exclusions for NETIDs - these are the NETIDs that have been added manually to SAS. */
/* This would account for power users and above - no need to worry about local SAS accounts. */
PROC SQL;
CREATE TABLE EXCLUDE.exclusion_rules
( tablename CHAR(255),
filter CHAR(255)
);
INSERT INTO EXCLUDE.exclusion_rules
VALUES('person', 'name="user1"')
VALUES('person', 'name="user2"')
VALUES('person', 'name="user3"')
VALUES('person', 'name="user4"')
;
QUIT;
%rcSet(&sqlrc);
/* The following line will cause this program to ONLY extract the data (and not perform the load). */
/* %let _EXTRACTONLY = ; */
/* Extract identity information from the metadata (target). */
%mduextr(libref=META);
%rcSet(&syserr);
/* Compare AD (master) to metadata (target). */
%mducmp(master=XWALK, target=META, change=UPDATES, exceptions=EXCLUDE.exclusion_rules);
%rcSet(&syserr);
/* Validate the change tables. */
%mduchgv(change=UPDATES, target=META, temp=work, errorsds=work.mduchgverrors);
%rcSet(&syserr);
/* Load the changes into the metadata. */
%macro exec_mduchglb;
%if (&MDUCHGV_ERRORS ^= 0) %then %do;
%put ERROR: Validation errors detected by %nrstr(%mduchgv). Load not attempted.;
%return;
%end;
%mduchglb(change=updates);
%mend;
%exec_mduchglb;
%rcSet(&syserr);
/*---- End of User Written Code ----*/
