CODEWITHSUNDEEP
sql
Lax
Lax

Reputation: 19

creating a table view and restricting the access by hours

I am new in here and wonder if any one can give me some help/ideas with this.

I am creating a view as follow:

CREATE VIEW V_ORDERS
AS 
SELECT ORDERNUMBER, 
       CUSTOMERNUMBER, 
       DATEORDERPLACED, 
       DELIVERYINSTRUCTION, 
SCHEDULEID
FROM ORDERS

so i grant access to sumone as follow:

GRANT ALL ON V_ORDERS TO "SUMONESPACE"

but i want to restrict the access to the table to the working hours 9:00am till 6:00pm

thank you in advance

Upvotes: 2

Views: 152

Answers (1)

biziclop
biziclop

Reputation: 14616

If nothing else helps, you can still deliver an empty table:

CREATE VIEW bla AS
SELECT blabla
WHERE HOUR(NOW()) BETWEEN 9 AND 18

Maybe you can add a UNION:

UNION
SELECT 'You','should','be','at','home','now!'
WHERE HOUR(NOW()) NOT BETWEEN 9 AND 18

Refinement of the first idea: you could create a 0-or-1 row view for joining (if Oracle happens to calculate the sysdate expression with each row which might be slower):

CREATE VIEW STAFF_ORDERVIEW_ALLOWED( current_sys_date ) AS 
SELECT sysdate
WHERE SYSDATE BETWEEN trunc(sysdate) + 9/24 AND trunc(sysdate) + 18/24;

Then you just have to join

CREATE VIEW STAFF_ORDERVIEW_ATHOURS( current_sys_date, ordernumber, customernumber,
  dateorderplaced, deliveryinstruction, scheduleid) AS
SELECT sysdate, ORDERNUMBER, CUSTOMERNUMBER,
  DATEORDERPLACED, DELIVERYINSTRUCTION, SCHEDULEID
FROM STAFF_ORDERVIEW_ALLOWED
INNER JOIN ORDERS;

Upvotes: 6

Related Questions