Re: assign permissions data_owner to data_select

HI
NOTHING IS IMPOSSIBLE. if you want , you can finish it I HAVE DO IT . in my env , When I create the oracle object , all the authority automatic processing .

On 16/02/07, Bob <orcl_at_comcast.net> wrote:
>
> 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 <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 <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