1 2 Previous Next 25 Replies Latest reply on Jun 17, 2013 8:03 AM by Steven Hawkins

    Row Level Security using TEIID Designer

    Neeta L Newbie

      Hello,

       

      As per our project, we are required to migrate from Oracle to SQL (rather support both databases instead of one).

       

      So my first question is : is VDB useful in this case where it can dynamically connect to either Oracle or SQL Server (both will have same table structures)

       

      I am required to implement the Oracle VPD (row level and column level securities defined in Oracle policy file) at middle layer.

       

      I have been told to explore JBoss TEIID for the same and I am new to it.

       

      I have installed JBoss AS 7.1 and TEIID designer 8.3.

       

      I have created VDB from SQL Server. For now I want to implement RLS.

       

      I read few of the discussions and understood that I can use virtual procedure, define data roles and can use security function hasRole() to implement RLS.

       

      But struggling to understand in detail. Can someone please help me with some examples or tutorial on it.

       

      Thanks,

      Neeta

        • 1. Re: Row Level Security using TEIID Designer
          Ramesh Reddy Master

          Neeta,

           

          In a virtual procedure you can use as

           

          IF (hasRole("superuser") && name = 'neeta')
          BEGIN
              ..do something       
          END
          

           

          You can also see https://docs.jboss.org/author/display/TEIID/Permissions "Row Based Security Section", it is a another way to define a condition (where clause) in configuration to apply row based secuirty with out the use of the hasRole function.

           

          Ramesh..

          • 2. Re: Row Level Security using TEIID Designer
            Neeta L Newbie

            Thanks Ramesh.. I will look into it.

             

            Right now I am stuck in error 'JDBC username and password' error. ( I have posted in another discussion)

             

            I have one more question.

             

            Our requirement is to create a VDB which can connect to either Oracle or SQL server (with same data structures) and then implement different data security on VDB which can be applicable to any database

             

            Is it possible to create a VDB which can dynamically connects to either Oracle or SQL Server? and is it possible to have common data security logic at middle layer which can be applicable to both the databases?

             

            I am exploring this new area, so please forgive me if I ask some stupid questions

             

            Thanks,

            Neeta

            • 3. Re: Row Level Security using TEIID Designer
              Ramesh Reddy Master

              Our requirement is to create a VDB which can connect to either Oracle or SQL server (with same data structures) and then implement different data security on VDB which can be applicable to any database

              Teiid can connect to both of them at same time or connect to one database at a time. If your usecase is required to connect to single database at one time, you need to import the metadata from one of the databases and create source model from it. then either use a Oracle translator and Oracle connection in the VDB, or use MS-SQL Server translator and its connection depending upon your need.

               

              You can also use what we call "multi-source" model, where a single model can be backed by multiple sources, but in this case data will be unioned from both databases for a user query. if you want data from single database you need to provide a WHERE clause in your user query to only select data from single database.

               

               

              Is it possible to create a VDB which can dynamically connects to either Oracle or SQL Server?

              See above, it is based on how you set it up.

               

              and is it possible to have common data security logic at middle layer which can be applicable to both the databases?

              Yes, as the security model is in the VDB which is middle tier as refereeing to, and at one time you are only connecting to single database.

               

               

              Ramesh..

              • 4. Re: Row Level Security using TEIID Designer
                Neeta L Newbie

                Thanks Ramesh..

                 

                So if I want to get data in a following manner

                 

                When UserA queries Items he should only see Item1 & Item2

                 

                When UserB queries Items he should only see item1 & Item3

                 

                So i have to write a virtual procedure and I have to write my select queries something like below ? Am I correct?

                 

                'Select * from Items where userid='UserA'

                 

                So its all manual query implementation of queries ? nothing OOB provided by TEIID?

                 

                Can I create new table in VDB, where I can map this security logic like which user has access to which items or it should be define in source table?

                 

                Thanks,

                Neeta

                • 5. Re: Row Level Security using TEIID Designer
                  Ramesh Reddy Master

                  Neeta,

                   

                  The metadata will expose all the columns all the time, like when you issue the call

                   

                  SELECT * FROM MyTable

                   

                  you will get all the three colums, item1, item2 and item3. However if you write the transformation like

                   

                  if (hasRole('UserARole')
                    BEGIN
                         SELECT item1, item2, null FROM Source
                    END
                  ELSE IF (hasRole('UserBRole')
                     BEGIN
                        SELECT item1, null, item3 FROM Source
                     END
                  

                   

                   

                  Now same code above can be done through new feature called "Column Masking" in 8.4 version of Teiid. See details here https://docs.jboss.org/author/display/TEIID/Permissions

                  • 6. Re: Row Level Security using TEIID Designer
                    Neeta L Newbie

                    Thanks Ramesh for all your inputs

                     

                    As per your specification :

                    "Teiid can connect to both of them at same time or connect to one database at a time. If your usecase is required to connect to single database at one time, you need to import the metadata from one of the databases

                    and create source model from it. then either use a Oracle translator and Oracle connection in the VDB, or use MS-SQL Server translator and its connection depending upon your need."

                     

                    I understood the first part : import the metadata from one of the databases and create source model from it

                     

                    Can you please provde more details on second part : "Either use a Oracle translator and Oracle connection in the VDB, or use MS-SQL Server translator and its connection depending upon your need"

                     

                    Till now I have not created any translator. I am using Teiid designer, where I have created connection profile and setting that connection profile to source and then creating View Model. Then create VDB having both source model and view model

                     

                    I have one more question : I want to define security criteria in VDB so that I dont have to define independently on Oracle and Server. So can I create individual table in VDB (kind of Base Table. But I guess base table gets the data from the source model). I want to maintain this table seperately in VDB.

                     

                    Thanks,

                    Neeta

                    • 7. Re: Row Level Security using TEIID Designer
                      Ramesh Reddy Master

                      Neeta,

                      Till now I have not created any translator. I am using Teiid designer, where I have created connection profile and setting that connection profile to source and then creating View Model. Then create VDB having both source model and view model

                      Using Designer you can create another connection profile for MS-SQL and set on the source model. But you can only set one connection profile at a time. or you can open the VDB (nothing but a zip file) an dedit the vdb.xml file and change the "translator-name" and "connection-jndi-name" to reflect to the database you need to access through the VDB. You do not need to create a translator, you just need to use a different one.

                       

                      I have one more question : I want to define security criteria in VDB so that I dont have to define independently on Oracle and Server. So can I create individual table in VDB (kind of Base Table. But I guess base table gets the data from the source model). I want to maintain this table seperately in VDB.

                      is security same on both databases? if yes, create data roles on the source model, and then if you created two different vdbs, one with oracle and one with ms-sql they as expected.

                       

                      Ramesh..

                      • 8. Re: Row Level Security using TEIID Designer
                        Neeta L Newbie

                        How do I set the connection profile programatically on the source model?

                         

                        I am really confused.

                         

                        In Teiid Designer :

                        I am importing data from oracle to my oracle source model and then creating oracle view on this source model. So I can query this view model to get data from oracle.

                         

                        I have done similar thing seperately for SQL server.

                         

                        Now I am not sure how can I connect same source model to different source like oracle/SQL programatically?

                         

                        I want to perform following steps.

                        - create one source model

                        - create 2 connection profile (one for oracle and one for sql)

                        - create a VDB

                        - create a virtual procedure on this VDB

                        - Based on configurable parameter, want to add source model and view model for either oracle or server

                        - then call the virtual procedure to get the data from either source.

                         

                        Please let me know, if I can do all the above steps programatically? If yes/no, please provide more details

                         

                        Thanks,

                        Neeta

                        • 9. Re: Row Level Security using TEIID Designer
                          Ramesh Reddy Master

                          Neeta,

                           

                          where is programmatic usage coming into picture? You have not mentioned anywhere above!

                           

                          Anything you are doing with Designer for creation of VDB can NOT be programmatically controlled. Look into Dynamic VDB. If you can create <name>-vdb.xml file programmatically then you can do the above. See TestVDBMetadata.java class on how to create this file.

                           

                          Note that you can not have one source model has two connections at the same time (unless it is multi-source), that means you can connect to only database at one time. So for your usecase you want to use multi-source. where you can define two connections. When multi-source is in use, it automatically adds a pseudo column with which you can select which database you are going against, then write a view model using transformations using this pseudo column values (configurable parameter, this has be a column and value passed through WHERE clause). Read more about multi-source models here https://docs.jboss.org/author/display/TEIID/Multi-Source+Models

                           

                          so, yes, this can be done, but not the way you are approaching.

                           

                           

                          Ramesh..

                          • 10. Re: Row Level Security using TEIID Designer
                            Neeta L Newbie

                            Thanks Ramesh.

                             

                            I do not want to connect my source model to two connections at the same time. I want to connect to either oracle or sql based on some input parameter.

                             

                            I guess I can not achieve it using Teiid designer, so thought of doing it programmatically.

                             

                            So as per you inputs I guess if I want to do all above steps using programatically,

                             

                            I have to follow following steps

                            - Create VDB

                            - Create datasource

                            - assign the connection profile based on input parameter

                            - create a view on top of this source model

                            - query on this view model

                             

                            now can you please suggest, what should be the approach for it. Whether it can be achieved using designer or programatically?

                             

                            Thanks,

                            Neeta

                            • 11. Re: Row Level Security using TEIID Designer
                              Ramesh Reddy Master

                              Neeta,

                               

                              As I already said you CAN NOT do what you want do programmatically using Designer. Designer is a design time tool that aids in developing artifacts that can be deployed on serverfor an application. Like using Eclipse to build a WAR file, to deploy on JBoss AS server for a web application. Here using Designer you build a VDB, to deploy on Teiid server you get Virtual Database. Your users of the application, at runtime (production) are not going to use Designer to get to their data, they are going to use either JDBC, ODBC to access their data on the Virtual Database you designed.

                               

                              A Dynamic  VDB is finctionally same as Designer based VDB, but it is defined using a XML file, that means if you can generate the XML file progamtically can design a VDB. Teiid also provides a "Admin API", using which you can deploy VDBs to server, create data sources etc. See Admin API here

                              https://docs.jboss.org/author/display/TEIID/Deploying+VDBs

                              http://docs.jboss.org/teiid/8.3.0.Final/apidocs/index.html?org/teiid/adminapi/AdminFactory.html

                              http://docs.jboss.org/teiid/8.3.0.Final/apidocs/org/teiid/adminapi/Admin.html

                               

                               

                              If you carefully read what I said in my previous comment and read and understand the links I provided above, I already provided all the necessary information for you do the above tasks programmatically. These are additional links can be of more help in providing some examples for showing various VDB related tasks with Admin API.

                               

                              https://github.com/teiid/teiid/blob/master/test-integration/common/src/test/java/org/teiid/arquillian/IntegrationTestDeployment.java

                              https://github.com/teiid/teiid/blob/master/test-integration/common/src/test/java/org/teiid/arquillian/IntegrationTestDynamicViewDefinition.java

                               

                              Ramesh..

                              • 12. Re: Row Level Security using TEIID Designer
                                Van Halbert Master

                                Neeta,

                                 

                                "I do not want to connect my source model to two connections at the same time. I want to connect to either oracle or sql based on some input parameter."

                                 

                                 

                                If I may interject.  As Ramesh mentioned, you can use the Multi-Source models feature to accomplish reading either database.    Then, it can be as simple as the query you write, example: 

                                select * from table where SOURCE_NAME = 'oracle'

                                 

                                where, in this example, Teiid will direct it to the translator/datasource based on the SOURCE_NAME and it will read from the 'oracle' datasource.  Based on the source names you define in the vdb.xml, those values would be your parameter values for SOURCE_NAME. 

                                 

                                Van

                                • 13. Re: Row Level Security using TEIID Designer
                                  Van Halbert Master

                                  Additionally,

                                   

                                  In Teiid Designer 8.1, it added support for configuring multi-source bindings.  So you could build your VDB in designer and then deploy it.  Then you can use the execute feature to submit your queries and play with quering the different datasources.

                                   

                                  van

                                  • 14. Re: Row Level Security using TEIID Designer
                                    Ramesh Reddy Master

                                    Neeta, Van

                                     

                                    I am not sure but Neeta requirement may be just that, programmatically select which source to against for queries, not to create source or vdb programmatically. May be she did not express quite right, and/or I assumed more of it.

                                     

                                    In that case case you Designer to create your VDB, then use multi-source model to define your oracle and ms-sql servers, then use Van's suggestion. I said the same thing on Comment # 9

                                     

                                    Ramesh..

                                    1 2 Previous Next