Questions to SAP Support: Is ODBC access to the database a good idea?

[lbreddemann]

Today I got a support message to work on that I believe is of rather broad interest.

Therefore, I decided to "forward" it to the SDN so that all community members can benefit from it.

I translated and reformulated the message a bit, so that the customer specific information is protected and the core questions are clear. Here's the message text:

The support message

Dear SAP Support,

for several reasons we need to provide data from our SAP Netweaver 
database to another company. 
We have to do this by contract and law - so there is no way to avoid this ‘data delivery'.
The company requests the access to our productive billing and financial data 
via ODBC and a third party tool.

Now my questions are:

How big is the security risk that occurs with such a procedure?

Are the SAP security mechanisms passed-by with that?

Is it possible to access data from other clients and or accounting areas?

Could data inconsistencies occur if the third party tools runs into an error or does something wrong?

In general, is it recommended to grant or deny other companies access to the database so that
they can access data without specific access control/monitoring?

Which procedure would you recommend?

Looking forward to hear from you soon.
With kind regards ...

My answers to this

Leaving alone license restrictions for such a direct database as they are imposed e.g. on Oracle licensed sold through SAP (see note 581312 - Oracle database: licensing restrictions), these are all common questions that I will try to answer one by one:

1. "How big is the security risk that occurs with such a procedure?"

The answer is (of course): it depends. If you just give away the SAP-schema user logon (SAPR3 or SAP<SID>), than you do have nothing to worry about security anymore. You just don't have any data security anymore! All of your business data is available to this user! There is no restriction or control mechanism whatsoever - so never ever give this logon away!
The next better option would be to create a new logon to the database and provide the data access by views or procedures so that no direct access to the base tables is possible. This will at least prevent the access of data that the ODBC should not access.

Anyhow - still this is a security issue. The ODBC connection requires a network access to the database server.
And here the real security issue only begins. If the company that requires the data needs to access it from outside of your company network, than it would be necessary to make the database server visible in the Internet. There is no bigger security no-no than doing such thing!

So the one sentence answer is: the security risk has to be considered very high!

2. "Are the SAP security mechanisms passed-by with that?"

Yes they are. All of them. There is no access control below the data access layer of SAP Netweaver. This is due to many different reasons, but technically speaking, we support many DBMS platforms that provide very different access mechanisms. Still the security functionality for Netweaver applications needs to be the same on all of the supported DBMS. Thus, the access layer ‘above' the database serves as an abstraction to the database functionality.

Also - and this is the far more important reason - the access models of today's DBMS are object centric. Which DB-logon is allowed to query what view or to insert into which table? However, business permissions are not about tables and views but about business data (often spread over many different db objects), about processes and responsibilities. Therefore, SAP Netweaver provides a very huge and flexible permission system that allows an enterprise wide security management from business perspective.

Accessing just the data storage (the database) means: ignore all these security functions, just look for raw data.

It the same as opening the database files with a Hex-Editor. You will see all the data stored in it.
One sentence answer: "Yes, all SAP security mechanisms are passed by!"

3. "Is it possible to access data from other clients and or accounting areas?"

Again: it depends. If the ODBC user has direct access to tables and views of the SAP-schema use, than it can see all data. No exception - everything will be visible.

If the data access is provided by the means of views and/or procedures, than you can limit the data access through this.

But if you do so, you have to do it manually for every single table and view the ODBC user should access. As soon as another table has to be visible, you've to create such an ‘access point' and limit the visible data.

That is a very error prone and time-consuming thing to do. So better, do not do it! Further, down I will point to some alternatives.

Answer in one sentence: "Yes - all data will be visible!"

4. "Could data inconsistencies occur if the third party tools runs into an error or does something wrong?"

I find that this question is a bit too narrow, so I widen it up to
"Is it possible that there will be any database problems just by allowing ODBC access to the database?"

Ok, let's just assume you don't grant change permissions (UPDATE, DELETE, INSERT) to the ODBC user. Of course, if you do, everything could happen - really everything!

However, what if the user just has read permission? Well, even than it might lead to serious problems.
Think of queries that cause the database to perform loads of I/O or CPU and thus slowing down your business application.
Think of read-locks that prevent that your business users change data and do their daily work.
Think of database errors that are caused by the ODBC queries and lead - by accident - to a database crash.

Therefore the answer is: "Yes, it's possible that your database will run into problems caused by the ODBC access."

5. "In general, is it recommended to grant or deny other companies access to the database so that they can access data without specific access control/monitoring?"

In one sentence: "It is never - really never - recommended to grant access like this!"

Even worse, it may be an illegal act to do so, since in most countries there exist strict laws against the abuse of data. In addition, many SAP customers need to apply business regulations like SOX or BASEL II.

As a company you are responsible today for who can access what data when. And you will likely hold liable if you fail to do so.

6. "Which procedure would you recommend?"

After all those ‘bad news' the answer to the last question should be the solution for the problem. Unfortunately, it's not that easy to give an answer to it, that suits for all situations.

Usually it is a good idea to implement the data access interface not on the database layer, but on the application layer. Therefore it would help a lot to implement an API for the data access in Netweaver that is then used by the third party tool, e.g. via RFC, SOAP, Web services or any other hip protocol.

SAP even offers a product just for such purpose: XI server. With that, you can do virtually everything with your data and still have complete control over the data access.

In any case, such an application interface needs careful planning and implementation.
As the data should be exchanged between different companies, a very clear interface specification needs to be done.
Exchanging data just  on the database level is by far the worst option of all available ones.