run native sql commands on objects in different database schema

[Former Member]

Our database is divided into two schemas: PSAPVR3 and VERTEX. One of our developers is trying to run native SQL statements on objects in the VERTEX schema. When he runs this he receives the short dump DBIF_DSQL2_OBJ_UNKNOWN. We're assuming this is because the OPS$DR1ADM which owns the PSAPVR3 schema doesn't have permissions to perform operations on the VERTEX schema objects (the lack of permissions has been confirmed). We've come up with two options but are not sure which would be the SAP preferred method.

1. Give OPS$DR1ADM permissions to perform needed SQL statments on VERTEX schema objects.

2. Add an entry to the DBCON table. Does this work as usual since we are not trying to access an external database?

[Former Member]

Hi Dana,

The other option to look at is to create synonyms for VERTEX schema objects in PSAPVR3 schema. This way you do not have to make changes to SAP standard settings and if granted the right privileges the synonyms will work.

Hope this helps.

Cheers,

Nisch

What Are Synonyms and Why Use Them?

===================================

A synonym is an alias for any table, view, materialized view (snapshot),

sequence, procedure, function, package, Java class schema object, or another

synonym. Synonyms are generally used for security and convenience.

When database objects, such as tables or views, are created they are associated

to a particular owner and location. In order for others in a project or group

to refer to these objects, they either need to:

1. know the fully qualified name of the objects, or

2. have access granted to themselves, each individually for each object by

the owner, or

3. create a public or private synonym for the object. This administers

and controls access to the object at one place, by using the synonym.

A public synonym is owned by the special user group named PUBLIC and every

user in a database can access it. Synonyms can simplify SQL statements for

the developers by the fact that if public synonyms are used, references to

database objects do not have to be fully qualified.

For Example:

SQL> SELECT * FROM jward.sales_data;

versus:

SQL> CREATE PUBLIC SYNONYM sales FOR jward.sales_data;

SQL> SELECT * FROM sales;

A private synonym is one created in the schema of a specific user who has

control over its availability to others. This is the same concept as public,

only with more specific access control. If a user has an object in his schema

with the same name as a public synonym that has been defined for a different

object, any statement issued by the user that uses that name will refer to the

object in his schema, not the object referenced by the synonym.

In order to create a public synonym you must have the CREATE PUBLIC SYNONYM

privilege. In order to create a private synonym in your schema you must have

the CREATE SYNONYM privlege. In order to create a private synonym in someone

else's schema you must have the CREATE ANY SYNONYM privilege.

[fidel_vales]

Hi,

I've never tried the DBCON option, but it should work.

Regarding the first option, OPS$DR1ADM does not own the SAPVR3 schema.

SAPVR3 schema is (probably) own by SAPVR3.

OPS$DR1ADM should own only th table SAPUSER and have not many authorizations ( connect, read this table is all this user does )

You can access the VERTEX schema if you give proper authorizations to the SAPVR3