Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: assign permissions data_owner to data_select

Re: assign permissions data_owner to data_select

From: Bob <orcl_at_comcast.net>
Date: Fri, 16 Feb 2007 08:41:18 -0500
Message-ID: <45D5B47E.907@comcast.net>


OK, I see. I suppose its Dependant on the sensitivity of t he data and size of the system.

I can see the manual method be driven by the "change management and release process" and how circumventing that could definetly be a problem.

On the other hand, it would be useful to have an automated process for certain environments. ie not to have to manually generate a grant script every time joe creates something and wants mary to see it.

Im my immediate situation - Ill opt for the manual, release process generated grants. Im still looking for a slick automated process though ;-)

Thanks for the input

Bob

Boyle, Christopher wrote:

>Typically in shops that I have worked in, it goes like this:
>
>Create table data_owner.table
>Create role select_only_role;
>Grant select on data_owner.table to select_only_role;
>Grant select_only_role to data_users;
>
>Repeat step one and two as necessary. If packages and procedures need
>to be written against the tables then they are owned by data_owner and
>execute is granted. I agree with Thomas's opinion of what will happen if
>you automate security. Bad things will happen.
>
>
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bob
>Sent: Thursday, February 15, 2007 8:19 AM
>Cc: oracle-l
>Subject: Re: assign permissions data_owner to data_select
>
>Hi Tom, thanks for the reply. Im really wondering "how" this is supposed
>
>to be handled. Ive looked at the docs, a few books and goggled - the
>docs and books give no specific examples of assigning privilege from
>"data_owners" to "data_users" ie -select privs on a schema-.
>
>Google shows a bunch of questions about this but no real way of doing
>it. Seems like this would be a common, problem
>
>Thats why I asked here, I was hoping there would be a straight forward
>
>professional way to handle this. Still looking
>
>Thanks
>Bob
>
>Mercadante, Thomas F (LABOR) wrote:
>
>
>
>>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
Received on Fri Feb 16 2007 - 07:41:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US