Re: Private Synonyms
- From: Tom Dale <tom.dale@xxxxxxxxxxxx>
- To: rjamya@xxxxxxxxx
- Date: Fri, 13 Dec 2013 13:13:10 +0000
Synonyms always get resolved, even on a grant
IE a grant on a synonym is a grant on the underlying object
Login as users1
SQL> create table a (a int);
SQL> create synonym a_syn for a;
--
-- Grant on the synonym
--
SQL> grant select on a_syn to user2;
SQL> create table b (b int);
SQL> create synonym b_syn for b;
--
-- Grant on the base table
--
SQL> grant select on b to user2;
--
-- Check the grants
--
SQL> select grantee,privilege from dba_tab_privs where table_name ='B' and
owner='USER1';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
USER2 SELECT
SQL> select grantee,privilege from dba_tab_privs where table_name ='A' and
owner='USER1';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
USER2 SELECT
--
-- What about the synonym?
--
SQL> select grantee,privilege from dba_tab_privs where table_name ='A_SYN'
and owner='USER1';
no rows selected
SQL>
Every user can resolve private synonyms not owned by them
look in all_synonyms where owner != 'PUBLIC' for any user.
Tom
On Thu, Dec 12, 2013 at 7:54 PM, rjamya <rjamya@xxxxxxxxx> wrote:
> I ran dbms_utility.name_resolve as user2 and passed in 'USER1.EMP' as a
> parameter, oracle did come back pointing to owner as 'SCOTT' and table name
> as 'EMP'. So based on privileges, it did figure out the correct object_name
> underneath.
>
> in my case I had my id own the table, user1 and user2 were created, user1
> had a synonym emp_new pointing to emp. oracle came back resolving
> user1.emp_new to emp table in my schema.
>
> Raj
>
>
Other related posts:
