RE: Privilege - View (ORA-1031)

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Tue, 2 Jan 2007 14:22:57 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE089AA@EXCNYSM0A1AJ.nysemail.nyenet>

Shiva,  

I should have said "with grant option" rather than "ADMIN" option:  

GRANT SELECT     ON table_name to user

    WITH GRANT OPTION;   Tom  

---

This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

---

From: Mercadante, Thomas F (LABOR)
Sent: Tuesday, January 02, 2007 2:21 PM
To: 'shivaswamykr_at_gmail.com'; ORACLE-L
Subject: RE: Privilege - View (ORA-1031)  

Shiva,  

Of course it is logical. User C does not have privs on the table in Schema B. And Schema B did not give the "ADMIN" priv to the table to user B which would allow User B to grant access to his view to anyone.  

You should *never* grant "select any table" to anybody. When you did this, you allowed user C select access to the table in Schema B.  

This is the way it works. Read up on database security.

Tom  

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Shivaswamy Raghunath Sent: Tuesday, January 02, 2007 2:00 PM
To: ORACLE-L
Subject: Privilege - View (ORA-1031)  

Hello.

Version - 10.1.0.5.0.

User C is NOT able to select (ORA-1031) from a View V in Schema A (On which C has select privilege), which is based on a table in Schema B.

If user C is granted Select any Table, he is able to select from the view V.

This does not loook logical to me. Have you come across this?

Thanks,
Shiva

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

Received on Tue Jan 02 2007 - 13:22:57 CST