Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database and a USER administrating it. Then I have an additional USER_APP to whom I granted CRUD access to all tables in my oracle data modeler (and then applied these via generated script )

I created a synonym for every table from USER_APP account, with the following syntax:

create synonym USER_APP.CLIENTS for USER.CLIENTS;

And for every table generation is successful and commit made. But when I try to use it anyhow on that account, for example:

select * from CLIENTS;

I get an error that CLIENTS do not exist, namely

ORA-00942 : table or view does not exist.

What can I do to fix that or where should I look to determine the cause/solution?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Synonyms have nothing to do with privileges. They are simply a way to simplify naming.

The error you are getting appears to indicate that user_app does not have privileges on the user.clients table. You'd need to grant that

GRANT SELECT, INSERT, UPDATE, DELETE
   ON user.clients
   TO user_app;

Of course, in your actual system, I'm guessing that there is a role that you would grant privileges to and that role would be granted to user_app.

share|improve this answer
    
That's strange since your solution actually worked. I mean, the thing is that I had such statement in my script, along lines generating tables themselves. I had explicitly: GRANT DELETE, INSERT, SELECT, UPDATE ON clients TO USER_APP WITH GRANT OPTION, executed as USER... Why didn't that work? Is it that I had clients instead of USER.clients? –  infoholic_anonymous Feb 1 at 21:00
    
@infoholic_anonymous - I would guess that either you didn't run that line or that you (or someone else) subsequently dropped and re-created the table without granting the appropriate privileges. –  Justin Cave Feb 1 at 21:06
    
I've run the whole script at once via sql*plus. Some mysterious drop could've happened though. Anyway, to be 100% sure, please tell me, the code I've mentioned above is correct and I didn't have to prefix the table names with USER., do I understand correctly? –  infoholic_anonymous Feb 1 at 21:16
    
@infoholic_anonymous - You don't need to fully qualify the table name assuming that you are running the script as the table owner. –  Justin Cave Feb 1 at 21:20
    
Thank you for the additional clarifications as well, unfortunately I can't yet vote up the last one. –  infoholic_anonymous Feb 2 at 3:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.