GRANT DROP priviledges to other users on localuv tables

dsnovice · Post by **dsnovice** » Wed Jul 05, 2006 10:11 am

*****FileSjob..XFORM_MERGE: |*****FileSjob..UV_MERGED_FILE.inMerge: DSD.BCIOpenW call to SQLExecDirect failed.
DROP TABLE "Hsh_ECRM_CON_ACCT"
SQLSTATE=42000, DBMS.CODE=950563
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: You don't have enough privileges to DROP "Hsh_ECRM_CON_ACCT".|

I am getting the above error when I try to rerun a Job, that was initially run by my colleague.

This job basically tries to use the Universe database to host merged data and help with partially selecting different columns to feed different tables. This Job runs fine when run by my colleague.

The error, I believe, occurs as the Table/file was created by my colleague and I don't have drop priviledges to access this file.

I even tried setting the DATA.30 and OVER.30 of the Hsh_ECRM_CON_ACCT file to chmode 777.

Please help me with how I can, as a part of the DDL grant priviledges on the table being created to the group or list of users.

Thank you,

a Novice

The question is

kcbland · Post by **kcbland** » Wed Jul 05, 2006 10:24 am

Have your associate login to the UV shell, logto the appropriate project, then issue the appropriate GRANT statements for select, insert, update, delete, drop, etc.

dsnovice · Post by **dsnovice** » Wed Jul 05, 2006 11:02 am

Hi ken,

My associate tried the following command
> GRANT SELECT,INSERT,UPDATE,DELETE,DROP ON Hsh_ECRM_CON_ACCT TO User1211;
and he got "DataStage/SQL: DROP unexpected"

how ever he is able to assign other priviledges like select, insert, update,delete on UV table. by the following command
GRANT SELECT,INSERT,UPDATE,DELETE ON Hsh_ECRM_CON_ACCT TO
User1211;

kcbland wrote:Have your associate login to the UV shell, logto the appropriate project, then issue the appropriate GRANT statements for select, insert, update, delete, drop, etc. ...

Is there any way to do this with out making DBA priviledges to every body.

I saw two columns Perm_SCHEMAS and PERM_TABLES in the UV_USERS which get updated when we grant SELECT, etc... but these do not allow the person to drop. how can we add drop priviledges or on the other hand create tables that are open to all?

thank you,

a Novice

kcbland · Post by **kcbland** » Wed Jul 05, 2006 12:07 pm

My brain is not working right now. Kim, Ray, you out there? Lend some help please.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 05, 2006 7:32 pm

DROP is not a privilege that can be granted to DataStage/SQL tables. Nor is CREATE. Hence the error in the first GRANT statement.

The creator of a table is its owner. You must be the owner of the table or have DBA privilege to be permitted to drop it. Those are the rules.