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>
