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

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
>
>
>
>
Received on Thu Feb 28 2002 - 08:53:28 CST