Skip to Main Content

SQL & PL/SQL

Not all privileges added to role

user545194Dec 22 2022 — edited Dec 22 2022

Hi,
Version: 19c SE2
After creating a role and adding privileges to that role, we noticed that not all object privileges were added to the role.

...
declare
 gv_role VARCHAR2(30) := 'NEW_ROLE';
begin
      for r in(
         select owner
              , object_name
         from all_objects
         where owner in('SCHEMA1', 'SCHEMA2', 'SCHEMA3', 'SCHEMA4')
               and object_type in('TABLE', 'VIEW', 'SYNONYM')
               and status = 'VALID'
      )loop
    -- this only adds the privileges to the role. Assign role to users where necessary.
         gv_sql_txt := 'GRANT SELECT ON '
                       || r.owner
                       || '.'
                       || r.object_name
                       || ' TO '
                       || gv_role;


         execute immediate gv_sql_txt;
             --dbms_output.put_line('Grant succeeded for '|| r.owner ||' '|| r.object_name);
      end loop;
...

After running this query some table privileges are missing.

WITH data
     AS (SELECT granted_role
         FROM   dba_role_privs
         CONNECT BY PRIOR granted_role = grantee
         START WITH grantee = 'NEW_ROLE')
SELECT 'SYSTEM'     type,
       grantee      grantee,
       privilege    priv,
       admin_option ad,
       '--'         tabnm,
       '--'         colnm,
       '--'         owner
FROM   dba_sys_privs
WHERE  grantee = 'NEW_ROLE'
        OR grantee IN (SELECT granted_role
                       FROM   data)
UNION
SELECT CASE WHEN TYPE = 'TABLE' THEN 'TABLE'
            WHEN TYPE = 'VIEW' THEN 'VIEW'
            WHEN TYPE = 'SYNONYM' THEN 'SYNONYM'
       END AS type,
       grantee    grantee,
       privilege  priv,
       grantable  ad,
       table_name tabnm,
       '--'       colnm,
       owner      owner
FROM   dba_tab_privs
WHERE  grantee = 'NEW_ROLE'
        OR grantee IN (SELECT granted_role
                       FROM   data)
       AND privilege IN ('SELECT')
ORDER  BY 5, 3;

Procedure executed through a privileged user. Any ideas greatly appreciated. Thanks!

Comments
Post Details
Added on Dec 22 2022
11 comments
118 views