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.
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.
//* 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 *//
//* Granting privileges on table to user *//
GRANT alter, update, delete ON TEST_SREE TO SREENATH;
To use Truncate option in SQLLDR we need
drop any table system privilege
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