Re: New Privilege model purposal

At 20:41 25/07/00 +0200, Jan Wieck wrote:
>Philip Warner wrote:
>> At 15:27 25/07/00 +0200, Jan Wieck wrote:
>>
>
>> INHERIT Do you need this?
>
> What other rights must a user have on the inherited relations
> to work properly with them?
>

I have no idea, I only suggested this because it's a feature that is easily
overlooked, and not part of most DBs, so we may need to thin about it...

The SQL standard also has a 'GRANT...WITH HIERARCHY' option that grants
access on all subtables.

FWIW, the SQL standard also defines a 'USAGE' priv that grants access to
domains, character sets, UDTs etc.

>> > System Privileges
>> >
>> > System Privileges are to grant permission to execute DDL-
>> > statements or for database wide Object permissions (valid
>> > for all objects of a particular kind).
>> >
>> > SUPERUSER A special System Privilege,
>> > superseding any other rights. What
>> > the holder of this right want's to
>> > do, he does. It is the same as now,
>> > usesuper in pg_shadow.
>>
>> I suspect this is good grounds for a religious war, but I like a priv
>> system where I have to 'turn on' a super privilege before I get it. If I am
>> a superuser, I don't want my cape flapping in the breeze *all* the time.
>> Can you add some kind of 'CLARK_KENT' priv (ie. 'can become superuser')?
>> And have SUPERUSER off at the beginning of all sessions?
>>
>> There are two reasons I think this is important: 1) I am accident prone,
>> and 2) it's good to live like a mortal most of the time - you get to see
>> problems before a user complains.
>
> If you don't need DBA privileges, don't log on as a DBA. Have
> a separate account for that (IMHO).

I guess if there is an 'ALTER SESSION SET AUTHORIZATION <superuser-name>'
or alternatively, 'SET ROLE <rolename>', then I'll be happy, since I could
define a 'superuser/dba' role.

I think there is a need for one or more users to have superuser-like access
to a single DB, but have little or no access to other ones. The suggestion
above would allow a normal user to be superuser for single database,
without having to set up (potentially) a separate DBA account for each
database.

>> > CREATE TABLE
>> > ALTER ANY TABLE
>> > DROP ANY TABLE
>> > INSERT ANY TABLE
>> > UPDATE ANY TABLE
>> > DELETE ANY TABLE
>> > SELECT ANY TABLE
>> > LOCK ANY TABLE
>> > REFERENCE ANY TABLE
>> > CREATE SEQUENCE
>> > ALTER ANY SEQUENCE
>> > DROP ANY SEQUENCE
>>
>> This seems like overkill; you will need a new priv for every object type.
>> It is also not clear how 'ALTER ANY TABLE' should interact with 'ALTER
>> TABLE (specific table)', but I assume the more specific priv rules.
>
> As I said, it should be fine grained. If a DBA wants some
> user to be able to create views, but not his own tables,
> functions etc., how could he if there aren't separate
> privileges for the single actions?
>
> The interactions will be hardwired in the pg_check_priv()
> function. Since the requested privilege is a #define'd
> constant, it'll be more or less a big switch statement,
> calling a single privilege lookup helper once in a while.

Maybe I was confusing implementation with interface, but what I was
suggesting (in a confused sort of way) was that you could define 'objects':
TABLE, TRIGGER, SEQUENCE, COLUMN..., and 'privileges': ALTER, DROP, CREATE,
UPDATE, DELETE, SELECT etc etc.

Then privs can be granted on objects, so the number of #defines only equals
the number of separate privs, not the number of privs times the number of
object types. Maybe I just misunderstood your plans? Did you mean that
'LOCK ANY TABLE' would be a priv granted at the database level, schema
level, or really at the system level?

>> It seems that this is just a way of defining 'default' privs for an object
>> that does not have an ACL, and if that is the case, why not define a
>> default protection at both the database level and the object-type level
>> (perhaps in the relevant pg_* table?). Certainly it seems that 'CREATE
>> TABLE' could be represented as 'INSERT' priv on the pg_class table etc.
>
> No. They are meant as user or group specific privileges.
>
> By default, only the owner has access to his tables. He (or a
> superuser) must explicitly GRANT other users or groups access
> to it. But a user with SELECT ANY TABLE can do so from the
> start, because the DBA decided that this user act's like a
> superuser if issuing some SELECT database wide.

So to use the above object/priv model, 'GRANT "CREATE ANY TABLE" on
database <dbname> to fred' might be equivalent to 'GRANT "INSERT" on OBJECT
TABLES to fred'.

I'm not particularly attached to my suggestion, but you can achieve
granularity without lots of priv named to remember.

>> >
>> > If a function is called through the fmgr (any user
>> > defined function is), the function manager honours
>> > these flags. Prosetuid will cause the function
>> > manager to switch to another effective user id, used
>> > during pg_check_perms() for the time of the function
>> > invocation.
>>
>> Wonderful! I've been hoping for this for a while.
>
> You never walk alone.

That's what I tell my dog.

Bye for now,

Philip.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/