RE: Private Synonyms

From: Patterson, Joel <jpatterson_at_entint.com>
Date: Thu, 12 Dec 2013 09:24:00 -0500
Message-ID: <C1117B1AA0340645894671E09A7891F71569177DE5_at_EIHQEXVM2.ei.local>

Didn't User2 have access to the data already? 5) as scott grant select on emp to user2.

However user1 did not grant access to user2... not being given the grant option. Yes, it is daft that user2 can select * from user1.emp when he can just select * from scott.emp. However it also seems odd that user2 can use a private synonym of user1 thus rendering the private synonym 'not private'... so fishy is as fishy does?

I stopped reading 174368.1 when I didn't know who owned the SQL> prompt, and it seemed to focus into VPD etc. as its purpose stated.

Joel Patterson
Database Administrator
904 928-2790

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield Sent: Thursday, December 12, 2013 6:13 AM To: D'Hooge Freek
Cc: rjgoulet_at_comcast.net; oracle-l_at_freelists.org Subject: Re: Private Synonyms

There isn't a security breach in the sense that User 2 gets access to data that they didn't already have access to. It is however somewhat counter-intuitive that you can run

CREATE PRIVATE SYNONYM x for SCHEMA.OBJECT;

and that someone else can utilize your synonym without explicit grants. Doing so is somewhat daft of course..

On Thu, Dec 12, 2013 at 8:49 AM, D'Hooge Freek <Freek.DHooge_at_uptime.be<mailto:Freek.DHooge_at_uptime.be>> wrote: Hi,

Why would that be fishy?
user2 has received access on the underlying object, to which the private synonym points, directly from scott. So, no security breach.

regards,
--

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be<mailto:freek.dhooge_at_uptime.be> tel +32(03) 451 23 82<tel:%2B32%2803%29%20451%2023%2082> http://www.uptime.be
disclaimer: www.uptime.be/disclaimer.html<http://www.uptime.be/disclaimer.html>

On wo, 2013-12-11 at 18:29 -0500, Dick Goulet wrote:

All,

    Is there anyone other than myself that doesn't think this is right. For those of you who have missed it, like I did, when Oracle started evolving Fine Grained Access Controls (FGA) the role of private synonyms changed. Try this for starters and I'll make it easy:

1. install the scott account, we'll need emp.
2. create another account, any name you like, I'll use user1.
3. create a third account, I'll call it user2.
4. as scott grant select on emp to user1.
5. as scott grant select on emp to user2.
6. as user1 create a private synonym to scott.emp
7. as user2 "select * from user1.emp;"

If you go back to a V8 database step 7 above will end in an ORA-00942. If your on V9 or higher, you get data.

Does this sound fishy??? I've opened an itar with Oracle. They referenced note:174368.1 Policies on Synonyms. But this just seems wrong to me. Any other opinion???

Dick Goulet
Senior Oracle DBA.

--

Niall Litchfield
Oracle DBA
http://www.orawin.info

--

Joel Patterson
Sr. Database Administrator | Enterprise Integration Phone: 904-928-2790 | Fax: 904-733-4916
www.entint.com<http://www.entint.com/>

[http://i1202.photobucket.com/albums/bb367/Entint/signaturev61.jpg]<http://www.entint.com/>

[http://i1202.photobucket.com/albums/bb367/Entint/th_FaceBook1.jpg]<http://www.facebook.com/pages/Enterprise-Integration/212351215444231> [http://i1202.photobucket.com/albums/bb367/Entint/th_Twitter1.jpg] <http://twitter.com/#!/entint> [http://i1202.photobucket.com/albums/bb367/Entint/th_LinkedIn1.jpg] <http://www.linkedin.com/company/18276?trk=tyah> [http://i1202.photobucket.com/albums/bb367/Entint/th_YouTube1.jpg] <http://www.youtube.com/user/ValueofIT>

This message (and any associated files) is intended only for the use of the addressee and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient, you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. [v.1.1]

--

http://www.freelists.org/webpage/oracle-l Received on Thu Dec 12 2013 - 15:24:00 CET