Hi,
Oracle 19c SE2 Version 19.15.0.0.0
We need to add grant select on tables and views whenever a new table or view has been created in a specific schema and has not yet been granted to the role.
SELECT
'grant '
|| privilege
|| ' on '
|| owner
|| '.'
|| table_name
|| ' to '
|| grantee
||
CASE
WHEN grantable = 'YES' THEN
' with grant option'
ELSE
NULL
END
|| ';' AS grant_for_role
FROM
dba_tab_privs
WHERE
owner IN ( 'SCHEMA1', 'SCHEMA2', 'SCHEMA3' )
AND grantee IN (
SELECT
role
FROM
dba_roles
WHERE
role = 'THE_ROLE'
)
AND table_name NOT IN (
SELECT
object_name
FROM
dba_objects
WHERE
owner IN ( 'SCHEMA1', 'SCHEMA2', 'SCHEMA3' )
AND object_type IN ('TABLE','VIEW')
AND object_name NOT LIKE 'BIN%'
)
ORDER BY
grantee,
owner;
Statement updated.
Thanks!