Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Difference in table ownership and schema concept across diff databases

Hi

My requirement is something like this: My application needs to determine the table owner given a tablename.

What confuses me is the different ways this need is handled for different databases in my application. I am trying to find to out how i should handle this in MaxDB.

In Oracle: The username which is used to establish a connection through OCI is returned as the default table owner every time. Assumption is we can't have different owners within the same schema because user id is schema id.

In MySQL: Doesn't have a concept of table ownership. The closest they come is the schema that qualifies various sub-database spaces within a database. So, schema name is returned as the table owner name every time.

Coming to MaxDB:

I see that the concept of both schema name and owner name are present. At least as per this:

sqlcli bwt=> \dc domain.columns

Table "DOMAIN.COLUMNS"

Column Name Type Length Nullable KEYPOS-----

-


-


SCHEMANAME CHAR UNICODE 32 YES

OWNER CHAR UNICODE 32 YES

TABLENAME CHAR UNICODE 32 YES

So, can you advise me how i should handle this for MaxDB.

Regards

Raj

Former Member
Former Member replied

Hallo,

1. a tablename is not the 'full' name of a table, as the current user may know different tables with the same tablename, contained in different schema of the same or different users.

2. If you use only tablename (without schmemaname) in DML /selects/... the table in the current schema will be used.

But for other commands you have to be more precise.

3. If you want to see the owner of a table, you may ask

select ownername from domain.tables

where tablename = '<...>'

and schemaname = '<....>'

( the second line is needed, because of 1. )

4. Things are like this, as every user has a schema with its own name, but may create other schemas with different names. And you may grant privileges to other users to allow them to make DDL in your schema.

Hope this info will help.

Elke

Elke

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question