Need help to enable intranet use of CR 10 and maintain data security

[SJSFoxPro]

We are using CE 10 to enable access to nearly 500 published reports via our company intranet (remote sites statewide). We are currently using Business Views for row security so that end users can only access their own division data. Our primary datasources are in Microsoft SQL Server. Currently, the only report developers are on the LAN.

Question: We would like to enable some of our remote intranet users the ability to use Crystal Reports 10 for reporting. We have found that Crystal Reports Explorer with CE 10 is insufficient for many of our power users and most of our reports are too complex to be modified with the tool.

Our DBA has concerns about data security. Because we are using Business Views for row security, there is concern that a CR user could establish an ODBC connection to the database and bypass our Business Views.

We believe we might be able to control this if we limit ODBC access to the database. The router would limit access to the SQL Server port to only the servers used in our CE 10 system. We had hoped that doing so would limit users to data access only through the established Business Views.

Is anyone else using Crystal Reports 10 this way successfully?

Can anyone point out data security concerns?

Can anyone offer other alternative ways to structure this?

Can anyone offer any Business Objects documentation that might help with this?

 

[Turkbear]

Hi,
Instead of trying to block ODBC in general, why not use database security to disallow any connections except for the administrator and the user account used by the business views..If the 'regular' user accounts ( even if you have some) have no rights to the data then a direct connection would be useless..

( Of course, keeping the Business View account information secure would be a necessity).

 

[SJSFoxPro]

Hi Turkbear,

I appreciate your response! Our business views are absolutely secure. We have a small number of developers who work directly with the DBA in creating them, so I don't see any problem there.

Currently, our LAN users who use CR all have read access to the database through NT authentication. It just happens that all LAN users are our business office personnel, so they have permissions to view all company data. They do not, however, need to make a direct ODBC connection. We prefer that they access the data through business views.

If I understand you correctly, we could limit database access to only the handful of NT users who create the business views and the DBA's? So, the intranet users and LAN users alike could access the database through the business view ODBC connections and could not establish a direct ODBC connection?

What do I set up in the ODBC connection for authentication?

And, then, we set up the user accounts / DBA access through the database permissions? (SQL Server in this case).

Thanks again! I GREATLY appreciate any help you can provide.

 

[Turkbear]

Hi,
The authentication would be at the database, not the connection method..Create a NT group that is allowed direct access and include those who need it..for all other NT users, deny connection to the data.

 

[SJSFoxPro]

Hi again,
We have our LAN users set up the way you suggest (members of an NT group that have access to the database). Our LAN users are not our security concern because they have access to all rows of data, for all divisions of the company.

We have found that when using the desktop application CR, even though the user logs into CE to use a BV in the repository, CR still uses an ODBC connection on the local PC to return the data. We are relying on the row security in the BV’s to filter the data that our WAN (remote divisions) users are allowed to see.

My concern is that if they must have an ODBC connection on their PC, what is preventing them from bypassing the BV’s and creating a report directly against the database tables or even connecting the tables using the ODBC connection with a product like Microsoft Access and seeing the data we are trying to prevent them from seeing?

Sorry to keep hashing this over. I’m just not seeing how we can get around the way CR works. We want to provide CR as a tool, but don’t want to create a security hole.

Thanks for taking the time to respond to these inquiries. Trial and error seems to be the only way to get answers and I appreciate sharing and learning through what others have found – positive or negative.

 

[Turkbear]

Hi,
If the users have to have the same ODBC connection, be sure they do not have access to the password it uses, if possible.

Would it not be sufficient to have the CE server have the ODBC connection, not the user's PC?
We have never tried to implement AdHoc design using Business Views, so I do not know if that is the case.


Sorry..

 

[Turkbear]

Hi,
A little more info:
It appears from the Business Views' docs that the connection information ( including username/password) is saved in the repository as part of the connection object.
I do not understand, then, why each user needs the ODBC DSN. Does not the Business View use the CE server's ODBC DSN to make the connection?

 

[SJSFoxPro]

Hi,
I appreciate the dialog on this!

What we are finding is that CR makes the connection to CE and gets the information it needs (user credentials, BV row security it needs). When we remove the ODBC connection on the local PC, it prompts the user with a message to choose which ODBC connection to use (on the local PC).

It doesn't make sense to me either. The answer I was hoping to find is to only have the ODBC connection on the CE servers and provide security as you suggested in your first thread - that would make logical sense to me! We have put so much pain into a solid security model for the repository, BV's, and CE. It doesn't make sense that CR bypasses it! But, we haven't found a way to get CR to stop prompting for the local ODBC connection.

If you need any information to duplicate a test, or find anything else on the subject, please let me know. I'm afraid the answer is that it doesn't work the way we'd like it to.

 

[Turkbear]

Hi,
That seems like odd behavior.
Have you contacted Business Objects' tech support..maybe there is a missing piece somewhere in your model..

 

[SJSFoxPro]

Hi,

We have had Business Objects review our system and they don't think there are issues.

I really appreciate your dialog on this, because it has sparked some other avenues to research that we might not have thought about.

One of the things we are going to try is changing the connection in our BV from ODBC to SQLOLEDB.

I'll post the results if anything positive is gained.
)

 

[cmmrfrds]

I don't think there is anyway to avoid people bypassing the business views. The only way is security at the database level. Don't give users permissions to the table(s) only allow access to the underlying tables through VIEWS. There is a feature in Crysal 10 that gives access to the logged in users NT or AD name. Have an underlying link to a table that contains all the user NT or AD names.

Make a parameter on ALL the views that requires the NT name.

Make it a requirement to pass the login name in the select criteria in any Crystal Report. The Crystal variable that contains the login name is "currentceusername"

For example the select criteria is:
?mylogin = currentceusername
and more select criteria.......

View.
Select * from myview A
inner join on logintable B
on b.id = A.id
Where b.myloginname = ?mylogin

This way it doesn't make any difference if the report is run from CE or the desktop or Microsoft Access the access is restricted by supplying the login name.