1

Having run a grant command in an Oracle SQL script, is there any way to tell if it actually changed anything. Like an equivalent of SQL%ROWCOUNT or similar?

I am writing an ansible module which will manage grants (using SQLPlus internally to run the commands). It would be nice to be able to accurately report if it changed anything or if it was OK already. I guess I could do a bunch of selects first, but then I have to map each grant (there are different types of grants involved, including grants from sys to schema owner) to a select.

What I'd like to be able to do is something like

declare
  vNum number := 0;

  procedure do_grant(vGrant in varchar2) is
  begin
    execute immediate 'grant ' || vGrant || ' to myrole';
    if SQL%CHANGED > 0 then
      vNum := vNum + 1;
    end if;
  end do_grant;
begin
  do_grant('select on foo');
  do_grant('select on bar');
  do_grant('execute on baz');

  dbms_output(vNum);
end;
/

(This is simplified pseudocode, without error handling, etc. for simplicity, but I capture the output and update the status to ansible based on that.)

Adam
  • 6,539
  • 3
  • 39
  • 65
  • Why do you think it would not change something? If there was an error, you would get an exception. – OldProgrammer Mar 09 '23 at 14:35
  • 1
    If that role already had that grant, it hasn't changed anything. It has re-affirmed the grant, it doesn't fail, but nothing actually changes. If the role doesn't have the privilege then the grant gives it to them - i.e. it changes something. – Adam Mar 09 '23 at 14:38
  • 1
    Oracle doesn't return anything differently based on whether a grant was already given or not. The grant is just re-executed and succeeds. If you want to know if the grant was already given, you'd have to do a separate test against the data dictionary first. – pmdba Mar 09 '23 at 14:46

1 Answers1

0

You can check the grants at any time with the code below:

    Select      PRIVILEGE
    From        sys.dba_sys_privs
    Where       GRANTEE = 'user_of_interest'
UNION
    Select      PRIVILEGE 
    From        dba_role_privs rp 
    Inner Join  role_sys_privs rsp ON (rp.GRANTED_ROLE = rsp.ROLE)
    Where       rp.GRANTEE = 'user_of_interest'

... or for tables ...

SELECT
    *
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE = 'user_of_interest'
d r
  • 3,848
  • 2
  • 4
  • 15
  • Thanks. I decided to take the easy way out and just always report changed in ansible when I apply the privileges. I'm sure this would have worked, but it wasn't worth the effort in this case I felt. – Adam Mar 31 '23 at 15:37