RE: assign permissions data_owner to data_select

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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



______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________



NOTICE OF CONFIDENTIALITY: Information included in and/or attached to this 
electronic mail transmission may be confidential. This electronic mail 
transmission is intended for the addressee(s) only. Any unauthorized 
disclosure, reproduction, or distribution of, and/or any unauthorized action 
taken in reliance on the information in this electronic mail is prohibited. If 
you believe that you have received this electronic mail transmission in error, 
please notify the sender by reply transmission, or contact 
helpdesk@xxxxxxxxxxxxx, and delete the message without copying or disclosing 
it. 

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________
--
http://www.freelists.org/webpage/oracle-l

Other related posts: