we have different hudson jobs that interact with the database. Because they all write to the database and delete data, we could not run 2 jobs at the same time, fearing a possibility of race condition.
So we decided that we will create different users for each job ( which in case will result in different schema's). I created a new user, logged into oracle, still I was able to see the tables and data that the other user inserted. My understanding is that you'll get a clean slate when the new user is created.
Will my hudson jobs still have the same problem of running into race condition or creating the new user for each job will solve the problem?
Any help will be appreciated.
To clarify the terms.
A database is a set of users each of which may own objects (eg tables).
A user may refer to objects owned by another user.
For example, FRED may own table BLUE. User BARNEY can write a SELECT * FROM FRED.BLUE statement. The statement will only work if BARNEY has been granted SELECT privilege on FRED.BLUE, or has a SELECT ANY TABLE privilege.
If a user (eg WILMA) does a SELECT * FROM RED, then RED is resolved as firstly an object in their default schema, or failing that as a public synonym. A user's default schema is generally their own, but it can be changed with an ALTER SESSION SET CURRENT_SCHEMA
So if your Hudson jobs are bumping into each other in the same database, they might be using a fully-qualified notation to refer to an object in a specific schema, or they might be using a PUBLIC SYNONYM that refers to an object in a specific schema, or they are doing an ALTER SESSION to the same schema.
Here's how you have to do it:
create user jobOneRunner identified by test;
-- At this point they should have no privileges, not even create session.
To be sure of this run the following SQL:
select lpad(' ', 2*level) || granted_role "User, his roles and privileges" from ( /* THE USERS */ select null grantee, username granted_role from dba_users where username like upper('%&enter_username%') /* THE ROLES TO ROLES RELATIONS */ union select grantee, granted_role from dba_role_privs /* THE ROLES TO PRIVILEGE RELATIONS */ union select grantee, privilege from dba_sys_privs ) start with grantee is null connect by grantee = prior granted_role;
If the JobOneRunner user has privileges, revoke them. Then grant them select/update/delete, etc access to whatever objects that they need to access. You will also have to grant them create session so they can connect.
To grant select/update/delete to an object owned by another schema do this:
grant select on SCHEMA.object to jobOneRunner;
To answer your second question, yes, it will solve your problem. However, have you determined for sure that a race condition is possible?