Creating database views with read-only type
Database views with the Read only table type may be subsets or unions of the underlying tables. Database view with the Read only type are predefined database views. Templates and formatting rules cannot be defined for columns in these views.
Database views of the Read only type are for display only and are mainly used for editing the user interface and creating reports.
Technical details
-
The technical identifier for the database view is automatically formed according to the CCC<Table name> schema.
-
The first column of the database query (view definition) is used as the primary key column of the database view.
NOTE: It is recommended that you reference the primary key column of the queried table in the view definition as the first column. If this is not possible, then at least select a unique characteristic.
-
If a database view contains a foreign key column, you specify which destinations tables should be referenced. The table and column relations are generated automatically.
-
If you want to index the database view for the full-text search, the XDateInserted, XDateUpdated, and XObjectKey columns must be available.
To create a database view of read-only type in the Schema Extension
-
Start the Launchpad and log in to the One Identity Manager database.
-
Open the Launchpad and select the One Identity Manager Schema Extension entry. This starts the Schema Extension program.
-
Click Next on the start page.
-
On the Database connection page, check the connection data for the One Identity Manager database.
-
On the Select method page, select Create view.
-
On the Create view page, enter the following information.
Table 175: Database view properties
|
Table |
A technical name for the database view. |
|
Display name |
Name displayed for the database view. The display name is used, for example, to identify the database view in a database search or error output. |
|
Description |
Comments on using the database view. |
|
View definition |
Enter the database query as a Select statement. |
-
(Optional) Create the foreign key relations on the Create FK relations for views page.
-
On the Configure columns of table page, enter display names for the columns.
-
(Optional) Click 
to configure more column properties. For more information, see Advanced configuration of columns.
Related topics
Using Common Table Expressions in read-only database views
In One Identity Manager 7.0, the behavior was changed for Common Table Expressions (CTEs) with the with keyword as condition for view definitions in Read-only database views. Conditions for view definitions are embedded in a summarized query. This means, there is no guarantee that CTEs are placed right at the top of the query.
Possible error messages
(execute slot single)50000 0 re-throw in Procedure QBM_ZViewBuildR, Line 1050000 0 rethrow in Procedure QBM_PViewBuildR_intern, Line 10250000 0 re-throw in Procedure QBM_PViewBuildR_intern, Line 8250000 0 re-throw in Procedure QBM_PViewBuild_FromAddOn, Line 6550000 0 re-throw in Procedure QBM_PSQLCreate, Line 26156 0 detected in (...) Procedure ..., Line 6156 0 Incorrect syntax near the keyword 'with'
Recommended action
-
Create a database view using the CTE.
Example:
create view CCC_Vxy as
with myWithClause (column1, column2) as (
select 1 as column1, 2 as column2
)
select * from myWithClause
go
-
Use the database view in the additional view definition (QBMViewAddon) of Read-only database views.
select * from CCC_Vxy
Creating database views with 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.
Technical details
-
The technical identifier for the database view is automatically formed according to the schema CCC<Table name>.
-
As the first column of the database query (view definition), the object key (XObjectKey) must be referenced. The object key allows faster access to a single object with its valid permissions.
-
If you want to index the database view for the full-text search, the XDateInserted, XDateUpdated, and XObjectKey columns must be available.
To create a database view of Union type in the Schema Extension
-
Start the Launchpad and log in to the One Identity Manager database.
-
Open the Launchpad and select the One Identity Manager Schema Extension entry. This starts the Schema Extension program.
-
Click Next on the start page.
-
On the Database connection page, check the connection data for the One Identity Manager database.
-
On the Select method page, select Create union view.
-
On the Create union view page, enter the following information.
Table 176: Database view properties
|
Table |
A technical name for the database view. |
|
Display name |
Name displayed for the database view. The display name is used, for example, to identify the database view in a database search or error output. |
|
Description |
Comments on using the database view. |
|
View definition |
Enter the database query as a Select statement. |
Related topics
Creating new assignment tables
Technical details
-
The technical identifier for the table is automatically formed according to the schema CCC<Table name>.
-
The XObjectKey and XMarkedForDeletion columns are automatically generated.
-
The table relations and column relations are generated automatically.
To create a new assignment (many-to-many) table in the Schema Extension.
-
Start the Launchpad and log in to the One Identity Manager database.
-
Open the Launchpad and select the One Identity Manager Schema Extension entry. This starts the Schema Extension program.
-
Click Next on the start page.
-
On the Database connection page, check the connection data for the One Identity Manager database.
-
On the Select method page, select New relation table.
-
On the Create a relation table page, enter the following information.
Table 177: Assignment table properties
|
Table |
A technical name for the table. |
|
Display name |
Displays table name The display name is used, for example, to identify the table in a database search or for error output. |
|
Description |
Comments on using the table. |
|
Create XOrigin column (for assignment requests) |
You can create the origin column (XOrigin) optionally. The origin of an assignment is stored in this column as a bit field. Each time an entry is made in the assignment table the bit position is changed according to the assignment type. |
|
Related tables |
Use the Left table and Right table menus to specify which tables are included in the relation table. |
|
Column names |
In Column name fields, enter the relevant columns for each side of the table. Select the table’s primary key column. |
-
On the Configure columns of table page, enter display names for the columns.
-
(Optional) Click to configure more column properties. For more information, see Advanced configuration of columns.
Related topics
icon in front of the column name and select the target table in the Target table column.