Обсуждение: Granting privileges on all tables,sequences , views, procedures
Dear all, Today I grant a user all privileges to all tables in a database by grant all privileges on svo_data_social to neha ; grant all privileges on svo_phrases to neha ; .. . ..... ..... ... Now i find it very uncomfort to grant privileges one by one table. Is there a simple way to grant privileges on all tables, views, sequences etc in a database. Also , after granting all privileges , rename command is not granted. Is it impossible to grant alter & drop privileges to user which is granted to only the user who created the object. What is ALL privileges comprised of ? Thanks
Per 8.0 documentation, in order to ALTER a table (including rename), you have to be the owner of a table:
"You must own the table to use ALTER TABLE; except for ALTER TABLE OWNER, which may only be executed by a superuser."
If you want to grant ownership of every table in a database to a specific user, you should be able to:
SELECT exec('alter table '||table_name||' OWNER TO {user}') FROM information_schema.tables WHERE table_schema='{schema}'
{user} = the user you want to grant to (looks like 'neha' in your case)
{schema} = schema search path for your database (probably 'public' if you haven't changed it)
HTH,
-Chris.
On Mon, May 16, 2011 at 9:41 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
Dear all,
Today I grant a user all privileges to all tables in a database by
grant all privileges on svo_data_social to neha ; grant all privileges on svo_phrases to neha ;
.. . .....
.....
...
Now i find it very uncomfort to grant privileges one by one table.
Is there a simple way to grant privileges on all tables, views, sequences etc in a database.
Also , after granting all privileges , rename command is not granted.
Is it impossible to grant alter & drop privileges to user which is granted to only the user who created the object.
What is ALL privileges comprised of ?
Thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2011/5/17 Adarsh Sharma <adarsh.sharma@orkash.com>: > Dear all, > > Today I grant a user all privileges to all tables in a database by > > grant all privileges on svo_data_social to neha ; grant all > privileges on svo_phrases to neha ; > .. . ..... > ..... > ... > > Now i find it very uncomfort to grant privileges one by one table. > > Is there a simple way to grant privileges on all tables, views, sequences > etc in a database. > > Also , after granting all privileges , rename command is not granted. > > Is it impossible to grant alter & drop privileges to user which is granted > to only the user who created the object. > > What is ALL privileges comprised of ? > > > Thanks > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > I think that you can grant membership in that role, so member role has ability to run ALTER, DROP etc. on tables, which even doesn't own. You can also change owner of object using ALTER TABLE for example ALTER TABLE sometable OWNER TO new_owner, but probably previous owner need to be granted as new_owner's member. To grant privileges on all tables, I think you can write sql function, which obtains list of all tables, schemas, views in database and fire GRANT ALL for each of them. Regards, Grzegorz Szpetkowski
Ah - my apologies, I realized you will need to add a function for exec (which may also require plpgsql language added to your database).
On Tue, May 17, 2011 at 12:00 AM, Christopher Opena <counterveil@gmail.com> wrote:
Per 8.0 documentation, in order to ALTER a table (including rename), you have to be the owner of a table:"You must own the table to use ALTER TABLE; except for ALTER TABLE OWNER, which may only be executed by a superuser."If you want to grant ownership of every table in a database to a specific user, you should be able to:SELECT exec('alter table '||table_name||' OWNER TO {user}') FROM information_schema.tables WHERE table_schema='{schema}'{user} = the user you want to grant to (looks like 'neha' in your case){schema} = schema search path for your database (probably 'public' if you haven't changed it)HTH,-Chris.On Mon, May 16, 2011 at 9:41 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:Dear all,
Today I grant a user all privileges to all tables in a database by
grant all privileges on svo_data_social to neha ; grant all privileges on svo_phrases to neha ;
.. . .....
.....
...
Now i find it very uncomfort to grant privileges one by one table.
Is there a simple way to grant privileges on all tables, views, sequences etc in a database.
Also , after granting all privileges , rename command is not granted.
Is it impossible to grant alter & drop privileges to user which is granted to only the user who created the object.
What is ALL privileges comprised of ?
Thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general