BHOLD Model Generator file formats

  • Article

 

Applies To: Forefront Identity Manager 2010

BHOLD Model Generator structures data from authoritative sources that contain user and organizational information and Access Control Lists (ACLs) into a normalized model which is directly loaded into the BHOLD database to make it available to BHOLD core and other modules for administration. The authoritative sources that are being used for this process are Excel (.xls) files. The format of the data of these files must comply with format and structure restrictions so they can be properly imported by the BHOLD Model Generator module. This section contains a detailed description of the type of files that are required and how they should be created and filled in.

This topic covers the following subjects:

  • File set

  • General requirements and information

  • Primary keys per file type

  • Requirements for the orgunit file

  • Requirements for the user file

  • Requirements for the permission application file

  • Requirements for the role file

  • Requirements for the account permission file

File set

An organization can deliver the required data to BHOLD in either a three-file or a five-file set. The following table shows the files required by each type of file set.

Three-file set Five-file set
- Organizational unit file (orgunit file)
- User file
- Account permission (target) file		 - Organizational unit file (orgunit file)
- User file
- Permission application file
- Role file
- Account permission file

Use a five-file set if you want to define roles in addition to the roles that BHOLD automatically creates. Use a three-file set if you want BHOLD to create all roles for users.

General requirements and information

The following applies to all files, both in a three-file set and a five-file set:

  • Files must be saved as Excel 97- Excel 2003 workbooks, that is, in .xls format or as comma-delimited (.csv) text files. Model Generator cannot read files saved in other Excel formats, including Excel 2007 and Excel 2010 workbooks (.xlsx format), or as comma separated–value (.CSV) files. Separate Excel workbook files can be used, or you can use one or more Excel files containing multiple sheets. The sheets must be named as follows:

    File type Sheet name
    Organizational unit file OUs
    User file Users
    Permission application file Permissions-Applications
    Role file Role
    Account permission file Accounts-Permissions

  • Each file must contain specific mandatory columns, which are specified for each type of file. For some mandatory columns, all records must contain values. The file requirements section for each file type specify which mandatory columns cannot contain blank entries.

  • Any file can contain variable columns. In these columns extra object attributes can be specified, for example, orgunit type, job title, permission type, and role type. The BHOLD Model Generator module uses values in variable columns to populate additional object attributes in the BHOLD database.

  • In order to link records among files, you must use the same names for the variable columns in each of the files, and the values must be identical in records that you want to link. This requirement also applies if you want to use the variable columns to specify an attribute-based authorization (ABA) rule.

  • Column names should not include Microsoft SQL Server reserved keywords. For a list of these reserved, see Reserved Keywords in the SQL Server 2000 books online.

  • Column names must not contain blank or invalid characters, such as a dash (-), and parentheses: ( ).

  • All entries in the Excel files must be formatted as text.

  • The Excel files must not contain any formulas.

  • The Excel files must have not active filters.

  • All numeric values must start with a leading apostrophe ( ' ).

  • Before attempting to import files, make sure that all objects and the relationships between objects have been defined.

If these requirements are not met, the file data might not be loaded or might be loaded incorrectly.

Important

When a mandatory column is missing, an error is raised and the processing of data is stopped. The error is displayed and logged in mg_pc_function.txt. If mandatory columns contain empty values or rules are violated, the error is logged in MG_PC_Static_Data_Warning.txt.

The following sections describe the requirements for each type of file.

Primary keys per file type

The following table shows the primary key of each BHOLD Model Generator file.

File Primary Key
Orgunit file OU_Key
User file Employee_ID
Permission application file Application_Name x Permission_Name
Role file Role_Name
Account permission file Employee_ID x Account

and

Application_Name x Account

If a file has more than one row with the same primary key, either an error message will result or a single record will be created in the BHOLD database, depending on the file type. For example, if multiple records in the user file have the same employee ID, only one user will be created, but that user will be linked to multiple orgunits.

Requirements for the orgunit file

The organizational unit file (orgunit file) is an Excel file containing orgunit information necessary to build the orgunit structure in different levels in the organization, to enter each individual orgunit in the structure, and to link each orgunit with its supervisor role. Some examples of orgunits in an organization are departments, project teams, and business processes.

These requirements apply to the orgunit file for both a three- and a five-file set. Unless otherwise indicated, named columns are required.

Orgunit file column layout (three- and five-file set)

Column Name Column Description
OU_Key Type: Alphanumeric

ID of the orgunit used as a reference among the files for file import. This value is not imported to the BHOLD Core database.

This column must contain values for all records.
BHOLD_OU_Key Type: Alphanumeric

ID of the orgunit. This key identifies the orgunit in BHOLD Core.

This column must contain values for all records.
Managers_CorperateKey Type: Alphanumeric

Supervisor (user) of a role as specified in SV_Role or Manager of the orgunit.

This column does not have to contain values for all records. Important: The column name must be spelled exactly as shown. Note the nonstandard spelling of the word corporate.
SV_Role Type: Alphanumeric

Role name of supervisor role of the orgunit specified in BHOLD_OU_Key in the same record. See the next table for information about how the Model Generator supplies this value when it is absent.

This column does not have to contain values for all records.
Parent_OU_Key Type: Alphanumeric

The BHOLD_OU_Key of the orgunit’s parent orgunit.

If this value is missing, the orgunit’s parent will default to the root orgunit in BHOLD.

This column does not have to contain values for all records.
Variable columns Optional

Type: Alphanumeric

Orgunit attributes, for example, OU_Type and OU_Description

These extra orgunit attributes that are specific for the organization are created in BHOLD Core and the values for them are set.

If a variable column Description is added to the orgunit file, this will automatically be created in BHOLD Core as Desciption_extra as there is already a default description with the name Description in BHOLD Core.

Important

When a mandatory column is missing, an error is raised and the processing of data is stopped. The error is displayed and logged in mg_pc_function.txt. If mandatory columns contain empty values or rules are violated, the error is logged in MG_PC_Static_Data_Warning.txt.

The following table describes additional considerations for the orgunit file.

Consideration Description
There must be at least one orgunit record with an empty Parent_OU_Key Any orgunit with an empty Parent_OU_key field will be linked to the root in BHOLD Core.

If a record with an empty Parent_OU_Key is not present in the orgunit file, an error is raised and the processing of data in BHOLD Model Generator is stopped. The error is logged in mg_pc_function.txt.
Empty Parent_OU_Key fields During processing of the orgunit file, BHOLD Model Generator first selects the records in the orgunit file with empty Parent_OU_Key values and links them to the root orgunit in BHOLD Core. After the second-level orgunits are created, it checks for the children of these orgunits and creates them in the BHOLD databases so that, upon completion, the orgunit tree is created in BHOLD Core.
If, for a record, a Parent_OU_Key is filled in, the orgunit with that Parent_OU_Key must be specified in the same file If a record contains a Parent_OU_Key that does not match an OU_Key in the orgunit file, the record is skipped.
If OU_Key_1 is filled in for a user in the user file, then that orgunit must be present in the orgunit file in OU_Key for a record When this value is not present in the orgunit file, a Lost-and-Found orgunit is created in BHOLD Core and this orgunit is populated with the users linking to a non-existing orgunit in the orgunit file.
If Managers_CorperateKey is filled in for a record in orgunit file, and the user does not exist in BHOLD Core The user is created for the orgunit specified in the orgunit file (BHOLD_OU_Key).
Empty SV_Role If no supervisor role has been identified, one will be automatically generated following this naming convention:

[SV][-][random number][-][BHOLD_OU_Key]

The newly created supervisor roles are linked to the orgunit indicated in the BHOLD_OU_Key column

If managers are indicated in the Managers_CorperateKey column, these will be created and linked to the newly created SV roles.

Inheritance

When an orgunit is created and its supervisor structure is specified via SV_Role and Managers_CorperateKey the inheritance cannot be set. By default the inheritance on the orgunit will be set to Yes (On). If this needs to be No, it must be manually changed after role import in BHOLD Core. Inheritance set to yes means that a role is inherited in lower levels of the orgunit structure.

Supervisors—extra accounts

When a user is linked to an active supervisor role, an alias is created for this user. This alias is created to give the user access to the B1 application needed as a supervisor. This functionality creates extra accounts in BHOLD Core that were not loaded via the data load.

Requirements for the user file

The user file is an Excel file containing the users within the organization that are to be imported and includes such data as user name, employee type, job title, and the user’s orgunit. Unless otherwise indicated, named columns are required.

These requirements apply to the user file both for a three- and a five-file set.

User file column layout (three- and five-file set)

Column name Description
Employee_ID Type: Alphanumeric

ID of employee as known by the organization. The value entered as Employee_ID becomes the default alias in BHOLD Core. Important: To ensure that the employee ID matches the default alias in BHOLD Core, specify the employee ID in the format <domain>\<username>, where <domain> is the NetBIOS (short) name of the user’s domain and <username> is the user’s logon name.

BHOLD Model Generator skips any record that does not contain a value in this column.
Managers_CorperateKey Type: Alphanumeric

This column does not have to contain values for all records. Information in this column is not currently used, but the column must be present in the file. Important: The column name must be spelled exactly as shown. Note the nonstandard spelling of the word corporate.
OU_Key_1 Type: Alphanumeric

ID of the user’s orgunit. The value for OU_Key_1 must be specified in the orgunit file in the OU_Key column. This column is used as reference between the different files for file import.

This column must contain values for all records.
Description Type: Alphanumeric

Description of the user. Typically, this is used to provide the user’s full (given) name.

This column must contain values for all records.
Variable columns Optional

Type: Alphanumeric

User attributes, for example, Employee_Type and Job_Title.

These extra user attributes that are specific for the organization are created in BHOLD Core and the values for them are set.

Important

When a mandatory column is missing, an error is raised and the processing of data is stopped. The error is displayed and logged in mg_pc_function.txt. If mandatory columns contain empty values or rules are violated, the error is logged in MG_PC_Static_Data_Warning.txt.

The following table describes additional considerations for the user file.

Consideration Description
If Employee_ID is filled in, but no OU_Key_1 is specified for that user A Lost-And-Found orgunit is created in BHOLD Core and this orgunit is populated with the user without an OU_Key_1 specified
If OU_Key_1 is filled in for a user, then that orgunit must be present in the orgunit file in OU_Key When this value is not present in the orgunit file, the user is linked to the Lost-and-Found orgunit that was created in BHOLD Core.

User linked to multiple orgunits

If a user must be linked to more than one orgunit, different records, one per orgunit, must be created for the user in the user file.

Linking users to (proposed) roles

Users must be linked to their relevant (proposed) roles via the account permission file, unless a five-file set is used and roles are activated for users based on attribute based authorization (ABA) in the role file.

Requirements for the permission application file

The permission application file is an Excel file containing the applications and the permissions that are relevant for the organization and that must be imported. The data in this file is used to create the application and its permissions, and to link these to the supervisor role. To add multiple permissions to the same application, create multiple records for the same application with different Permission_Name values. To define multiple supervisor roles for an application, create multiple records for the same application with different values for SV_Role to the file. The data in this file is not linked to users. Unless otherwise indicated, named columns are required.

These requirements apply to the permissions application file, which is required only for the five-file set.

Permission application file column layout (five-file set)

Column name Column description
Application_Name Name of the application. An application is created in BHOLD based on this value.

This column must contain values for all records. If empty, the record will be skipped. Note: If the application is mentioned in other files, it will be created based on that information and will be linked to the default supervisor role. Records that were skipped in the permissions application file are not loaded.
Company Name of the organization.

This column does not have to contain values for all records.
Permission_Name Name of the permission in the application specified in Application_Name in the same record a role needs to be created for.

This column must contain values for all records.
Permission_Description Description of the permission in application specified in Permission_Name in the same record.

This column does not have to contain values for all records.
Permission_Owner_ID User responsible for (owner of) permission specified in Permission_Name in the same record.

This column does not have to contain values for all records.
SV_Role Role name of supervisor role of the application and permission as specified in Application_name and Permission_Name in the same record.

This column does not have to contain values for all records. If empty, a supervisor role will automatically be created.
Variable columns Optional

Type: Alphanumeric

Permission Attributes, such as Permission_Type

These extra permission attributes that are specific for the organization are created in BHOLD Core and the values for them are set.

If a variable column Description is added to the permission application file, this attribute will automatically be created in BHOLD Core as Desciption_extra, because an attribute with the name Description already exists in BHOLD Core.

Important

When a mandatory column is missing, an error is raised and the processing of data is stopped. The error is displayed and logged in mg_pc_function.txt. If mandatory columns contain empty values or rules are violated, the error is logged in MG_PC_Static_Data_Warning.txt.

The following table describes additional considerations for the permission application file.

Consideration Description
Creating permissions Permissions are created using Permission_Name, Permission_Description, SV_Role and Application_Name columns

If Application_Name is not filled in, BHOLD Model Generator will attempt to create the permission with Permission_Name, Permission_Description, and SV_Role.
Do not use identical descriptions for different permissions in the same application When duplicate permission descriptions are found for one application, an error is raised and the processing of data in BHOLD Model Generator is stopped. The error is displayed and logged in mg_pc_function.txt. Security Note: Permission descriptions must be unique per application

Requirements for the role file

The role file is an Excel file containing all the necessary roles that must be imported into the BHOLD role model, for example, all supervisor roles, permission roles, and application roles. It is used to create the role structure (parent and sub-roles) and to create each individual role and link each role to the supervisor role, the orgunit and relevant application permissions. The data in this file is not linked to users.

The role file can be used to enter different types of records. For example:

  • The relation between a role and the orgunits where it must be used

  • The relation between a parent and child role

  • The relation between a role and its permissions

  • To set an attribute-based authorization (ABA) rule on a role

For each type of record, different input checks are valid. These are described below. Unless otherwise indicated, named columns are required.

The following requirements apply to the role file, a file only necessary for the five-file set.

Role file column layout (five-file set)

Column name Column description
Role_Name Type: Alphanumeric

Name of the role for the permission as specified in Permission_Name in the application specified in Application_Name in the same record. The role name cannot begin with the number sign (#) and cannot exceed 100 characters in length.

If empty, the record will be skipped.

Roles are created based on Role_Name.

If roles with the same name are imported for more than one application, only the first one will be created. Other roles with the same name will be ignored.

This column must contain values for all records.
Permission_Name Type: Alphanumeric

The permission a role needs to be created for.

This column does not have to contain values for all records.
Application_Name Type: Alphanumeric

The application linked to the permission a role is created for.

This column does not have to contain values for all records.
Role_Type Type: Alphanumeric

The type of role. If an entry in this column is set to Supervisor, Model Generator creates the role as a supervisor role. Note: This column does not set the Role Type attribute of the BHOLD role object.

This column does not have to contain values for all records.
ABA_Attribute Type: Alphanumeric

Attribute, for example, Job_Title or Employee_type, on basis of which the ABA rule is created for the role as specified in Role_Name in the same record.

This column does not have to contain values for all records except when record values have been entered for ABA_Operator and ABA_Value. The three ABA columns must all be empty or all be filled.
ABA_Operator Type: Operator

Specifies the relation between the ABA_Attribute and ABA_Value in the same record. The ABA operator must be one of the following:

- =
- >
- <
- <=
- >=
- <>

This column does not have to contain values for all records except when record values have been entered for ABA_Attribute and ABA_Value. The three ABA columns must all be empty or all be filled.
ABA_Value Type: Alphanumeric

Actual value (for example, accountant) of the attribute specified in ABA_Attribute (job title) on the basis of which the ABA rule is created for the role specified in Role_Name.

This column does not have to contain values for all records except when record values have been entered for ABA_Attribute and ABA_Operator. The three ABA columns must all be empty or all be filled.
Inherited_Role Type: Alphanumeric

Specifies if a role is inherited in lower levels of the orgunit structure. Must be either YES or NO.

This column does not have to contain values for all records.
SV_Role Type: Alphanumeric

Role name of supervisor role (to manage the role) of the role as specified in Role_name in the same record.

If an unknown supervisor role (not in other files or in BHOLD Core) or no role is indicated in the SV_Role column, then the role specified in the Role_Name column is linked to the default supervisor role.

This column does not have to contain values for all records.
Parent_Role_Name Type: Alphanumeric

Role that is linked as parent to the role specified in Role_name in the same record. The parent role is inked to the role to create a role structure.

If a role has a parent role indicated in Parent_Role_Name column, the parent role is created if it does not exist already. The role is linked to the parent role.

If left empty, no parent role will be linked to the role.

This column does not have to contain values for all records.
OU_Key Type: Alphanumeric

ID of the orgunit. Value for OU_Key must be specified in the OU_Key column of the orgunit file.

See considerations below for when values in this column are required.
OU_Link_Type Type: Alphanumeric

How the role is linked to the orgunit.

Value in OU_Link_Type column must be one of the following values:

- PROPOSED
- EFFECTIVE

Depending on the value in OU_Link_Type, the role is linked to the OU as proposed or effective. If OU_Link_Type is EFFECTIVE, the role is linked to all users in that orgunit. If OU_Link_Type is PROPOSED, the role must be activated separately for each user in the orgunit.

This column does not have to contain values for all records.
Variable columns Type: Alphanumeric

Role attributes, for example, Role_Description

These extra role attributes are created in BHOLD Core and the values for them are set. If a variable column Description is added to the Role File, this will automatically be created in BHOLD Core as Desciption_extra as there is already a default description with the name Description in BHOLD Core.

Important

When a mandatory column is missing, an error is raised and the processing of data is stopped. The error is displayed and logged in mg_pc_function.txt. If mandatory columns contain empty values or rules are violated, the error is logged in MG_PC_Static_Data_Warning.txt.

The following table describes additional considerations for the role file.

Consideration Description
Creating an ABA rule If ABA_Attribute, ABA_Operator and ABA_Value are filled in, the ABA rule is created. Note: If these fields contain values, the values must have been previously created in BHOLD Core.
Linking a role to orgunit If Role_Name, OU_Key and OU_Link_Type are filled in, the role is linked to the organizational unit specified in the OU_Key column
Linking a role to a permission If Role_Name and Permission_Name are filled in, the permission is linked to the role.

The following table describes additional role file requirements based on the purpose of the file load selected in BHOLD Model Generator. When Audit is selected, the user cannot create membership roles, attribute roles, or proposed roles. The role model is only based on actual user permissions grouped in personal roles. When Generic is selected, these role types can be created as part of the normalized role model.

Role file requirements depending on type of file load

Requirement Additional information
If, for a record, Permission_Name and Application_Name are filled in, they should also exist in the permissions application file. When both values are not found in the permissions application file, they will be created and linked to the role that was specified in Role_Name. If no role was specified, the permission and application will be created and linked to each other.

If the value for Application is not found in the permissions application file, and the permission is listed in the file, the permission will be linked to the specified role and the application will be created but will not be linked to the permission or the role.
When defining the role content by means of the role file, the following fields must be filled in:

Role_Name

Permission_Name

Application_Name

Also, the fields for that record listed in the next column must be empty.		 ABA_Attribute

ABA_Operator

ABA_Value

Inherited_Role

SV_Role

Parent_Role_Name

OU_Key

OU_Link_Type
When defining the role structure by means of the role file, the following fields must be filled in:

Role_Name

Role_Type

Parent_Role_Name

Also, the fields for that record listed in the next column must be empty.		 Permission_Name

Application_Name

ABA_Attribute

ABA_Operator

ABA_Value

Inherited_Role

SV_Role

OU_Key

OU_Link_Type
If Role_Name, Permission_Name and Application_Name are filled in for a record

OR

if Role_Name, Role_Type and Parent_Role_Name are filled in for a record

The rules listed in the next column apply to those records.		 Role_Name is mandatory

Role_Type is mandatory

ABA_Attribute should be equal to one of the following : Employee_Type, Job_Title

ABA_Operator should be equal to one of the following : =, >, <, <=, >=, <>

If ABA_Attribute is filled in, then both of the following should also be filled in : ABA_Operator, ABA_Value

If Permission_Name is filled in, Application_Name should also be filled in

If Application_Name is filled in, Permission_Name should also be filled in

For Non-ABA roles, if Permission_Name and Application_Name are filled in, then the following should be empty: Inherited_Role, OU_Key, OU_Link_Type

If Inherited_Role, OU_Key, OU_Link_Type are filled in, then the following fields for that record should be empty : Permission_Name, Application_Name

Requirements for the account permission file

The account permission file is an Excel file that specifies the relation between a user and application permissions and/or the relation between a user and directly linked roles.

Based on these records, BHOLD Model Generator fills the content of personal roles or links a user directly to a specific role.

You can use the account permission file for two purposes:

  • Create a link between a user (Employee_ID) and a role (Role_Name).

  • Activate a link between a user and a role. This requires that Account and Application_Name are also supplied.

Unless otherwise indicated, named columns are required.

These requirements apply to the account permission file both for a three- and a five-file set.

Account permission file column layout (three- and five-file set)

Column name Column description
Employee_ID Type: Alphanumeric

ID of employee as known by the organization. The value entered as Employee_ID must match an entry in the Employee_ID of the user input file.
Account Type: Alphanumeric

Account used by the user (as specified in Employee_ID) to access the application specified in Application_Name.

This column does not have to contain values for all records.
Application_Name Type: Alphanumeric

Name of the application.

This column must contain values for all records. Restrictions are listed following this table.
Permission_Name Type: Alphanumeric

Name of the permission in the application specified in Application_Name in the record that a role is being created for.

This name must be unique per application.

This column does not have to contain values for all records. Restrictions are listed following this table.
Role_Name Type: Alphanumeric

Name of the role for the permission specified in Permission_Name in the application specified by Application_Name in the same record.

If multiple roles with the same name are loaded for more than one application, only the first one will be taken into account and created. Other roles with the same name will be ignored.

This column does not have to contain values for all records. Restrictions are listed following this table.
Variable columns Optional

Type: Alphanumeric

 The following applies only to a three-file set. These variable columns would already be created in the role file of a five-file set.

Role attributes, for example, Role_Type (Attribute_Type is necessary to define if a role is a personal, membership, attribute, proposed or ownership role).

Extra role attributes that are specific for the organization are created in BHOLD Core and the values for them are set. If a variable column Description is added to the permission application file, this attribute will automatically be created in BHOLD Core as Desciption_extra, because an attribute with the name Description already exists in BHOLD Core.

Important

When a mandatory column is missing, an error is raised and the processing of data is stopped. The error is displayed and logged in mg_pc_function.txt. If mandatory columns contain empty values or rules are violated, the error is logged in MG_PC_Static_Data_Warning.txt.

The following table describes additional considerations for the account permission file.

Consideration Description
Employee_ID is empty If, for a record, Employee_ID column is empty or user is not found in BHOLD Core or User File (=unknown user) then a ghost account is created using this naming convention:

[ACC][-][Account]

This ghost account is linked to the orgunit "Ghost Accounts"
Employee_ID and Role_Name filled in If both Employee_ID and Role_Name are supplied in a record, the following happens:

- A role is created
- The newly created role is marked as a personal role
- The user is created
- User is linked to this newly created role
- The role is linked to all supervisor roles of all the orgunits this user is linked to
Employee_ID, Account, Application_Name and Permission_Name are filled in If Employee_ID, Account, Application_Name and Permission_Name are all supplied in a record, the following happens:

 
  • The user is created.
  • The application is created.
  • A user alias is created.
  • If no Role_Name is specified, a personal role name is created by using the following naming conventions:

     
    • A prefix is added (to be specified, default value PR)
    • A random number or user attribute is added . A user attribute is added only if Employee_ID is given a value. No attribute will exist for unknown users.
    • If provided, the Department name is added. The department name Ghost_Accounts is added when the user is unknown. Otherwise, the role is linked to the orgunit of the user specified by Employee_ID.
  • Check for a personal role. If no personal role found then Model Generator does the following by using the ghost role name ([ACC][-][Account]) or by using the generated name:

     
    • Create the role
    • Mark the role as a personal role
    • Add the user to the role
    • Add the permission to the role
    • Link the role to all supervisor roles of all orgunits that contain the user
  • If an existing personal role is found, then the permission is added to the role
If a user was given an Employee_ID value in the account permission file, then it must exist in the User file in the Employee_ID field When this value is not present in the orgunit file, a ghost account is created using this naming convention:

[ACC][-][Account]
Create a correct Alias in BHOLD Core To create a correct Alias, the Employee_ID, Account and Application_Name must be filled in.

The following combinations are possible for the account permission file:

  • Empl_ID + Account + Application_Name + Permission_Name + Role_Name

  • Empl_ID + Account + Application_Name + Role_Name

  • Account + Application_Name + Permission_Name + Role_Name (A ghost account is created.)

  • Account + Application_Name + Role_Name (A ghost account is created.)

  • Empl_ID + Application_Name + Permission_Name + Role_Name

When a user has multiple accounts for an application, multiple records must be entered.