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!