Search code examples
sqloracledatabase-trigger

SQL prevent select from table

Is there a way to prevent select from a table, say between 9 to 5 allow select, otherwise do not return anything?

Triggers are for DML, DDL, database operations.

Is it correct to do it with a procedure, and check the time/other conditions?

Or is there an easier way to prevent the select?

Solution

  • You can create a view and only grant SELECT privilege on the view and revoke any privileges on the underlying tables.

    The view can then include a WHERE condition that simply returns false, if the current time is not allowed:

    create view .. 
as
select ...
from ...
where to_char(sysdate, 'hh24:mi') >= '09:00' 
  and to_char(sysdate, 'hh24:mi') <  '17:00'