Re: Fwd: Question about Privileges

On Thursday 12 March 2009 5:00:39 pm Jack W wrote:
> On Thu, Mar 12, 2009 at 3:45 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> > Jack W wrote:
> >> I also find that if I only grant privileges on database to dbuser as
> >> below, without granting privileges on Schema and table to dbuser, dbuser
> >> still can not do SELECT on the tables.
> >> mydb=# grant all privileges on Database mydb to dbuser;
> >>
> >> Is there any simple way to grant All privileges to dbuser on all the 10
> >> tables?
> >
> > the simplest way is to make dbuser the OWNER of the database, and have
> > him create all the tables too, then he automatically has full rights to
> > it.
>
> Thanks. In my application, one user will create the database, then other
> users can remotely access it through ODBC/JDBC to access the database. In
> this case, I have to grant the privileges to each user one by one, right?

Easier to create a group role and assign it the privileges to the tables as you
create them. Then as you create users assign them to the group.

>
> > SCHEMA privileges grant the rights to connect to a schema, and
> > create/modify schemas
> >
> > table/view/etc privileges grant the rights to select/insert/update/etc
> > the mentioned tables.
> >
> >
> > In my test as below:
>
> mydb=#select * from mydb_schema.mydb_table1;
>
> If I only grant the privileges to the table:
> mydb=# grant all privileges on mydb_schema.mydb_table1 to dbuser;
>
> The SELECT will fail, the error is:
>
> STATEMENT: select * from mydb_schema.mydb_table1;
> ERROR: permission denied for schema mydb_schema
>
> So I have to grant the privileges to the schema mydb_schema in order to do
> SELECT on mydb_table1.
>
> Jack

--
Adrian Klaver
aklaver(at)comcast(dot)net