3 Replies Latest reply: Mar 29, 2012 12:46 PM by Frank Kulash RSS

    Creating a VIEW via Sysdate

    926991
      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
          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
            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
              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