RE: assign permissions data_owner to data_select

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Wed, 14 Feb 2007 19:17:57 -0000
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663BD7F@ENYC11P32005.corpny.csfb.com>

Bob,

Your trigger wouldn't be on logon for data_select, since it cannot grant privileges to itself. You'd start out with grants on existing objects, then create a DDL trigger for data_owner that grants SELECT on newly-created selectable (tables, views, sequences, clusters).

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bob Sent: Wednesday, February 14, 2007 12:49 PM To: oracle-l
Subject: assign permissions data_owner to data_select

Elementary question but....

Say I have schema data_owner - this guy owns his data and can do what he likes in that schema.

I want to create a user "data_select" who can only select from "data_owner" and must be dynamic ie when new objects get created data_select need to see that.

Is there a nice straight forward way to do this? Im thinking dynamic sql and a log on trigger for data_select, but hopefully there is a nice clean way to do this

I believe this is a common technique and Im wondering how most dba's handle it.

Thanks
Bob

--

"Oracle error messages being what they are, do not highlight the correct cause of fault, but will identify some other error located close to where the real fault lies."

--

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

---

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

---

--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 14 2007 - 13:17:57 CST