Romans
asked on
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 !
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 !
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the response. I have a much clearer picture of what a grant and synonym are.
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.
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.
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.