RE: assign permissions data_owner to data

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Thu, 15 Feb 2007 07:51:21 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE08B3C@EXCNYSM0A1AJ.nysemail.nyenet>

Bob,

Create a database trigger that fires after a ddl event - check the documentation. Something like:

CREATE TRIGGER audit_db_object AFTER CREATE

ON SCHEMA
pl/sql_block

You will need to use dynamic sql to issue the grant statements.

BTW - you said you thought this was a common technique. It is *not* a common technique. Database security should not be done this way. Sooner or later you or someone else will forget about this trigger and someone will gain access to info they should not see. And your ass will be on the line.

Just my 2 cents.

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.

-----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
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 15 2007 - 06:51:21 CST

RE: assign permissions data_owner to data_select