Oracle - Hudson Jobs : Schema vs User

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?