Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Unanswered: grants randomly not creating during install

    We are using Oracle 9.

    We have a ksh script that calls several .sql scripts in a row to create our database.

    The first sql creates the tablespaces, sets up the users and creates all the tables.

    The second sql then logs in as the primary user and just does a whole series of grants.

    The problem we are having is that the second sql doesn't always apply all the grants. It's a strange problem, because it doesn't always happen, sometimes all the grants will exist after an install and sometimes they won't. We get no errors from the sql itself, and if you run it manually after the fact, all the grants get created correctly.

    Has anyone seen a problem like this? Could we be running into some kind of race condition when setting up the database?

    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Could it be that (some) users are not created yet, while another script grants privileges to those "non-existent" users?

    Did you try to SPOOL result of your SQL scripts and later review spooled log files? Grants, both successful and unsuccessful should be visible in there.

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    We're using a table that we insert rows into whenever there is a failure using the pl/sql syntax. For example:

    BEGIN
    EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON owner.TABLENAME to OWNER_USER_ROLE';
    EXCEPTION
    WHEN OTHERS THEN
    wk_message := 'ERROR';
    EXECUTE IMMEDIATE 'insert into install_table values
    (''error GRANT SELECT,INSERT,UPDATE,DELETE ON owner.TABLENAME to OWNER_USER_ROLE'')';
    END;

    At the end, we then select * from install_table. We show no errors from the grants failing to execute. This sql file is the last file that gets run during an install and it only does grants. All users are created in a previous file.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your code is incorrect. I assume that this is a code fragment where you are looping through a cursor that is returning the columns owner and tablename. Write it like this


    BEGIN
    EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON '|| owner||'.'||TABLENAME||' to OWNER_USER_ROLE';
    EXCEPTION
    WHEN OTHERS THEN
    wk_message := 'ERROR';
    EXECUTE IMMEDIATE 'insert into install_table values
    (''error GRANT SELECT,INSERT,UPDATE,DELETE ON '|| owner||'.'||TABLENAME ||' to OWNER_USER_ROLE'')';
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, but why using EXECUTE IMMEDIATE in exception handler? Wouldn't pure INSERT INTO do the job?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Wouldn't pure INSERT INTO do the job?
    That would be too efficient.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Sorry, I was concentrating on the GRANT and ignored the insert.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    sometimes all the grants will exist after an install and sometimes they won't
    So it's either all or nothing? Are you sure the KSH is correct - perhaps the SQL grant script isn't getting called (or the call is bombing) under some conditions...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •