13 Managing Security

About the Security Service

Only users with administrative privileges can access the security service under Globals Navigator to manage users and roles of the security policy in Oracle Warehouse Builder API and Scripting Reference.

When you install Oracle Warehouse Builder and then use the Repository Assistant to create a design repository, Oracle Warehouse Builder makes the design repository owner 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.

When you log in to the Oracle Warehouse Builder Design Center as the design repository owner, it displays the Globals Navigator.

To view default security settings:

1. In Globals Navigator, expand Security.

2. Expand Users, and then expand Roles.

   
   Description of the illustration security_01.gif
   
   

3. Notice that there are two predefined roles, ADMINISTRATOR and EVERYONE.

4. The one predefined user is the design repository owner; it is assigned the ADMINISTRATOR role by default.

   To view or edit the details for a user, in the globals Navigator, under Security and then under Users, select that and double-click the user. The Edit User screen appears.

   
   Description of the illustration security_02.gif
   
   

For a complete list of all the tasks administrators can perform, see "Administrator Role".

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 over time, you may apply other metadata security strategies. For example, you may not need extra metadata security if you are implementing an internal pilot project, or if you anticipate only few trusted users.

In the case of a minimal metadata security strategy, all users may log in to Oracle Warehouse Builder with the same user name and password, that of the design repository owner. 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.

Multiuser Security Strategy

If your implementation has multiple users and you want to track who performs what operations, implement a multiuser security strategy. This strategy restricts 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 Oracle 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 Oracle 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.

Be sure to edit the security properties for all projects in the Project Navigator. By default, the EVERYONE role has FULL_CONTROL object privileges. To change, select the project and then, from the View menu, select Security. Edit the privileges to the EVERYONE role to be more restrictive, and then press Propagate Security Settings icon on the upper left corner. This action applies the new restrictions to all children of this project. For newly created projects and other objects, use the default object privilege setting of Oracle Warehouse Builder API and Scripting Reference users to define access privileges.

To implement full metadata security for multiple users:

Log in to Oracle 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, expand Oracle Warehouse Builder, and then select 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"

Registering Existing Database Users

This section explains how to register existing database users.

To register existing database users:

1. In the Globals Navigator, under Security, right-click Users and select New User.

   
   Description of the illustration new_user_01.gif
   
   

   The Create User: Welcome screen appears.

2. On the Create User Welcome screen, click Next.

   
   Description of the illustration new_user_02.gif
   
   

3. On the Select DB user to register screen, under Available DB Users, select the user or users you want to register, and click the appropriate transfer icon to add the user or users to the Selected Users list.

   Click Next.

   
   Description of the illustration new_user_03.gif
   
   

4. On the Check to create a location screen, check Create option next to the user you are registering.

   Click Next.

   
   Description of the illustration new_user_04.gif
   
   

5. On the Summary screen, review the new user definition.

   Click Finish.

   
   Description of the illustration new_user_05.gif
   
   

6. The Register Users Progress appears. It disappears when registration is complete.

   
   Description of the illustration new_user_06.gif
   
   

7. Your new user is now listed under Users.

   
   Description of the illustration new_user_07.gif
   
   

Creating New Oracle Database Users

This section explains how to create new database users. You must have the database system privilege CREATE USER.

To create a database user:

1. In the Globals Navigator, under Security, right-click Users and select New User.

   
   Description of the illustration new_user_01.gif
   
   

   The Create User: Welcome screen appears.

2. On the Create User Welcome screen, click Next.

   
   Description of the illustration new_user_02.gif
   
   

3. On the Select DB user to register screen, click Create DB User.

   
   Description of the illustration new_user_08.gif
   
   

4. On the Create Database User screen, enter the DBA password, and the Name and Password (with confirmation) of the new user.

   Click OK.

   You must specify a valid user name and password, and adhere to the security standard implemented on the Oracle Database. For more information about user names, passwords, and password complexity verification routines, refer to Oracle Database Security Guide.

   
   Description of the illustration new_user_09.gif
   
   

5. On the Select DB user to register screen, the new user is automatically added to the Selected Users list.

   Click Next.

   
   Description of the illustration new_user_10.gif
   
   

6. On the Check to create a location screen, check Create option next to the user you are registering.

   Click Next.

   
   Description of the illustration new_user_11.gif
   
   

7. On the Summary screen, review the new user definition.

   Click Finish.

   
   Description of the illustration new_user_12.gif
   
   

8. The Register Users Progress appears. It disappears when registration is complete.

   
   Description of the illustration new_user_06.gif
   
   

9. Your new user is now listed under Users.

   
   Description of the illustration new_user_13.gif
   
   

Changing Database Default Roles

For security reasons, you cannot register database users that have ALL default roles in the database. However, it is possible to change, default setting by correcting the role assignment. There are two options: "Fix Now" and "Fix Later".

Fix Now

If you select the Fix Now option, type the user name and password with SYSDBA privileges. The user is registered, and the necessary commands are issued.

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 under these conditions and then select Fix Now, the user is registered and the following command is issued:

alter user username default role all except OWB$CLIENT

Fix Later

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

Note the following SQL script for changing the default roles of selected users. It changes the default role setting so that any role subsequently granted to the user cannot be the default role of that user. To change this, register the user and then issue a the following command:

alter user username default role all except OWB$CLIENT

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.

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 role, right-click Roles under the Security node in the Globals Navigator and select New Role. 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 that the selected user creates. These privileges do not impact the privileges the user has for accessing objects created by other users.

For example, for all objects that JANE_DOE creates, JANE_DOE, and ADMINISTRATOR and DEVELOPMENT roles, have full access. EVERYONE, PRODUCTION, and TEST roles are restricted to read-only.

If you are familiar with UNIX operating system security, the default object privilege is similar to the UMASK command. When you edit the default object privilege, the change only effects objects the user creates subsequently; there is no effect on previously created objects. Therefore, if you set default object privileges early, you can expect little or no additional object-level security management.

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.

Access may be granted both to roles and to individual users. Note, however, that when you grant access to a role, the privilege is extended to all users in that role. For example, even though JOHN_DOE is not specifically granted access, he has read access through the EVERYONE role. Furthermore, if JOHN_DOE is a member of the DEVELOPMENT role, he has full control and access.

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

Securing Metadata Objects Throughout their Life Cycle

Default object privileges work with object security properties to provide security options throughout the life cycle 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 JANE_DOE creates several mappings. When JANE_DOE designs and develops these objects, the security policy described earlier in this section may be desirable. However, assume that JANE_DOE completes the mappings and releases the objects to the testing team. In this case, the default object privilege is too restrictive. To extend access to the TEST role, JANE_DOE can select the mapping, then from the View menu, select Security. She can then manually add all necessary privileges to the TEST role.

For more details on overriding the default security on an object by object basis, see "Applying Security Properties on Specific Metadata Objects".

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_PLATFORM: Allows users to create new platforms in the workspace using OMB*Plus.

• CREATE_PROJECT: Enables users to create projects, which administrators create projects to organize metadata objects.

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

• CONTROL_CENTER_DEPLOYMENT: Enables users to deploy to the Control Center and then run those procedures.

• CONTROL_CENTER_EXECUTION: Enables users to run procedures from the Control Center.

• CONTROL_CENTER_VIEW: Enables users to view procedures from the Control Center.

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, or if you have only READ permissions for the object, then Oracle 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.

A user who is editing an object in READ/WRITE mode may save changes while a user with read-only privileges views the object. To synchronize the object with the repository, click Refresh.

Everyone Role

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

Administrator Role

Administrators in Oracle Warehouse Builder can perform various security tasks, such as:

• "Registering Database Users"

• "Editing User Profiles"

• Changing User Passwords

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

• "Defining Security Roles"

• "Editing Role Profiles"

• Deleting Users and Roles

  You may delete users by right-clicking in the Globals navigator, and selecting Delete. You may delete all Oracle Warehouse Builder users expect the repository owner. It does not delete or alter the user account in the Oracle Database.

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

  You can delete all Oracle Warehouse Builder roles expect ADMINISTRATOR and EVERYONE roles. Deleting a role from Oracle Warehouse Builder does not delete or alter roles in the Oracle Database.

• Renaming Roles

  From the Globals Navigator, 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"

Users

You can assign multiple users to a role. 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 Globals Navigator, and then edit the role. To create a user, right-click Users from the Security node and select New User. For information on creating and editing users, see "Registering Database Users" and "Editing User Profiles".

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 life cycle 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 JANE_DOE is a developer that creates mappings and process flows. If you want all objects created by JANE_DOE made available to another developer, such as JOHN_DOE, then use the "Default Object Privilege". However, to make only a few objects created by JANE_DOE available to JOHN_DOE or even every user who has a TEST role, locate each object in the Design Center and alter its security options.

To enforce a full metadata strategy, edit the security properties for all projects in the Project navigator. 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 Security Settings icon to apply the changes to all children.

Propagating Security Properties to Child 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 child objects.

Credential Memory on Logon Panel

The logon dialog that appears when Oracle Warehouse Builder Design Center is started retains a list of previously used credentials. This is a convenience for Design Center users who frequently work with the same workspaces. The feature enables Oracle Warehouse Builder to remember log in information.

Changing Passwords that Access Oracle Warehouse Builder

In keeping with standard security practices, you may want to periodically change the passwords used to access Oracle 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_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 Oracle Warehouse Builder Locations

Oracle 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. Oracle 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 Oracle9i 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_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 Oracle Database JDBC Developer's Guide.