Thread: It's time to support GRANT SELECT,UPDATE,...,...,... ON database.* to username

I don't care if it's part of the SQL standard or not. I don't care if
oracle does it or not.  You're losing mysql converts as they go
through the tutorial and get to this point. Or worse, they just "grant
all" because it's easier, thus causing security holes.  User
friendliness matters.

There's like a hundred posts asking for this for the past four years,
and all they get in response is standards nazi's saying it won't be
supported because it's not in the standard and telling them to write
their own functions.  You write the function. Fuck the standard and
wake up.

On 9/30/07, dterrors@hotmail.com <dterrors@hotmail.com> wrote:
>
> I don't care if it's part of the SQL standard or not. I don't care if
> oracle does it or not.  You're losing mysql converts as they go
> through the tutorial and get to this point.

If that's all it takes for them to switch, seriously, I'd rather see
them go.  There are other solutions, like using a role instead of a
user for the target of the permissions.

> There's like a hundred posts asking for this for the past four years,
> and all they get in response is standards nazi's saying it won't be
> supported because it's not in the standard

Ummm.  no.  I've never seen that response.  I have seen plenty of
people saying that no one's had the urge to hack it into working code.
 Also, a common answer is to use roles (i.e. groups) for such things.
grant permission to the role, add users to the role, all done.

> and telling them to write
> their own functions.

About 75% of the time I see that response, it comes with the actual
code to do just that.  I.e. cut and paste and voila, you've got the
functions.

> You write the function. Fuck the standard and
> wake up.

Me?  What about you?  The fact is there's a limited number of hackers
capable of writing what you're asking for cleanly and correctly, and
they're working on other stuff.  Asking them politely has been know to
work.  Using the F word not so much.

scott.marlowe@gmail.com ("Scott Marlowe") writes:
> About 75% of the time I see that response, it comes with the actual
> code to do just that.  I.e. cut and paste and voila, you've got the
> functions.
>
>> You write the function. Fuck the standard and
>> wake up.
>
> Me?  What about you?  The fact is there's a limited number of hackers
> capable of writing what you're asking for cleanly and correctly, and
> they're working on other stuff.  Asking them politely has been know to
> work.  Using the F word not so much.

"Feel free  to contribute build  files.  Or work on  your motivational
skills, and maybe someone somewhere will write them for you..."
-- "Fredrik Lundh" <effbot@telia.com>

This is the usual sort of *right* answer to this...

It has tended to turn into recommendations to "write a function"
because the desired functionality is almost never a constant.  People
*claim* that they want to grant access to everything, but there are
commonly exceptions.

"Oh, but that table needs to be kept secure from the users..."

- Does it cover all tables?  Really?
- How about views?
- How about functions?  Operators?
- What about the security definer functions?  Are they exceptions?

- How to deal with the exceptions that there are sure to be?

The trouble is that "GRANT ON *.*" seems to be a lazy shortcut for
someone who *thinks* they're trying to secure their system, but that
would rather say "well, everything" as opposed to looking at things
properly.

That is, if you don't know what tables and other objects need to be
secured, how can you have any idea that you're handling the securing
of your application properly???
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/nonrdbms.html
Should vegetarians eat animal crackers?

On Tue, 2007-10-02 at 12:06 -0400, Chris Browne wrote:
> It has tended to turn into recommendations to "write a function"
> because the desired functionality is almost never a constant.  People
> *claim* that they want to grant access to everything, but there are
> commonly exceptions.
>
> "Oh, but that table needs to be kept secure from the users..."
>
> - Does it cover all tables?  Really?
> - How about views?
> - How about functions?  Operators?
> - What about the security definer functions?  Are they exceptions?
>
> - How to deal with the exceptions that there are sure to be?
>

I think that <database>.* would be a bad idea, but a macro for
<schema>.* might not be so bad.

There are already different GRANT commands for TABLE, FUNCTION, SCHEMA,
SEQUENCE, etc.

I'm sure there are exceptions that would not be covered by such a blunt
tool, but granularity is not the point of this feature.

> The trouble is that "GRANT ON *.*" seems to be a lazy shortcut for
> someone who *thinks* they're trying to secure their system, but that
> would rather say "well, everything" as opposed to looking at things
> properly.
>
> That is, if you don't know what tables and other objects need to be
> secured, how can you have any idea that you're handling the securing
> of your application properly???

A reasonable use case for this feature would be adding a read-only
reporting role that needs access to a group of tables that all happen to
be within a schema.

This isn't critical, but for people who use an ORM that don't want to
think about the database, it's handy.

That being said, I'm not volunteering to write it, especially not in
response to a rude request.

Regards,
    Jeff Davis

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jeff Davis wrote:
> On Tue, 2007-10-02 at 12:06 -0400, Chris Browne wrote:
>> It has tended to turn into recommendations to "write a function"
>> because the desired functionality is almost never a constant.  People
>> *claim* that they want to grant access to everything, but there are
>> commonly exceptions.

> A reasonable use case for this feature would be adding a read-only
> reporting role that needs access to a group of tables that all happen to
> be within a schema.
>
> This isn't critical, but for people who use an ORM that don't want to
> think about the database, it's handy.

I could easily argue that this is more a problem than a solution. Don't
get me wrong, I understand your point but frankly, if one is willing to
take such a lax approach to your data security... they should just run
flat files with RAID 0 ;)

Joshua D. Drake

>
> That being said, I'm not volunteering to write it, especially not in
> response to a rude request.
>
> Regards,
>     Jeff Davis
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>


- --

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHAqJ6ATb/zqfZUUQRAv8rAJ9Q+36xiJEDSSymkueS/HmJJOlVNwCbBKHY
NxC9TbGJLy6qpzYxBwI6vdM=
=L5pF
-----END PGP SIGNATURE-----

On Oct 2, 12:06 pm, Chris Browne <cbbro...@acm.org> wrote:
> The trouble is that "GRANT ON *.*" seems to be a lazy shortcut for
> someone who *thinks* they're trying to secure their system, but that
> would rather say "well, everything" as opposed to looking at things
> properly.
>
> That is, if you don't know what tables and other objects need to be
> secured, how can you have any idea that you're handling the securing
> of your application properly???

I wonder if you would've given the same response 40 years ago when
someone invented "chmod <perms> *"

"If you don't know what files and other objects need to be secured,
how can you have any idea that you're handling the securing of your
file system properly???"

What if I DO know what tables I have, and I DO want them to all have
the same perms?