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

On 1 Mar 2002 22:14:27 -0800, ganesh_at_gtfs-gulf.com (Ganesh Raja) wrote:

>"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.
>>

In addition to the above problems with PUBLIC synonyms, i/o via a synonym has a performance penalty. PUBLIC synonyms have a very high penalty. If you must use synonyms, you need to keep them in the schema as much as possible.
>> 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 - 23:27:03 CST