You Asked
A database user creating a private synonym has no control over its usage by other database users.
For. example
conn user1/user1@db1
create table t1 (c1 number);
grant select on t1 to user2;
grant select on t1 to user3;
conn user2/user2@db1
create synonym t1 on user1.t1;
conn user3/user3@db1
select * from user2.t1;
<lists the data of table user1.t1>
user3 is able to successfully access the synonym of user2 even though user2 has not granted access on the synonym to user3. Any database code of user3 that uses user2.t1 would get affected when user2 changes the definition of the synonym user2.t1.
Following is an excerpt from the Oracle 9iR2 SQL Reference manual (in the section documenting the GRANT statement):
---------------------------------------------------------
Synonym privileges are the same as the privileges for the base object. Granting a privilege on a synonym is
equivalent to granting the privilege on the base object. Similarly, granting a privilege on a base object is
equivalent to granting the privilege on all synonyms for the object. If you grant to a user a privilege on a
synonym, then the user can use either the synonym name or the base object name in the SQL statement that
exercises the privilege.
---------------------------------------------------------
Does this mean that granting access on a base table implicitly grants access to all (currently existing and created in the future) private synonyms (created by any user) based on this table to all users.
If such private synonym access is by design, then:
1. A private synonym is not so private anymore
2. Does'nt this blur the difference between a private synonym and a public synonym in terms of access (accessing a private synonym would ofcourse require prefixing the synonym name with the owning schema name).
This seems to be against the concept of object (i.e. synonym) access only through privileges.
and Tom said...
synonyms do not have "privileges". there is no way to grant "access" to a synonym.
1) it is "private" to the schema meaning you have to reference it in the context of that schema to use it. Regardless you need access to the base object, that you could reference it via u1.t1 or u2.t1 is not relevant -- u2.t1 is in fact just another name for U1.T1. no security, nothing has been subverted or gotten around here.
2) the "private" is a scope thing, like a private rollback segment vs public rollback segment.
Synonyms are documented explicitly "not to have privs". You cannot "grant" on a synonym for granting on a synonym is "equivalent to granting the privilege on the base object."
There are "synonyms" (scoped) and "public synonyms" (not scoped). A synonym is not an "object", it is an alias.
Any yes, of course, if something uses u2.t1 and depends on u2.t1, a change to u2.t1 would affect them -- but in the same vein "if something uses u1.t1...." -- same thing.
perhaps this paragraph:
If you omit this clause, then the synonym is private and is accessible only within its schema. A private synonym name must be unique in its schema.
would be better stated as:
If you omit this clause, then the synonym is private and is accessible only #bwithin the scope#b of its schema. A private synonym name must be unique in its schema.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment