sqlldr problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: sqlldr problem

  1. #1
    Join Date
    Feb 2001
    Posts
    203
    One of my user is trying to load data through sql loader with truncate option.

    If table in his schema then it's loading, But when he try to load
    data in other schema table then it's giving this error.

    SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table "oraman"."TEST_SREE"
    ORA-01031: insufficient privileges

    All ready i issued delete any table,select any table privilege to this user. What other privileges he need?

    Thanks.
    sree

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You would first want to login as that user, and grant the alter, update, delete privileges on the table to that user. You have to be the owner of the schema to issue these to the other user, inorder for them to succeed.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    DELETE ANY TABLE is not the correct privilege, you need to grant the user DROP ANY TABLE system privilege to be able to truncate a table from some other schema.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Feb 2001
    Posts
    203
    As a system i created a user and granted connect,resource,app_user,delete any table to user;
    As a schema owner i granted alter, update, delete privileges on the table to the user.
    Now i am able to delete rows but i can't truncate the table.
    If i am missing any thing then let me know.

    I tried to load data thourgh sql loader. I am getting this error
    SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table ORAMAN.TEST_SREE
    ORA-01031: insufficient privileges.

    CONNECT SYSTEM/MANAGER;
    //* CREATING USER *//

    CREATE USER SREENATH
    IDENTIFIED BY NEW1
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP;

    //* GRANTING PRIVILEGES TO NEW USER *//

    GRANT CONNECT,RESOURCE,APP_USER TO SREENATH;

    //* GRANTING DELETE ANY TABLE PRIVILEGE TO USER *//

    GRANT DELETE ANY TABLE TO SREENATH;

    //* connecting as schema owner *//

    CONNECT ORAMAN/PASSWORD;

    //* Granting privileges on table to user *//
    GRANT alter, update, delete ON TEST_SREE TO SREENATH;

    //* connecting as user *//

    CONNECT SREENATH/NEW1@TSOC

    //* select values from table

    SELECT * FROM oraman.TEST_SREE;

    ----
    value1
    value2

    2 rows selected

    //* delete from oraman table *//

    DELETE FROM oraman.TEST_SREE;

    1 row processed.


    //* rollbacking the transaction *//

    ROLLBACK

    Statement processed

    //* truncation oraman.test_sree table *//

    TRUNCATE TABLE ORAMAN.TEST_SREE
    2>
    TRUNCATE TABLE ORAMAN.TEST_SREE
    *
    ORA-01031: insufficient privileges

    Thanks.
    sree

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    As jurij pointed out you would require to give drop any table privilege to the user. That would allow him to truncate the tables.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Feb 2001
    Posts
    203
    Yeah! All ready i tried by giving the
    drop any table
    privilege to user but still it's not working. I am getting the same error. Thanks for your fast responce.

    sree

  7. #7
    Join Date
    Feb 2001
    Posts
    203
    Thanks Guys, It's working.

    To use Truncate option in SQLLDR we need
    drop any table system privilege

    and

    insert,select object privileges on that table.

    I dropped the user re-created him, And i granted a regular
    application user role to him. Then i granted him
    drop any table
    privilege and then i connected as schema owner and then i granted select,insert privileges on the table to user. Then i connected a user and truncated the the other user table.

    If he wants to use delete from sqlplus or use REPLACE option then he need delete privilege on that table.

    the other way to use replace option in sqlldr then he need
    delete any table system privilege.

    In future other DBA's can share this information. That's why i am writing the whole process. Thanks

    [Edited by sree_sri on 08-30-2001 at 05:30 PM]
    sree

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Thank you for the feed back.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


Posting Permissions

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



Click Here to Expand Forum to Full Width