Grant / Synonym Definition ..

I am really new to oracle here so bear with me....

Can somebody please tell me what a grant and a synonym is in really simple terms and what they do for the the different users I have in my database?

Thanks !
Who is Participating?
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Synonyms and grants are independant database objects.  You can create one but not the other, and vice versa.

Synonyms are a shortcut way of refering to an object (table, view, package, procedure, etc.) usually in another schema.  You can create the following synonym:
create public synonym emp for scott.emp;
to make it easy for any user in the database to refer to the "emp" table in the "scott" schema simply as "emp", instead of the "scott.emp" syntax that would be required if there was no synonym.  The existance of a synonym like this though does not by itself give other uses the right to use scott's table.

A grant is a permission (to someone else) to use an object in a schema they do not own.  For example, if you log in as scott, then:
grant select on emp to public:
anyone will be able to see (select) the data in scott's emp table, although they will not be able to add, change or delete it.  You could:
grant select, insert, update, delete on emp;
if you want to allow the other actions.

In addition to grants to public (all users) you may grant priviliges to specific users or to roles instead.  The roles can then be granted to users.  This centralizes grants and reduces grant maintenance complexity.

Each table's full name is it's schema's name + the table name.
If scott has created the EMP table, the full table name is:
If you are connected to the DB via the SCOTT user, you can ommit the SCOTT preffix. If you try to access the same table while connected to the database via a diferent account, you will have to call the EMP table by its full name (SCOTT.EMP).
In order to by pass this limitation, you can create a synonym that will translate each call to EMP to SCOTT.EMP

The user who creates an object (table, function...) has privilidges on it. These privileges can be INSERT,UPDATE,DELETE, SELECT or EXECUTE.
Other users do not have any privilidges on this table unless you specifically grant them.
The GRANT commands acctually enables you to give other users premissions to mass with your table's data.

Hope this help.
RomansAuthor Commented:
Thanks for the response. I have a much clearer picture of what a grant and synonym are.

Mark GeerlingsDatabase AdministratorCommented:
There is another complexity to synonyms.  Oracle supports both "public" synonyms (like the ones I gave you examples of above) or "private" synonyms that belong to, and can only be used in, the schema that owns them.

Public synonyms are available to any/all users and are a good way to make sure that all users see the same data when they issue a statement like:
select * from emp;

If your database though has multiple "emp" tables in separate schemas, then you may need private synonyms.  For example, if schema "bob" also has an "emp" table, and users Mary and Sue should use the "emp" table in bob's schema rather the the one in the scott schema, but all other users should use the one in the scott schema, then you would still want a public synonym "emp" for "scott.emp", but in Mary's and Sue's schema they each need to:
create synonym emp for bob.emp;
(Note: no "public" in this synonym definition)

This insures that when they issue:
select * from emp;
they will see the data in the bob.emp table rather than the scott.emp table.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.