ODBC Connection to SAP ECC Oracle Database

[Steven_ZA]

I am the resident SAP Basis Support person on the site. We have been requested by the site data architect to supply a ODBC link, user with sysdba rights and password in order to link Power designer to our ERP ECC6 Oracle database.

I am however reluctant to allow this, as this will impact on our SAP/Oracle license agreement. It will also bypass normal SAP security rights and make visible Human Resource and Financial data. Is there another method to link the your PowerDesigner software to our  ERP Oracle Database in order to export the repository/metadata other than ODBC like RFC or Bapi.

I am battling to come to terms with the fact that SAP Note 581312 states clearly that ODBC connections are forbidden.

"If other software is used, the following actions, among other things, are therefore forbidden at database level:

• Creating database users
• Creating database segments
• Querying/changing/creating data in the database
• Using ODBC or other SAP external access methods"

On the other hand, the only connection available from SAP PowerDesigner to the SAP database is via ODBC.

Specific location in the tool:

SAP Business Suite Data Dictionary Import Wizard

     SAP Business Suite Database Connection

          Enter your SAP Business Suite database connection parameters.

It is here where we need to provide the ODBC connection when selecting Oracle.

Any clarification and how to proceed on this will be appreciated.

[Former Member · Accepted solution]

Hi,

The DBA does not want that data architects have access to production data.

How reverse engineer an Oracle schema production with PowerDesigner if the DBA does not grant the role SELECT_CATALOG_ROLE?

When the DBA grants the CONNECT and SELECT_CATALOG_ROLE permissions without others grants. The consequence is the following; If the user SELECT * FROM ALL_TABLES It can't read the tables owned by another user / schema. It can't describe content on a table in another schema unless the DBA grants to the user explicit rights.

By cons, if the user SELECT * FROM DBA_TABLES It can view owners tables from others schemas

Solutions :

1) So if you give grants CONNECT, SELECT_CATALOG_ROLE to each user for each schema. There're no problem. But this can be a tedious task!

2) Open the Oracle .XDB definition file and replace all ALL_ by DBA_ (Do a copy before)

3) Create a task that perform a reverse engineering schema on demand (create a DDL file). This task will have privileges on the schemas and objects. Users have access to the schemas in the form of .SQL file (DDL).

4) Create a PL/SQL procedure thats users can launch. The PL/SQL procedure can use DBMS_METADATA.GET_DDL to extract the DDL

Note : The problem occurs because PowerDesigner XDB definition use _ALL_ and not _DBA_ views from Oracle dictionnary.

Some definitions:

• USER_TABLES: Used to describe a list of tables owned by the current user (current schema)
• ALL_TABLES: Used to describe a list of available tables to the current user. That is to say, all the tables owned by the current user and all the tables for which was granted an explicit privilege to that user.
• DBA_TABLES: Used to describe a list of all tables in the DBMS
• SELECT_CATALOG_ROLE: Provides SELECT privilege on objects in the data dictionary. this role can not Be used to create Any objects (tables). Also include, HS_ADMIN_ROLE.
• HS_ADMIN_ROLE: Used to protect access to the Heterogeneous Services (HS) data dictionary tables (grants SELECT) and packages (grants EXECUTE). It fournisseur to SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE That Such users with generic data dictionary access aussi can access the HS data dictionary.