How to grant priviledges on all tables without effort?

Database permissions are a juicy piece of code. Appropriate authorization is the key to data security. You should not give permission with a generous hand, without proper justification.

However, there are such users or situations where we can and want to grant these permissions generously. It can be in the case of a technical role with access to all tables. We may also want to grant permissions to all tables in test or development environments. In such cases, we could use a clever tool that would do it for us.

How to grant priviledges to table?
We grant table privileges such as SELECT, INSERT, DELETE, UPDATE or ALL using the GRANT command.

GRANT SELECT, UPDATE, DELETE, INSERT ON <table_name> to <user_name>

GRANT ALL ON <table_name> to <user_name>

We remove using REVOKE command.

REVOKE SELECT, UPDATE, DELETE, INSERT ON <table_name> FROM <user_name>

REVOKE ALL ON <table_name> FROM <user_name>

We can grant priviledges only for a single table. Unfortunately, there is nothing like GRANT ALL ON ALL TABLES: D. But. What are the dictionary tables and EXECUTE IMMEDIATE for? EXECUTE IMMEDIATE is very inefficient and I usually do not recommend using it but it will be suitable for such one-off actions.

---------------------------------------------------------------------------------------------------------------------------------
-- grant to all tables
---------------------------------------------------------------------------------------------------------------------------------

set serveroutput on

DECLARE 
vUserName  ALL_TABLES.OWNER%TYPE  := 'PERF_IDX';
vOwnerName ALL_TABLES.OWNER%TYPE  := 'HR';
BEGIN

    FOR z IN (
        SELECT owner, table_name 
        FROM all_tables 
        WHERE owner = vOwnerName
    )
    LOOP 
      
        EXECUTE IMMEDIATE 
           'GRANT SELECT, UPDATE, DELETE, INSERT ON ' || z.owner||'.' || z.table_name|| '  to '|| vUserName ;     
    END LOOP;

END; 

/

---------------------------------------------------------------------------------------------------------------------------------
-- revoke from all tables
---------------------------------------------------------------------------------------------------------------------------------

set serveroutput on

DECLARE 
vUserName  ALL_TABLES.OWNER%TYPE  := 'PERF_IDX';
vOwnerName ALL_TABLES.OWNER%TYPE  := 'HR';
vSql varchar2(500);
BEGIN

    FOR z IN (
        SELECT owner, table_name 
        FROM all_tables 
        WHERE owner = vOwnerName
    )
    LOOP
        vSql := 'REVOKE SELECT, UPDATE, DELETE, INSERT ON ' || z.owner||'.' || z.table_name|| '  from '|| vUserName ; 
        
        dbms_output.put_line(vsql);
        EXECUTE IMMEDIATE 
           vsql;
    END LOOP;

END; 

/

And what if we would like to give the user permission automatically just after creating the table?

We can use the DDL trigger. There is only one catch - because granting permissions also belongs to DDL commands - we cannot execute GRANT or REVOKE directly from the trigger. But we can create a job instead. And voila, there is our tool.

Procedure for granting permissions. The procedure will be started with Job.

create or replace procedure grant_all_on_table(pvTableName varchar2, pvUserName varchar2 , pvOwner varchar2) is

begin
   EXECUTE IMMEDIATE  'GRANT SELECT, UPDATE, DELETE, INSERT ON ' ||pvOwner||'.'|| pvTableName ||   ' TO ' || pvUserName ;    
end;

Triger DDL - AFTER CREATE ON SCHEMA.

To limit execution to tables only, we will use the attributes:
sys.dictionary_obj_type = object type, we are only interested in TABLES
sys.dictionary_obj_name - the name of the object
sys.dictionary_obj_owner - owner of the object
I will use dbms_job.submit to run the job.

create or replace trigger tab_priv_grants after create  on schema
declare
 iJobId integer;
 vsql varchar2(500);
begin
     IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' then 
       vsql := 'BEGIN grant_all_on_table( pvTableName => '''|| sys.dictionary_obj_name || ''' , 
                                          pvUserName => ''PERF_IDX'' , 
                                          pvOwner => '''||sys.dictionary_obj_owner ||''') ; 
                '|| ' END;';
      
      dbms_output.put_line(vsql);
       dbms_job.submit( iJobId,
                     vsql,
                    sysdate +3/86400 );
      end if ;

end;

Test was run on HR schema and newly created  PERF_TEST user.

-- hr user
create table test_grants (a number);

--perf_test user
select * from hr.test_grants;
ORA-00942: tabela lub perspektywa nie istnieje
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 1 Column: 18

--- after 3 seconds
no rows selected

It works !:D --------------------------------------------------------------------------------------------------------------------------

And finally, code with many variables that you can control, what priviledge to give. Whether to grant or revoke. One of those codes that you wrote with fun, and which probably will never be used. It is easier to run a simple script than to set so many variables: D But what the hell ,  coding is fun D :D :D

set serveroutput on

DECLARE 
vUserName  ALL_TABLES.OWNER%TYPE  := 'PERF_IDX';
vOwnerName ALL_TABLES.OWNER%TYPE  := 'HR';
-------------------------------------------
bGrants_Revokes varchar2(1) := 'G';  --G - grants /R - revokes
-------------------------------------------
bSel boolean := true;
bUpd boolean := true;
bDel boolean := true;
bIns boolean := true;
-------------------------------------------
bExecute boolean := true;
-------------------------------------------
vSql varchar2(200);
vSqlGrants varchar2(200);
eNoGrants exception;
BEGIN


if (bSel or bUpd or bDel or bIns) = false 
then
   raise eNoGrants;
end if;

vSqlGrants :=  case bGrants_Revokes when 'G' then  'GRANT ' else 'REVOKE ' end||  
                    case when  bSel =  true then  'SELECT, ' end ||
                    case when  bUpd =  true then  'UPDATE, ' end ||
                    case when  bDel =  true then  'DELETE, ' end ||
                    case when  bIns =  true then  'INSERT, ' end ;

-- cut last ,
vSqlGrants := substr(vSqlGrants, 1, length(trim(vSqlGrants)) -1);       


    FOR z IN (
        SELECT owner, table_name 
        FROM all_tables 
        WHERE owner = vOwnerName
    )
    LOOP
    vSql := vSqlGrants || ' ON '||z.owner||'.'||z.table_name|| 
            case bGrants_Revokes when 'G' then  ' TO ' else ' FROM ' end|| vUserName;
   
    dbms_output.put_line (vSql);
  
    if bExecute then 
        EXECUTE IMMEDIATE 
            vSql;
    end if;
    
    END LOOP;

exception  when eNoGrants then 
    dbms_output.put_line ('No grants was specified');
END; 

/