-
1. Re: Row Level Security using TEIID Designer
rareddy May 21, 2013 8:09 AM (in response to neetacomp)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
neetacomp May 22, 2013 1:18 AM (in response to rareddy)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
rareddy May 22, 2013 9:13 AM (in response to neetacomp)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
neetacomp May 27, 2013 6:49 AM (in response to rareddy)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
rareddy May 27, 2013 8:44 PM (in response to neetacomp)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
neetacomp Jun 5, 2013 1:06 AM (in response to rareddy)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
rareddy Jun 5, 2013 9:31 AM (in response to neetacomp)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
neetacomp Jun 6, 2013 7:08 AM (in response to rareddy)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
rareddy Jun 6, 2013 8:39 AM (in response to neetacomp)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
neetacomp Jun 7, 2013 12:01 AM (in response to rareddy)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
rareddy Jun 7, 2013 7:54 AM (in response to neetacomp)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.
Ramesh..
-
12. Re: Row Level Security using TEIID Designer
van.halbert Jun 7, 2013 8:41 AM (in response to rareddy)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 Jun 7, 2013 9:03 AM (in response to 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
-
14. Re: Row Level Security using TEIID Designer
rareddy Jun 7, 2013 10:09 AM (in response to van.halbert)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..