Re: "grant select" for all tables: Is it possible?

On 12 Aug 2005 07:27:10 -0700, "Mark D Powell" <Mark.Powell_at_eds.com> wrote:

>Jaap, how did you try it? Did you try to issue the grant via execute
>immediate in the DDL trigger or did you call a procedure owned by userA
>or a DBA to issue the grant?
>
>I can believe that the process is not allowed but I am thinking there
>is probably a way around the restriction. If nothing else by
>submitting a job via dbms_job that performs the dirty work.
>
>I wonder when I can take the time to try this?
>
>-- Mark D Powell --

Mark, as I said it was a long time ago, but I think I tried it with EXECUTE IMMEDIATE. This thread made me do some digging though, and I found a Metalink note,

210693.1 "How to Automate Grant Operations When New Objects Are Created in a SCHEMA/DATABASE".

It does the job indirectly (so implicitly Oracle agrees it cannot be done directly).
The gist:
Create a logtable to hold the object identification of the objects that get created .
Create a DDL-trigger that inserts a record in the table when a new object is created and starts a job that reads this record from the table and performs the required grants.

Jaap. Received on Fri Aug 12 2005 - 14:53:07 CDT