19
Extending Warehouse Builder Functionality

This chapter describes how to extend current Warehouse Builder functionality. This chapter includes the following topics:

About Oracle Metabase (OMB) Plus

This chapter discusses tasks you can perform to extend Warehouse Builder functionality via Oracle Metabase (OMB) Plus.

OMB Plus is the flexible, high-level command line metadata access tool for Oracle9i Warehouse Builder. With OMB Plus, you can write the syntactic constructs such as variable support, conditional and looping control structures, error handling, and standard library procedures. You can access the Warehouse Builder metadata repository and the runtime repository. Using OMB Plus, you can navigate repositories and manage and manipulate metadata in repositories.

The remainder of this chapter discusses how to perform specialized tasks in Warehouse Builder using the OMB scripting language. For syntax information on specific OMB Plus commands, see Oracle9i Warehouse Builder Scripting Reference.

User-Defined Properties

Warehouse Builder allows you to extend its design repository through User Defined Properties (UDP). Each repository object has a pre-defined property set. You can add custom properties to an object by creating a UDP.

You create and manage UDPs using the Oracle MetaBase (OMB) Scripting Language. You can view UDPs using OMB or the Warehouse Builder client. In the client, UDPs display on property sheets and in the Warehouse Builder Browser.

UDPs behave like native properties and follow Warehouse Builder rules for object locking, multiuser access, transactions, and security. When you take metadata snapshots of the object, Warehouse Builder captures the associated UDPs. You can also import and export UDPs using the Metadata Loader (MDL).

Managing User Defined Properties

As the Warehouse Builder administrator, you should define all user-defined properties into the Warehouse Builder repository before allowing end users to access it. In doing so, you avoid the task of supplying values for UDPs on existing objects.

You should register all user-defined properties centrally in the design repository, and not locally on the client. To create or edit a UDP, you must be the single user accessing to the Warehouse Builder repository.

You can use the following OMB Plus commands for creating and manipulating user defined properties:

When you create and commit a UDP, OMB performs the following validations:

A namespace check ensures that you did not define two identically named properties within the same class hierarchy. Prefix the user defined property with 'UDP_' to avoid conflicts with the future names introduced by Warehouse Builder.
A property value check ensures that you defined default values consistent with the data types you specified.
A user access check ensures that you have single-user access to the entire repository.

OMBDEFINE

The OMBREDEFINE CLASS_DEFINITION allows you to manipulate UDPs. To create a UDP on the Dimension object, issue the following statement. This adds a UDP definition to class definition 'DIMENSION':

OMBREDEFINE CLASS_DEFINITION 'DIMENSION_TABLE' 
     ADD PROPERTY_DEFINITION 'UDP_Dim' SET PROPERTIES (TYPE, DEFAULT_VALUE)
     VALUES ('INTEGER', '100')

The following command adds a property to the 'COLUMN' type. This property displays in the Table, View, Materialized View, External Table and Sequence Property Sheets:

OMBREDEFINE CLASS_DEFINITION 'COLUMN' 
     ADD PROPERTY_DEFINITION 'UDP_Col' SET PROPERTIES (TYPE, DEFAULT_VALUE)
     VALUES ('STRING', 'foo')

The following command allows you to change the name or the default value of a given property.

OMBREDEFINE CLASS_DEFINITION 'TABLE' MODIFY PROPERTY_DEFINITION 'UDP_Tbl'
     SET PROPERTIES (DEFAULT_VALUE, BUSINESS_NAME) 
     VALUES ('99', 'UDP_Tbl')

The following command deletes the tbl_udp property from the 'Table' class. This is a very destructive and highly deprecated action since it cannot be undone. It renders all property value customizations made for this property definition in your repository irretrievable:

OMBREDEFINE CLASS_DEFINITION 'TABLE' DELETE PROPERTY_DEFINITION 'UDP_Tbl'

OMBDESCRIBE

You can use OMBDESCRIBE on a Class Definition to view the attributes for a metadata element. OMBDESCRIBE allows you to list the user defined properties you for a given object type. For instance, the following command lists the user defined properties for a dimension:

OMBDESCRIBE CLASS_DEFINITION 'DIMENSION_TABLE' GET PROPERTY_DEFINITIONS

You can also use OMBDESCRIBE to introspect the properties of a Property Definition. For instance, for a user defined property called UDP_Dim under the Dimension Class Definition, you can learn the datatype, default value and business name with the following command:

OMBDESCRIBE CLASS_DEFINITION 'DIMENSION_TABLE' PROPERTY_DEFINITION  'UDP_Dim' 
     GET PROPERTIES (TYPE, DEFAULT_VALUE, BUSINESS_NAME)

You can specify the data type of a user-defined property such as char, number and date.

Viewing User Defined Properties

In the user interface, you can view UDPs in the following components:

Warehouse Builder Client

Once you create a UDP using scripting, Warehouse Builder displays the UDP in the User Defined Properties tab on the associated properties sheet. The User Defined Properties tab does not appear until you create a UDP. For example, the Dimension Properties sheet typically does not display the User Defined Properties tab. However, once you add a UDP to a dimension, the UDP appears on the Dimension Properties sheet shown in Figure 19-1.

Figure 19-1 Sample Properties Sheet with User-Defined Properties

Text description of samplepr.gif follows.

Text description of the illustration samplepr.gif

In the left panel, the tab displays the object navigation tree. In the right panel, the tab lists all the contained objects and corresponding extended properties. It shows the values and categories you specified when you created the UDP in scripting. You can modify the values but not the categories in the User Defined Properties tab. You must use OMB Plus to edit categories.

Warehouse Builder Design Browser

The Warehouse Builder Browser also displays UDPs. Warehouse Builder Browser is a metadata management and reporting portal for Warehouse Builder. Browser displays object properties, object relationships, and lineage and impact analysis reports.

If you define a UDP for a given object, the Browser lists the UDP name and values as Extended Property Name and Extended Property Value as shown in Figure 19-2.

Figure 19-2 Sample Properties Sheet with User-Defined Properties

Text description of samplebr.gif follows.

Text description of the illustration samplebr.gif

Transferring UPDs to Other Repositories

The primary method for propagating changes from one repository to another is via MDL. The MDL allows you to export and import the metadata definition of the user-defined properties and its contents.

Exporting UDPs

You can export UDPs from the command line only. In the MDL Control file, the option is DEFINITIONFILE=filename to export the metadata definition. For example:

## Sample Export file 
USERID=UserName/Password@HostName:PortID:OracleServiceName
#
DEFINITIONFILE=Drive:\DirectoryName\filename.mdd

FILE=Drive:\DirectoryName\filename.mdl
LOG=Drive:\DirectoryName\filename.log

Importing UDPs

You can import UDPs from the command line only. During import, MDL updates the user-defined properties for all objects.In the MDL Control file, the option is DEFINITIONFILE=filename to import the metadata definition. For example:

## Sample Import file 
USERID=UserName/Password@HostName:PortID:OracleServiceName
#
DEFINITIONFILE=Drive:\DirectoryName\filename.mdd

FILE=Drive:\DirectoryName\filename.mdl
LOG=Drive:\DirectoryName\filename.log

You can import UDPs using one of the following search criteria:

Universal ID: The metadata definition contains a Universal Object ID (UOID). The UOID uniquely identifies objects across repositories. If you import the MDL file by UOID, then MDL looks up the metadata definition by UOID. If the metadata definition name in the source MDL file is different from the metadata definition in the repository, then MDL renames it.
Physical Name: MDL looks up the metadata definition by physical name.

Regardless of the import mode, MDL either adds the metadata definition if it does not exist in the repository, or updates the metadata definition if it already exists. MDL will not delete metadata definitions in the repository.

When updating the metadata definition, MDL only renames the object if the names are different (search criteria is by UOID), and update the default value. MDL does not change the datatype (e.g., STRING).

Managing Security with PL/SQL

This section contains the following topics:

Maintaining Repository Users

Multiple identifiable Warehouse Builder users can access the same central repository schema when they are registered by the repository owner.

Warehouse Builder includes utility procedures for the following maintenance tasks:

Update the role protecting password

Although the repository owner does not explicitly use the protecting password for the role, it is recommended that the repository owner change the password often.

The repository owner must connect to the repository schema using SQL Plus and issue the following statement:
```
Call WBSecurityHelper.updateRolePwd('newpwd'); 
```
where 'newpwd' is the new password used to protect the role chosen by repository owner. The changed password encryption appears in the table called OWB_ROLE_INFO table.
Register repository users

Warehouse Builder users must be database users located in the same database instance as the repository. The database user's default role should not be ALL. Use 'After user xxx DEFAULT Role x' to set the default role property.

To register a repository user, execute the following statement in SQL Plus:

call WBSecurityHelper.registerOWBUser ('username')
Unregister repository users

To unregister a user from a repository, execute the following statement in SQL Plus:
```
Call WBSecurityHelper.unregisterOWBUser('username');
```
List all repository users

The repository owner must connect to the database containing the repository and issue the following statement:
```
Set serveroutput on;
Call WBSecurityHelper.listOWBUser( );
```
Set serveroutput on is required since listOWBUser() uses the DBMS_OUTPUT.put_line for the output to dump the data to the user interface. Otherwise, DBMS_OUTPUT.put_line only dumps the output into an intermediate data structure.

The above user maintenance tasks should be performed in the repository by the repository owner.

Plug-in Interface for the Security PL/SQL Package Specification

This section describes the plug-in interface specification for the PL/SQL security package provided by Warehouse Builder. You need to substitute the dummy PL/SQL package body provided by Warehouse Builder by implementing this interface in your Warehouse Builder repository. The interface specification and the dummy implementation are also available from your repository schema.

The functions and procedures described below are invoked for all the Warehouse Builder operations if the function isSecurityServiceCustomized is changed to return a value of 1. The default return value of this function, if you do not customize the package, is 0.

The functions and procedures described below are empty when you install Warehouse Builder. To implement these functions and procedures, you must implement a security logic within them and build your own security data model to connect objects such as operations, users, and object types.

CREATE OR REPLACE PACKAGE WBSecurityServiceImpl AS

FUNCTION isSecurityServiceCustomized RETURN NUMBER;

Use Function: isSecurityServiceCustomized() to differentiate the implementation of security service, whether you customize the security PL/SQL implementation or use the implementation provided by Warehouse Builder.

Return value: this function returns 1 if you want to implement the PL/SQL package of this specification, otherwise it returns 0;

PROCEDURE securityCheckForCreation(outcome OUT NUMBER, 
userId IN VARCHAR2,
objectUOIDOperationInvokedOn IN VARCHAR2,
status IN VARCHAR2, 
parentModuleUOID IN VARCHAR2,
parentProjUOID   IN VARCHAR2, 
repos_Schema IN VARCHAR2,
objectType IN NUMBER);

Procedure: securityCheckForCreation: Used to create an operation security check. When you try to create an object, Warehouse Builder calls this procedure to ask the implementation whether the creation operation is acceptable or not.

Argument explanation:

Outcome: 1: The creation operation is acceptable.

Outcome: 0: The creation operation is not acceptable.

userId: The login user's database user name.

objectUOIDOperationInvokedOn: The parent folder UOID, where you create a new object. For objects, such as Projects and Snapshots, that do not have folder parent, this argument is NULL.

status: An attribute of a module: WB_DEV_STATUS, WB_QA_STATUS, WB_PROD_STATUS defined in this specification. This attribute describes the status of the module. For objects, such as Projects, Modules, or Snapshots, that are not children of any module in the hierarchy, this argument is NULL.

parentModuleUOID: The UOID of the module. For objects such as Projects or Snapshots that are not children of any module in the hierarchy, this argument is NULL.

parentProjUOID: The UOID of the project. For objects such as Projects or Snapshots that are not children of any module in the hierarchy, this argument is NULL.

repos_schema: The central repository schema name you are working on.

objectType: The type of object you want to create. It is one of the object type constants defined in this specification.

PROCEDURE securityCheck(outcome OUT NUMBER,
           userId IN VARCHAR2,
    operation IN NUMBER, 
    objectUOIDOperationInvokedOn IN VARCHAR2,
    objectTypeOperationInvokedOn IN NUMBER, 
    status IN VARCHAR2, 
    parentModuleUOID IN VARCHAR2,
    parentProjUOID   IN VARCHAR2, 
    repos_Schema IN VARCHAR2);

PROCEDURE: securityCheck is used for the following operations:

WB_EDIT  
WB_DELETE  
WB_VALIDATE 
WB_GENERATION  
WB_VERSION

Whenever you invoke one of the above listed operations, Warehouse Builder calls this procedure to see whether the operation is acceptable or not.

Argument explanation:

Outcome: 1: The creation operation is acceptable.

Outcome: 0: The creation operation is not acceptable.

userId: The login user's database user name.

operation: One of constants listed above.

objectUOIDOperationInvokedOn: The target object's UOID.

objectTypeOperationInvokedOn: The type of object the operation is invoked on (one of the object type constants defined in this specification).

status: An attribute of a module.

WB_DEV_STATUS

WB_QA_STATUS,

WB_PROD_STATUS

This attribute is used to describe the status of the module. If you operate on a project, module, or snapshot, this argument is NULL.

parentModuleUOID: The UOID of the module. If you invoke the operation on a module, then the objectUOIDOperationInvokedOn and the parentModuleUOID are the same. If you invoke an operation on a project or snapshot that is not a child of any module in the hierarchy, this argument is NULL.

parentProjUOID: The UOID of the project. If you invoke the operation on a project, then the objectUOIDOperationInvokedOn and the parentProjUOID are the same. If you invoke the operation on a snapshot that is not the child of any project in the hierarchy, this argument is NULL.

repos_schema: The central repository schema name you are working on

PROCEDURE securityCheckForService(outcome OUT NUMBER,
           userId IN VARCHAR2,
    serviceOp IN NUMBER, 
    moduleUOID IN VARCHAR2,
    projUOID   IN VARCHAR2,     
    repos_Schema IN VARCHAR2);

PROCEDURE securityCheckForService is used for the following service operations:

WB_DEPLOY

WB_MDL_IMPORT

WB_MDL_EXPORT

WB_BRIDGE_IMPORT

WB_BRIDGE_EXPORT

WB_SOURCE_IMPORT

WB_RUNTIME_EXECUTE

WB_SNAPSHOT_RESTORE

Argument explanation:

Outcome: 1: The creation operation is acceptable.

Outcome: 0: The creation operation is not acceptable.

userId: The login user's database user name.

serviceOp: One of constants listed above.

moduleUOID: The UOID of a module on which the user is invoking operation:serviceOp. The result is NULL for serviceOp:WB_DEPLOY,WB_BRIDGE_EXPORT, WB_RUNTIME_EXECUTE. It is valid for: WB_MDL_IMPORT, WB_MDL_EXPORT, WB_SOURCE_IMPORT, WB_SNAPSHOT_RESTORE.

projUOID: The UOID of a project on which the user is invoking the operation: serviceOp on it. The result is NULL for the serviceOp:WB_DEPLOY, WB_BRIDGE_EXPORT, WB_RUNTIME_EXECUTE. It is valid for: WB_MDL_IMPORT, WB_MDL_EXPORT, WB_SOURCE_IMPORT, WB_SNAPSHOT_RESTORE.

repos_schema: The central repository schema name you are working on

--BEGIN CONSTANT DEFINITION

CUSTOM_SHARED_LIBRARY CONSTANT 
VARCHAR2(100):='9E012195D16211D48D7100B0D02A59E8';

CUSTOM_SHARED_LIBRARY: The UOID constant for a predefined Warehouse Builder folder: Custom shared library. On the UI, this folder is called Custom located under the Public Transformation node.

This folder contains all global shared library transformations created by the user.

To control the users who have permission to create transformations under the Custom folder, you can check if the argument objectUOIDOperationInvokedOn in procedure securityCheckForCreation equals to this constant or not. To control users who can invoke the operations such as WB_EDIT or WB_DELETE, on a shared function or a procedure, check if the argument parentModuleUOID in the procedure securityCheck equals this constant or not.

Since this module is predefined, you cannot change the status or use the status of this module for access control.

--Definition of constants for all basic operations

WB_EDIT   CONSTANT INTEGER := 0;
WB_DELETE   CONSTANT INTEGER := 1;
WB_REFERENCE   CONSTANT INTEGER := 2;
WB_CREATE        CONSTANT INTEGER := 3;
WB_VALIDATE   CONSTANT INTEGER := 4;
WB_GENERATION   CONSTANT INTEGER := 5;
WB_VERSION  CONSTANT INTEGER := 6;

Use this group of operation constants in the SecurityCheck procedure. Use one of the above constants for the argument "operation". You can also use it to control which user can invoke this operation.

--Definition of constants for all service type operations

WB_DEPLOY   CONSTANT INTEGER := 100;
WB_MDL_IMPORT   CONSTANT INTEGER := 101;
WB_MDL_EXPORT   CONSTANT INTEGER := 102;
WB_BRIDGE_IMPORT CONSTANT INTEGER := 103;
WB_BRIDGE_EXPORT CONSTANT INTEGER := 104;
WB_SOURCE_IMPORT CONSTANT INTEGER := 105;
WB_RUNTIME_EXECUTE  CONSTANT INTEGER :=106;
WB_SNAPSHOT_RESTORE   CONSTANT INTEGER := 107;

Use this group of service operation constants in SecurityCheckForService procedure. Use one of these constants for the argument "serviceOp". You can also use it to control which user can invoke this service operation.

--Definition of the module status

WB_DEV_STATUS  CONSTANT VARCHAR2(100) := 'DEV_STATUS';
WB_QA_STATUS  CONSTANT VARCHAR2(100) := 'QA_STATUS';
WB_PROD_STATUS  CONSTANT VARCHAR2(100) := 'PROD_STATUS';

Use this group of module status constants should be used in the securityCheckForCreation and SecurityCheck procedures. Use the above constants if you want to create a child of a module (for securityCheckForCreation) or if you invoke an operation on a module or the child of a module (for SecurityCheck), otherwise the argument "status" will be null. You can also use it to implement your access control based on the status of the module.

--Definition of object type

WB_PROJECT   CONSTANT INTEGER := 1; 
WB_ORACLE_MODULE   CONSTANT INTEGER := 2;
WB_GATEWAY_MODULE CONSTANT INTEGER := 3;
WB_SAP_MODULE  CONSTANT INTEGER := 4;
WB_FLAT_FILE_MODULE CONSTANT INTEGER := 5;
WB_SHARED_MODULE  CONSTANT INTEGER := 6;
WB_REPOS_MODULE   CONSTANT INTEGER := 7;
WB_COLLECTION  CONSTANT INTEGER := 8;
WB_WAREHOUSE   CONSTANT INTEGER := 9;
WB_TABLE   CONSTANT INTEGER := 10;
WB_VIEW    CONSTANT INTEGER := 11;
WB_MATERIALIZED_VIEW  CONSTANT INTEGER := 12;
WB_SEQUENCE   CONSTANT INTEGER := 13;
WB_DIMENSION_TABLE  CONSTANT INTEGER := 14;
WB_CUBE_TABLE   CONSTANT INTEGER := 15;
WB_FLAT_FILE   CONSTANT INTEGER := 16;
WB_PACKAGE   CONSTANT INTEGER := 17;
WB_TRANSFORMATION   CONSTANT INTEGER := 18;
WB_MAPPING   CONSTANT INTEGER := 19;
WB_MIV_MODULE   CONSTANT INTEGER := 20;
WB_CONNECTOR   CONSTANT INTEGER := 21;
WB_LOCATION   CONSTANT INTEGER := 22;
WB_RUNTIME_REPOSITORY   CONSTANT INTEGER := 23;
WB_BUSINESS_AREA CONSTANT INTEGER := 24;
WB_INTELLIGENCE_MODULE CONSTANT INTEGER := 25;
WB_PROCESS_FLOW  CONSTANT INTEGER := 26;
WB_PROCESS_FLOW_MODULE  CONSTANT INTEGER := 27;
WB_PROCESS_FLOW_PACKAGE CONSTANT INTEGER:=  28;
WB_QUERY_OBJECT  CONSTANT INTEGER:=  29;
WB_ADVANCED_QUEUE  CONSTANT INTEGER:=  30;
WB_EXTERNAL_TABLE  CONSTANT INTEGER:=  31;
WB_REPORT   CONSTANT INTEGER:=  32;
WB_REPORT_GROUP   CONSTANT INTEGER:=  33;
WB_REPORT_MODULE  CONSTANT INTEGER:=  34;
WB_OBJECT_TYPE   CONSTANT INTEGER:=  35;
WB_SNAPSHOT  CONSTANT INTEGER:=  36;

This group of object type constants must be used in procedures securityCheckForCreation and securityCheck. The argument "objectType" in securityCheckForCreation and argument "objectTypeOperationInvokedOn" in securityCheck will be one of the above constants. You can use this constant to control which user can create which kind of object or which user can invoke operations such as WB_EDIT or WB_DELETE on what types of objects.

Because there are many arguments, the procedures will depend on whether the object on which the user invokes an operation is the child of the project or a module. The information is listed below.

--Children of Project

While WB_SNAPSHOT and WB_PROJECT are not children of a project, other objects in the list of type constants are.

--Children of Module

/* The following are not children of any module: WB_PROJECT, WB_ORACLE_MODULE, WB_GATEWAY_MODULE, WB_SAP_MODULE, WB_FLAT_FILE_MODULE, WB_SHARED_MODULE, WB_REPOS_MODULE, WB_COLLECTION, WB_WAREHOUSE, WB_MIV_MODULE, WB_LOCATION, WB_CONNECTOR, WB_RUNTIME_REPOSITORY, WB_BUSINESS_AREA, WB_INTELLIGENCE_MODULE, WB_PROCESS_FLOW_MODULE, WB_REPORT_MODULE, WB_SNAPSHOT.

Other object types can be the children of module.

END WBSecurityServiceImpl;

Definitions of the Constants in the Package Specification

The following section lists the constant definitions of basic operations. For these operations, Warehouse Builder checks if the operation is acceptable or not on an object instance level

WB_EDIT: Edit (update) operation
WB_DELETE: Delete operation
WB_REFERENCE: The operation you want to reference an object within another object. For example, when you want to drag a table into a mapping as an operation.
WB_CREATE: Create operation
WB_VALIDATE: Validate operation used to check the accuracy of the object definitions
WB_GENERATION: Generate the SQL scripts for the definitions in Warehouse Builder
WB_VERSION: Versioning objects which means user can add an object to a snapshot version, remove an object from a snapshot version, or replace an existing versioned object with a new copy.

The following section lists the constant definitions for service operations. For these operations, Warehouse Builder checks if the operation is acceptable or not on a system wide level. If a user has the privilege to invoke a service operation, he should invoke this operation on any object. For operations such as WB_MDL_IMPORT, WB_MDL_EXPORT, WB_BRIDGE_IMPORT, WB_SOURCE_IMPORT, WB_SNAPSHOT_RESTORE, Warehouse Builder provides security at the project or module level.

WB_DEPLOY: First generates the SQL script (if does not exist) and then deploy the SQL scripts to a runtime database schema.
WB_MDL_IMPORT: Imports the metadata into Warehouse Builder from a flat file format.
WB_MDL_EXPORT: Exports the metadata from Warehouse Builder into a flat file.
WB_BRIDGE_IMPORT: Another importing solution. Warehouse Builder also provides Discoverer, OLAP, CWM, and third party bridges that use a different format from MDL.
WB_BRIDGE_EXPORT: Another exporting solution. Warehouse Builder also provides Discoverer, OLAP, CWM, and third party bridges that use a different format from MDL.
WB_SOURCE_IMPORT: Imports the metadata information of database objects, such as tables and views, into Warehouse Builder from a given database link.
WB_RUNTIME_EXECUTE: Executes the deployed SQL script from the runtime database schema.
WB_SNAPSHOT_RESTORE: Restores the design space from a given snapshot, selectively or fully.

Constant definitions of the module status. You can change the module status from the Warehouse Builder client using the property page of a module.

WB_DEV_STATUS: The module is under development.
WB_QA_STATUS: The module is in QA status.
WB_PROD_STATUS: The module is in production.

Constant definitions of object types.

WB_PROJECT: Project
WB_ORACLE_MODULE: Oracle Module
WB_GATEWAY_MODULE: Gateway Module
WB_SAP_MODULE: SAP Module
WB_FLAT_FILE_MODULE: Flat File Module
WB_SHARED_MODULE: Shared Module
WB_REPOS_MODULE: Repository Module
WB_COLLECTION: Collection
WB_WAREHOUSE: Warehouse Module
WB_TABLE: Table
WB_VIEW: View
WB_MATERIALIZED_VIEW: Materialized View
WB_SEQUENCE: Sequence
WB_DIMENSION_TABLE: Dimension Table
WB_CUBE_TABLE: Cube Table
WB_FLAT_FILE: Flat File
WB_PACKAGE: Package
WB_TRANSFORMATION: Transformation
WB_MAPPING: Mapping
WB_MIV_MODULE: MIV Module
WB_CONNECTOR: Connector
WB_LOCATION: Location
WB_RUNTIME_REPOSITORY: Runtime Repository
WB_BUSINESS_AREA: Business Area
WB_INTELLIGENCE_MODULE: Intelligence Module
WB_PROCESS_FLOW: Process Flow
WB_PROCESS_FLOW_MODULE: Process Flow Module
WB_PROCESS_FLOW_PACKAGE: Process Flow Package
WB_QUERY_OBJECT: Query Object
WB_ADVANCED_QUEUE: Advanced Queue
WB_EXTERNAL_TABLE: External Table
WB_REPORT: Report
WB_REPORT_GROUP: Report Group
WB_REPORT_MODULE: Report Module
WB_OBJECT_TYPE: Object Type
WB_SNAPSHOT: Snapshot

Implementing the PL/SQL Interface

When you implement the PL/SQL interface, you decide whether an operation is accepted or rejected based on the arguments passed by the Warehouse Builder client. Warehouse Builder also provides a public view named ALL_IV_FIRSTCLASS_OBJECTS in its repository. This view contains the following information about an object:

Object UOID and class type
Object name and business name
Created by and updated by
Creation and update timestamps

Given the object UOID, you can reference the public view for the above information. You can use this information from the public view to decide whether the operation is acceptable or not.

The arguments passed by the caller provide you with different levels of security granularity:

Repository level: Check the repos_Schema argument as to whether to freeze the whole repository.
Project level: Check the argument parentProjUOID to freeze the whole project or not.
Module level: Check the argument parentModuleUOID to freeze the whole module or not.
Object class type level: Check the argument objectTypeOperationInvokedOn to accept or prevent access to the entire type.
Object instance level: Check the object UOID.
Development process level: Check the argument: status (development, quality assurance, or production) to make access control decisions for an entire module. The status is an attribute of any module that you can change using the Warehouse Builder user interface through the module properties page.

Considerations

To implement the security framework, you need to deploy your own security data model. For example, for object level security, you need to create a table or a series of tables that contain information on which user can perform which operation on an object. You also need to implement your own procedures or user interface to maintain this data model.

The following are considerations for implementing the PL/SQL interface:

If you base your access control decisions on the status of a module, Warehouse Builder recommends that only users in the administration group have the access to edit the module.
If a user has the validate/generate privilege on an object instance, such as a Table, and has no edit privilege on the object instance, the user can only validate/generate the object without the generated result persisting.
When you invoke an operation (operation A) from the Warehouse Builder client, in rare cases, operation B is invoked underneath. This can result in a situation where you have granted a user permission to invoke operation A, but the user can not successfully finish operation A because s/he has no permission to invoke operation B.

To resolve this issue, you need to either grant the user privileges for both operations A and B, or not grant the user privileges on either operation.

For example, in the mapping editor, when a bound object is added to a mapping, or when inbound reconcile is performed on an object, Warehouse Builder automatically creates a connector to the bound object's location if one does not already exist. To allow the user to finish adding a bound object into mapping editor, or to finish the inbound reconcile, you need to grant the user EDIT permission on the mapping and CREATE permission for the connector.
To implement the PL/SQL procedures, you must not issue any commit or rollback command.

Because the connection used to call the security PL/SQL procedures is the connection used to persist all the metadata, this connection is controlled by Warehouse Builder through its own transaction manager to determine when to commit or rollback.

If you invoke commit or rollback in the procedure, it will corrupt the repository. If you want to record debug information in your PL/SQL procedure, you can do it in a separate child procedure, with autonomous transactions (use progma autonomous_transaction at the beginning of the procedure). In this procedure, you can issue a commit or rollback command.
If you grant a user the permission to CREATE an object, the user also needs permission to EDIT the object. After an object is created, you can change your policy to revoke the user's EDIT permission on the object.
Do not perform any DML operations on the repository database objects created by Warehouse Builder when you implement the security PL/SQL procedures. This may damage the repository and make the Warehouse Builder client malfunction or stop functioning.

If you install the implementation package for security while another user is logged into the Warehouse Builder repository, you may receive the following SQL Error: ORA-04061: Existing state of package body "OWB_REPO_513.WBSECURITYSERVICEIMPL" has been invalidated. The user must exit Warehouse Builder and restart the installation.
For the following procedure:
```
PROCEDURE securityCheckForService(outcome OUT NUMBER,
           userId IN VARCHAR2,
    serviceOp IN NUMBER, 
    moduleUOID IN VARCHAR2,
    projUOID   IN VARCHAR2,     
repos_Schema IN VARCHAR2)
```
The moduleUOID and projUOID refer to the UOID of modules and projects in . Warehouse Builder. The security only applies to objects in the Warehouse Builder repository.

If you try to import an MDL file or if you try to restore a snapshot containing projects or modules that do not exist in the repository, then Warehouse Builder will not perform any security check on those projects or modules. It is recommended that the repository owner perform MDL import or snapshot restore on folder objects such as projects and modules.

Implementing Sample Security Policies

This section describes three types of advanced security policies available through Warehouse Builder:

Proactive Security: Warehouse Builder enables you to plug in a customized security PL/SQL implementation package in the Warehouse Builder repository to provide tailored access control to users according to the security rules defined by your organization.
Reactive Security: Tracks audit information using metadata history and also determines security policies from such audit information.
Data Stewardship: Warehouse Builder enables an individual or a group of individuals to "own" portions of the metadata rather than the technical administrators. Metadata ownership thus becomes an important component of metadata security management. The data steward's role is important when the metadata repository is used to model the source, target, and ETL operations for your enterprise, and to capture the structure of your enterprise for business users through Portals or ad-hoc query tools. Warehouse Builder enables you to incorporate the responsibility of the data steward into a predefined metadata security authorization policy.

The Warehouse Builder Security Architecture

The following sections describe the Warehouse Builder security model and architecture, and the various ways in which the tool can leverage existing infrastructure security systems.

The server-side security architecture of Warehouse Builder is shown in Figure 19-3.

Figure 19-3 Warehouse Builder Server-Side Security Architecture

Text description of security.gif follows.

Text description of the illustration security.gif

The repository database has five schemas including two Warehouse Builder repositories (also known as Central Repositories or Warehouse Builder Repositories) and three repository users: Albert, Bob and Cynthia. Repository user schemas are always located in the same database instance as the central repository. Warehouse Builder requires that these design repositories have an assigned Administrator to administer the registration and deregistration of repository users. Only the Administrator of a Warehouse Builder repository can access the central repository schema. Although Albert, Bob and Cynthia have schemas in the database, in order for them to have access to a specific Warehouse Builder repository, they need to be registered by the Administrator of that specific central repository. Because they are not the owners of the Warehouse Builder metadata tables, these users cannot modify the metadata through other access tools such as SQL Plus.

For additional information, see "Maintaining Repository Users".

Using a Customizable Security Authorization Framework

Warehouse Builder publishes a PL/SQL Security Package Specification and loads it into the central repository. By default, the package implementation that is loaded when you create a new Warehouse Builder repository gives all permissions to all registered users. However, Warehouse Builder administrators can design their own security policies and implement these policies according to the framework of the security package specification. This customized security implementation can be plugged into the Warehouse Builder central schema. All subsequent Warehouse Builder actions in the central repository pass through this new security policy.

Security checks are made at every point in the tool when you try to modify an object. For example, when you create, update, or delete an object. The security checks call the PL/SQL security implementation package in the central repository. For details on the specification for the security implementation package, see "Implementing the PL/SQL Interface".

The security implementation package can use the information available in the central repository. For example, an Excel spreadsheet can be used to generate the relational lookup tables to indicate to the Warehouse Builder client whether a permission is granted or not.

By default, the Warehouse Builder Security Implementation Package allows all operations. Table 19-1 shows sample security permissions.

Table 19-1 Permissions for User Albert

Object	Create	Edit	Delete	Generate	Deploy
Table	Y	Y	Y	N	N
View	Y	Y	Y	N	N
Materialized View	Y	Y	Y	N	N
Dimension	N	N	N	N	N
Cube	N	N	N	N	N
Mapping	N	Y	N	N	N
Process Flow	N	Y	N	N	N

Freezing Projects

If you want to freeze the project MY_PROJECT and prevent access to all its contents, the following restrictions will apply:

You cannot create, edit, or delete any objects under a frozen project.
You cannot invoke any of the services that modify objects within this frozen project. For example, you cannot perform an MDL import, a source import, or a snapshot restore in this project.
You can deploy, export, and execute runtime procedures within a frozen project.
You can validate and generate within a frozen project.
You cannot add or remove any objects from a frozen project to a snapshot.

The frozen project security policy is implemented within Warehouse Builder through the following files. These files are located on your installation CD under: samples/security_feature/frozenproject.

frozenProject.pkb: Holds the implementation of the security policy.

frozenProject.sql: Contains a table of the structure as shown in Table 19-2. The administrator can freeze projects by inserting them into this table and setting the isFrozen flag to 1.

For Table 19-2, the repository owner must issue the following SQL statements to frozen and unfrozen projects from SQL Plus:

insert into frozen_projects (projectName,isFrozen) values('SAMPLEPROJECT1', 
'1');
insert into frozen_projects (projectName,isFrozen) values('SAMPLEPROJECT2', 
'0');
insert into frozen_projects (projectName,isFrozen) values('SAMPLEPROJECT3', 
'1');
commit;

Table 19-2 Example File Structure for frozenProject.sql

Project Name	isFrozen
SampleProject 1	1
SampleProject 2	0
SampleProject 3	1

Development Cycle Based Security

If your data warehousing project passes through cycles of Development, QA, and Production phases, Warehouse Builder provides flexibility in determining your security policy, with regards to metadata definitions and changes during these different phases. For example, you have classified your users into one or more of the following groups: Engineering, Quality Assurance, and Sustaining Engineering. Depending on the various phases in the cycle, only certain actions are permitted to certain groups. Table 19-3 lists the permissible actions for each group.

Legend for Table 19-3: A = Administrators (the repository owner), E = Engineers, Q = Quality Assurance, S = Sustaining Engineers.

Table 19-3 Permissible Actions for Different Groups

Action	Development	QA	Production
Create	A,E	A,Q	A,S
Edit	A,E	A,Q	A,S
Delete	A,E	A,Q	A,S
Reference	A,E	A,Q	A,S
Validate	A,E	A,Q	A,S
Generate	A,E	A,Q	A,S
Version	A,E	A,Q	A,S
MDL Import	A,E	A,Q	A,S
Bridge Import	A,E	A,Q	A,S
Source Import	A,E	A,Q	A,S
Snapshot Restore	A,E	A,Q	A,S
Runtime Execute	A,E,Q,S	A,E,Q,S	A,E,Q,S
Deployment	A,E,Q,S	A,E,Q,S	A,E,Q,S
MDL Export	A,E,Q,S	A,E,Q,S	A,E,Q,S
Bridge Export	A,E,Q,S	A,E,Q,S	A,E,Q,S

For example, only Administrators and Sustaining Engineers are allowed to create, edit, or delete objects under a module that has been marked Production.

This security policy contains the following exceptions:

Only the administrators (repository owners) can invoke operations on objects that are at a higher level than modules. For example, projects and snapshots.
QA and Sustaining Engineering cannot create or delete modules.
Any user can perform operations on non-module dependent objects such as locations, collections, connectors, and business areas.
The global shared library and its transformation objects are accessible to all users.
Only a member of a group can change the module status to a different status. For example, only a QA member can change a module's development status from QA to something else. Only Engineers can change the module's status from Development to something else. This can be used to implement a disciplined bug-fixing policy within a development group.

The following files are used to implement this particular security policy: These files are located on your installation CD under: samples/security_feature/developcycle.

developCyclePolicyLoader.sql: Contains the information specified in Table 19-3.

developCyclePolicy.sql: Contains a table to track Users to Groups. For example, if your table is populated as shown in Table 19-4:

For Table 19-4, the repository owner must issue the following SQL statements to assign user groups using SQL Plus:

insert into user_group_assignment(userName,groupID) values('ALBERT1', 1);
insert into user_group_assignment(userName,groupID) values('ANDREW', 3);
insert into user_group_assignment(userName,groupID) values('BOB', 2);
insert into user_group_assignment(userName,groupID) values('CYNTHIA', 1);

Table 19-4 Sample Table Tracking Users to Groups

Username	OWBGroup
Albert	Engineer
Albert	Sustaining Engineer
Bob	Quality Assurance
Cynthia	Engineer
...	...

developCyclePolicy.pkb: Contains the main business logic to implement the development cycle based security policy.

In the implementation, the status flag is used to check whenever an object is manipulated. In Warehouse Builder, because the development status can be defined at the granularity of a module, the development status is only relevant for objects that are descendents of a module.

Warehouse Builder only provides security for objects in the repository if a user tries to import an MDL file or to restore a snapshot containing projects or modules that do not exist inside the repository. The sample implementation developCyclePolicy.pkb will not prevent the user from creating projects or modules and their children through an MDL import or snapshot restore. It is recommended that the repository owner perform the MDL import or snapshot restore on folder objects such as projects and modules.

Reactive Security and Audit-based Security

Reactive security pertains to the identification and tracking of potential security security breaches. Auditing is used for security, to track ownership, and to ensure quality. In Warehouse Builder, auditing is performed at an object level. Thus, auditing trails can be defined at the finest granularity. For example, who and when a particular column, constraint, or level attribute was created or updated. You can view this information using the Warehouse Builder Browser if the users are set up using the Warehouse Builder security architecture described above.

This audit information can also be used to enforce security policies. You can infer ownership of a particular object by the user that created it. Your security policy can consist of preventing anyone other than object's owner's group from performing any action on an object. For example, Albert from Marketing has created a new Promotions cube. He has added all the measures and dimensions to it. Bob, who is in Inventory has no permissions to modify, delete, generate, or deploy the Promotions cube. However, Cynthia, also from Marketing, has the same permissions as Albert and can make any modifications (including deleting) on the Promotions cube.

The following security implementation requires you to locate the object in the central repository to find out who created the object. The object creator is captured in the audit column createdBy, which is available for any object in Warehouse Builder.

You can locate the object within the Warehouse Builder Public Views system. If you want to write generic code for any type of Warehouse Builder object, then a join to the ALL_IV_FIRSTCLASS_OBJECTS view is recommended.

To locate an object within the public views, you need to join the argument object_UOID to the UOID exposed in the ALL_IV_FIRSTCLASS_OBJECTS view. The UOID stands for the Unique Object Identifier and enables you to uniquely identify an object within and across any repository.

Use the following policies for basic operations:

Object X can only be created if the container object's (parent object's) createdBy points to a user who belongs to the same division as the Object X's creator's division.
Object X can only be modified, deleted, validated, generated, or versioned if the createdBy points to a user who belongs to the same division as the Object X's creator's division.
Regular users cannot invoke service operations such as deploy, MDL import or export, Bridge import or export, source import, runtime execution, and snapshot restore. Although this can be relaxed by modifying the security package implementation, it is important to keep this restriction for imports and snapshot restore services. These operations can only be invoked by administrators.

This security policy requires you to define Administrators for each division. These Group Administrators are allowed to perform critical operations, such as MDL import, source import, and snapshot restore. These operations can also modify or delete objects that are not within a particular Administrator's group. Therefore, this is an important role. This restriction is imposed because Warehouse Builder does not perform security checks at the first class object level when service operations such as MDL import, source import, and snapshot restore are invoked. Administrators are defined according to the structure shown in Table 19-5.

Table 19-5 Administrator Roles

User	Group (Foreign Key)	IsAdmin
Albert	Marketing	0
Bob	Sales	0
Cynthia	Marketing	1
Derrick	Finance	0
Eeyore	Sales	1

The central Administrator (or repository owner) is allowed to perform any operation. Objects that the central Administrator creates do not belong to any group and can be shared across all groups. This is recommended if divisions need to share container objects such as projects or modules.

Rules for the Administrator role are listed below:

Division Administrators are responsible for performing service operations that modify a group of metadata objects (that may not necessarily be within the Division Administrator's division). After the objects are created using MDL import, then anyone in the Administrator's division can edit or delete the object.
The Division Administrator can also perform any operation normally restricted to the regular user, such as modifying or deleting objects from another division.
The Central Administrator is responsible for creating the container objects, such as projects and warehouse modules. Users from any division can create objects under this shared container.
The Central Administrator can also perform any operation normally restricted to the regular user, such as modifying or deleting objects from any division.

Two following files can be used to enforce this policy. These files are located on your installation CD under: samples/security_feature/creatorIsOwner.

creatorIsOwner.sql: Contains the tables illustrated in Table 19-5.

creatorIsOwner.pkb: Contains the security implementation that enforces this policy.

Data Stewardship

The creator of an object belongs to a pool of data stewards responsible for the quality of metadata in a specific subject area. For example, subject areas such as Marketing and Inventory, Sales, Budget, Production, Finance, etc. In Warehouse Builder, these subject areas are captured using the Warehouse Builder collection object. The collection objects consist of shortcuts to actual objects in Warehouse Builder, such as modules, tables, facts, mappings, process flows, dimensions, and files, that appear in the Warehouse Builder navigation tree. These collection objects enable you to classify Warehouse Builder objects according to subject areas. A collection does not imply ownership. Certain pieces of metadata can belong to more than one collection. For example, the metadata for the Customers dimension may be shared by data stewards in the Marketing, Sales, and Order Fulfillment divisions.

When there are multiple owners for certain types of metadata, you cannot implement the Creator Is Owner policy implementation described in the previous section. Many data stewards can have security privileges on the same metadata object. Also, the creator of an object's metadata may not necessarily be the assigned data steward for that metadata. For example, if the Warehouse Builder Administrator imports a number of tables from a schema.

The Warehouse Builder collection object is the pivotal element in the data stewardship security policy. Access to data stewardship must be restricted to prevent users from adding new objects to that list. In the above example, only the Warehouse Builder Administrator is allowed to add to the collections lists. Every time Developers create an object, they need to inform the Administrator to add it to the collections list. A condition must be added to the security policy to allow the creator of the object to modify that object. Therefore, there is only one Administrator in this policy, rather than one administrator per division.

To understand this policy, you need to understand the behavior of the shortcuts defined under a collection. There are two types of shortcuts defined in a collection. An explicit shortcut is one that the user has explicitly created and an implicit shortcut is one that is created by Warehouse Builder when a user creates a child shortcut without the parent. For example, when you add a table to a collection, Warehouse Builder automatically adds all the parent objects of this table in the collection. The table here is an explicit shortcut and its parent folder, such as the Oracle Module, is an implicit shortcut. Implicit shortcuts are deleted when the last child is deleted. For example, if a user deletes the last shortcut to a table and if the shortcut to its parent module was implicit, the module shortcut is also deleted.

Users and their corresponding division collections must be set up according to the structure described in Table 19-6.

Table 19-6 Users and Corresponding Division Collections

Collection ID (UK)	Collection Name	Project Name	Coll ID (FK)	User Name
1	Sales Collection	My Project	1	Arthur
2	Finance Collection	My Project	2	Billy
3	Marketing Collection	My Project	3	Caroline
4	AR Collection	My Project	4	David
5	AP Collection	My Project	3	Edward
6	Inventory Collection	My Project	4	Frederick
7	Manufacturing Collection	My Project	2	George
8	HR Collection	My Project	3	George

In Table 19-6, user George belongs to more than one division, and therefore has access to objects in both collections. The collections must match the collection name and id as defined in Warehouse Builder. By using the above table, you can limit the number of collections that can be used for administrative security purposes. Warehouse Builder can define other collections that have nothing to do with the security infrastructure.

This security policy is described as follows:

When a user creates an object, only that user and the Central Administrator can modify, delete, validate, or generate that object until that object is registered in a collection.
Only the Central Administrator has the privilege to modify collections.
Only the Central Administrator is allowed to perform service tasks such as deploy, MDL import/export, Bridge import/export, source import, runtime execution and snapshot restore.
Users can only edit, delete, validate, or generate an object that they have not created if that object is explicitly registered under a collection accessible to that user. An explicit shortcut must be used in the collection to prevent users from deleting a module that is registered through an implicit shortcut.
A user can create an object within a container that has not been explicitly registered in a collection. After registration, a user can only create objects under this container if the user has permissions on the collection that owns or references the container object. For example, if a user has an implicit shortcut to Module X (because of several tables under Module X), the user will have no privilege to create objects under Module X. However, if the user has an explicit shortcut to the module, then the user is allowed to create a child object within that module.

The following two files implement this security policy. These files are located on your installation CD under: samples/security_feature/stewardship.

stewardship.sql: Contains the DDL to create tables with the same structure as shown in Table 19-6.

stewardship.plb: The security package implementation.

These are sample implementations of the security interface. They can be modified or combined to provide a more sophisticated security policy for your organization. Use these implementations as templates to implement your own security policies.

19 Extending Warehouse Builder Functionality

About Oracle Metabase (OMB) Plus

User-Defined Properties

Managing User Defined Properties

OMBDEFINE

OMBDESCRIBE

Viewing User Defined Properties

Warehouse Builder Client

Figure 19-1 Sample Properties Sheet with User-Defined Properties

Warehouse Builder Design Browser

Figure 19-2 Sample Properties Sheet with User-Defined Properties

Transferring UPDs to Other Repositories

Exporting UDPs

Managing Security with PL/SQL

Maintaining Repository Users

Plug-in Interface for the Security PL/SQL Package Specification

Definitions of the Constants in the Package Specification

Implementing the PL/SQL Interface

Considerations

Implementing Sample Security Policies

The Warehouse Builder Security Architecture

Figure 19-3 Warehouse Builder Server-Side Security Architecture

Using a Customizable Security Authorization Framework

Table 19-1 Permissions for User Albert

Freezing Projects

Table 19-2 Example File Structure for frozenProject.sql

Development Cycle Based Security

Table 19-3 Permissible Actions for Different Groups

Table 19-4 Sample Table Tracking Users to Groups

Reactive Security and Audit-based Security

Table 19-5 Administrator Roles

Data Stewardship

Table 19-6 Users and Corresponding Division Collections

19
Extending Warehouse Builder Functionality