CODEWITHSUNDEEP
oracle-databasedate-arithmetic
user1288657
user1288657

Reputation: 11

Restrict view of table between certain times by using sysdate statement in Oracle

I need to restrict a view on a table between certain times to a certain user. I can't find any information about how to do this.

Any help would be appreciated.

Upvotes: 1

Views: 582

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50047

How's about something like

CREATE OR REPLACE VIEW SOME_VIEW AS
  SELECT FIELD1, FIELD2, FIELD_ETC
    FROM REAL_TABLE
    WHERE USER = 'MY_BUDDY' AND
          TO_NUMBER(TO_CHAR(SYSDATE, 'HH')) BETWEEEN 8 AND 16
  UNION ALL
  SELECT NULL AS FIELD1, NULL AS FIELD2, NULL AS FIELD_ETC
    FROM DUAL
    WHERE USER <> 'MY_BUDDY' OR
          TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) NOT BETWEEEN 8 AND 16;

This would allow user MY_BUDDY to see the data from REAL_TABLE between 08:00 and 16:59 each day.

Share and enjoy.

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132650

You could use a Virtual Private Database (VPD) policy to enforce this, or more simply grant users access to a view instead of the table where the view is something like:

create view data_vw as
select * from data
where (to_char(sysdate,'HH24MI') between '0800' and '1730'
      or user = 'CERTAINUSER'
      );

In either case the users will still have access but the table/view will return no rows sometimes.

Upvotes: 2

Related Questions