Solved

Grant / Synonym Definition ..

Posted on 2002-05-21
4
3,485 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
  • 2
4 Comments
 
LVL 1

Expert Comment

by:peledc
Comment Utility
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 34

Accepted Solution

by:
Mark Geerlings earned 50 total points
Comment Utility
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
Comment Utility
Thanks for the response. I have a much clearer picture of what a grant and synonym are.

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now