问题描述:

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?

相关阅读:
Top