Errors with Data Module using Uploaded File & Data Server

[adam_mc]

CA 11.0.13

Scenario:

-   Data server has two connections (c1 and c2)
-       Create data module, choose data server, get prompted for connection.  Choose c1
        Save data module
-   Use Data Module in Report/Dashboard, get prompted to pick a connection.  Works with either connection.
-   Open data module to edit, pick c2, no issues editing data module.
-   Secure c2 denying a user group access.
        Secure c1 granting same user group access.
-   Administrator still gets prompted when editing data module or using report/dashboard
-   Log in as one of the denied users.  Create report/dashboard - No issues.  No prompt – assuming that it is using c1.
-   Log in as administrator. 
        Grant c2 to user group. 
        Deny  c1 to the same user group.
-   Log in as one of the denied users. 
        Create report/dashboard - Report Fails.
        When attempting to open Data module - Shows errors.

It appears if you deny access to the connection that was used to create the data module, it causes errors for those users in the denied group.
However, will work for other users.

Need this to work similar to Cognos connections with multiple signon's (with grant/deny permissions) which will automatically use appropriate signon based on user's permissions.

Any thoughts would be greatly appreciated.
Thanks in advance,
Adam. 

[adam_mc]

Update:

I was able to select "Allow Web-Based Modeling" on the data source connections defined in Cognos Administration > Configuration > Data Source Connections and then use those in my Data Module.
This allowed successful selection of a data source connection and the report/dashboard to run with the required user.

However, it "hard-coded" the associated schema name to the file names in the SQL running on the database.
This subverts our Oracle database security as we use synonyms to redirect to a view for certain tables (so that certain sensitive information is masked for a user group).

Again, any thoughts would be appreciated.
Thanks,
Adam.

[bus_pass_man]

It appears that your objective is to have data security. 

Is there any particular reason why you cannot implement it using the data security filters functionality which is built into CA?

[Andrei I]

Adam,
You might be able to leverage Oracle Virtual Private Database

Restricting Access with Oracle Virtual Private Database
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tdpsg/restricting-access-with-oracle-virtual-private-database.html#GUID-92A1A94D-319C-4FB2-AEC3-B86415D72628

Example - Using Command Blocks for Virtual Private Databases for Oracle
https://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cra.10.2.2.doc/c_example--usingcommandblocksforvirtualprivatedatabasesfororacle.html#Example--UsingCommandBlocksforVirtualPrivateDatabasesforOracle

[adam_mc]

Ideally, I don't want to create any new security - I want Data Servers & Data Modules to work in the same way as the security is already predefined Cognos Connections/Sign-on's!

Currently, our PROD connection has 2 sign-on's (cognos & cognos_secure).
The sign-on cognos has deny authority for a group of users and the sign-on cognos_secure has grant permissions for that same group of users.
When a report is run using a package, the user is directed by permissions as to which sign-on the connection uses.
For users in the cognos group, all rows are returned but, certain data items are masked with the value 'SECURE' (we want the rows returned, but just not the ability to see certain data elements).  However, when the same report is run by a user in the cognos_secure group, all data items are returned and visible.

So, a report over a package using a cognos connection, run by a user in the cognos (masked data group) has their query on the database "redirected" to views (for certain tables) using Oracle synonyms - These views mask the returned data.  The same report run by a user in the cognos_secure (all data group) has their query go directly over the tables in the Oracle database.

This is the way Cognos works for us NOW!

However, when I use a Data Server in a Data Module for a report (because it seems I need to define schemas in the data server connection), it defines the schema in the generated SQL.

Simply, this is the difference in generated SQL's:

   With Package and Cogons Connection/Sign-On's:  select * from table
   With Data Module & Data Server:                         select * from schema.table

This "hardcoding" of the schema name, even though the selected user sign-on works, overrides the database synonym functionality as the schema is explicitly defined.  Thus undermining our security. 

It seems that we will never be able to take advantage of Data Server Connections without having to completely redesign our security within Cognos Analytics.
Unless... anyone has any other thoughts?

Thanks in advance,
Adam.