5 Replies Latest reply: Jun 18, 2012 3:23 PM by 944418 RSS

    Rec'd error -01927 revoke privs even though same GRANTOR

    856765
      Greetings,

      I hope this post finds you well.

      I've an interesting issue. I have a script that executes a grant privs on a table as follows :

      grant select, insert, delete, update on wpown.t_sched_meet_meeting_room to ap_portal_role;
      /

      ... and another script to revoke those privs as follows :

      revoke select, insert, delete, update on wpown.t_sched_meet_meeting_room from ap_portal_role;
      /

      The issue is when the REVOKE script is executed I am getting the following error :

      ---- ** Grant privileges on T_SCHED_MEET_MEETING_ROOM ** ----
      revoke select, insert, delete, update on wpown.t_sched_meet_meeting_xxxx from ap_xxxxxx_role
      *
      ERROR at line 1:
      ORA-01927: cannot REVOKE privileges you did not grant

      I am perplexed for one, the scripts are executed using the same id, and two, even though the REVOKE is generating an error, the privs are being revoked.

      Here is the output for review :

      jf015g@clavius:~/5.200.0/cc3594/centcom_3594_privs_t_sched_meet_meeting_room$ sqlplus wpown@DEV2/wpown_d1 @apply.sql

      SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 8 17:15:30 2011

      Copyright (c) 1982, 2005, Oracle. All rights reserved.


      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options


      ---- BEGINNING SCRIPT TO APPLY PATCH For CENTCOM-2910 ----
      ---- Results logged to apply_CENTCOM-3594_patch.log ----

      ---- ** Grant privileges on T_SCHED_MEET_MEETING_ROOM ** ----

      ---- Results logged to apply_CENTCOM-3594_patch.log ----
      ---- SCRIPT TO APPLY PATCH DONE: Exiting ----

      Elapsed: 00:00:00.09
      SQL> select count(*) from dba_tab_privs
      2 where table_name = 'T_SCHED_MEET_MEETING_ROOM'
      3 and grantee = 'AP_PORTAL_ROLE';
      4
      SQL> exit
      Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options
      jf015g@clavius:~/5.200.0/cc3594/centcom_3594_privs_t_sched_meet_meeting_room$ sqlplus wpown@DEV2/wpown_d1 @remove.sql

      SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 8 17:16:36 2011

      Copyright (c) 1982, 2005, Oracle. All rights reserved.


      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options


      ---- BEGINNING SCRIPT TO APPLY PATCH For CENTCOM-2910 ----
      ---- Results logged to apply_CENTCOM-3594_patch.log ----

      ---- ** Revoke privileges on T_SCHED_MEET_MEETING_ROOM ** ----
      revoke select, insert, delete, update on wpown.t_sched_meet_meeting_room from ap_portal_role
      *
      ERROR at line 1:
      ORA-01927: cannot REVOKE privileges you did not grant



      ---- Results logged to apply_CENTCOM-3594_patch.log ----
      ---- SCRIPT TO APPLY PATCH DONE: Exiting ----

      Elapsed: 00:00:00.05
      SQL> select count(*) from dba_tab_privs
      2 where table_name = 'T_SCHED_MEET_MEETING_ROOM'
      3 and grantee = 'AP_PORTAL_ROLE';
      0
      SQL>


      Any insight is greatly appreciated.

      Thank you