How to change the owner of a database object?

[VolkerBarth]

A question that came up in the NNTP groups lately...

Well, I'm quite sure that SQL Anywhere (up to v12.0.1) does not allow to alter the owner of a database object at all, i.e. to do something like

ALTER TABLE User1.Table1 RENAME User2.Table1;

or

ALTER SCHEMA User2 TRANSFER User1.Table1;

Furthermore, I remember to have read several NNTP discussions on that in the past, but I can't find an official statement there nor in this nice forum nor in the docs.

Where does it tell that the only way to "change ownership" is by dropping and recreating the object for the new owner?

[Former Member]

Out of curiosity, you used tables as an example. Is that your primary target, or do you have a broader scope in mind?

There is a strong argument for allowing ownership changes for tables since there is data that has to be copied in the standard method of create/copy/drop, which takes both time and space.

Other objects, it seems to me, would cater to convenience rather than providing a measurable advantage. But, please share your thoughts.

[Breck_Carter]

If you wanted to cover 80% of the current known cases, do it for tables.

Heck, that would probably cover 100% of the current KNOWN cases since that is all people ever ask about.

HOWEVER, no good deed goes unpunished... five minutes after implementing ALTER OWNER, someone will abuse use it to implement some new application feature... for example, ALTER OWNER to "delete" yesterday's data without actually deleting it yet, instead of altering the table name... perhaps not a good example, but as they say, "availability is the mother of invention"... no, wait, that's not what they say... but they should 🙂

[VolkerBarth]

No particular requirements on my part - I've not been actively asking for that feature, just been looking for the docs on the current state.

The only time I happen to need "alter" the ownership is when I have forgotten to use the according SETUSER statement, and that happens in development and with temporary tables only, and then it's no big deal to fix that.

That being said, I see two approaches:

1. Just add a facility to alter tables one by one, as their re-creation needs to copy existing data. (As Breck suggests.)
2. In case a real bunch of related tables with FK relationships, triggers, procedures, permissions and the like should be moved to another user I would expect that owner names in FK clauses, triggers, procedure code might have to be adapted as well. Here, I would rather then suggest to separate between users and schemata, which would obviously be a much bigger change in the database engine, and then create a facility to "transfer whole schemata to another user" - or simply detach users from schemata, so that changing schema ownership would be independent of the table definition.

If I would currently have to deal with "changing the owner" of a bunch of complex tables, that would apparently require to modify client code as well, as the owner name will commonly be used in SQL statements there. In that situation, much work has to be taken, and therefore I would accept "Frum Dude"'s "modified reload" as a workaround...

[Breck_Carter]

@Volker: To be perfectly clear, the ALTER OWNER feature would not copy or move any table data... it would only be useful if it was instantaneous... a table's "owner" is just single column value in a single row in ISYSTAB.creator, and (cascading aside) that's all that needs to be changed.

Also, I am 100% opposed to any separation of users and schemata... that would introduce complexity the world does not need. If someone wants to **** around with "schemas" they should switch over to Oracle or some other offense against nature.

( PS if you want to kill anything in SAP, that's how you do it: you just say "that's how it's done in Oracle" 🙂

[VolkerBarth]

@Breck: I surely agree on both points:

To clarify: The hint on ALTER TABLE CHANGE OWNER to "copy data" was just that currently one has to copy data for existing tables, whereas an ALTER PROCEDURE CHANGE OWNER would not be much easier than to drop/re-create the procedure...

I guess typical SA databases will have

1. either only one DBA user owning all user-defined tables
2. or have such a particular general schema and allow users to add their own tables and the like,

and for these situations the current owner/namespace concept seems fine with me, and I would clearly second your suggestion to leave it that way.

That's my point of view - I simply don't know how many "big" SQL Anywhere setups support several distinct applications in one central database in which each application would "naturally" have their own schema (i.e. would use their individual owner). - Not my sports:)

[Former Member · Accepted solution]

I cannot recall seeing this explicitly documented, Volker. But you are correct that, currently, SQL Anywhere does not permit an ALTER statement to change the owner of a table.

[VolkerBarth · Accepted solution]

@Glenn, I'm very happy that this forum is not "Glenn-free"...:)

[VolkerBarth · Accepted solution]

...and does not permit any other statement to the change the owner of a table, either - right?

[Former Member · Accepted solution]

Correct. Currently, with 12.x software there is no way to change a table owner.

[Breck_Carter]

This should be a Product Suggestion. This has been a common request over the years, often from folks coming from Transact SQL who mistakenly thought "dbo" as an owner was a good idea (it's not, for a plethora of reasons, not the least of which is that it's a user id in SQL Anywhere but some other incomprehensible thing in Transact SQL).

FWIW the unfortunately-named PostgreSQL has ALTER TABLE name SET SCHEMA new_schema which is kind of cheesy as syntax goes ("it's an owner, not a "schema", sheesh!) but it apparently does the trick; e.g., it cascades to other objects.

I'll bet money that "cascading" is the reason it hasn't been implemented yet... but having it would be yet another feather in SQL Anywhere's cap, something to make the administrator's life easier that most other products don't have.

[Former Member]

To provide a bit of background material on this subject, PostgreSQL uses ALTER TABLE name OWNER TO new_owner. Changing the owner doesn't have the cascading effect you mention. Schemas and owners are different things.

Microsoft also supports this with its ALTER AUTHORIZATION statement.

ASE recently introduced ALTER TABLE name MODIFY OWNER as well.

I don't know of any other database products that support this feature.

[VolkerBarth]

@Breck: Feel free to post this as a product suggestion (as you will have done several times in the past, I'd bet).

Personally, I was just searching for an "official document" (and thankfully Glenn can still speak "officially") - I have never had the need to alter the owner myself...

[Breck_Carter]

Both PostgreSQL and SQL Anywhere use "owner" and "schema" to mean different things. Arguably, the PostgreSQL "schema" is analogous to SQL Anywhere's "owner" since both are useful: you can code SELECT FROM schema.table in PostgreSQL and SELECT FROM owner.table in SQL Anywhere. Folks seem to ask "how do I change schema in PostgreSQL" as often as they ask about changing the owner in SQL Anywhere.

Similarly, the PostgreSQL "owner" seems to be analogous to SQL Anywhere's "schema" since both seem utterly useless... well, I'm not sure about PostgreSQL's owner being useless, but CREATE SCHEMA in SQL Anywhere certainly is... not one in one thousand SQL Anywhere developers even knows it exists, far fewer than the number who know about CREATE DATATYPE, possibly the second-most-useless feature...

...but, that's just thread drift 🙂

[Former Member]

@Breck, I don't want to drift this thread too much, and I'm sure you are much more familiar with PostgreSQL than I am. However, it would seem to me that in PostgreSQL, ownership is strictly focused on security-related grouping, whereas schemas are strictly focused on namespace separation. Of course, privileges can be granted to schemas, so I suspect you can fulfill your security requirements with schemas as well.

In contrast, SQL Anywhere uses ownership for both security and namespace separation. The CREATE SCHEMA statement does neither and simply provides a way to create multiple objects and specify their privileges in a single atomic statement.

[Breck_Carter]

@Phil: I think you are correct on all points, except one: "I'm sure you are much more familiar with PostgreSQL than I am"... if you have actually used PostgreSQL at all, ever, in your lifetime, then you're way ahead of me 🙂

Any problems developers have understanding security and namespaces in SQL Anyhwere are NOTHING compared to the problems they have in products like Oracle (which, unlike PostgreSQL, is familiar to me).

[Former Member]

Of course the 'work-around' (I've done this on numerous occassions...) is:

select * into NewOwner.TableName from OldOwner.TableName

or ... if you have lots of indexes & triggers .... or for views, functions, and procedures ....

Unload the database into a script using dbunload, find the 'CREATE <object>' statement ...

and paste and run the modified version with the new owner .. then drop the old object!