This discussion is archived
3 Replies Latest reply: Mar 29, 2012 10:46 AM by Frank Kulash RSS

Creating a VIEW via Sysdate

926991 Newbie
Currently Being Moderated
Hello Guys,

I can create simple views for MYSQL Database tables but I am unsure on how to create a view for the following requirement:

I want to create a view which restricts staff user access to a specific table between working hours of 13:00 - 14:00.


I have been told that this can be implemented via the sysdate function. Any ideas on how I would do this?

All help is well appreciated!

Thank You!
  • 1. Re: Creating a VIEW via Sysdate
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    In Oracle, you can create a view like this:
    CREATE OR REPLACE     emp_view
    AS
    SELECT     *
    FROM     scott.emp
    WHERE     TO_CHAR (SYSDATE, 'HH24:MI')     BETWEEN     '13:00'
                               AND     '14:00'
    ;
    When people use the view between 13:00 and 14:00 any day, they can see all the rows that are in the emp table.
    When people use the view before 13:00, or after 14:00, then it will appear as if there are no rows in the table.

     

    In the future, post questions like this in the SQL and PL/SQL forum:
    SQL and PL/SQL
    You'll get better replies faster, since more people wathc that forum. This forum is supposed to deal only with the iSQL and SQL*Plus front ends.
  • 2. Re: Creating a VIEW via Sysdate
    926991 Newbie
    Currently Being Moderated
    Hello mate,

    Thank you very much for your reply!! IT WORKED!!

    My problem is that because I am the ADMIN for the Database why doesn't it allow me to view the tables. I only want to grant permissions to certain members of staff. Me being an ADMIN I want to see the view. At the moment it says 'there are no rows selected' for me as well because I am trying to access it...

    So basically how do I change it so I can always view it, never mind the time.
    Secondly how I do I grant appropriate permissions to my staff so they cant view it..

    Thank You.
  • 3. Re: Creating a VIEW via Sysdate
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    If ADMIN is allowed to see all the rows all the time, then you can do something like this:
    CREATE OR REPLACE     emp_view
    AS
    SELECT     *
    FROM     scott.emp
    WHERE     TO_CHAR (SYSDATE, 'HH24:MI')     BETWEEN     '13:00'
                               AND     '14:00'
    OR     USER     = 'ADMIN'
    ;
    If user KING is allowed to see data through the view between 1:00 and 5:00 PM, then you can add this condition to the WHERE clause:
    OR     (     USER     = 'KING'
         AND     TO_CHAR (SYSDATE, 'HH24:MI')     BETWEEN     '13:00'
                                    AND     '17:00'
         )
    If you're using Enterprise Edition, you can also do this kind of thing through Virtual Private Database (VPD) using the dbms_rls package:
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_rls.htm#i1000830

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points