ORA-01031: insufficient privileges

Hi,

I've recently installed Oracle10g and I'm testing some of our App DBA scripts but I'm running into "Insufficient Privileges" errors.  In Oracle8i and 9i, I granted our schema owner the following privs:

grant connect to schema_owner;
grant unlimited tablespace to schema_owner;
grant create any trigger to schema_owner;
grant create any procedure to schema_owner;
grant create public database link to schema_owner;
grant drop public database link to schema_owner;
grant create public synonym to schema_owner;
grant drop public synonym to schema_owner;
grant create role to schema_owner;
grant drop any role to schema_owner;

With these grants, specifically the unlimited tablespace, I was able to create any database object as the schema_owner…..sequences, tables, indexes, procs, funcs, trigs, etc.  Besides granting the schema_owner everything under the sun, is there a role in Oracle10g that applies to schema owners ?  Any best practices ?

Thanks,
Frank
timbocAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MikeOM_DBAConnect With a Mentor Commented:

There is no scheduler in 8i and 9i, but you could create a scheduler_admin role with 'CREATE ANY JOB' privilege in these versions.

-- OR --

Temporarily GRANT DBA privilege and after installation revoke DBA.





0
 
MikeOM_DBACommented:

Grant RESOURCE to Schema_Owner;
0
 
timbocAuthor Commented:
Hi Mike,

My schema owner also needs to create jobs.  For Oracle10g, I noticed the role scheduler_admin.   The other important thing is that I need to ensure that these scripts will continue to work in Oracle8i and 9i.

Thanks,
Frank
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
You many want this to give

grant SELECT ANY DICTIONARY to Schema_Owner; because somewhere down the line you may need it because some of your scripts might be trying some data dictionary view of SYS.

Thanks
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
you may also want to try :

SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE

Search for the above words in the below URL:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/privs.htm#14881

Thanks

0
 
sunnypointConnect With a Mentor Commented:
grant create role to schema_owner;
This is not enough.

Try this:
grant create table to schema_owner;
This must be granted from sys schema

If this works for you, I'll explain it.
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.