| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: DBMS_SQL: Problem with Some DDL's (create view ....)
code that executes in a stored procedure executes with the base priveleges of the owner of the procedure. Base priveleges are those that are granted directly to that person OR to public. Roles and all priveleges from roles are always disabled in stored procedure.
You and the DBA have the 'create view' privelege via a role. To correct this issue, simply "grant create view to THE-PROCEDURE-OWNER".
On Thu, 16 Jan 1997 00:31:34 GMT, vahidt_at_dbsun.vitek.com (Adam Tadj) wrote:
>I am trying to to execte a DDL module using SQL_DMS.PARSE, but have'nt
>had any luck yet. My intention is to use it for the creating views
>dynamically. There error says "insufficient privileges", but we tried
>it from the DBA account and got the same result. Here is the PL/SQL:
>
>PROCEDURE test_view_p IS
> cursor_handle INTEGER;
> exec_var INTEGER;
>BEGIN
> cursor_handle := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_handle,
> 'CREATE VIEW ops$a_user.a_view (a_col) AS SELECT hr '||
> 'FROM ops$a_user.a_table',
> DBMS_SQL.NATIVE);
> DBMS_SQL.CLOSE_CURSOR (cursor_handle);
>END;
>
>and here is the error messages I get when I run it from SQL>:
>SQL> EXECUTE test_view_p;
>begin test_view_p; end;
>*
>ERROR at line 1:
>ORA-01031: insufficient privilages
>ORA-06512: at "SYS.DBMS_SYS_SQL" line 239
>ORA-06512: at "SYS.DBMS_SQL", line 25
>ORA-06512: at "OPS$A_USER.TEST_VIEW_P, line 8
>ORA-06512: at line 1
>
>
>I would like to mention I can create an index on a table or run a
>SELECT or UPDATE commands with this built in procedure without any
>problem. This only occurs when creating a view or a table and that
>when it is executed from a procedure. If I ran the code interactively
>(declare .begin ....) it works fine. There was no Oracle
>documentation on this built in procedure and I read about it from a
>third party books.
>
>Your prompt assistance will be highly appreciated!
>Thanks in advance.
>
>Adam T.
>
>
>
>
>
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |