Row Level Security using TEIID Designer

[neetacomp]

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

[rareddy]

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

[neetacomp]

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

[rareddy]

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

[neetacomp]

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

[rareddy]

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

[neetacomp]

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

[rareddy]

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

[neetacomp]

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

[rareddy]

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

[neetacomp]

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

[rareddy]

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

[van.halbert]

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

[van.halbert]

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

[rareddy]

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