ODI 11g : Results of Deleting a User

One of my customers had a high turnover rate. Sometimes we ran into some ODI objects which are locked by a developer who has gone. That triggered a question. What if I delete an ODI user while it has locks on objects. So I gave it a try, and while trying this other cases also pop-up. I also tried them on my VM.

Case 1: Deleting a user with locked objects

My expectation was being unable to delete a user. I was expecting there should be a FK between user and lock tables in repository. But I was able to delete. Then I thought about two scenarios,
a) object will be unlocked
b) object will stay locked and when I try to visualize lock, ODI will crash since there will be nothing returned from lock query.

I was wrong. You may find the screenshot about how ODI responds to deleting a user with locks.

Locked Object by deleted user
Locked Object by deleted user


I can open object, ODI will pop-up “This object is locked by DEVELOPER. You can not edit” dialog. You can view everything in the object. But to begin using object again, you need supervisor to unlock it.

Case 2: Deleting a user who created some objects

In that case I was expecting to find “Created By:” text box in object’s version tab to be empty. But I was wrong again. ODI just worked fine. When I checked the repository, I saw that ODI holds usernames for these text box not the user ID’s.

Case 3: Deleting an online user


After first two cases and failures of my expectations, now I had nothing to expect. I would just try and see. I created a user, open another ODI instance, connect with new user and deleted the user. Then I tried to take some actions in ODI. Some I was able to, some I was not.



Create Scenario
Create Scenario

Things I was able to:

  • View operator logs
  • View topology definitions
  • View models
  • Create scenarios
View interface
Save an open interface

Things I was not able to :

  • View interfaces
  • Run interfaces or scenarios
  • Selective Reverse Engineering a model
  • Edit topology definitions.



Exevute scenario
Exevute scenario
View interface
View interface



If you have some other cases for me to try please write in the comments area.

Thanks for reading, dont forget to share & comment.

ODI 11g: Step by Step Creating a User

In the last post here: http://www.canburaktumer.com/blog/odi-11g-step-by-step-master-and-work-repository-creation/ , we have created our master and work repository. As we only had one user, which is SUPERVISOR, we need other users to have a safe environment. For a safe environment give every user privileges, only  what they need.

For example, an OPERATION ADMIN role should not be able to use designer tab to edit interfaces, packages and procedures. A DEVELOPER role should not have privileges to create or alter users in security tab.

Creating a Typical DEVELOPER

So now let’s create a DEVELOPER user now, who will have the rights to Designer , Operator and Topology tabs. First connect to your master or work repositories with SUPERVISOR.

Create a User - 1
Create a User – 1

Expand the Users accordion to see user list, in our case there is only SUPERVISOR in list. Click on the little man with plus on it, a pop-up menu will appear with only one option; New User. Click that option.

Create User -2
Create User -2

So this is the main screeen where you enter information of a new user. There is some little details I like to tell about. You can create an account with an expiration date. So when the day comes, user will become an invalid user. And its icon will be red. (See second picture below. I created DEVELOPER user with expiration date, and let it expire to have a screenshot.)

If you check Supervisor checkbox than this user will have SUPERVISOR privileges, and actually you don’t have to assign any other privileges to this user as it has all of them.

Last detail is the Password tex box, which you can not edit. There is a button below text box, which says Enter a Password. When you click it you’ll be able to create a password for user. Password also has an expiration option. You can create passwords that expire to increase level of security. Also there is other password policies you can set, but we will be talking about it in next post. By default ODI has only one password policy, which is ‘Passwords should have six or more characters.’

Create User -3
Create User -3
Expired User
Expired User
Password Window
Password Window

After selecting a password, we save and close and user appears in our user list.

Assigning Privileges to a User

Now it’s time for our user to have privileges a developer will need. We will assign privileges by using predefined roles, there is also a longer way to do it; giving privileges object by object like giving ‘view interface’, ‘edit interface’, ‘new interface’ privileges. But it is a longer method also it requires more attention and knowledge, so in this post we are going to use roles, as we are learning about the basics.

If you have used ODI before, but not assign a role to a user, it may become confusing in the ways of ODI’s user experience perspective. In ODI, when you are doing something in accordions, then usually you click, right-click or double click and open the edit window and do whatever you want. However, in security tab while you are assigning a profile to a user, you simply drag profile on to the user. It is a simple way to do it, on the other hand it is not the obvious way in terms of ODI’s user experience.

So I will drag and drop CONNECT, DESIGNER, METADATA ADMIN, OPERATOR and TOPOLOGY ADMIN privileges to our DEVELOPER user. Final view should be like below:


If I should briefly explain these privileges:

CONNECT : The basic profile to connect an ODI repository, it is like CREATE SESSION privilege on Oracle Databases. It has some more rights like viewing some objects. You can see objects if you click + next to CONNECT.

DESIGNER : This is where our codes are. There are interfaces, procedures, scenarios, packages, projects, variables in designer. And designer profile gives privileges to create, edit, delete ability for these objects.

METADATA ADMIN : This is actually about Model accordion. Model is the place where data stores’ metadata are being held. Like tables, files, web services and other data stores. METADATA ADMIN gives you ability of create, edit, view and delete of Model objects or Model folders.

OPERATOR : Operator has sessions’ information, load plans, scenarios … etc. Actually running codes, and their sessions are being held in this tab. By using operator you can see errors, see successful runs, re-run scenarios…

TOPOLOGY ADMIN : Topology is where you have connection information to data stores, TNS info for a table’s database or path of a file or URL of a web service. All connections are stored here. We will see topology tab in detail in upcoming posts.

So now we learned how we create a user, how to assign privileges to it. But how can we connect to repository with our new user. OK, nice question, let’s create a connection for our new user.

Creating a Connection

So we have already created connections, so I will make a quick review of it.

Click on connect to repository, you should see login window, click on green plus to create a new connection. Now enter your DEVELOPER user’s information like you see in figure below.

Connection of DEVELOPER
Connection of DEVELOPER

Our connection is ready.

Next post will be about security tab in detail. Before we begin to set up our environment for development, we should secure it.

Questions in the comment area please.