Difference in table ownership and schema concept across diff databases

[Former Member]

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 · Accepted solution]

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

[lbreddemann]

Hi Raja,

maybe you want to check on how schema and owner differ as concepts.

[Schema Name (schema_name)|http://maxdb.sap.com/doc/7_6/fa/7974404a6fcf62e10000000a155106/frameset.htm]

A schema creates a namespace. [http://en.wikipedia.org/wiki/Ontology_(computer_science)]

The owner of a schema or any other db object is a user that has certain privileges on the objects he owns.

Therefore the answer to your question relates to what you really want to know.

KR Lars

[Former Member]

Hi Elke and Lars,

Thanks for the excellent information. So, if we talk in terms of examples inside database instance 'MAXDB1', MONA is the user/owner with default schema 'MONA'.Additionally there is another schema 'HOTEL' for the same user/owner MONA. Now in the below example for table TEST as well as CITY the owner will be MONA irrespective of the schema name.

sqlcli MAXDB1=> select SCHEMANAME,OWNER,TABLENAME from DOMAIN.TABLES

SCHEMANAME	OWNER	TABLENAME
MONA	MONA	TEST
HOTEL	MONA	CITY
HOTEL	MONA	CUSTOMER

I think in my application if am logging into the db with MONA,RED as the db username,password then MONA should be returned as the ownername. My application doesn't look like handling the possibility of being queryied about the ownership of a table which is not owned by the current user.

Regards

Raja