Oracle 10g – ORA-01031 insufficient privileges

The CONNECT role in Oracle 10g has fewer system privileges than it had in earlier versions of Oracle. When migrating databases from versions below Oracle 10g and especially if the users are recreated in the 10g databases with the privileges from the old version, certain privileges are required to be granted so that the application users can connect to the database with proper privileges. This article discusses this special case on the occurrence of ORA-01031 error in Oracle 10g databases.

The ‘CONNECT’ role in Oracle 10g has only ‘CREATE SESSION’ privilege, whereas the previous version had some additional privileges. If the privileges are missing, the user won’t be able to create objects which will result in ‘ORA-01031 insufficient privileges’ messages to the user.

To fix the issue, grant the 8 privileges listed below to the users.

In Oracle 9i, the CONNECT role had the following privileges:

SQL>  select * from dba_sys_privs  where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO

8 rows selected.

In Oracle 10g, only the ‘CREATE SESSION’ privilege is granted to the CONNECT ROLE.

SQL>  select * from dba_sys_privs  where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL>
Share

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>