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

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.

CodePudding user response：

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

CodePudding user response：

you will not run your sql successfully because you essentially need 'dynamic sql', you may need write a piece of block of pl/sql as below in SqlPlus.

begin
for tab in (select owner,object_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')
loop
    execute immediate 'grant select on ' || tab.owner || '.' || tab.object_name || ' to PSREAD_ROLE_W';
end loop;
end;
/

try this code with sqlplus, if your need call it from jdbc, you still need create a named procedure and then call from java code.