Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've had a few issues where users have mistakenly created public synonyms which have led to people thinking some objects are in one schema when they're actually in another schema. Everyone knows they should be using private synonyms, but occasionally they forget or they make a mistake and someone gets burned. Is it possible to GRANT users the permission to create private synonyms but disallow public ones?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

In order to create public synonyms, you must have the CREATE PUBLIC SYNONYM privilege. To create synonyms in your own schema, you must have the CREATE SYNONYM privilege. So, to do what you want, use:

GRANT CREATE SYNONYM TO the_user;

If users have the CREATE PUBLIC SYNONYM privilege, you can revoke it:

REVOKE CREATE PUBLIC SYNONYM FROM the_user;
share|improve this answer

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.