ASEXPORT Procedure

Concepts: ASEXPORT Procedure

Overview

The matches between DataFlux Authentication Server and SAS Metadata Server objects are managed internally by the relationships in the tabular data represented in the following three schemas:

AS Schema
META Schema
X Schema

Note that the maximal set of working objects available for export is controlled by the various filters specified on the procedure statement.

AS Schema

The AS schema includes the working set of DataFlux Authentication Server objects that are extracted using the initial filters specified in the AS(FILTER) procedure suboptions. The AS schema is a one to one tabular snapshot of Authentication Server objects read in using the META/FILTER options.

This schema consists of the following tables:

DOMAINS

extracted using the AS(FILTER(DOMAINS)) suboption.

USERS

extracted using the AS(FILTER(USERS)) suboption.

GROUPS

extracted using the AS(FILTER(GROUPS)) suboption.

LOGINS

extracted using the AS(FILTER(LOGINS)) suboption.

The AS schema contains a representation of the DataFlux Authentication Server objects currently in the working set of source objects. These objects are available for selection into the working set of export mappings in the X.DOMAIN_MAP, X.USER_MAP and X.GROUP_MAP tables. The schema is displayed in the following sample:

create table AS.DOMAINS
(
   NAME              NVARCHAR(256)  NOT NULL,
   NAME_N            NVARCHAR(256)  NOT NULL,
   "DESC"            NVARCHAR(256)  NOT NULL,
   IS_CS_USERID      NCHAR(1)       NOT NULL,
   IS_DQ_USERID      NCHAR(1)       NOT NULL,
   IS_UPN_USERID     NCHAR(1)       NOT NULL
);
create table AS.USERS
(
   ID                NCHAR(32)      NOT NULL,
   NAME              NVARCHAR(256)  NOT NULL,
   NAME_N            NVARCHAR(256)  NOT NULL,
   "DESC"            NVARCHAR(256)  NOT NULL,
   ENABLED           NCHAR(1)       NOT NULL
);
create table AS.LOGINS
(
   FQLN              NVARCHAR(256)  NOT NULL,
   DOMAIN_N          NVARCHAR(256)  NOT NULL,
   NAME              NVARCHAR(256)  NOT NULL,
   USER_ID           NCHAR(32)      NOT NULL
);
create table AS.GROUPS
(
   ID                NCHAR(32)      NOT NULL,
   NAME              NVARCHAR(256)  NOT NULL,
   NAME_N            NVARCHAR(256)  NOT NULL,
   "DESC"            NVARCHAR(256)  NOT NULL,
   OWNER_ID          NCHAR(32)
);

META Schema

The META schema includes the working set of SAS Metadata Server objects extracted using the initial filters specified in the META(FILTER) procedure suboptions.

This schema consists of the following tables:

DOMAINS

extracted using the META(FILTER(DOMAINS)) suboption.

USERS

extracted using the META(FILTER(GROUPS)) suboption.

GROUPS

extracted using the META(FILTER(GROUPS)) suboption.

LOGINS

extracted using the META(FILTER(LOGINS)) suboption.

The META schema contains a representation of the SAS Metadata Server objects currently in the working set of destination objects. These objects are available for selection into the working set of export mappings in the X.DOMAIN_MAP, X.USER_MAP and X.GROUP_MAP tables. The schema is displayed in the following sample:

create table META.DOMAINS 
( 
   ID                NCHAR(17)      NOT NULL, 
   AS_ID             NVARCHAR(128), 
   NAME              NVARCHAR(60)   NOT NULL, 
   NAME_N            NVARCHAR(60)   NOT NULL, 
   "DESC"            NVARCHAR(200)  NOT NULL, 
   OUTBOUND_ONLY     NCHAR(1)       NOT NULL, 
   TRUSTED_ONLY      NCHAR(1)       NOT NULL 
);
create table META.USERS 
( 
   ID                NCHAR(17)      NOT NULL, 
   AS_ID             NCHAR(32), 
   NAME              NVARCHAR(60)   NOT NULL, 
   NAME_N            NVARCHAR(60)   NOT NULL, 
   "DESC"            NVARCHAR(200)  NOT NULL 
);
create table META.LOGINS 
( 
   ID                NCHAR(17)      NOT NULL, 
   AS_ID             NVARCHAR(128), 
   FQLN              NVARCHAR(128)  NOT NULL, 
   NAME              NVARCHAR(60)   NOT NULL, 
   "DESC"            NVARCHAR(200)  NOT NULL, 
   DOMAIN_ID         NCHAR(17)      NOT NULL, 
   OWNER_ID          NCHAR(17)      NOT NULL, 
   TRUSTED_ONLY      NCHAR(1)       NOT NULL 
);
create table META.GROUPS 
( 
   ID                NCHAR(17)      NOT NULL, 
   AS_ID             NVARCHAR(32), 
   NAME              NVARCHAR(60)   NOT NULL, 
   NAME_N            NVARCHAR(60)   NOT NULL, 
   "DESC"            NVARCHAR(200)  NOT NULL 
);

X Schema

The X schema includes normalized content, views, and joined result sets produced from matches between objects represented in the AS and META schemas.

This schema consists of the following tables or views:

DOMAIN_MAP

contains the working set of (AS:Domain, OMSOBJ:AuthenticationDomain) domain mappings currently queued for export.

USER_MAP

contains the working set of (AS:Group, OMSOBJ:IdentityGroup) group mappings currently queued for export.

GROUP_MAP

contains the working set of (AS:Group, OMSOBJ:IdentityGroup) group mappings currently queued for export.

AS_LOGINS_N

contains views of AS.LOGINS with additional FQLN_N column where the column contains a normalized fully qualified login name that can be matched with logins in MS.LOGINS. Login name qualification and normalization is governed by the naming rules inferred from the AS.DOMAINS(IS_CS_USERID, IS_DQ_USERID, IS_UPN_USERID) columns.

MS_LOGINS_N

contains views of MS.LOGINS with additional FQLN_N column where the column contains a normalized fully qualified login name that can be matched with logins in AS.LOGINS. Login name qualification and normalization is governed by the naming rules inferred from the AS.DOMAINS(IS_CS_USERID, IS_DQ_USERID, IS_UPN_USERID) columns.

The X schema contains the working set of DataFlux Authentication Server:SAS Metadata Server export mappings. These mappings are used along with utility tables to assist in matching and selection criteria when using the MATCH, MATCH SINGLETON, ADD, and REMOVE statements.

The contents are listed in following table:

X Schema Contents
Table or View	Description
X.DOMAIN_MAP	Current working set of domain object mappings.
X.USER_MAP	Current working set of user object mappings.
X.GROUP_MAP	Current working set of group object mappings.
X.AS_LOGINS_N	View of AS.LOGINS with normalized fully qualified login name column, FQLN_N.
X.MS_LOGINS_N	View of META.LOGINS with normalized fully qualified login name, FQLN_N.

The schema is displayed in the following sample:

create table X.DOMAIN_MAP 
( 
   AS_NAME              NVARCHAR(256)  NOT NULL, 
   AS_NAME_N            NVARCHAR(256)  NOT NULL, 
   AS_DESC              NVARCHAR(256)  NOT NULL, 
   AS_IS_CS_USERID      NCHAR(1)       NOT NULL, 
   AS_IS_DQ_USERID      NCHAR(1)       NOT NULL, 
   AS_IS_UPN_USERID     NCHAR(1)       NOT NULL, 
   META_ID              NCHAR(17), 
   META_AS_ID           NVARCHAR(128), 
   META_NAME            NVARCHAR(60)   NOT NULL, 
   META_NAME_N          NVARCHAR(60)   NOT NULL, 
   META_DESC            NVARCHAR(200)  NOT NULL, 
   META_OUTBOUND_ONLY   NCHAR(1)       NOT NULL, 
   META_TRUSTED_ONLY    NCHAR(1)       NOT NULL 
);
create table X.USER_MAP 
( 
   AS_ID                NCHAR(32)      NOT NULL, 
   AS_NAME              NVARCHAR(256)  NOT NULL, 
   AS_NAME_N            NVARCHAR(256)  NOT NULL, 
   AS_DESC              NVARCHAR(256)  NOT NULL, 
   AS_ENABLED           NCHAR(1)       NOT NULL, 
   META_ID              NCHAR(17), 
   META_AS_ID           NCHAR(32), 
   META_NAME            NVARCHAR(60)   NOT NULL, 
   META_NAME_N          NVARCHAR(60)   NOT NULL, 
   META_DESC            NVARCHAR(200)  NOT NULL 
);
create table X.GROUP_MAP 
( 
   AS_ID                NCHAR(32)      NOT NULL, 
   AS_NAME              NVARCHAR(256)  NOT NULL, 
   AS_NAME_N            NVARCHAR(256)  NOT NULL, 
   AS_DESC              NVARCHAR(256)  NOT NULL, 
   AS_OWNER_ID          NCHAR(32), 
   META_ID              NCHAR(17), 
   META_AS_ID           NCHAR(32), 
   META_NAME            NVARCHAR(60)   NOT NULL, 
   META_NAME_N          NVARCHAR(60)   NOT NULL, 
   META_DESC            NVARCHAR(200)  NOT NULL 
);
create view X.AS_LOGINS_N as 
   select AL.*, 
          case 
             when (DX.AS_IS_DQ_USERID || DX.AS_IS_CS_USERID) = 'FF' then 
                upper(AL.NAME) 
             when (DX.AS_IS_DQ_USERID || DX.AS_IS_CS_USERID) = 'FT' then 
                AL.NAME 
             when (DX.AS_IS_DQ_USERID || DX.AS_IS_CS_USERID) = 'TF' then 
                upper(AL.NAME) || '@' || AL.DOMAIN_N 
             else
                AL.NAME || '@' || AL.DOMAIN_N 
          end as "FQLN_N"         
   from AS.LOGINS        AL, 
        X.DOMAIN_MAP_ALL DX 
   where AL.DOMAIN_N = DX.AS_NAME_N
;
create view X.MS_LOGINS_N as 
   select ML.*, 
          case 
             when (DX.AS_IS_DQ_USERID || DX.AS_IS_CS_USERID) = 'FF' then 
                upper(ML.NAME) 
             when (DX.AS_IS_DQ_USERID || DX.AS_IS_CS_USERID) = 'FT' then 
                ML.NAME 
             when (DX.AS_IS_DQ_USERID || DX.AS_IS_CS_USERID) = 'TF' then 
                upper(ML.NAME) || '@' || DX.AS_NAME_N 
             else 
                ML.NAME || '@' || DX.AS_NAME_N 
          end as "FQLN_N" 
   from META.LOGINS      ML, 
        X.DOMAIN_MAP_ALL DX 
   where ML.DOMAIN_ID = DX.META_ID
;

Last updated: June 7, 2017