SQL.sql: Error (4,1): ORA-01951: ROLE 'CUSTOM_MASTER_ROLE' not granted to 'OPS$DOMAIN-CodePudding

I am using someone else's database and if I try to revoke a custom role it fails with an ORA-01951 if the user does not have the role.

SQL.sql: Error (4,1): ORA-01951: ROLE 'CUSTOM_MASTER_ROLE' not granted to 'OPS$DOMAIN\USER'

How can I check in the SQL so I only try to remove the role if the user has the role?

The role creation is in a package I do not have the source code to so I do not know exactly how they created the role in the first place.

CodePudding user response：

I managed to create this code to do it.

declare
  hasRole number(1);
  v_sql varchar2(500);
begin
  select case 
           when exists(select 1 from DBA_ROLE_PRIVS where 
           grantee = 'OPS$DOMAIN\USER' and 
           granted_role = 'CUSTOM_MASTER_ROLE')
         then 1
         else 0
       end  into hasRole
  from dual;

  if (hasRole=1) then
    v_sql := 'revoke CUSTOM_MASTER_ROLE from "OPS$DOMAIN\USER"';
    execute immediate v_sql;
  end if;
end;

Hope it helps someone else.

CodePudding user response：

Your query works but a bit complicated. I would use

For aRow in (select * from 
       DBA_ROLE_PRIVS where 
       grantee = 'OPS$DOMAIN\USER' 
       and granted_role = 'CUSTOM_MASTER_ROLE') 
LOOP
   v_sql := 'revoke CUSTOM_MASTER_ROLE from "OPS$DOMAIN\USER"';
   execute immediate v_sql;
END LOOP;