Skip Headers
Oracle® Warehouse Builder Installation and Administration Guide,
10g Release 2 (10.2.0.2) for Windows and UNIX

Part Number B28224-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

7 Implementing Security in Warehouse Builder

This chapter includes:

About Metadata Security in Warehouse Builder

Warehouse Builder enables you to define security on the metadata you store in the design repository. The design repository is an Oracle Database with users, roles, and access privileges already defined. Warehouse Builder metadata security operates in addition to the Oracle Database security. The Oracle Database provides security for data while Warehouse Builder provides security for the metadata.

In addition to being registered in the Warehouse Builder repository, all Warehouse Builder users must also be database users on the design repository database. Database users have access to the data in the database through SQL Plus but do not have access to Warehouse Builder and its metadata unless also registered in Warehouse Builder.

Metadata security is optional and flexible. Beginning with Oracle Warehouse Builder 10g, you can continue to apply no metadata security controls or define a metadata security policy. You can define multiple users and apply full security. Or implement your own security strategy based on the Warehouse Builder security interface. Also, after you define a security strategy, you can later adapt the strategy to be more or less restrictive.

The following sections describe how to implement metadata security using the Warehouse Builder Design Center. You can also implement security through OMB Plus. For more information, refer to the Oracle Warehouse Builder Scripting Reference.

About the Security Interface

Only users with administrative privileges can access the security interface and change security policy in Warehouse Builder.

When you install Warehouse Builder and then use the Repository Assistant to create a design repository, Warehouse Builder assigns the design repository owner you define to be the default administrator. The first time you launch the Design Center after installation, you must log in as the design repository owner. You can then define additional administrators or other users as necessary.

Log in to the Warehouse Builder Design Center as the design repository owner and Warehouse Builder displays the Global Explorer as shown in Figure 7-1, "Global Explorer" in the lower right corner of the Design Center.

Figure 7-1 Global Explorer

This illustration is described in the surrounding text.
Description of "Figure 7-1 Global Explorer"

Under the security node, notice there are two predefined roles, ADMINISTRATOR and EVERYONE. The one predefined user is the design repository owner, REPOS_OWNER in this example, which is assigned the ADMINISTRATOR role by default.

To perform actions under the Security node, select an object and right-click to view all possible operations. Or select an object and select Edit from the menu bar. For a complete list of all the tasks administrators can perform, see "Administrator Role".

Metadata Security Strategies

Warehouse Builder enables you to design a metadata security strategy that fits your implementation requirements. As you define you metadata security strategy, recognize that more restrictive policies are more time consuming to implement and maintain.

Consider modeling your strategy on one of the following security strategies:

Minimal Metadata Security Strategy (Default)

Minimal metadata security is the default security policy when you create a new design repository. As your project requirements change, you can apply other metadata security strategies at any time.

You may not want or need to apply extra metadata security if, for instance, you are implementing a pilot project or anticipate only one or a few users accessing Warehouse Builder.

All users log in to Warehouse Builder with the same user name and password- that of the design repository owner. In this case, Oracle Database security policies keep the data in the design repository secure and the metadata is available to anyone who knows the design repository owner logon information. All users can create, edit, and delete all objects and you cannot discern which user performed which operation.

Multi-user Security Strategy

Use this strategy if your implementation has multiple users and you want to track who performs which operations. Also, use this strategy to restrict to a single user the rights and access granted to the design repository owner. Although this strategy does not restrict user access to metadata objects, you can apply restrictions at a later date.

To implement security for multiple users, log on to Warehouse Builder as an administrator and complete the instructions in the following sections:

  1. Registering Database Users as Warehouse Builder Users

  2. Editing User Profiles

Full Metadata Security Strategy

This section describes a process for applying all the metadata security options available in Warehouse Builder. You can enable all or some of these options. For instance, you could take steps one through three but ignore the remaining steps.

To implement full metadata security for multiple users, log on to Warehouse Builder as an administrator and complete the instructions in the following sections:

  1. Set the parameter Default Metadata Security Policy to maximum.

    In the Design Center select Tools, Preferences, and then Security Parameters.

  2. Registering Database Users as Warehouse Builder Users

  3. Editing User Profiles

    Important:

    The Default Metadata Security Policy you set in step one of these instructions is not retroactive. It applies only to users you register after changing the setting. You must manually edit the profiles of preexisting users.
  4. Defining Security Roles

  5. Editing User Profiles

  6. Applying Security Properties on Specific Metadata Objects

    Important:

    Be sure to edit the security properties for all projects in the Project Explorer. By default, the EVERYONE role has its object privileges set to full control. Select each project, press <F2>, select the Security tab, and edit the privileges to the EVERYONE role to be more restrictive.

Registering Database Users as Warehouse Builder Users

You can use a wizard to register users in Warehouse Builder. All Warehouse Builder users must also be Oracle Database users. You can use the wizard to either register existing database users or create new database users and then register them in Warehouse Builder.

To launch the registration wizard, go to the Security node in the Global Explorer, right-click Users and select New. Follow the prompts in the wizard to complete the following steps:

  1. Selecting Existing or Creating New Database Users

  2. User Schema as Target Option

Selecting Existing or Creating New Database Users

The left panel in Figure 7-2 lists the Oracle Database users defined for the design repository. Either select existing database users from the list or define and register a new user by clicking on Create DB User... located in the lower left corner.

When selecting from the list, you can select one or more database users. Notice that, for security reasons, database administrator users such as SYS are not available for registering as Warehouse Builder users. The database default role settings must not be set to ALL. You can change the database default role settings from within Warehouse Builder as described in Changing Database Default Roles.

Figure 7-2 Creating and Registering Users

This illustration is described in the surrounding text.
Description of "Figure 7-2 Creating and Registering Users"

Creating an Oracle Database User in Warehouse Builder

If you have database system privilege CREATE USER, then you can create new database users. The Create Database User dialog prompts you to type a user name and password for the new user and assign the default table space and temporary table space.

To specify a valid user name and password, adhere to the security standard implemented on the Oracle Database. The default minimum requirement is that both the user name and password be a VARCHAR(30). Also, do not include any special characters. Your database may have more requirements if a password complexity verification routine was applied.

For more information about user names, passwords, and password complexity verification routines, refer to the Oracle® Database Security Guide.

Changing Database Default Roles

For security reasons, Warehouse Builder does not allow you to register database users with default roles in the database set to ALL. If you attempt to do so, Warehouse Builder gives you options for changing the default setting. You can instruct Warehouse Builder to correct the role assignment by selecting Fix Now. Or you can correct the role assignment yourself by selecting Fix Later.

Fix Now

If you select Fix Now, type the user name and password with SYSDBA privileges. Warehouse Builder registers the user and issues the necessary commands to the database. For example, when you register new users, Warehouse Builder assigns a database role OWB_<repository name> to each user. For security reasons, this role must not be default role of any registered user. If you attempt to register a user U1 under these conditions and then select Fix Now, Warehouse Builder registers the new user and issues a command such as alter user U1 default role all except OWB_<repository name>.

Fix Later

If you select Fix Later, Warehouse Builder does not register the user. You must manually change the default role setting in the database and then return to Warehouse Builder to register the user. To manually change the setting, connect to the database as a user with the ALTER USER system privilege and issue the required commands.

Under the Fix Later option, Warehouse Builder provides you with a recommended SQL script for changing the default roles for the selected users. The script also changes the default role setting such that any role subsequently granted to the user cannot be the default role of the user. To change this, you can register the user and then issue a command such as the following:

ALTER USER U1 DEFAULT ROLE all EXCEPT owb_<repository name>

User Schema as Target Option

You can designate a user schema as a target to enable deployment to that schema. When you check mark User a Target Schema? and select Next, Warehouse Builder prompts you to enter the password for the user.

Warehouse Builder saves to the schema certain required synonyms. To enable deployment, Warehouse Builder also defines an Oracle location for the user schema, <USER_SCHEMA_NAME>_LOCATION in the Connection Explorer.

Editing User Profiles

For each Warehouse Builder user, you can enter an optional description, assign the user to existing Roles, specify the Default Object Privilege and the System Privileges, and set the User Schema as Target Option.

Since Warehouse Builder users are also defined as Oracle Database users, you cannot rename a user from within Warehouse Builder. Rename Warehouse Builder users through the Oracle Database.

Roles

You can assign a user to one or more roles. If you assign multiple roles with conflicting privileges, Warehouse Builder grants the user the more permissive privilege, which is the union of all the privileges granted to the multiple roles. For example, if you assign to the same user a role that allows creating a snapshot and a role that restricts it, Warehouse Builder allows the user to create snapshots.

If you want to assign a user to a role that does not display on the Available Roles List, close the editor, create the new role, and then edit the user account. To create a new role, right-click Roles under the Security node in the Global Explorer and select New. For information on creating and editing roles, see Defining Security Roles and Editing Role Profiles.

Default Object Privilege

Default object privileges define the access other users and roles have to objects the selected user creates. These privileges do not impact the privileges the user has for accessing objects that others create.

For example, Figure 7-3, "Default Object Privilege Settings for USER1" shows that for all objects that USER1 creates, USER1 and the ADMINISTRATOR and DEVELOPMENT roles have full access while the EVERYONE, PRODUCTION, and QA roles are restricted to read only.

If you are familiar with UNIX operating system security, note that the default object privilege behaves similarly to the UNMASK command. When you edit the default object privilege, the change only affects objects the user subsequently creates. There is no affect on previously created objects. Therefore, if you set default object privileges at the onset of your Warehouse Builder implementation, little or no additional object-level security setup is necessary.

To define the privileges other users have to objects the selected user creates, check the appropriate box for each role or user. You can grant the following privileges: FULL CONTROL, EDIT, COMPILE, and READ. All the privileges are additive. If you select COMPILE, then you apply both the compile and read privileges.

Figure 7-3 Default Object Privilege Settings for USER1

This illustration is described in the surrounding text.
Description of "Figure 7-3 Default Object Privilege Settings for USER1"

Figure 7-3, "Default Object Privilege Settings for USER1" shows access granted to roles. You can also grant access to individual users. However, when you grant access to a role, the privilege is also extended to all users in that role. Therefore, in Figure 7-3, even though USER2 is not specifically granted access, USER2 has read access through the EVERYONE role. Furthermore, if USER2 is a member of the DEVELOPMENT role, that user has full control and access.

Important:

By default, when you create a new user, the EVERYONE role has full control on all objects. To enable metadata security, be sure to edit all user profiles and restrict the access the EVERYONE role has to objects each user creates.

Securing a Metadata Object Throughout its Lifecycle

Default object privileges work in conjunction with object security properties to provide security options throughout the lifecycle of a given metadata object. Settings you specify on the Default Object Privilege tab persist until a qualified user overrides the restrictions on an object by object basis.

Assume that USER1 creates several mappings. When USER1 designs and develops those objects, the security policy shown in Figure 7-3 may be desirable. However, assume that USER1 completes the work on mappings and wants to release the objects to the quality assurance team for testing. The default object privilege therefore becomes too restrictive. To extend access to the QA role, USER1 can navigate to the mapping, right click, select Properties, and select the Security tab. For more details on overriding the default security on an object by object basis, see "Applying Security Properties on Specific Metadata Objects".

Object Privileges

Object privileges apply to all metadata objects in the repository including projects, modules, and collections.

FULL CONTROL

Full control includes all the other privileges plus the ability to grant and revoke privileges on an object. Only users with full control over an object can override default security on an object-by-object basis as described in "Applying Security Properties on Specific Metadata Objects".

EDIT

The edit privilege includes the compile, and read privileges. Additionally, edit allows users to delete, rename, and modify an object.

COMPILE

The compile privilege includes the read privilege and enables validation and generation of an object.

READ

The read privilege enables viewing of an object.

System Privileges

System privileges define user access to repository-wide services. Use the System Privilege tab to allow or restrict users and roles from performing administrative tasks in Warehouse Builder. You can control access to the following operations:

  • CREATE_SNAPSHOT: Allows users to create snapshots which administrators use when backing up Warehouse Builder repositories.

  • CREATE_EXTENSIONMODEL: Allows users to create new object types in the repository.

  • CREATE_MIVDEFINITION: MIV Definitions are metadata objects that enable access to data stored in third party applications.

  • CREATE_PROJECT: Allows users to create projects, which administrators create projects as a means of organizing metadata objects.

  • CONTROL_CENTER_ADMIN: Allows users to perform administrative functions in the Control Center and also deploy to and execute from the Control Center. For security reasons, you can enable this privilege only on a user by user basis; that is, you cannot extend this privilege to roles.

  • CONTROL_CENTER_DEPLOY: Allows users to deploy to the Control Center and then execute those procedures. For security reasons, you can enable this privilege only on a user by user basis; that is, you cannot extend this privilege to roles.

  • CONTROL_CENTER_EXECUTE: Allows users to execute procedures from the Control Center. For security reasons, you can enable this privilege only on a user by user basis; that is, you cannot extend this privilege to roles.

Defining Security Roles

You can use roles to represent groups of users with similar responsibilities and privileges. Unlike Warehouse Builder users which are also database users, Warehouse Builder roles are not database roles. These are purely Warehouse Builder design security constructs.

Roles enable you to more efficiently manage privileges since it is more efficient to grant or restrict privileges to a single role rather than multiple users.

Warehouse Builder provides the Everyone Role and the Administrator Role as predefined roles. You edit the privileges but cannot delete or rename the predefined roles.

Everyone Role

Use this role to easily manage privileges for all users. When you register new users, Warehouse Builder assigns those users to the Everyone role.

Administrator Role

Administrators in Warehouse Builder can perform the security tasks described in Table 7-1.

Table 7-1 Administrator Security Tasks

Task Instructions

Registering Database Users as Warehouse Builder Users

From the Global Explorer, right-click Users and select New.

Editing User Profiles

When you register a user, Warehouse Builder assigns the user to the everyone role and grants access to metadata objects based on that role. To change a user profile, right click the user and select Open Editor.

Changing User Passwords

You cannot change user passwords from within Warehouse Builder. Change passwords directly in the Oracle Database as described in the Oracle® Database Security Guide.

Defining Security Roles

Warehouse Builder provides two roles, the administrator role and the everyone role. Define additional roles by right-clicking on Roles in the Global Explorer and selecting New.

Editing Role Profiles

Right-click a role and select Open Editor. You can add and remove users and change the system privileges for the role.

Deleting Users and Roles

From the Global Explorer, right-click a user or a role and select Delete.

You can delete all Warehouse Builder users expect for the design repository owner. Deleting the user from Warehouse Builder does not delete or alter the user account on the Oracle Database.

You can delete all roles expect the predefined ADMINISTRATOR and EVERYONE roles. Warehouse Builder roles and database roles are separate constructs. Therefore deleting a Warehouse Builder has no effect on the database.

Renaming Roles

From the Global Explorer, right-click a role and select Rename. You can rename all roles expect the predefined administrator and everyone roles.

Applying Security Properties on Specific Metadata Objects

Right-click any metadata object in any of the three explorers on the Design Center, select Properties, and select the Security Tab. Or select any metadata object, press <F2>, and then select the Security tab.


Editing Role Profiles

For each Warehouse Builder role that you create, you can edit the name, enter an optional description, assign the role to existing Users, and specify the system privilege. System privileges for roles behave the same as they do for users. For more information on system privilege, see System Privileges.

You cannot rename or edit the descriptions for the predefined roles Everyone and Administrator.

Users

You can assign multiple users to a role. If you want to assign a user that does not display on the Available Users list, close the editor, create the user from the Security node in the Global Explorer, and then edit the role. To create a new user, right-click Users from the Security node and select New. For information on creating and editing users, see Registering Database Users as Warehouse Builder Users and Editing User Profiles.

Applying Security Properties on Specific Metadata Objects

You can grant or restrict access to metadata objects on an object-by-object basis.

View the Security Tab by right-clicking on any metadata object and selecting Properties.

Security Tab

Use the Security tab to define metadata security on an object-by-object basis. Only users that have full control privileges on an object can change the metadata access controls on the Security tab. Security properties are important in managing the lifecycle of your projects, as described in "Example: Using Security Properties to Freeze a Project Design".

While the Default Object Privilege defines metadata security for objects a specific user creates, the Security tab overrides that metadata security policy on an object-by-object basis. Assume that USER1 is a developer that creates mappings and process flows. If you want all objects created by USER1 available to another developer, use the Default Object Privilege. However, if you want to make only a few objects created by USER1 available to the QA group, locate each object in the Design Center and use the Security tab.

Important:

To enforce a full metadata strategy, edit the security properties for all projects in the Project Explorer. By default, the EVERYONE role has its object privileges set to full control. Change the EVERYONE role privilege to be more restrictive and select Propagate to apply the changes to all children.

Propagating Security Properties to Dependent Objects

You can apply security properties to an object and all its children by selecting Propagate on the Security tab. This option is disabled when you select an object that cannot have dependent objects.

Example: Using Security Properties to Freeze a Project Design

When Warehouse Builder users complete the design of a project, you may want to freeze the contents of the project. Once you complete the following steps, only administrators can change the objects in the project.

To freeze a project design:

  1. Log on as an administrator.

  2. In the Project Explorer, right-click the project node and select Properties.

  3. On the Security tab, restrict the privileges for all user and roles other than the administrators as appropriate.

  4. Click the Propagate button.

Security Enforcement

When any user attempts to perform an operation in Warehouse Builder, Warehouse Builder first verifies that the user has the required privileges to perform the operation. Table 7-2 lists the privileges required to execute operations in Warehouse Builder.

Table 7-2 Privileges Required to Execute Operations

Warehouse Builder Operation Security Check

Configure

User must have EDIT privilege on objects to be configured.

Copy

User must have READ privilege on the object to be copied.

Create object

User must have EDIT privilege on parent. For example, to create a mapping you must have Edit privilege on the module.

Cut

User must have EDIT privilege on the object to be cut.

Delete

User must have EDIT privilege on the object to be deleted.

Deploy

No security check is necessary on the Deploy operation because Warehouse Builder checks the previous Generate operation.

Edit

User must have EDIT privilege on the object to be edited.

Export

User must have READ privilege on objects to be exported. Administrative users can export security information such as roles, users, and privileges when Export security information is enabled.

Generate

User must have COMPILE privilege on object to be generated.

Import

User must have EDIT privilege on objects to be exported. Administrative users can import security information such as roles, users, and privileges when Import security information is enabled.

Move

User must have privileges listed for the Cut and Paste operations.

Paste

User must have EDIT privilege on the parent to receive the copied object.

Rename

User must have EDIT privilege on the object to be renamed.

Snapshot: compare snapshots

To compare with another snapshot or other repository object, user must have READ privilege on that snapshot and the snapshot or other repository object.

Snapshot: restore snapshot

To restore an object based on a snapshot, a user must have READ privilege on that object. To restore a folder, a user must have EDIT privilege on the folder and all of its children.

Snapshot: take snapshot

User must have the CREATE_SNAPSHOT system privilege to create snapshots.

Source import

User must have EDIT privilege on objects to be replaced by imported objects.

Synchronize inbound

User must have READ privilege on the object in the repository and EDIT privilege on the object in the editor.

Synchronize outbound

User must have EDIT privilege on the object in the repository.

Validate

User must have COMPILE privilege on object to be validated.


Managing Passwords in Warehouse Builder

Password security in Warehouse Builder relies on the Oracle Advanced Security Option (ASO) available in the Oracle Database Enterprise Edition only. With ASO enabled, you can manage passwords within Warehouse Builder in the following ways:

If the Warehouse Builder repository resides on an Oracle 10g Standard Edition Database, be aware of differences regarding password encryption as described in Limited Password Security in Oracle Database Standard Edition.

Changing Passwords

In keeping with standard security practices, you may want to periodically change the passwords used to access Warehouse Builder repositories.

Changing Passwords that Access Design Repositories

Manage the password to design repositories as you would any other Oracle Database.

Changing Passwords that Access Control Centers

To change the password for a repository that hosts a Control Center and is therefore a deployment environment, you must first stop the Control Center service, run a script to change the password, and restart the Control Center service.

To change the password for a repository that hosts a Control Center:

  1. Log on to the Control Center as the repository owner.

  2. Stop the Control Center Service by running the script <owb home>/owb/rtp/sql/stop_service.sql.

    The script returns values of Unavailable or Available to indicate the status of Control Center.

  3. Change the password by running the script <owb home>/owb/rtp/sql/set_repository_password.sql.

    When prompted, specify the new password.

  4. Restart the Control Center by running the script <owb home>/owb/rtp/sql/start_service.sql.

Encrypting Passwords to Warehouse Builder Locations

Warehouse Builder users create a location for each database, file server, or application that want to extract or load metadata and data. Locations include the user name and password used to access these various sources and targets. Assuming that Oracle Advanced Security is enabled on the repository database, Warehouse Builder stores these passwords in the repository in an encrypted manner. The switch that turns on and off the password storage is Persist Location Password in Metadata, which is located in the Design Center under Tools, Preferences, Security Parameters.

The default encryption algorithm utilized is DES56C that is valid for Oracle Database 9i and subsequent versions. If the repository database is version10g, you can set the encryption algorithm to 3DES168 or any other more powerful encryption by changing <OWB_ORACLE_HOME>/bin/admin/jdbcdriver.properties file and specifying the following encryption parameters:

encryption_client; default = REQUIRED

encryption_types_client; default = ( DES56C )

crypto_checksum_client; default = REQUESTED

crypto_checksum_types_client; default = ( MD5 )

The server should at least be set to "ACCEPTED" (which is default) mode for the protocol to work. For more information, see the Oracle® Database JDBC Developer's Guide and Reference.

Limited Password Security in Oracle Database Standard Edition

The Oracle Advanced Security Option (ASO) provides maximum security of passwords by encrypting passwords when they are sent over the network. However, the ASO option is only available with the Enterprise Edition. As a result, when using an Oracle Database Standard Edition to host the Warehouse Builder repository, the following circumstances apply:In the Design Center, passwords are not persisted across sessions. Passwords are not persisted regardless of the setting of the Persist Password Location in Metadata user preference to True.

When registering locations in the Control Center, the password is sent over the network unencrypted, but stored securely and encrypted. When you execute a job such as a mapping, the Control Center typically reads a password from the runtime repository to connect to the target system. This password is also sent over the network unencrypted.For secure password transmission, you should upgrade to Oracle Database Enterprise Edition.

Support for a Multiple-user Environment

Warehouse Builder enables multiple users to access the same Warehouse Builder repository at the same time by managing read/write privileges. Only one user is given write privileges to an object at any given time. All other users can have read-only access. If a user has write access to an object, Warehouse Builder maintains a lock on the object while the object editor is open. If no changes were made to the object, the lock is released as soon as the object editor is closed. If changes were made, the lock is maintained until the user closes all editors associated with the object and either saves the changes or reverts to the last saved version. Other users cannot delete an object while it is in use.

Read/Write Mode

Whenever you open an editor, property sheet, or dialog box, you access objects in read/write mode by default. Your changes are available to other users only after you save them to the repository.

Read-Only Mode

If you attempt to open an object locked by another user, Warehouse Builder displays a message that prompts you either to cancel the request or access the object in read-only mode. If you choose to continue in read-only mode, the editor displays Read only in the title bar.

The user who is editing an object in read/write mode may save his or her changes while you have the object open in read-only mode. To update your view with the repository, click the Refresh button on the toolbar.