Difference in table ownership and schema concept across diff databases
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
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.
Elke Zietlow replied
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.