Workspace warning ORA-01927: Cannot REVOKE Privileges You Did Not Grant while upgrade to 12.1.0.1/12.1.0.2 (Doc ID 2266838.1)

Last updated on NOVEMBER 20, 2019

Applies to:

Symptoms

While upgrading the database to 12.1.0.1/12.1.0.2 a warning is raised and the upgrade process did not complete all 73 phases automatically. 

This upgrade failed after Phase #:69 and did not run 70,71,72,73.

<>

------------------------------------------------------
Phases [0-73] End Time:[2017_05_11 16:02:46]
------------------------------------------------------

*** WARNING: ERRORS FOUND DURING UPGRADE ***

Due to errors found during the upgrade process, the post upgrade actions in catuppst.sql have not been automatically run.

*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***

1. Evaluate the errors found in the upgrade logs
and determine the proper action.
2. Execute the post upgrade script as described in Chapter 3
of the Database Upgrade Guide.

REASON:
catuppst.sql unable to run in Database: orcl  Id: 0
ERRORS FOUND: during upgrade CATCTL ERROR COUNT=2
------------------------------------------------------
Identifier OWM 17-05-11 03:34:02 Script = /u01/12.1.0.2/db/rdbms/admin/sqlsesss
ERROR = [ORA-01927: cannot REVOKE privileges you did not grant ORA-06512: at line 14
ORA-06512: at line 38

<>

catupgrd0.log

<>

Elapsed: 00:00:02.11
15:34:01 SQL> declare
15:34:01 2 procedure revokeGrant(priv varchar2, grantee varchar2, obj varchar2, common varchar2) is
15:34:01 3 containerStr varchar2(25) ;
15:34:01 4 begin
15:34:01 5 if (common='YES') then
15:34:01 6 containerStr := ' container=all' ;
15:34:01 7 else
15:34:01 8 containerStr := ' container=current' ;
15:34:01 9 end if ;
15:34:01 10
15:34:01 11 if (obj is null) then
15:34:01 12 execute immediate 'revoke ' || priv || ' from ' || grantee || containerStr ;
15:34:01 13 else
15:34:01 14 execute immediate 'revoke ' || priv || ' on ' || obj || ' from ' || grantee || containerStr ;
15:34:01 15 end if ;
15:34:01 16 end ;
15:34:01 17 begin
15:34:01 18 for rpriv_rec in (select granted_role, grantee, common
15:34:01 19 from dba_role_privs
15:34:01 20 where grantee = 'WMSYS' and
15:34:01 21 granted_role <> 'WM_ADMIN_ROLE') loop
15:34:01 22 revokeGrant(rpriv_rec.granted_role, rpriv_rec.grantee, null, rpriv_rec.common) ;
15:34:01 23 end loop ;
15:34:01 24
15:34:01 25 for spriv_rec in (select privilege, grantee, common
15:34:01 26 from dba_sys_privs
15:34:01 27 where grantee = 'WMSYS') loop
15:34:01 28 revokeGrant(spriv_rec.privilege, spriv_rec.grantee, null, spriv_rec.common) ;
15:34:01 29 end loop ;
15:34:01 30
15:34:01 31 for tpriv_rec in (select privilege, grantee, owner, table_name, common
15:34:01 32 from dba_tab_privs dtp
15:34:01 33 where grantee = 'WMSYS' and
15:34:01 34 type <> 'USER' and
15:34:01 35 not exists(select 1 from dba_nested_tables nt where dtp.owner = nt.owner and dtp.table_name=nt.table_name) and
15:34:01 36 (owner || '.' || table_name) not like 'SYS.QT%BUFFER' and
15:34:01 37 (owner || '.' || table_name) not in ('SYS.AQ$_UNFLUSHED_DEQUEUES')) loop
15:34:01 38 revokeGrant(tpriv_rec.privilege, tpriv_rec.grantee, tpriv_rec.owner || '.' || tpriv_rec.table_name, tpriv_rec.common) ;
15:34:01 39 end loop ;
15:34:01 40
15:34:01 41 for tpriv_rec in (select privilege, grantee, common
15:34:01 42 from dba_tab_privs
15:34:01 43 where table_name = 'WMSYS' and
15:34:01 44 type = 'USER') loop
15:34:01 45 revokeGrant(tpriv_rec.privilege, tpriv_rec.grantee, 'user WMSYS', tpriv_rec.common) ;
15:34:01 46 end loop ;
15:34:01 47 end;
15:34:01 48 /
declare
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant
ORA-06512: at line 14
ORA-06512: at line 38

Elapsed: 00:00:00.00
++++++++++++++++++++++++++++++++++++++++++++++++++++++
CATCTL ERROR COUNT=2
------------------------------------------------------
Identifier OWM 17-05-11 03:34:02 Script = /fesint/12.1.0.2/db/rdbms/admin/sqlsesss
ERROR = [ORA-01927: cannot REVOKE privileges you did not grant ORA-06512: at line 14
ORA-06512: at line 38
]
STATEMENT = [declare
procedure revokeGrant(priv varchar2, grantee varchar2, obj varchar2, common varchar2) is
containerStr varchar2(25) ;
begin
if (common='YES') then
containerStr := ' container=all' ;
else
containerStr := ' container=current' ;
end if ;

if (obj is null) then
execute immediate 'revoke ' || priv || ' from ' || grantee || containerStr ;
else
execute immediate 'revoke ' || priv || ' on ' || obj || ' from ' || grantee || containerStr ;
end if ;
end ;
begin
for rpriv_rec in (select granted_role, grantee, common
from dba_role_privs
where grantee = 'WMSYS' and
granted_role <> 'WM_ADMIN_ROLE') loop
revokeGrant(rpriv_rec.granted_role, rpriv_rec.grantee, null, rpriv_rec.common) ;
end loop ;

Oracle Workspace Manager
ORA-01927: cannot REVOKE privileges you did not grant ORA-06512: at line 14

<>

Changes

 Upgrading the database from 11.2.0.3 to 12.1.0.2

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.