Note 834917 - Oracle Database 10g: New database role SAPCONN

Version / Date	31 / 2010-08-24
Priority	Recommendations/additional info
Category	Installation information
Primary Component	BC-DB-ORA Oracle
Secondary Components	BC-DB-ORA-DBA Database Administration with Oracle

Summary

Symptom Database role SAPCONN This note describes the use of the SAP-specific database role SAPCONN. If, after you upgrade your database from Oracle Release 9.2 or lower to Oracle Release 10.2 or higher,  you receive the error message "ORA-01031:  insufficient privileges" when starting SAP, first check whether the role SAPCONN has been installed and assigned correctly and also that it is active (Note 1028220). Validity of this note The adjustments concerning the use of the SAPCONN role that are described in this note are optional for Oracle Releases 9.2 and 10.1, but they are mandatory as of Release 10.2. Other terms CONNECT, RESOURCE, DBA dba_role_privs, dba_sys_privs, dba_tab_privs user_role_privs, user_sys_privs, user_tab_privs role_role_privs, role_sys_privs, role_tab_privs sapdba.sql, sapconn_role.sql ORA-01031: insufficient privileges session_privs, session_roles Reason and Prerequisites For Oracle Release 10.1 and earlier releases, the CONNECT role included extensive database authorizations. As of Release 10.2, Oracle restricts the CONNECT role to the CREATE SESSION privilege for safety reasons (see chapter 7: Security Policies in Oracle Database 10g Security Guide). Experience has shown that the CONNECT role is assigned to most database users as a sort of minimum role so that they could log on to the database. However, the less comprehensive CREATE SESSION authorization is sufficient to log on to the database. We assume that the incorrect use of the role which leads to an excessive number of users with too many authorizations is due to the name of the role, CONNECT, in relation to the "connect <user>/<pwd>" SQL command. This should be prevented for safety reasons. We recommend that you define application-specific database roles. In higher Oracle releases, both the CONNECT and RESOURCE roles no longer exist. You should therefore no longer use either role in future. As of Oracle Release 10.2, SAP database users only use application-specific database roles (SAPDBA, SAPCONN). Solution Contents of this note SAPCONN application-specific database role The role schema for database users up to Oracle Release 9.2/10.1 The role schema for database users as of Oracle Release 10.2 Installing the SAPCONN role Adjusting the authorizations for <SAP USER> Adjusting the SAPDBA role Adjusting the authorizations for <OPS$-USER> Appendix Monitoring of database roles and database system authorizations System privileges of the CONNECT role up to 10.1 System privileges of the CONNECT role as of 10.2 SAPCONN application-specific database role As of Oracle Release 10.2, you must assign only the SAP-specific database role SAPCONN to a SAP database user, instead of the individual Oracle roles CONNECT, RESOURCE, SELECT_CATALOG_ROLE. The authorizations contained in the Oracle roles CONNECT, RESOURCE, and SELECT_CATALOG_ROLE have been integrated into the SAPCONN roles. The role SAPCONN was adjusted especially to the requirements of the SAP application and contains all the required database authorizations. You cannot assign the system privilege "UNLIMITED TABLESPACE" to a role (ORA-01931: cannot grant UNLIMITED TABLESPACE to a role) and it is therefore not contained in the role SAPCONN. It must be assigned separately. The SAPCONN role is independent from the SAP release you use. Definitions used In the following, <SAP USER> specifies the SAP database user for an SAP application schema: SAP<SCHEMA_ID> or SAPR3 for the ABAP stack SAP<SCHEMA_ID>DB for the Java stack <OPS$-USER> specifies the following database users (the users exist depending on the relevant platform of the database server): OPS$ORA<DBSID> OPS$<SAPSID>ADM OPS$<DOMAIN>\SAPSERVICE<SID> or OPS$SAPSERVICE<SID> OPS$<DOMAIN>\<SID>ADM The role schema for database users up to Oracle Release 9.2/10.1 Role assignment up to Release 9.2/10.1: <SAP USER>:  CONNECT, RESOURCE, SELECT_CATALOG_ROLE <OPS$-USER>: CONNECT, RESOURCE, SAPDBA Quota assignment up to Release 9.2/10.1: <SAP USER>: unlimited tablespace <OPS$-USER>: unlimited tablespace The role schema for database users as of Oracle Release 10.2 Role assignment as of Release 10.2: <SAP USER>: SAPCONN <OPS$-USER>: SAPDBA Quota assignment as of Release 10.2: <SAP USER>: unlimited tablespace <OPS$-USER>: unlimited tablespace Installing the SAPCONN role To create the SAPCONN role, use the sapconn_role.sql script, which includes an installation instruction and is contained in the <SAPEXE> directory. This script is also attached to this note. Install the SAPCONN role in the database with the following command: OS> sqlplus /nolog @sapconn_role Adjusting the authorizations for <SAP USER> The SAP system should be stopped for these changes. The Oracle roles CONNECT and RESOURCE are taken away from the <SAP USER> database users. When you use 'revoke resource', the system authorization 'UNLIMITED TABLESPACE' is automatically revoked at the same time. For this reason, this authorization must be granted again afterwards. SQL> revoke connect, resource, select_catalog_role from <SAP USER>; SQL> grant unlimited tablespace to <SAP USER>; With the following command, the required database authorizations are granted to SAP database users through the role SAPCONN: SQL> grant SAPCONN to <SAP USER>; Adjusting the SAPDBA role The SAPDBA role must also be modified slightly for Oracle Release 10.2. Note 134592 contains detailed information about installing and adjusting the new SAPDBA role (see script sapdba_role.sql in the <SAPEXE> directory. Adjusting the authorizations for <OPS$-USER> The authorizations of the OPS$ users are adjusted as follows: SQL> revoke connect, resource from <OPS$-USER>; SQL> grant unlimited tablespace to <OPS$-USER>; The assignment of the SAPDBA role to <OPS$-USER> occurs implicitly during the execution of the script sapdba_role.sql. Therefore, you do not need to explicitly execute the following command again. SQL> grant SAPDBA to <OPS$-USER>; Appendix Monitoring of database roles and database system authorizations Which default roles are available to the <SAP USER>? SQL> connect <SAP USER>/<pwd> SQL> select * from session_roles; The result should look as follows: ROLE ---------------------- SAPCONN SELECT_CATALOG_ROLE HS_ADMIN_ROLE Which system privileges are available to the <SAP USER>? SQL> connect <SAP USER>/<pwd> SQL> select * from session_privs; The result should look as follows: PRIVILEGE: ---------------------- CREATE SESSION ALTER SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SYNONYM CREATE VIEW CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER ANALYZE ANY CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE How is the SAPCONN role defined? SQL> select granted_role from role_role_privs where role = 'SAPCONN'; SQL> select privilege    from role_sys_privs  where role = 'SAPCONN'; SQL> select *            from role_tab_privs  where role = 'SAPCONN'; How is the CONNECT role defined? SQL> select privilege    from role_sys_privs  where role = 'CONNECT'; New view DBA_CONNECT_ROLE_GRANTEES: The DBA_CONNECT_ROLE_GRANTEES view displays to which database users the CONNECT role is assigned. This view should not display any SAP database users. SQL> select * from DBA_CONNECT_ROLE_GRANTEES      WHERE GRANTEE like 'SAP%'         OR GRANTEE like 'OPS$%'; To what was the SAPCONN or SAPDBA role assigned? SQL> select grantee, granted_role     from dba_role_privs      where granted_role in ('SAPDBA', 'SAPCONN'); Which roles or system authorizations are assigned to an SAP database user? SQL> select * from dba_role_privs where grantee = '<SAP USER>'; GRANTEE  GRANTED_ROLE           ADM DEF -------- ---------------------- --- --- SAPSR3  SAPCONN                NO  YES SQL> select * from dba_sys_privs where grantee = '<SAP USER>'; GRANTEE  PRIVILEGE             ADM -------- --------------------- --- SAPSR3   UNLIMITED TABLESPACE  NO System privileges of the CONNECT role up to 10.1 CREATE VIEW CREATE TABLE ALTER SESSION CREATE CLUSTER CREATE SESSION CREATE SYNONYM CREATE SEQUENCE CREATE DATABASE LINK System privileges of the CONNECT role as of 10.2 CREATE SESSION

Related Notes
1256322	Establishing a remote database connection in DBACOCKPIT
1078293	DBACockpit: Submonitor Workload Reporting
1028220	ORA-01031: Insufficient privileges despite SAPCONN role
985607	ORA-01031 Creating views after upgrade to Oracle 10g
963760	'ORA-20000: Insufficient privileges' for creating statistics
700548	FAQ: Oracle authorizations
134592	Importing the SAPDBA role (sapdba_role.sql)