Re: Creating views with procedures, help!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 10 Jul 1998 14:59:22 GMT
Message-ID: <35a82bd0.7600018@192.86.155.100>

A copy of this was sent to "Jose M." <jmjulia_at_arrakis.es> (if that email address didn't require changing) On Wed, 08 Jul 1998 16:06:01 +0200, you wrote:

>Hi!
>
>I've got a problem programming in PL/SQL and I hope someone can give me some
>clues...
>
>I have a procedure which creates a view. It does this creating a string like
>"Create view ... as select * ... etc.", and executing that string using dbms
>commands. But I want some other users to use the procedure, so I've created a
>public synonym on it, and I've granted execution on it to that users.
>
>Suppose the procedure's owner is called vader, and another user is called
>luke. When luke uses the procedure to create that view, it's created ok, but
>its owner is vader, like the procedure! And I want it to be luke's, because
>it's luke who runs the procedure and he should be the owner of that view,
>isn't it? How can I do it?
>
>I've tried to change the "create view my_view" statement inside the procedure
>to "create view luke.my_view.." but it doesn't work... any tip?
>
>Thanx in advance!!!

It works but the owner of the procedure needs the CREATE ANY VIEW privelege. for example:

SQL> create or replace procedure create_view( view_name in varchar2,

  2                                           the_query in varchar2 )
  3  as
  4      exec_cursor     integer default dbms_sql.open_cursor;
  5      rows_processed  number  default 0;
  6  begin
  7          dbms_sql.parse(exec_cursor,
  8         'create view ' || user || '.' || view_name || ' as ' || the_query,
  9                                          dbms_sql.native );
 10          rows_processed := dbms_sql.execute(exec_cursor);
 11          dbms_sql.close_cursor( exec_cursor );
 12  exception
 13      when others then
 14        if dbms_sql.is_open(exec_cursor) then
 15          dbms_sql.close_cursor(exec_cursor);
 16        end if;
 17        raise;

 18 end;
 19 /

Procedure created.

SQL> grant execute on create_view to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> exec demo.create_view( 'testing_view', 'select * from emp' ); begin demo.create_view( 'testing_view', 'select * from emp' ); end;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "DEMO.CREATE_VIEW", line 17
ORA-06512: at line 1

SQL> connect sys/xxxxx
Connected.
SQL> grant create any view to demo;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> select * from testing_view;
select * from testing_view

              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> exec demo.create_view( 'testing_view', 'select * from emp' );

PL/SQL procedure successfully completed.

SQL> select * from testing_view;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------

---

      7369 SMITH CLERK 7903 17-DEC-80 800 20

      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 800 30

so, once the owner of the procedure has the CREATE ANY VIEW command, it'll work. be careful with the CREATE ANY type of priveleges -- they can be powerfull...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jul 10 1998 - 09:59:22 CDT