Kbase 19954: How Oracle Dataserver resolves OWNER information
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/05/2009 |
|
Status: Verified
GOAL:
How does the Oracle DataServer resolve owner information pertaining to tables, stored procedures, user-defined sequences, and Progress-defined sequences within Oracle?
GOAL:
If the Oracle objects are owned by user A, and another user that does not own the tables, (User B) connects to foriegn datasource, how does the DataServer know which objects to refer to?
FACT(s) (Environment):
All Supported Operating Systems
Oracle DataServer
FIX:
The Dataserver stores the owner information inside the schema holder that is used to build the SQL statements passed to the Oracle database.
The owner information is resolved differently depending on the object as in the following examples.
1) For regular 4GL statements referencing tables and stored-procedures:
The owner information is resolved at run time for 4GL statements referencing tables and stored-procedures. The Dataserver gets the owner one time, when the schema check is done, and when it uses an Oracle function to compare the schema information. Progress does not store the owner information for these objects within the r-code.
For example, you may have two identical schema holders that expect the owner information. You can compile the application against the schema holder with a table owner of X and run it against the schema holder with a table owner of Y with no problem. The SQL statements will reference owner Y, in this example.
If the owner information in the schema holder is blank, the owner piece is not referenced in the SQL statement. This makes Oracle search for the object in the schema of the user that is connected to the database.
2) For user-defined sequences:
The Dataserver gets the owner information for user-defined sequences at compile time. There is no compare function for sequences, so the owner is resolved at compile time. This means owner information is stored in the r-code. In other words, the sequence owner in the schema holder has to match in both the compile time and run-time.
If this turns out to be a problem due to different schema owners, you must leave the foreign owner blank in the schema holder, compile the application, and create a public synonym for the sequence in the target database. Even though the Dataserver does not pass the owner in the SQL statement, Oracle will be able to find it using the synonym.
3) For Progress-defined sequences:
There is no record of the sequences that Progress uses for 4GL compatibility inside the schema holder. The owner is resolved at run time.
4) For progress SQL statement (SQL pass through):
When a sql pass through statement is compiled, the sql statement generated and the user connect at this time is store in the rcode. From this point on, the Sql is cannot manipulate runtime, so whatever is generated at compile time it's what is going to run.
5) For Send-Sql-Statement:
The dataserver uses whaterver string you defined in your code. This means that, if you specify the owner, like the example below, the owner is going to be used. But is you ommit the owner xxx, the user connected at run time is going to be used.
RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE NO-ERROR
("select count (*) from xxx.customer where name between 'A' and 'Z' ").