How to grant access to role for the tables that are created today

Question

How to grant access to role for the tables that are created today :

grant select on ( select 
       object_name as table_name
from sys.all_objects
where object_type = 'TABLE'
-- excluding some Oracle maintained schemas
and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
  'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
  'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST','WKSYS', 
  'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
  'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
  'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
and created > sysdate -1
)  to  PSREAD_ROLE_W;   

This query is not working. I am not sure how to pass the selected values in order to grant the access.

Answer 1

you can run this SQL, generate the output and execute the SQL generated. Or the other option is to USE dynamic SQL and execute immediate to run the SQL

    SELECT ' GRANT SELECT ON ' ||    object_name || ' to PSREAD_ROLE_W; ' 
from sys.all_objects obj   where object_type = 'TABLE'
and created > sysdate -1