Re: Use of SYS views (V$...) in trigger

"RSH" <RSH_Oracle_at_worldnet.att.net> wrote in message news:<Ivrf8.5340$106.336923_at_bgtnsc05-news.ops.worldnet.att.net>...
> Let's start with a real simple and important rule.
>
> You should NEVER, EVER create your own objects in SYS or SYSTEM unless
> Oracle Support tells you to or you ask them and they say it is okay, or a
> manual from them instructs you to do so.
>
> Failure to comply may result in a messed up instance, revocation of support
> from Oracle, etc. That's why every Oracle document I've ever seen says DON'T
> DO THAT.
>
> Moving along, ah, well first of all I don't like public synonyms, they cause
> a great deal of trouble and apart from Oracle's own public synonyms, the
> notion makes me ill. You may not have felt the pain, yet, say of installing
> Oracle Applications & Oracle Manufacturing, and Oracle itself, and maybe 6
> third-party (not made by Oracle) applications, and one of them creates a
> public synonym called "PO", and suddenly all of Oracle Purchasing doesn't
> work anymore.
>
> Second, if you're serious about this thing in granting non-DBA's access to
> read the V$ views, you have to remember to GRANT SELECT ON (whatever) to
> PUBLIC. as well as creating the synonym. A lot of people think creating the
> synonym automatically takes care of privilege, but NO, that is not true!
> Identity abstraction (via synonyms, public or not; or views) is a separate
> matter than access control (being able to see and pull data out of the
> object you just made a synonym for.)
>
> So that means, for example, for view, SELECT privileges on the underlying
> tables must be granted to PUBLIC, SELECT must be granted to PUBLIC on the
> view, and then a public synonym can be created for the whole thing, and then
> it will work.
>
> You can regulate and ease the process somewhat with using roles, but be
> forewarned, you must think about that 3 times over and sleep on it before
> you embark on using roles for object access control. The issues regarding
> inheritance of privileges and other matters can mean a nightmare for a DBA.
>
> But I cannot stress enough how important it is to heed Oracle's warnings to
> not create or alter any objects in the SYS and SYSTEM schemas unless they
> tell you to do so.
>
> RSH.
>
> "Andrei Romazanov" <romazanov.andrei_at_gfos.de> wrote in message
> news:a5ldgr$8d6ll$1_at_ID-70985.news.dfncis.de...
> > Hallo all!
> >
> > how can I use V$-views in trigger other user? I get always an error:
> > PLS-00201 more identifier ' SYS.V_$... ' must declared.
> > I already tried to hide it in a function my_function which I create as SYS
> > and then:
> > create public synonym .... In this case ORACLE says:
> > PLS-00201 more identifier ' SYS.my_function ' must declared
> > (although already public synonym exists!).
> >
> > Does someone have a Idea?
> >
> > Thank you in advance
> >
> >
> >
> >

Adding to the ABove...

Dont Give Rights Via a Role and Give it Directly to the user.

HTH Regards,
Ganesh R Received on Sat Mar 02 2002 - 00:14:27 CST