Skip to Main Content

Adding grants to a role

user545194Nov 16 2022 — edited Nov 16 2022

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!

Comments
Post Details
Added on Nov 16 2022
17 comments
165 views