Oracle: grant to role if role exists-CodePudding

How to execute

GRANT SELECT ON <ownschema>.<sometable> TO <somerole>;

but backing off gracefully if somerole does not exist. The user executing the statement is a standard user (think SCOTT) without any special privileges.

Version: Oracle Database 19 or later

CodePudding user response：

It all depends on the tool, but you can do something like this (very crude as usually you should have better exception handling):

begin
execute immediate 'grant .....';
exception
when others then null;
end;

CodePudding user response：

I don't think you can.

If you're running it at SQL level, then Oracle will raise an error if role doesn't exist.

If you want to check whether it exists, you'll need some kind of a PL/SQL procedure (which is not a problem), but - DBA should grant you select privilege on dba_roles so that you could check it. Then, if it exists, you'd grant that privilege; otherwise, return some kind of an information (dbms_output.put_line in my example is pretty much stupid; it wouldn't be visible out of SQL*Plus, SQL Developer, TOAD and similar), but you got the idea, I hope.

Something like this:

create or replace procedure p_grant_to_role (par_role in varchar2) is
  l_cnt number;
begin
  select count(*) 
    into l_cnt 
    from dba_roles
    where role_name = par_role;
    
  if l_cnt > 0 then
     execute immediate 'grant select on emp to ' || par_role;
  else
     dbms_output.put_line('Role does not exist');
  end if;
end;
/