Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_SQL to Create views - PROBLEM SOLVED

RE: DBMS_SQL to Create views - PROBLEM SOLVED

From: Kevin Lange <kgel_at_ppoone.com>
Date: Mon, 24 Sep 2001 11:07:22 -0700
Message-ID: <F001.00396A9A.20010924111037@fatcity.com>

Thomas;
  First, I am not creating it IN SYS, I am just compiling it by SYS because I thought the priviledges would then be set for running the package by anyone.

I was wrong.

Here is the scenario:

  1. Created package as PCM$. PCM$.CALCFEESCHEDGETDATA.CREATEMDRVIEW
  2. Compile package using SYS.
  3. Grant select on underlying PCM$ tables to RRS by PCM$.

I forgot the most important step whieh your note eluded to ....... Grant Create Any View to PCM$. Once I did this, the program worked just fine. Since it was owned by PCM$, that ID needed the Create View. It was Independed of who compiled it apparently.

Thank you ALL for steering me in the right direction !!

Kevin

-----Original Message-----
Sent: Monday, September 24, 2001 12:33 PM To: 'ORACLE-L_at_fatcity.com'
Cc: 'kgel_at_ppoone.com'

Kevin,

I would say start over.

First, it is *very* bad practice to create and deploy *any* code within the SYS or SYSTEM account. These accounts should be restricted to Oracle software only. With the view being created in the SYS system, you might actually be attempting to create the view with the SYS account - probably something you do not want to do anyway.

I just created the following procedure under my DBA account. This account has DBA role, and is the repository for the schema for the database (all tables, views etc reside here). I needed to grant "create any view" to the account to get this to work, but it did work.

I also tested this proc by running it from another account within the same database. That account needs to have been granted at least 'select' on the table that the view is based on to be allowed to create the view. It also then needs to be granted 'select' access to be able to use the view after it had been created. It does not, however, require any other system privs to work (like create any view, etc.).

hope this helps.

CREATE OR REPLACE PROCEDURE cr_view(in_table_name IN USER_TABLES.table_name%TYPE) IS
BEGIN BEGIN
   EXECUTE IMMEDIATE 'drop view view_'||in_table_name;

     EXCEPTION
            WHEN OTHERS THEN NULL;

END; EXECUTE IMMEDIATE 'create view wtwdba.view_'||in_table_name ||
                    ' as select * from wtwdba.' || in_table_name;
END; Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Monday, September 24, 2001 1:40 PM To: Multiple recipients of list ORACLE-L

I had originally created the Procedure under SYS after first having PCM$ granting SELECT explicitly on the tables involved. I made sure SYS could CREATE ANY VIEW . On both the DEV and the TEST instances I DIRECTLY granted select on the tables to the ID RRS.

On the DEV instance RRS can run the procedure and it will create the view.

On the TEST instance RRS gets the error of Insufficient privileges .

I thought maybe it was that RRS could not run the DBMS_SQL routines so I granted execute directly from sys to RRS on those. No dice.

I am STUMPED at this point.

Kevin

-----Original Message-----
Sent: Monday, September 24, 2001 12:16 PM To: Multiple recipients of list ORACLE-L

Have you granted explicitly to the user ?. How about AUTHID ?. Have you created the procedure with the default value of this clause ?.

Regards.


Eng. Christian Trassens
Senior DBA
Systems Engineer
ctrassens_at_yahoo.com
ctrassens_at_hotmail.com
Phone : 541149816062

Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christian Trassens
  INET: ctrassens_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: kgel_at_ppoone.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: kgel_at_ppoone.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Sep 24 2001 - 13:07:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US