Solved

Grant / Synonym Definition ..

Posted on 2002-05-21
4
3,505 Views
Last Modified: 2008-02-01
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 !
0
Comment
Question by:Romans
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Expert Comment

by:peledc
ID: 7025181
Hi,

Synonyms:
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:
SCOTT.EMP .
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

GRANTS:
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.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 50 total points
ID: 7025501
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.
0
 

Author Comment

by:Romans
ID: 7027143
Thanks for the response. I have a much clearer picture of what a grant and synonym are.

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7027188
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question