Oracle Warehouse Builder enables multiple users working on the same repository during the whole lifecycle of warehouse development. As a well-organized group, these users are usually grouped by different roles: some are in charge of developing ETL metadata; others are responsible for testing these objects (mappings, process flows, etc) and making sure their correctness. In this article I'd like to provide a simple example on building up multi-role development environment in OWB - with OWB Security.
OWB Security enables us to define security on the objects stored in repository (object privileges) and control access to system-level services (system privileges). With OWB Security we are able to define multiple users/roles, and apply a different security strategy to each. I will implement a 2-role environment utilizing OWB Security: DEVELOPER, who develops ETL objects; and QA, who tests those developed objects and verify their correctness. I will also create users as DEVELOPER or QA. Then we will see how we benefit from this 2-roles environment during warehouse development.
Let's start by creating a new workspace. A workspace is the place where all ETL objects live. In OWB 11.2, workspaces are stored in a single schema and it's simple to create one - using the Repository Assistant.
Here I create a workspace named "wksp1" with user "admin" as its owner. Understand that the workspace owner is the super user of the workspace, just like root in Linux. We would proceed with subsequent steps using this account.
As we have the workspace prepared, we are able to log into the Design Client as workspace owner "admin". Before creating any role/user, we should do one thing immediately - revoke all privileges from EVERYONE role.
OWB automatically creates two predefined roles for each workspace - ADMINISTRATOR and EVERYONE. ADMINISTRATOR owns ALL object privileges and system privileges to the workspace. Workspace owner ("admin" in our case) is by default the granted ADMINISTRATOR role. EVERYONE is by default granted to every workspace user and it cannot be revoked for any user.
EVERYONE role has many default system privileges and object privileges. As each OWB user is by default granted with EVERYONE role, they would also inherit privileges from EVERYONE. Since we will create users with security strategy inherit from our own roles, we don't want it affected by role EVERYONE. So we should de-select all privileges from EVERYONE role immediately after we log into the new workspace. The following lists what we should do:
Now we can start defining roles inside the workspace. But I would talk about basic concepts of default object privileges and system privileges before going on.
Default object privileges define the access other users and roles have to objects the selected user creates. There defined following object privileges in OWB: FULL_CONTROL, EDIT, COMPILE and READ. They are additive, which means when you select COMPILE you apply both COMPILE and READ. Notice that FULL_CONTROL means EDIT plus the ability to grant and revoke privilege on an object.
System privileges define user access to workspace-wide service. For example, CREATE_PROJECT allows user to create projects, while CONTROL_CENTER_DEPLOYMENT allows user to deploy to the Control Center and then run those procedure.
For more details on object privileges and system privileges, please refer to OWB Installation and Administration Guide.
In our case we should define two roles: DEVELOPER, who is responsible for developing all ETL objects inside the workspace; and QA, who is responsible for testing ETL objects created by the developer.
As for DEVELOPER, they should be able to create and edit projects and all ETL objects under them, and to deploy and execute them inside the control center. But they do not need to grant and revoke privilege of other users/roles, or view created objects in the browser, etc. So we should grant EDIT as default object privilege, and all system privilege except ACCESS_PUBLICVIEW_BROWSER.
As for QA, they should NOT be able to create/edit ETL objects inside the workspace. But they should be able to compile, deploy and execute the ETL objects in order to test them. So we would grant COMPILE as its default object privilege, and grant all control center system privileges to it.
As roles are defined, we are able to create users and grant proper role to them. Remember that we should also set default object privileges after creating users.
Firstly let's create user DEV1, assign role DEVELOPER to it, and user QA1, with role QA.
Then we grant default object privileges to roles DEVELOPER and QA for users DEV1 and QA1. Pay attention we should also revoke all privileges from role EVERYONE! (See preceding section: Revoke all privileges from EVERYONE role)
Here for DEV1, we allow users with role DEVELOPER to EDIT all objects created by it, and only allow users with role QA to COMPILE.
Settings for user QA1 looks somewhat strange - we revoke all default object privileges from all roles! Remember that QA cannot create/edit ETL objects inside workspace? And it's save to revoke all default object privileges for all roles, as there will be no objects created by QA1!
Now there comes out another question - how we guarantee that QA1 is not allowed to create ANY objects inside workspace? The answer is as follows.
Now we've done with all settings for workspace "wksp1" and can proceed with our warehouse development tasks on it. Let's check it out.
Firstly let's log in the workspace as user DEV1, and we are able to start our ETL development. We can create new project and oracle module, import tables, create mapping, deploy and execute the mapping created.
Then let's log into the workspace with user QA1.
If we double-click to open mapping MAP_SRC_TO_TGT created by user DEV1, it would pop-up warning message showing that we can only open the object in Read-Only mode.
Meanwhile we are able to compile and deploy the same mapping as user QA1. That's what we desired as QA.
And if we try to create a new mapping under same oracle module ORA_TARGET, it would warn us that we are not allowed to do so - also what we wanted in this case!
In summary this article illustrates a simple example on building up a multi-role development environment in OWB. We hope you find it helpful in building up your own environment - possibly with more complex roles and users.