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.
--- Kevin Lange <[EMAIL PROTECTED]> wrote:
> I made sure all my authorities on everything that I
> thought they were
> required on was set. But I am still getting the
> errors below.
>
> Does anyone have any ideas why the error would be
> occuring ?? I really need
> to get this fixed.
>
> Kevin
>
> -----Original Message-----
> Sent: Friday, September 21, 2001 3:25 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Gang;
> I have a piece of SQL that generates a SQL
> statement that will create a
> view for a user.
>
> On our DEV machine the user can use it just fine.
> But, on our Test machine
> they keep getting the error :
>
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "PCM$.CALCFEESCHEDGETDATA", line 71
> ORA-06512: at line 1
>
> I looked at all the security I could think of:
> select on all the mentioned tables by the owner
> select any table granted by sys
> create view granted by sys
> create any view granted by sys
> execute on dbms_sql and dbms_sys_sql granted by
> sys
>
> Nothing works.
>
>
> HELP.--------- Any suggestions on where to go now
> ??
>
> Thanks
>
> Kevin
>
> -----Original Message-----
> Sent: Friday, September 21, 2001 2:17 PM
> To: Multiple recipients of list ORACLE-L
>
>
> How are your permissions?
>
>
>
>
>
> jacinth
>
> <jacinth To:
> Multiple recipients of list
> ORACLE-L
> @mail.ev1.net
> <[EMAIL PROTECTED]>
>
> > cc:
>
> Sent by: root Subject:
> setting up HACMP and
> Oracle 8.1.6
>
>
>
>
> 09/21/2001
>
> 02:20 PM
>
> Please
>
> respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
> I hope someone may be able to shed some light on
> this. Oracle does not
> seem to have much information on setting up failover
> on HA, other than
> OPS (which we are not doing). I am not an HA
> expert, so I may just be
> missing something or not asking right questions.
>
> HW: IBM S7A
> OS: AIX 4.3.3
> Oracle 8.1.6
>
> Scenario: Two nodes, one set up production, the
> other test/batch. We
> are trying to configure so that when production goes
> down, it fails over
> to node B, shutting down test/batch in process.
> Oracle is installed on
> each node, but in different directories.
>
> Problem: On failover to node B, there are problems
> starting up DB, and
> a noteable failure to start the listener. Listener
> log not much help.
> HACMP log shows:
>
> Could not load program oracleeml:
> Dependent module libobk.a(shr.o) could not
> be loaded.
> Could not load module libobk.a(shr.o).
> Error was: No such file or directory
>
> The file does exist.
>
> Anyone have ideas?
>
>
> -Denise Horton-
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: jacinth
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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:
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
=====
Eng. Christian Trassens
Senior DBA
Systems Engineer
[EMAIL PROTECTED]
[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
