Forum Stats

  • 3,874,184 Users
  • 2,266,680 Discussions
  • 7,911,761 Comments

Discussions

Adding grants to a role

user545194
user545194 Member Posts: 637 Bronze Badge
edited Nov 16, 2022 10:41AM in SQL & PL/SQL

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!

Tagged:
«1

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,253 Gold Crown

    You'll have to put this in a DB Trigger

    note from what I'm reading, 23c Beta might have the required feature. I'll know more once it's officially out.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond
    edited Nov 16, 2022 10:42AM

    Hi, @user545194

    We understand that this can be run via PLSQL block with EXECUTE IMMEDIATE.

    That's right. For example:

    grant_statement   VARCHAR2 (32767);
    ...
    grant_statement := Q'{select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee
    		     ...
    		     }';
    dbms_output.put_line (grant_statement || ' = grant_statement');  -- FOR DEBUGGING ONLY
    EXECUTE IMMEDIATE  grant_statement;
    

    assuming that the statement does not contain curly brackets ('{' or '}').

    Test carefully. Are you sure the statement you posted is doing what you want to do? It looks like it's granting roles that already have been granted (as recorded in db_tab_privs) but only for tables that don't exist (as recorded in dba-objects). Isn't that backwards? Don't you want to grant privileges on tables that do exist, but only if the ptivileges have not already been granted?

    user545194
  • User_3ABCE
    User_3ABCE Member Posts: 192 Silver Badge
    select t.*,
      case when p.grantable = t.grantable then 'exists'
           when p.grantable is null       then 'new grant'
                                          else 'grantable differs' end diff
    from (
      select owner, table_name, privilege_name privilege, 'NO' grantable
      from dba_tables
      cross join (select privilege_name from v$object_privilege where object_type_name='TABLE')
      where owner in ('SCHEMA1', 'SCHEMA2', 'SCHEMA3')
    ) t
    left join (select owner, table_name, privilege, max(grantable) grantable
               from dba_tab_privs where grantee = 'THE_ROLE' and type = 'TABLE'
               group by owner, table_name, privilege) p
      on (t.owner, t.table_name, t.privilege) = ((p.owner, p.table_name, p.privilege));
    

    You have to decide what to do with grant option, hierarchy, inheritance, grantor, READ vs SELECT, if grant already exists with a different options. Not all table types can be granted.

    user545194
  • user545194
    user545194 Member Posts: 637 Bronze Badge

    Hi, @Frank Kulash

    The requirement is to add the privilege to the role for newly created tables in the selected schemas.

  • user545194
    user545194 Member Posts: 637 Bronze Badge

    Another question: Is the number of grants assigned to a role limited or unlimited?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond

    Hi, @user545194

    Another question: Is the number of grants assigned to a role limited or unlimited?

    There's no limit.

    user545194
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,937 Red Diamond
    edited Nov 16, 2022 11:57AM

    Your query doesn't add up. First of all:

    and grantee in ( select role from dba_roles WHERE role = 'THE_ROLE' )
    

    Is nothing but

    and grantee = 'THE_ROLE' 
    

    I'll assume you meant

    and grantee in (select grantee from dba_role_privss WHERE granted_role = 'THE_ROLE')
    

    And:

    where owner in ('SCHEMA1', 'SCHEMA2', 'SCHEMA3')
    and table_name NOT IN ( SELECT table_name FROM dba_objects WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3' ))
    

    doesn't make sense either. I'll assume it was your attempt to get newly created table. So far so good. New table is created in SCHEMA1 or SCHEMA2 or SCHEMA3 and you want to grant privileges on that table to users/roles who are granted role THE_ROLE. But what privileges to grant? And who gets it with grant option? Remember, this is brand new table. At this point I'll assume SELECT without GRANT option:

    CREATE OR REPLACE TRIGGER
      GRANTS_TRG
        AFTER CREATE
        ON DATABASE
        DECLARE
            V_JOB_ACTION VARCHAR2(4000);
        BEGIN
            IF ORA_DICT_OBJ_OWNER IN ('SCHEMA1','SCHEMA2','SCHEMA3')
              THEN
                IF ORA_DICT_OBJ_TYPE = 'TABLE'
                  THEN
                    V_JOB_ACTION := Q'[DECLARE
                                           CURSOR V_CUR
                                             IS
                                               SELECT  GRANTEE
                                                 FROM  DBA_ROLE_PRIVS
                                                 WHERE GRANTED_ROLE = 'THE_ROLE'
                                                   AND GRANTEE NOT IN ('SCHEMA1','SCHEMA2','SCHEMA3');
                                       BEGIN
                                           FOR V_REC IN V_CUR LOOP
                                             EXECUTE IMMEDIATE 'GRANT SELECT ON ]' || ORA_DICT_OBJ_OWNER ||
                                                               '.' || ORA_DICT_OBJ_NAME ||
                                                               Q'[ TO ' || V_REC.GRANTEE;
                                           END LOOP;
                                       END;]';
                    DBMS_SCHEDULER.CREATE_JOB(
                                              JOB_NAME   => 'GRANTS_TRG_JOB',
                                              JOB_TYPE   => 'PLSQL_BLOCK',
                                              JOB_ACTION => V_JOB_ACTION,
                                              START_DATE => SYSDATE,
                                              ENABLED    => TRUE
                                             );
                END IF;
            END IF;
    END;
    /
    

    Now:

    SQL> CREATE ROLE THE_ROLE;
    
    Role created.
    
    SQL> GRANT THE_ROLE TO U1;
    
    Grant succeeded.
    
    SQL> GRANT THE_ROLE TO U2;
    
    Grant succeeded.
    
    SQL> CREATE USER SCHEMA1 IDENTIFIED BY SCHEMA1
      2  DEFAULT TABLESPACE USERS
      3  QUOTA UNLIMITED ON USERS;
    
    User created.
    
    SQL> CREATE TABLE SCHEMA1.TEST_TABLE(N NUMBER);
    
    Table created.
    
    SQL> SELECT  GRANTEE,
      2          PRIVILEGE
      3    FROM  DBA_TAB_PRIVS
      4    WHERE OWNER = 'SCHEMA1'
      5      AND TABLE_NAME = 'TEST_TABLE';
    
    
    GRANTEE    PRIVILEGE
    ---------- ----------
    U2         SELECT
    U1         SELECT
    
    SQL>
    

    Keep in mind triiger owner must have directly granted privilege GRANT ANY OBJECT PRIVILEGE (or GRANT ANY PRIVILEGE) and must not be granted role THE_ROLE since Oracle doesn't allow granting/revoking privileges to/from self.

    SY.

    user545194_jum
  • Mike Kutz
    Mike Kutz Member Posts: 6,253 Gold Crown

    Here I am thinking you can do the GRANT in an AFTER CREATE DDL trigger. Or am I missing something?

    (un tested - modify as needed)

    create or replace
    trigger auto_add_perm
        after create on schema
            disable -- don't forget to enable
    DECLARE
      l_owner  varchar2(128 byte);
      l_object varchar2(128 byte);
      l_type   varchar2(128 byte);
     
      sql_txt  varchar2(1000);
    BEGIN
      select ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type
        into l_owner, l_object, l_type
      from dual;
    
      -- take action only for Tables/Views in Schema1/2/3
      if l_type in ('TABLE', 'VIEW', 'MATERIALIZED VIEW' ) -- i'm sure there are others (analytic views?)
         and l_owner in ( 'SCHEMA1, 'SCHEMA2', 'SCHEMA3' )
      then
        -- does not discriminate -- always grants
        -- unknown: do the names be surrounded with double-quotes(")??
    
        -- this only grants to the role. expand the code to direct grant to users.
        sql_txt := 'grant select on ' || l_owner || '.' || l_object || ' to THE_ROLE';
    
        -- log SQL_TXT here
    
        execute immediate sql_txt;
      end if;
    END;
    /
    
    
    
    user545194
  • user545194
    user545194 Member Posts: 637 Bronze Badge

    @Solomon Yakobson Thanks for the code example. Didn't think about a trigger, but this might be a good idea to have a job run it on demand. In any case, your suggestion comes close to what we are aiming for.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,937 Red Diamond
    edited Nov 16, 2022 12:39PM

    @Mike Kutz: This will not work. Trigger is called before data dictionary changes are commited. That's why we have to schedule a job.

    SY.