Re: user/grant - best practices handling permission in production system

On Thu, Jul 23, 2009 at 10:09 PM, Stefano
Nichele<stefano(dot)nichele(at)gmail(dot)com> wrote:

> 2. using the user used in step 1, create the schema and populate tables with

> At this point the webapp should work correctly.
> The main missing point for me is how to perform step 4 in a simple way since
> it seems there is not a way to give the right grants to all db objects in
> one shot.

Well there isn't a way to do step 2 in one shot either. You'll have to
issue a CREATE statement for each object, it's no extra work to issue
a GRANT for each object with the specific rights the application
should have at that time. Think of it as an important part of the
process of creating a new object.

Note that it's probably not necessary to grant all rights to every
table. Most applications have some tables that are read-only or
insert-only from the point of view of the application. Your system
will be more secure if the application does not have unnecessary
privileges. So thinking about what rights to grant to the application
for each object when it's created is not a bad thing.

--
greg
http://mit.edu/~gsstark/resume.pdf