Re: DDL and Dynamic SQL

From: <oren_at_gsit.co.il>
Date: 1997/10/22
Message-ID: <877517315.32450@dejanews.com>#1/1

In article <344CDA16.5DC6_at_bnr.ca>,
  Ken Denny <kendenny_at_bnr.ca> wrote:
>
> Oren Nakdimon wrote:
> >
> > Nasier.
> > Grant CREATE ANY VIEW to MAIN_USER.
> >
> > Because, when USER1 is executing TEST_SQL, it is like being executed by
> > MAIN_USER, so MAIN_USER is trying to create a view in USER1's schema, and
> > for doing that MAIN_USER needs a privilege to create a view in other user's
> > schema.
> >
> > USER1 needs only the "execute on test_sql" privilege. He doesn't need the
> > "create view" and the "execute on dbms_sql".
>
> Another note: If TEST_SQL does a "SELECT user FROM dual INTO
> current_user;" it will get "MAIN_USER". The process which calls TEST_SQL
> should pass the user name in as a parameter.
>
> Hope this helps
> Ken Denny
> kendenny_at_bnr.ca (work)
> kdenny_at_interpath.com (home)

Although TEST_SQL is supposedly executed by MAIN_USER, the pseudo-column USER contains the "real" executing user, i.e. USER1, so the original code is correct. You can however use USER directly instead of select it from

dual. -- ==================================================== Oren

Nakdimon Golden Screens Ltd. address: 17 Abba-Hillel st., Ramat-Gan 52522, ISRAEL email: oren_at_gsit.co.il tel: +972-3-7510836 fax:

+972-3-7518221 ====================================================

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Wed Oct 22 1997 - 00:00:00 CDT