Re: select any table

On Wed, Mar 26, 2008 at 07:27:49AM -0500, Roberts, Jon wrote:
> I think the bigger foot gun would be a lazy dba granting auditors
> "superuser" in place of a read-only account.

At least that would stop users revoking audit access to the tables!
:) Any scheme that purports to allow this (i.e. disallows revoking
of access) should be taken out and shot quickly. Any language of
reasonable complexity will support some form of information hiding (aka
abstraction) and suggesting you can stop this by disallowing revoking of
access is just silly.

> Sam Mason wrote:
> > In ACL (Access Control List) systems this sort of "privilege" isn't
> > very natural. The closest thing I can imagine is by having a
> > "default" set of permissions that the user has control over, rather
> > than currently where the set of default permissions is fixed by PG
> > to only include unrestricted access by the owner. Another solution,
> > and probably the footgun that Joshua was referring to, would be
> > to have some code that is automatically run when a new object is
> > created that grants read-only access. I don't think PG provides a
> > way to do this at the moment though.
>
> Hmm, that is probably why Oracle treats this as a "system privilege" as
> apposed to being granted rights to a table or role.

Sorry, I don't know Oracle. That sounds like a rather awkward way
of doing things in general, though it probably works well enough in
practise.

> The ANSI standard is database.schema.table right? So when you don't
> specify the database name, it is supposed to default to the current one.
> When executing a query, couldn't PG check the database first for "read"
> like it probably already does for connect, create, and temporary?

Sounds pretty intractable, how do you revoke access sanely?

> > Other security models allow this case to be more directly expressed.
> > My current favourite is capability based security, it allows you to
> > directly say that "auditors" have transitively read-only access to
> > specific things (i.e. the entire database).
>
> I like that too. I know Oracle and MS SQL Server have this (select any
> table and db_datareader respectively). I've not used MySQL but a quick
> google shows they have a "grant all on db.* to user".

Sorry, I was using "capability" as a technical term and not a
descriptive one. Capability security is *very* different from the ACL
(or more technically, "identity" or "role") based security mechanisms in
Oracle and MS SQL.

Sam