Skip Headers
Oracle® Warehouse Builder Installation and Administration Guide
11g Release 1 (11.1) for Windows and UNIX

B31280-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

7 Implementing Security in Warehouse Builder

This chapter includes:

About Metadata Security

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 repository, all users must also be database users in the design repository database. Database users have access to the data in the database by using SQL Plus. However, they do not have access to Warehouse Builder and its metadata unless the users are also registered in Warehouse Builder.

Metadata security is optional and flexible. You can 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 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 Design Center.

You can also implement security through OMB Plus. For more information, refer to the Oracle Warehouse Builder API and 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 start 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 of the 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 your metadata security strategy, recognize that more restrictive policies are more time consuming to implement and maintain.

Consider modeling your strategy based 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.

Multiuser 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

  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

  3. Editing User Profiles

    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 Note: 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

All Warehouse Builder users must also be Oracle Database users.

Use a wizard within Warehouse Builder to either register existing database users or create new database users and then register them in Warehouse Builder.

Note: Although, you are able to create users in SQL Plus, it is not advisable. Creating users through the Warehouse Builder interface ensures that users are assigned the necessary roles and privileges.

To start the registration wizard, go to the Security node in the Global Explorer, right-click Users and select New. Follow the prompts in the wizard as described in "Selecting Existing or Creating New Database Users".

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, you cannot register database administrator users such as SYS. The database default role settings must not be set to ALL. You can change the database default role settings from within the wizard 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

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 Oracle Database Security Guide.

Changing Database Default Roles

For security reasons, you cannot register database users that have default roles in the database set to ALL. You can, however, change the default setting. 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. The product registers the user and issues the necessary commands to the database. For example, when you register new users, the database role OWB_repository name is assigned to each user. For security reasons, this role must not be the default role of any registered user. If you attempt to register a user U1 under these conditions and then select Fix Now, then the product registers the new user and issues a command such as the following:

alter user U1 default role all except OWB_repository_name

Fix Later

If you select Fix Later, then the product does not register the user. You must manually change the default role setting in the database and then return to the product 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, notice 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

Editing User Profiles

For each user, you can enter an optional description, assign the user to existing Roles, specify the Default Object Privilege and the System Privileges.

Because these users are also defined as Oracle Database users, you cannot rename a user from within this product. Rename users through the Oracle Database.

Roles

You can assign a user to one or more roles. If you assign multiple roles with conflicting privileges, then the user is granted 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, then the user is allowed 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, then note that the default object privilege behaves similarly to the UMASK command. When you edit the default object privilege, the change only effects objects the user subsequently creates. There is no effect on previously created objects. Therefore, if you set default object privileges at the onset, then 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, then 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 you to validate and generate an object.

READ

The read privilege enables you to view an object.

System Privileges

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

  • ACCESS_PUBLICVIEW_BROWSER: Allows users to access the Repository Browser.

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

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

  • CREATE_SNAPSHOT: Allows users to create snapshots which administrators use when backing up workspaces.

  • CONTROL_CENTER_DEPLOYMENT: Allows users to deploy to the Control Center and then run 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_EXECUTION: Allows users to run 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.

  • CONTROL_CENTER_VIEW: Allows users to view 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.

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, then the lock is released as soon as the object editor is closed. If changes were made, then 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, then 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, then 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.

Defining Security Roles

You can use roles to represent groups of users with similar responsibilities and privileges. Unlike users which are also database users, these roles are not database roles. These roles are purely design constructs for implementing security within the product.

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

The Everyone Role and the Administrator Role are 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

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 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 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, then 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 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, then use the Default Object Privilege. However, if you want to make only a few objects created by USER1 available to the QA group, then 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 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 run operations in Warehouse Builder.

Table 7-2 Privileges Required for the Execution of 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

You can manage passwords within Warehouse Builder in the following ways:

Changing Passwords that Access Warehouse Builder

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 by running the script

    OWB_ORACLE_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_ORACLE_HOME/owb/rtp/sql/set_repository_password.sql

    When prompted, specify the new password.

  4. Restart the Control Center by running the script

    OWB_ORACLE_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. Warehouse Builder can store 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 version 10g or later, then you can set the encryption algorithm to 3DES168 or any other more powerful encryption by changing OWB_ORACLE_HOME/owb/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 )

For the protocol to work, set the server to the default ACCEPTED mode. For more information, see the Oracle® Database JDBC Developer's Guide and Reference.