Thanks for your reply, I appreciate that.
All what you asked for is exactly done. Look at the SYSTEM user's privs. Like I said when executing the actual statement in SQLPLUS everything works except when it is executed in PL/SQL proc.
GRANT DBA TO SYSTEM;
GRANT CONNECT TO SYSTEM;
GRANT RESOURCE TO SYSTEM;
GRANT CONNECT_MTN TO SYSTEM;
GRANT EXP_FULL_DATABASE TO SYSTEM;
GRANT IMP_FULL_DATABASE TO SYSTEM;
GRANT SELECT_CATALOG_ROLE TO SYSTEM;
GRANT EXECUTE_CATALOG_ROLE TO SYSTEM;
GRANT AQ_ADMINISTRATOR_ROLE TO SYSTEM;
GRANT GATHER_SYSTEM_STATISTICS TO SYSTEM;
ALTER USER SYSTEM DEFAULT ROLE ALL;
GRANT ANALYZE ANY TO SYSTEM;
GRANT ALTER ANY TYPE TO SYSTEM;
GRANT ALTER ANY INDEX TO SYSTEM;
GRANT ALTER ANY TABLE TO SYSTEM;
GRANT EXECUTE ANY TYPE TO SYSTEM;
GRANT SELECT ANY TABLE TO SYSTEM;
GRANT ALTER ANY INDEXTYPE TO SYSTEM;
GRANT ALTER ANY PROCEDURE TO SYSTEM;
GRANT DEBUG ANY PROCEDURE TO SYSTEM;
GRANT UNLIMITED TABLESPACE TO SYSTEM;
GRANT EXECUTE ANY INDEXTYPE TO SYSTEM;
GRANT EXECUTE ANY PROCEDURE TO SYSTEM;
GRANT SELECT ANY DICTIONARY TO SYSTEM;
Regards,
Deon
-----Original Message-----
From: Deutsch Gerald [mailto:oracledba-ezmlmshield-x407275.[Email address protected]
Sent: Thursday, 30 September 2004 01:56 PM
To: LazyDBA Discussion
Subject: AW: ORA-01031: insufficient privileges
Hi Deon,
Are you executing those procedures as SYSTEM? Afaik SYSTEM has his privieges
granted via the DBA role. Remember, you'll have to grant create table, alter
table, drop table privileges directly to the user to do DDL from PLSQL.
Moreover you'll also need the EXECUTE (ANY) PROCEDURE privilege granted. You
should also check, if said procedure is executed whith INVOKER or DEFINER
rights.
Regards,
gerald
-----Ursprüngliche Nachricht-----
Von: Deon Bouwer [ MTN - Innovation Centre ]
[mailto:oracledba-ezmlmshield-x46756413.[Email address protected]
Gesendet: Thursday, September 30, 2004 1:18 PM
An: LazyDBA Discussion
Betreff: ORA-01031: insufficient privileges
Hi gurus,
I am having a little problem where I have some kind of procedures where
they are managing the partition tables for me but they don't complete
because of an ORA-01031: insufficient privileges Oracle error.
The SYSTEM user own the procs and try to manage other schema's partition
tables. Explicitly SYSTEM user do have alter any table and alter any
index as well as indextype etc granted.
I used a 9.2.0.4 database. Why is the privileges still a problem? When I
executed it manually it is working fine except when it is executed in a
proc. Sample of a SQL statement.
ALTER TABLE SMS.CDR_SMS DROP PARTITION P_20040628 UPDATE GLOBAL INDEXES
Any help would be appreciated.
Regards,
Deon Bouwer
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/default.aspx?pid=34411
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page