Re: Schema (namespace) privilege details

Oliver Elphick wrote:
> On Fri, 2002-04-19 at 00:14, Tom Lane wrote:
> I think it could be both: a database owner may not want any schemas
> created by anyone else, or by some particular user; alternatively, the
> administrator may not want a particular user to create any schemas
> anywhere. These are two different kinds of restriction:
>
> GRANT CREATE SCHEMA TO user | PUBLIC
> REVOKE CREATE SCHEMA FROM user | PUBLIC
>
> would allow/disallow the user (other than the database owner) the
> theoretical right to create a schema, whereas
>
> GRANT CREATE SCHEMA IN database TO user | PUBLIC
> REVOKE CREATE SCHEMA IN database FROM user | PUBLIC
>
> would allow/disallow him it on a particular database. Having both gives
> more flexibility and allows different people control for different
> purposes (suppose someone needs to pay for the privilege to create
> schemas in a variable set of databases; the general permission could be
> turned on or off according to whether the bill was paid.). A general
> permission would be needed before permission could be effective on a
> particular database.

I like this general idea and syntax. But it seems awkward to have to
have the privilege granted twice. What about:

GRANT CREATE SCHEMA [IN { database | ALL }] TO user | PUBLIC
REVOKE CREATE SCHEMA [IN { database | ALL }] FROM user | PUBLIC

where lack of the IN clause implies the current database, and ALL
implies a system-wide grant/revoke. System-wide could only be issued by
a superuser, while a specific database command could be issued by the DB
owner or a superuser.

>
>>Should the owner of a database (assume he's not a superuser) have the
>>right to drop any schema in his database, even if he doesn't own it?
>>I can see arguments either way on that one.
>
>
> I think a database owner should be able to override the owner of a
> schema within the database; similarly a schema owner should be able to
> override the owner of an object within the schema. This makes sense in
> practice, since the higher owner can delete the schema/object and
> recreate it under his own ownership; so there is little point in not
> allowing him to change it directly.

Yeah, I still feel that the owner of a "container" object like a
database or schema should have complete control of whatever is contained
therein. Anything else would strike me as surprising behavior.

Joe