Table types in One Identity Manager
For access through the object layer, the tables in the One Identity Manager schema are labeled with a particular table type. Additional properties are required for the table definition, depending on the table type.
Table 15: Table types in the One Identity Manager schema
|
Table |
The Table table type is used for simple tables, many-to-many tables, M:all tables, and work tables. |
|
Base table |
The Base table table type is used for simple tables, many-to-many tables, M:all tables, and work tables in order to define database views with the View type. Examples of base tables include the BaseTree table for mapping roles and organizations, and the BasetreeHas* assignment tables for assigning company resources to organizations and roles. |
|
View |
The View table type is used for database views on tables with the Base table type. Database views with the View type represent subsets of the underlying tables. Database views with the View type are mainly used to map roles. For example, the database views Department, Locality and Profitcenter are subsets of the Basetree base table. |
|
Proxy |
The Proxy table type is used for database views on tables with the Table type or on database views with the View type. Database views with the Proxy type are union views of different tables. Columns are mapped between a database view of the Proxy type and the underlying tables by means of the column definitions and proxy view extensions. Database views with the Proxy type are mainly used for mapping in the Unified Namespace. |
|
Union |
The Union table type is used for database views on tables with the Table type or on database views with the View, or Proxy type. Database views with the Union type are union views of different tables and are used to group together different object types with the same context. For example, the QERAccProductUsage database view identifies which service items are used in which IT Shop products. Database views with the Union type are mainly used for editing the user interface and creating reports. |
|
Read only |
The Read only table type is used for database views on tables with the Table type or on database views with the View, Proxy, or Union type. Database views with the Read only table type may be subsets or unions of the underlying tables. Database views with the Read only type are for display only and are mainly used for editing the user interface and creating reports. |
Related topics
Database views of the View type
Database views with the View type represent subsets of the underlying tables. Database views with the View type are mainly used to map roles. For example, the Department, Locality, and Profitcenter database views are subsets of the Basetree base table.
Database views with the View type are predefined database views. Templates and formatting rules can be defined for columns in these views.
The following information is used to define a database view of the View type.
Table 16: Properties for defining a database view of the View type
|
Table |
Name of the table in the data model. |
|
Type |
View type of table. |
|
Base table |
Base table that the view is based on. |
|
Condition for view definition |
Restricting condition for creating the database view as a WHERE clause for database queries. The condition relates to the underlying base table. |
|
Columns |
A reference is required for each column of the database view to a column in the underlying base column. Make the assignment in the column definition. |
|
Insert values |
Default settings for individual columns that are assigned when a new data set is added. The values are entered in VB.Net syntax. |
|
Selection script |
Selection script as a VB.Net term, to determine during runtime whether the object passed belongs to the view. |
Example:
The Department table is defined as a database view of the View type. When you enter data in the Department table, the UID_OrgRoot column should be populated with the QER-V-Department value.
Table 17: Example of defining a database view of type "View"
|
Table |
Department |
|
Type |
View |
|
Base table |
BaseTree |
|
Condition for view definition |
UID_OrgRoot = 'QER-V-Department' |
| Insert values |
base.putvalue("UID_OrgRoot", "QER-V-Department") |
| Selection script |
Value = (String.Equals($UID_OrgRoot$, "QER-V-Department", StringComparison.OrdinalIgnoreCase)) |
|
Columns -->base columns (excerpt from column definition) |
Department.DepartmentName-->BaseTree.Ident_Org
Department.Description-->BaseTree.Description |
|
Resulting view definition |
create view dbo.Department as
select
Ident_Org as DepartmentName,
Description as Description,
...
from BaseTree
where UID_OrgRoot = 'QER-V-Department' |
Related topics
Database views of the proxy type
Database views with the Proxy table type are union views of different tables. Columns are mapped between a database view of the Proxy type and the underlying tables by means of the column definitions and proxy view extensions. The DBQueue Processor calculates the actual view definition from the column mapping. This only takes into account tables that are not disabled by a preprocessor condition. Templates and formatting rules cannot be defined for columns in these views.
Database views of the Proxy type are mainly used for mapping the Unified Namespace. For example, the UNSRoot database view is used for mapping of the ADSDomain or LDAPDomain tables in the Unified Namespace.
The following information is used to define a database view of the Proxy type.
Table 18: Properties for defining a database view of the proxy type
|
Table |
Name of the table in the data model. |
|
Type |
Type of Proxy table |
|
Additional view definition |
Database query generated as a SELECT statement for setting up the database view. View definition extensions are generated by the DBQueue Processor. The following are taken into account when generating:
-
Tables in which the database view is entered as the proxy view
-
Columns that have a reference to a proxy view column
-
Columns that are defined as extensions to the proxy view
The extensions are linked to each other internally with the Union operator. |
|
Condition for view definition |
Restricting condition for creating the database view as a WHERE clause for database queries. |
|
Columns |
Database view columns. |
Example:
The following mappings are required to map the ADSDomain table in the Unified Namespace to the USRoot database view.
-
The UNSRoot database view is entered as a proxy view in the ADSDomain table.
-
The columns of the ADSDomain table to be mapped in the Unified Namespace are given a reference to the corresponding columns in the proxy view.
For example, the Ident_Domain column in the ADSDomain table is mapped to the Ident_root column of the UNSRoot proxy view.
-
Columns that are expected in the UNSRoot database view but are not contained in the ADSDomain table must be entered in the ADSDomain table as extensions to the proxy view.
For example, the UNSRoot view expects input of the target system type in the UID_DPRNameSpace column. This column is not in the ADSDomain tables. Therefore, 'ADS-DPRNameSpace-ADS' as UID_DPRNameSpace is entered as an extension to the proxy view in the ADSDomain table.
The DBQueue Processor generates the extended view definition from the data. The following statement is a excerpt from the generated extension.
select ... Ident_Domain as Ident_UNSRoot..., 'ADS-DPRNameSpace-ADS' as UID_DPRNameSpace from ADSDomain
Related topics
Database views of the Union type
Database views with the Union table type are union views of various tables and are mainly used to group various object types with the same context. In the QERAccProductUsage union view, for example, you determine which service items are used in which IT Shop products.
Database views with the Union type are predefined database views. Templates and formatting rules cannot be defined for columns in these views. In the view definition, the object key column (XObjectKey) must be referenced. This makes it possible to create a single object with its permitted permissions.
Database views of the Union type are mainly used for editing the user interface and creating reports.
The following information is used to define a database view of the Union type.
Table 19: Properties for defining a database view of the Union type
|
Table |
Name of the table in the data model. |
|
Type |
Type of table Union. |
|
Additional view definition |
Database query as a SELECT statement for setting up the database view.
NOTE: Never select NULL as <Column>. Instead, convert this explicitly to the requested value type.
Example:
convert(nvarchar(max), NULL) as <column>
convert(varchar(38), NULL) as UID_<column>
convert(varchar(138), NULL) as ObjectKey<column>
Several extensions for the view definition can be defined. The extensions are linked to each other internally with the Union operator.
When you add a column to a custom table, an entry is created in the DialogColumn table. When you delete a column, the entry is removed from the DialogColumn table. Changes to the schema of default database views are not permitted. |
|
Condition for view definition |
Restricting condition for creating the database view as a WHERE clause for database queries. |
|
Columns |
Database view columns. |
Example:
The QERAccProductUsage table is defined as a database view of the Union type. In the union view, you establish which service item is used in which products. The following example shows an except from the definition based on system entitlements (table ESet) and report subscriptions (RPSReport table).
Table 20: Example of defining a database view of Union type
|
Table |
QERAccProductUsage |
|
Type |
Union |
|
Columns |
TableName, UID_AccProduct, XObjectKey |
|
Extension 1: Additional view definition |
ESet |
|
Extension 1: Query |
select 'ESet' as TableName, g.XObjectKey, g.UID_AccProduct
from ESet g |
|
Extension 2: Additional view definition |
RPSReport |
|
Extension 2: Query |
select 'RPSReport' as TableName, g.XObjectKey, g.UID_AccProduct
from RPSReport g |
|
Resulting view definition |
create view dbo.QERAccProductUsage as
select * from
(
select convert(varchar(11), null) as TableName, convert(varchar(38), null) as UID_AccProduct, convert(varchar(138), null) as XObjectKey where 1=0
union all
select xxTab.TableName, xxTab.UID_AccProduct, xxTab.XObjectKey
from (
select 'ESet' as TableName, g.XObjectKey, g.UID_AccProduct
from ESet g
) as xxTab
union all
select xxTab.TableName, xxTab.UID_AccProduct, xxTab.XObjectKey
from (
select 'RPSReport' as TableName, g.XObjectKey, g.UID_AccProduct
from RPSReport g
) as xxTab
) as x |
Related topics
