JDBC;DRIVER={org.teiid.jdbc.TeiidDriver};

URL={jdbc:teiid:@mm://:30000};

driver=org.teiid.jdbc.TeiidDriver

driverFile="teiid-client-7.7.7-hotfix.jar";MSTR_JDBC_JAR_FOLDER=C:\dev\Jars;

JDBC;DRIVER=org.teiid.jdbc.TeiidDriver; URL={jdbc:teiid:DAL@mm://dal-dev.fm.rbsgrp.net:32200;disableLocalTxn=false;ansiQuotedIdentifiers=false;autoCommitTxn=OFF}; driverFile="teiid-jdbc-8.12.5.redhat-8.jar";MSTR_JDBC_JAR_FOLDER=C:\dev\Jars;

Microstrategy Impala ODBC driver desktop (Certified)

I am new to Microstrategy so found it difficult to identify exact way to connect DV from ms. I tried ODBC, Cisco Data Virtualisation and JDBC via Presto but no luck.

DataVirtualisation is not listed in available databases. Finally it worked in hadoop way

Follow these steps to Connect via

Download Teiid JDBC jar either be teiid-jdbc-8.12.5.redhat-8.jar or teiid-client-7.7.7-hotfix.jar or any other teiid jdbc client jar.(Or use the one I suggested for Squirrel). Place it somewhere in local path.

Open Microstrategy.

Click on Add New Data

Now this is interesting that , I tried Prestro and cisco data virtualization that also not worked.

Select Hadoop >> Impala

Click any one of “Build a Query/Type a Query/Pick Tables”.

Click “Add new data source”, choose Database:Impala, Version: Impala 1.x or Impala 2.x.

Click “Add driver” and choose the driver which downloaded in step 1.

Choose ”Edit connection string”, and input below connection string:

Then we can import data from impala on MicroStrategy 10.0 desktop successfully.

TN250923: Connecting to Databases using JDBC Drivers in MicroStrategy 10.x - MicroStrategy Community

Security patterns can be enforced based on constraints defined within DAL. Row-based and column-based permissions may be used together or separately to control at a more granular and consistent level the data returned to users.

In order to understand current requirement, Users will be most likely divided into two kinds of groups/role :

Country Specific Role

Username : usr-dv_<country specific, say UK>

Role name ; UK Role

Mapped Group : G USR - ROLE - UK - DAL READ ONLY

  Users from this group can view data for records having atleast one column declaring country as UK.

  No access to view/tables where country is not mentioned

  For this we can have two ways :

  A separate virtual db contains models which are fetchin data only for UK user. In brief this vdb will fetch only UK specific data.

  And we can add more securioty constrains on top of that UK data.

  A Generic VDB has one of the role as UK role,

  User belongs to this role can only view Filtered rows of results from table/view based condition provided for this data-role

Similarly, we can have more roles as well as user assigned to that role to have country specific filtering.

Username : usr-dv_emea

Username : usr-dv_all

GCN White role

Username : usr-dv_<GCN group specific>

Role name ; GCN White Role

Mapped Group : G USR - ROLE - GCN - DAL READ ONLY

  Users from this group also can view any country data but subject to their approved GCN only.

  As there would be multiple GCNs, It would not be an efficient way to create multiple roles specific to GCN.

  So this group user can view data for all approved GCNs and to query a specific gcn info, gcn code must need to be passed in query.

  A view in VDB will be accessible for this group's user. But to limit only client code based data, client code/gcn number would need to pass in where clause. This can be done via application like tableu to send logged in client' gcn to the query.

  <Require mote input for this role>

For any other custom security pattern and to understand security in general, We can have basic two level of security :

Object level Security:

  It includes hiding an object(table/view) for a specific role.

  Allowing different set of permission on a role for (CREATE, READ, UPDATE, DELETE)

Column level Security: It includes-

  Hiding some columns of an object for a given role.

  Allowing different set of permission on a role for (CREATE, READ, UPDATE, DELETE) on that specific column.

  Column masking is applied only against SELECTs. Column.

Row level Security : It includes -

  Showing filtered set of rows for a given role based on a certain predefined condition. The condition will act as a row-based filter and as a checked constraint.

  A condition is applied conjunctively to update/delete/select where clauses against the affected resource. Those queries will therefore only ever be effective against the subset of rows that pass the condition, i.e. "SELECT * FROM TBL WHERE blah AND condition". The condition will be present regardless of how the table/view is used in the query, whether via a union, join, etc.

  Inserts and updates against physical tables affected by a condition are further validated so that the insert/change values must pass the condition (evaluate to true) for the insert/update to succeed - this is effectively the same a SQL constraint.

Apart from this, in case some custom security enforcements is contradicting or not possible to apply on some object, DAL can be used to create virtual models on top of actual objects and thus separation of security rules can be applied on this new virtual view.

SELECT

  A.segname AS segname, A.segnumber AS segnumber

  FROM

  (EXEC XMLSrc.getTextFiles('test.xml')) AS f, XMLTABLE('/pnr/Segements/segment' PASSING XMLPARSE(DOCUMENT f.file) COLUMNS id string PATH 'id/text()', segname string PATH 'segname/text()', segnumber string PATH 'segnumber/text()') AS A

<security-domain name="teiid-security" cache-type="default">

                    <authentication>

                        <login-module code="org.jboss.security.auth.spi.UsersRolesLoginModule" flag="required">

                            <module-option name="usersProperties" value="${jboss.server.config.dir}/teiid-security-users.properties"/>

                            <module-option name="rolesProperties" value="${jboss.server.config.dir}/teiid-security-roles.properties"/>

                        </login-module>

                    </authentication>

                </security-domain>