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. |
