Re: Private Synonyms

From: Tom Dale <tom.dale_at_fivium.co.uk>
Date: Fri, 13 Dec 2013 13:13:10 +0000
Message-ID: <CAGDf7wRngOcT2KXAgNfTMJ1SN=Caowu-T5VaJqqz_=CXmXOC2Q_at_mail.gmail.com>

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_at_gmail.com> 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

>

>


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 13 2013 - 14:13:10 CET