DB13: ORA-01031: insufficient privileges

[Former Member]

Hello All,

I entered the t-code DB13 and I have the following error: ORA-01031: insufficient privileges, which I re-searched on the OSS for support and I found this note: Note 1028220 - ORA-01031: Insufficient privileges despite SAPCONN role, which I have verfied all the permssions and roles. Still no luck on accessingt he DB13 t-codes. I tried to execute this SQL Script that is being executed by Db13 in OS using SQL Plus. i still get the same error. I am not sure what permission I am missing here. Could you please tell me what I am exactly missing here.

SELECT beg, funct, sysid, obj, rc, ende, actid,
 line FROM sap_sdbah  WHERE beg BETWEEN
 '20090105000000' AND '20090210235959' AND sysid =
 'BIQ'

SQL> select grantee, granted_role, default_role
  2        from dba_role_privs
  3         where grantee = 'SAPSR3';

GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SAPSR3                         SAPCONN                        YES

I did run this script: sapconn_role.sql to set the permission correctly. No luck.

Note: I logged to the system as <SID>adm user.

Thanks in Advance

Thanks

Kumar

[stefan_koehler · Accepted solution]

Hello Kumar,

please execute the following SQL statement and post the result.

shell> su - ora<SID>
shell> sqlplus "/ as sysdba"
SQL> SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS;

Regards

Stefan

[Former Member · Accepted solution]

Thank you for your quick response.

FYI: We are in Windows 2003 and Oracle.

Here is the Query that you requested for. File is too big, it wouldnt allow me to post. if you need all the queries then I have to post twice. Let me know below information is enough to trouble shoot.

Thanks

O$SURESH-LOCAL\SAPSERVICEBIQ  SAPUSER                        O$SURESH-LOCAL\BIQADM         SAPUSER                                                                                
PUBLIC                         SAP$KSMSP                      SYS                            SAP_$KSMSP                                                                                
PUBLIC                         SAP$KCBFWAIT                   SYS                            SAP_$KCBFWAIT                                                                                
PUBLIC                         SAP$BH                         SYS                            SAP_$BH                                                                                
PUBLIC                         SAP_SDBAH                      SAPSR3DB                       SDBAH                                                                                
PUBLIC                         SAP_SDBAD                      SAPSR3DB                       SDBAD                                                                                
PUBLIC                         SAP_DBSTATC                    SAPSR3DB                       DBSTATC                                                                                
PUBLIC                         SAP_DBSTATTORA                 SAPSR3DB                       DBSTATTORA                                                                                
PUBLIC                         SAP_DBSTATIORA                 SAPSR3DB                       DBSTATIORA                                                                                
PUBLIC                         SAP_DBSTATHORA                 SAPSR3DB                       DBSTATHORA                                                                                
PUBLIC                         SAP_DBSTAIHORA                 SAPSR3DB                       DBSTAIHORA                                                                                
PUBLIC                         SAP_DBCHECKORA                 SAPSR3DB                       DBCHECKORA                                                                                
PUBLIC                         SAP_DBMSGORA                   SAPSR3DB                       DBMSGORA                                                                                
2353 rows selected.

[Former Member · Accepted solution]

Hi Stefan,

I just compared my BIQ System which is giving problem with accessing DB13 with BIP (production system) which works fine.

My BIQ has total rows: 2353 when I execute this SQL Script that you posted. Where as BIP gave me: 2383 rows selected.

Also BIP has the following output:

O$SURESH-LOCAL\SAPSERVICEBIP  SAPUSER                        O$SURESH-LOCAL\BIPADM         SAPUSER                                                                                
PUBLIC                         SAP$KSMSP                      SYS                            SAP_$KSMSP                                                                                
PUBLIC                         SAP$KCBFWAIT                   SYS                            SAP_$KCBFWAIT                                                                                
PUBLIC                         SAP$BH                         SYS                            SAP_$BH                                                                                
PUBLIC                         SAP_SDBAH                      SAPSR3                         SDBAH                                                                                
PUBLIC                         SAP_SDBAD                      SAPSR3                         SDBAD                                                                                
PUBLIC                         SAP_MLICHECK                   SAPSR3                         MLICHECK                                                                                
PUBLIC                         SAP_SAPLIKEY                   SAPSR3                         SAPLIKEY                                                                                
PUBLIC                         SAP_DBAML                      SAPSR3                         DBAML                                                                                
PUBLIC                         SAP_DBARCL                     SAPSR3                         DBARCL                                                                                
PUBLIC                         SAP_DBAFID                     SAPSR3                         DBAFID                                                                                
PUBLIC                         SAP_DBAEXTL                    SAPSR3                         DBAEXTL                                                                                
PUBLIC                         SAP_DBAREOL                    SAPSR3                         DBAREOL                                                                                
PUBLIC                         SAP_DBABARL                    SAPSR3                         DBABARL                                                                                
PUBLIC                         SAP_DBADFL                     SAPSR3                         DBADFL                                                                                
PUBLIC                         SAP_DBAOPTL                    SAPSR3                         DBAOPTL                                                                                
PUBLIC                         SAP_DBASPAL                    SAPSR3                         DBASPAL                                                                                
PUBLIC                         SAP_DBABD                      SAPSR3                         DBABD                                                                                
PUBLIC                         SAP_DBABL                      SAPSR3                         DBABL                                                                                
PUBLIC                         SAP_DBATL                      SAPSR3                         DBATL                                                                                
PUBLIC                         SAP_DBAOBJL                    SAPSR3                         DBAOBJL                                                                                
PUBLIC                         SAP_DBAPHAL                    SAPSR3                         DBAPHAL                                                                                
PUBLIC                         SAP_DBAGRP                     SAPSR3                         DBAGRP                                                                                
PUBLIC                         SAP_DBAERR                     SAPSR3                         DBAERR                                                                                
PUBLIC                         SAP_DBATRIAL                   SAPSR3                         DBATRIAL                                                                                
PUBLIC                         SAP_SVERS                      SAPSR3                         SVERS                                                                                
PUBLIC                         SAP_TGORA                      SAPSR3                         TGORA                                                                                
PUBLIC                         SAP_IGORA                      SAPSR3                         IGORA                                                                                
PUBLIC                         SAP_TSORA                      SAPSR3                         TSORA                                                                                
PUBLIC                         SAP_TAORA                      SAPSR3                         TAORA                                                                                
PUBLIC                         SAP_IAORA                      SAPSR3                         IAORA                                                                                
PUBLIC                         SAP_DD02L                      SAPSR3                         DD02L                                                                                
PUBLIC                         SAP_DD09L                      SAPSR3                         DD09L                                                                                
PUBLIC                         SAP_DDNTT                      SAPSR3                         DDNTT                                                                                
PUBLIC                         SAP_DBCHK                      SAPSR3                         DBCHK                                                                                
PUBLIC                         SAP_DBDIFF                     SAPSR3                         DBDIFF                                                                                
PUBLIC                         SAP_DBSTATC                    SAPSR3                         DBSTATC                                                                                
PUBLIC                         SAP_DBSTATTORA                 SAPSR3                         DBSTATTORA                                                                                
PUBLIC                         SAP_DBSTATIORA                 SAPSR3                         DBSTATIORA                                                                                
PUBLIC                         SAP_DBSTATHORA                 SAPSR3                         DBSTATHORA                                                                                
PUBLIC                         SAP_DBSTAIHORA                 SAPSR3                         DBSTAIHORA                                                                                
PUBLIC                         SAP_DBCHECKORA                 SAPSR3                         DBCHECKORA                                                                                
PUBLIC                         SAP_DBMSGORA                   SAPSR3                         DBMSGORA

and BIQ has the following output:

O$SURESH-LOCAL\SAPSERVICEBIQ  SAPUSER                        O$SURESH-LOCAL\BIQADM         SAPUSER                                                                                
PUBLIC                         SAP$KSMSP                      SYS                            SAP_$KSMSP                                                                                
PUBLIC                         SAP$KCBFWAIT                   SYS                            SAP_$KCBFWAIT                                                                                
PUBLIC                         SAP$BH                         SYS                            SAP_$BH                                                                                
PUBLIC                         SAP_SDBAH                      SAPSR3DB                       SDBAH                                                                                
PUBLIC                         SAP_SDBAD                      SAPSR3DB                       SDBAD                                                                                
PUBLIC                         SAP_DBSTATC                    SAPSR3DB                       DBSTATC                                                                                
PUBLIC                         SAP_DBSTATTORA                 SAPSR3DB                       DBSTATTORA                                                                                
PUBLIC                         SAP_DBSTATIORA                 SAPSR3DB                       DBSTATIORA                                                                                
PUBLIC                         SAP_DBSTATHORA                 SAPSR3DB                       DBSTATHORA                                                                                
PUBLIC                         SAP_DBSTAIHORA                 SAPSR3DB                       DBSTAIHORA                                                                                
PUBLIC                         SAP_DBCHECKORA                 SAPSR3DB                       DBCHECKORA                                                                                
PUBLIC                         SAP_DBMSGORA                   SAPSR3DB                       DBMSGORA

The owner for BIQ has SAPSR3DB (which is Java schema) not sure how that happen. Where as BIP seems like fine with SAPSR3 schema owner. Looks like I need to change the owner ship of the tables from SAPSR3DB to SAPSR3 of those above BIQ tables. How do you change the owner ship for above rows?

My next question would be, what I do to those missing values in BIQ?

Please advice.

Thanks in Advance.

Kumar

[stefan_koehler · Accepted solution]

Hello Kumar,

thanks for the output - it helps alot.

> FYI: We are in Windows 2003 and Oracle.

Yes, but which Oracle version?

Please check sapnote #834917 (for the sql script) and execute it for the user SAPSR3DB (Java) and SAPSR3 (ABAP) - then try it again.

>Looks like I need to change the owner ship of the tables from SAPSR3DB to SAPSR3

No, you maybe need to change the PUBLIC synoym - but please try the solution with sapnote #834917 first.

The other thing you can check is:

SQL>desc SAPSR3.SDBAH
SQL>desc SAPSR3DB.SDBAH

Regards

Stefan

[Former Member · Accepted solution]

I need to apply the solution 834917 to both user: SAPSR3 and SAPSR3DB correct?

Example:

SQL> revoke connect, resource, select_catalog_role from <SAP USER>;

SQL> grant sapconn, unlimited tablespace to <SAP USER>;

to run above script do you to login as sysdba or SAP USER (SAPSR3, and SAPSR3DB). Can please tell me this?

thanks

[Former Member · Accepted solution]

Another thing. When I run the following DB13 SQL Script at OS Level via SQL PLUS.

SELECT beg, funct, sysid, obj, rc, ende, actid,
 line FROM sap_sdbah  WHERE beg BETWEEN
 '20090105000000' AND '20090210235959' AND sysid =
 'BIQ';

I ran this first as SYSDBA - it works fine

Next I ran the script as SAPSR3 - ORA-01031: insufficient privileges

third I ran as SAPSRDB - which I was able to run the script as well.

Let me know what you think? should I change the permission to SAPSR3

thanks

[stefan_koehler · Accepted solution]

Hello Kumar,

your tests are correct, but will not solve the original problem.

Please tell us your oracle version and post the output of the following:

SQL>desc SAPSR3.SDBAH
SQL>desc SAPSR3DB.SDBAH

There are some sap notes where the synonyms are created for the wrong schema (#692494).

Do you also have executed the sapdba_role script from note #134592 (there is also a cross reference in the note #834917 which i mentioned before)?

Please download the correct script for your oracle version and execute it like described.

> to run above script do you to login as sysdba or SAP USER (SAPSR3, and SAPSR3DB). Can please tell me this?

Just execute "sqlplus /nolog @sapconn_role" - there is an explicit login as sysdba in the script.

Regards

Stefan

[Former Member · Accepted solution]

Thanks again.

oracle version: 10.2.0.2

SQL> desc SAPSR3.SDBAH;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BEG                                       NOT NULL VARCHAR2(42)
 FUNCT                                     NOT NULL VARCHAR2(9)
 SYSID                                     NOT NULL VARCHAR2(24)
 OBJ                                       NOT NULL VARCHAR2(48)
 RC                                        NOT NULL VARCHAR2(12)
 ENDE                                      NOT NULL VARCHAR2(42)
 ACTID                                     NOT NULL VARCHAR2(48)
 LINE                                      NOT NULL VARCHAR2(762)

SQL> desc SAPSR3DB.SDBAH;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BEG                                       NOT NULL VARCHAR2(42)
 FUNCT                                     NOT NULL VARCHAR2(9)
 SYSID                                     NOT NULL VARCHAR2(24)
 OBJ                                       NOT NULL VARCHAR2(48)
 RC                                        NOT NULL VARCHAR2(12)
 ENDE                                      NOT NULL VARCHAR2(42)
 ACTID                                     NOT NULL VARCHAR2(48)
 LINE                                      NOT NULL VARCHAR2(762)

I have followed both note: 134592 (there is also a cross reference in the note #834917) and I have excuted those sapconn_role.sql and sapdba_role.sql (ran this as both schema (SR3, and SR3DB). Still no luck,

still with same error.

Thanks

Kumar

[stefan_koehler · Accepted solution]

Hello Kumar,

ok in this case it seems like the problem, that the PUBLIC synonym was created for the wrong schema.

Now i have to say that i am not very familiar with the BR*Tools, but here is such an option (-f crsyn):

http://help.sap.com/saphelp_nw04/helpdata/en/5a/4a704ba2308a4db5a4d36e39e7d05a/content.htm

Regards

Stefan

[Former Member · Accepted solution]

Thank you Stefan so much for your help. your link did help to resolve the issue. And you're correct about Public Synonym.

This what I had to do eh.

BRCONNECT options for creation of SAP/DBA synonyms

 1 - BRCONNECT profile (profile) ......... [initBIQ.sap]
 2 - Database user/password (user) ....... [system/********]
 3 ~ Database owner for synonyms (owner) . [SAPSR3]
 4 ~ Log file name (log) ................. []
 5 - Message language (language) ......... [E]
 6 - BRCONNECT command line (command) .... [-p initBIQ.sap -l E -f crsyn -o SAPSR3]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

[Former Member · Accepted solution]

Thank you Stefan,

my issue is also solved with your solution.

For others:

This error occurs in a mcod database.

we need to compare the tables sdbad, sdbah under both sap schemas.

desc SAPSR3.SDBAH;

desc SAPSR3DB.SDBAH;

Also check the below queries. The PUBLIC synonym was created for the wrong schema.

select synonym_name, table_owner, table_name from dba_synonyms where table_name like 'SDBAD';

select synonym_name, table_owner, table_name from dba_synonyms where table_name like 'SDBAH';

SYNONYM_NAME                   TABLE_OWNER

------------------------------ -----------------------------

SAP_SDBAH                      SAPSR3DB

SAP_SDBAD                      SAPSR3DB

Regards,

Mohan.

[Former Member · Accepted solution]

Thank you so much Stefan, this has saved my day!