9 Replies Latest reply: Jul 18, 2014 4:35 PM by rp0428 RSS

    Time based access of schema objects

    user12050217

      Oracle 11.2.0.1

      Windows

       

      Is there any possibility to access the different schema objects based upon a specific time period only ?  For example, usera have a table tab1.  Userb, UserC can just insert the rows between Jan and Feb months only, UserD, UserE should be able to perform selects in the month of Feb, March and June only like that.  I wish to have a table and access should be based upon the table like :

       

      Owner  ApplicationUserObject   ObjectNamePrivilegesTimePeriod
      ABC        USERA      Table    Tab1      Insert    Jan to Feb
      ABC        USERA      Table    Tab1      Delete    March only
      ABC        USERA      Table    Tab1      Update    June to Dec
      ABC        USERA      Table    Tab1      Select    Sept
      USERA      ABC        MV       MV1       Select    Sept to Oct
      USERD      USERD      ProcedureProc1     Execute   Feb
      USERK      USERD      Function Func1     Execute   June

       

      We have an application and we wish to restrict above DMLs at database level.  I know at application level it is easy to achieve this, but what if I goes at db level.

       

      Thanks.

        • 1. Re: Time based access of schema objects
          John Stegeman

          Yes, Oracle has a feature called Virtual Private Database (aka DBMS_RLS) that can do this.

           

          Using Oracle Virtual Private Database to Control Data Access

          • 2. Re: Time based access of schema objects
            user12050217

            Thanks for fast and helpful reply.  Can you please share an example or give me a link which shows code in action.  After writing the question, Trigger popped up in my mind, do you think trigger is also an option ?  Any piece of code for this please ?

            • 3. Re: Time based access of schema objects
              John Stegeman

              did you read the document that I linked?

               

              How about Googling for DBMS_RLS?

               

              You don't need triggers

              • 4. Re: Time based access of schema objects
                Perf_PS

                Maybe he/she has restricted access to google => DBMS_RLS

                • 5. Re: Time based access of schema objects
                  rp0428
                  Is there any possibility to access the different schema objects based upon a specific time period only ?  For example, usera have a table tab1.  Userb, UserC can just insert the rows between Jan and Feb months only, UserD, UserE should be able to perform selects in the month of Feb, March and June only like that.  I wish to have a table and access should be based upon the table

                  Post a complete set of requirements.

                   

                  1. how are the users connecting to the database? what tool are they using? are they connecting via a 3-tier application? Using a connection pool?

                  2. why are users performing direct inserts into a table? normally they would use an application and/or batch process for that

                  3. do the users need access to other objects even while they are restricted on that particular table?

                   

                  Roles, proxy users and other options exist depending on the full requirements that must be met.

                  • 6. Re: Time based access of schema objects
                    user12050217

                    Our client is a paper manufacturing company.  They have divided their manufacturing and IT part into 4 main parts i.e. :

                     

                    1.Purchasing of raw material

                    2.Manufacturing paper

                    3.Sales and billing

                    4.Taxation and year end accounting

                     

                    Since they have integrated their IT activities as their manufacturing process goes on, so they wish that if suppose currently they are in 2nd stage (Manufacturing of paper), and since there is no need to have access of other parts of db objects, they wish to restrict other application and db activities; like manufacturing of paper is going, so only those tables, functions and procedures should be "online", rest should be "offline", so that there should not be any unnecessary errors.  At the application level, they have such functionality and features, but some of the parts of application need to be corrected.  So they are thinking to have rest part at db level.

                     

                    When I tried to go with dbms_rls as John suggested, but I failed because if I am able to see a bit code in action, I may guess; how it exactly fits in my environment.  Even though the manual have some examples, but I failed to understand them that how this dbms_rls will work as per my above table of "accessmaster".

                    • 7. Re: Time based access of schema objects
                      Girish Sharma

                      It seems me that you have a feature request to Oracle to have "access parameters" for any schema object something like this : ?

                       

                      create table test

                      (

                      col1 ...

                      ...

                      )

                      storage parameters

                      ...

                      ...

                      access parameters (Based upon nls setting and characterset)

                      Insert 1,2,7,8

                      Update 1,3

                      Delete Any

                      Select 2,3,4,5,6

                      Merge 10

                      Alter 12

                       

                      i.e. the above example, test table will only accept insert in the month of Jan, Feb, July and Aug,  Updates will only be allowed in Jan and March. Deletes are opened for all 12 months etc. like that.

                       

                      Wow... found a new feature request.....

                       

                      Regards

                      Girish Sharma

                      • 8. Re: Time based access of schema objects
                        Hemant K Chitale

                        If the users USERA, ABCD, USERD (as show in your table) are *application users* it would imply that they are authenticated by the application.  Privileges would also be controlled by the application.

                         

                        However, your table confuses or mixes  Database Users with Application Users..  So it isn't really clear if authentication and and privileges are controlled by the application or whether the application is a transparent pass-through interface simply relying on database privileges.

                        If this (relying on database privileges) can't you schedule or execute the GRANT and REVOKE scripts to be executed appropriate to the months ?

                         

                         

                        Hemant K Chitale


                        • 9. Re: Time based access of schema objects
                          rp0428
                          Our client is a paper manufacturing company.  They have divided their manufacturing and IT part into 4 main parts i.e. :

                           

                          1.Purchasing of raw material

                          2.Manufacturing paper

                          3.Sales and billing

                          4.Taxation and year end accounting

                           

                          Since they have integrated their IT activities as their manufacturing process goes on, so they wish that if suppose currently they are in 2nd stage (Manufacturing of paper), and since there is no need to have access of other parts of db objects, they wish to restrict other application and db activities; like manufacturing of paper is going, so only those tables, functions and procedures should be "online", rest should be "offline", so that there should not be any unnecessary errors.  At the application level, they have such functionality and features, but some of the parts of application need to be corrected.  So they are thinking to have rest part at db level.

                           

                          When I tried to go with dbms_rls as John suggested, but I failed because if I am able to see a bit code in action, I may guess; how it exactly fits in my environment.  Even though the manual have some examples, but I failed to understand them that how this dbms_rls will work as per my above table of "accessmaster".

                          One way to implement that is to create a set of roles.

                          Userb, UserC can just insert the rows between Jan and Feb months only, UserD, UserE should be able to perform selects in the month of Feb, March and June only like that.  I wish to have a table and access should be based upon the table like :

                          1. create roles for INSERT - insertTableA_role, insertTableB_role, etc

                          2. create roles for SELECT - selectTableA_role, selectTableB_role, etc

                          3. create a logon trigger - the trigger will grant the appropriate role(s) to the user based on that table you first posted

                           

                          That way your users will have NO privileges except the privileges for the roles they are been granted by the logon trigger each time they logon.  That logon trigger can determine what 'stage' things are in and what 'month' it is and query your table for the proper roles to grant.