sqlldr problem

[sree_sri]

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.

[sambavan]

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

[jmodic]

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.

[sree_sri]

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.

[sambavan]

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

[sree_sri]

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_sri]

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]

[sambavan]

Thank you for the feed back.

Sam