DB2 - Problembeschreibung
Problem IT07092 | Status: Geschlossen |
REVOKE ROLE STATEMENT FAILS WITH SQL0901N IN ORACLE COMPAT WITH "SQLRI_DDL_COMMON: UNEXPECTED SQLCODE FOR DDL!". | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
A REVOKE ROLE operation will fail with an SQL0901N error in ORACLE compatibility database with a reason "sqlri_ddl_common: unexpected sqlcode for DDL!" message. The error occurs when the DBMS_STANDARD.DBMS_STANDARD_RAISE_APPLICATION procedure was previously granted a permission to the role. The problem is easily reproducible: db2set DB2_COMPATIBILITY_VECTOR=ORA db2 create db db105ora db2 connect to db105ora user myuser1 db2 create role myrole1 db2 grant role myrole1 to user myuser2 db2 grant EXECUTE ON SPECIFIC PROCEDURE SYSIBMADM.DBMS_STANDARD.DBMS_STANDARD_RAISE_APPLICATION_ERROR to role myrole1 db2 grant EXECUTE ON SPECIFIC PROCEDURE SYSIBMADM.DBMS_STANDARD.DBMS_STANDARD_RAISE_APPLICATION_ERROR to user myuser2 db2 revoke role myrole1 from user myuser2 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0901N The SQL statement or command failed because of a database system error. (Reason "sqlri_ddl_common: unexpected sqlcode for DDL!".) SQLSTATE=58004 The db2diag.log will contain entries similar to: 2015-02-11-09.37.57.873019-300 E2447E1584 LEVEL: Info (Origin) PID : 516 TID : 140736976643840 PROC : db2sysc INSTANCE: dsciaraf NODE : 000 DB : DB105ORA APPHDL : 0-7 APPID: *LOCAL.dsciaraf.150211142508 AUTHID : DSCIARAF HOSTNAME: hotellnx108 EDUID : 18 EDUNAME: db2agent (DB105ORA) FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650 MESSAGE : ZRC=0x80040001=-2147221503=SQLD_NOREC "NO MORE RECORDS FOUND ON FETCH" DIA8000C An unexpected end of file was reached "". DATA #1 : String, 62 bytes An unexpected error was detected during statement compilation. DATA #2 : Boolean, 1 bytes false DATA #3 : Boolean, 1 bytes false DATA #4 : Boolean, 1 bytes true DATA #5 : Boolean, 1 bytes false DATA #6 : Hex integer, 4 bytes 0x00000000 DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: 100 sqlerrml: 0 sqlerrmc: sqlerrp : SQLRL5A4 sqlerrd : (1) 0x80040001 (2) 0x00000001 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: DATA #8 : Hex integer, 4 bytes 0x00000040 DATA #9 : String with size, 40 bytes revoke role daverole1 from user dsciara2 DATA #10: String, 161 bytes Compiler error stack for rc = -2147221503: sqlnn_cmpl[300] sqlnp_main[250] sqlnp_parser[510] sqlnp_smactn[100] sqlnq_auth_role_stmt[120] sqlnq_auth_stmt_end[50] In retrospect, the ability to GRANT and REVOKE privileges from routines within an oracle module does not really make sense, and instead privileges should be GRANTED and REVOKED only to the module itself. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to inclusive fixpack. * **************************************************************** | |
Local-Fix: | |
Revoke EXECUTE permission on SYSIBMADM.DBMS_STANDARD.DBMS_STANDARD_RAISE_APPLICATION_ERROR, and then try to revoke the role. | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 11.02.2015 22.01.2016 22.01.2016 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.5.0.7 |