Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Grant / Synonym Definition ..

Posted on 2002-05-21
4
Medium Priority
?
3,510 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 200 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

596 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