Thread: grant all the database object automatically
How do I give all database object to another user so that user can do everything he wants? ALTER DATABASE blabla OWNER TO another_user; does not give what I want because that another user still does not have right to manipulate table inside database. Since there is no ALTER TABLE * blabla command ( see the * / wild card sign ), I alter table one by one so that another user can do some operation on those table..... Is there any easier way to do this.....? Ok, after this, what should I do to give database objects fully to another user beside tables?? Should I alter function, view??? Is there any tool or command to give all database objects to another user automatically?
At the moment there is no way to globally grant or revoke permissions on all objects in a database. It is on the developerstodo list. On Thu, Jan 20, 2005 at 05:11:16PM +0700, Akbar wrote: > How do I give all database object to another user so that user can do > everything he wants? > > ALTER DATABASE blabla OWNER TO another_user; > does not give what I want because that another user > still does not have right to manipulate table inside > database. > > Since there is no ALTER TABLE * blabla command ( see the * / wild card > sign ), I alter table one by one so that another user can do some > operation on those table..... > Is there any easier way to do this.....? > > Ok, after this, what should I do to give database objects fully to > another user beside tables?? Should I alter function, view??? Is there > any tool or command to give all database objects to another user > automatically? > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
On Thu, Jan 20, 2005 at 17:11:16 +0700, Akbar <melinda_sayang@hotpop.com> wrote: > > Ok, after this, what should I do to give database objects fully to > another user beside tables?? Should I alter function, view??? Is there > any tool or command to give all database objects to another user > automatically? There isn't one now, but it isn't too hard to write scripts that query the database to get a list of objects and than issue corresponding GRANT or REVOKE commands.
I am sorry... but could you give me a link about that? tutorial writing script to list all the objects from database.... Thank you. On Thu, 2005-01-20 at 18:22 -0600, Bruno Wolff III wrote: > On Thu, Jan 20, 2005 at 17:11:16 +0700, > Akbar <melinda_sayang@hotpop.com> wrote: > > > > Ok, after this, what should I do to give database objects fully to > > another user beside tables?? Should I alter function, view??? Is there > > any tool or command to give all database objects to another user > > automatically? > > There isn't one now, but it isn't too hard to write scripts that query > the database to get a list of objects and than issue corresponding > GRANT or REVOKE commands. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Someone may have more specific information, but the information about the database is in the catalogs and information schema. http://www.postgresql.org/docs/8.0/interactive/information-schema.html http://www.postgresql.org/docs/8.0/interactive/catalogs.html Sean On Jan 21, 2005, at 10:08 AM, Akbar wrote: > I am sorry... but could you give me a link about that? tutorial writing > script to list all the objects from database.... > > Thank you. > > On Thu, 2005-01-20 at 18:22 -0600, Bruno Wolff III wrote: >> On Thu, Jan 20, 2005 at 17:11:16 +0700, >> Akbar <melinda_sayang@hotpop.com> wrote: >>> >>> Ok, after this, what should I do to give database objects fully to >>> another user beside tables?? Should I alter function, view??? Is >>> there >>> any tool or command to give all database objects to another user >>> automatically? >> >> There isn't one now, but it isn't too hard to write scripts that query >> the database to get a list of objects and than issue corresponding >> GRANT or REVOKE commands. >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 6: Have you searched our list archives? >> >> http://archives.postgresql.org > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match
On Jan 21, 2005, at 10:08 AM, Akbar wrote: > I am sorry... but could you give me a link about that? tutorial writing > script to list all the objects from database.... > I found some nice looking access control source from Afilias Canada Corporation. Unfortunately, I can't recall where I found it and I could not turn it up with google. For now I have put the files on my web site. If some one has the official home of this source, please let me know so I can provide proper credit. sql: http://pgedit.com/public/sql/acl_admin/acl_admin.sql PostgreSQL Autodoc: http://pgedit.com/public/sql/acl_admin/acl_admin.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Hi John, Trying to go to the links you provided results in a Page Not Found message. This seems to stem from the Public folder no being accessible. Regards, George ----- Original Message ----- From: "John DeSoi" <desoi@pgedit.com> To: "Akbar" <melinda_sayang@hotpop.com> Cc: "PostgreSQL Novice" <pgsql-novice@postgresql.org> Sent: Friday, January 21, 2005 7:47 AM Subject: Re: [NOVICE] grant all the database object automatically > > On Jan 21, 2005, at 10:08 AM, Akbar wrote: > >> I am sorry... but could you give me a link about that? tutorial writing >> script to list all the objects from database.... >> > > > I found some nice looking access control source from Afilias Canada > Corporation. Unfortunately, I can't recall where I found it and I could > not turn it up with google. For now I have put the files on my web site. > If some one has the official home of this source, please let me know so I > can provide proper credit. > > sql: http://pgedit.com/public/sql/acl_admin/acl_admin.sql > PostgreSQL Autodoc: http://pgedit.com/public/sql/acl_admin/acl_admin.html > > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Hi George, On Jan 21, 2005, at 9:53 AM, George Weaver wrote: > Trying to go to the links you provided results in a Page Not Found > message. This seems to stem from the Public folder no being > accessible. > Thanks for letting me know. I tested the .html file and that worked, but I did not try the .sql file. There must be some restriction from the content management system. I changed the extension to pgsql and it is OK now from here. Let me know if they are still not working for you. sql: http://pgedit.com/public/sql/acl_admin/acl_admin.pgsql PostgreSQL Autodoc: http://pgedit.com/public/sql/acl_admin/acl_admin.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Hi John, Works fine now! Thanks, George ----- Original Message ----- From: "John DeSoi" <desoi@pgedit.com> To: "George Weaver" <gweaver@shaw.ca> Cc: "Akbar" <melinda_sayang@hotpop.com>; "PostgreSQL Novice" <pgsql-novice@postgresql.org> Sent: Friday, January 21, 2005 9:53 AM Subject: Re: [NOVICE] grant all the database object automatically > Hi George, > > On Jan 21, 2005, at 9:53 AM, George Weaver wrote: > >> Trying to go to the links you provided results in a Page Not Found >> message. This seems to stem from the Public folder no being accessible. >> > > Thanks for letting me know. I tested the .html file and that worked, but I > did not try the .sql file. There must be some restriction from the content > management system. I changed the extension to pgsql and it is OK now from > here. Let me know if they are still not working for you. > > sql: http://pgedit.com/public/sql/acl_admin/acl_admin.pgsql > PostgreSQL Autodoc: http://pgedit.com/public/sql/acl_admin/acl_admin.html > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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 >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I wrote the bulk of it and posted it to the list. Tim Goodaire helped me with some. There's no canonical webpage. It's in cvs internally here. I'd be happy to apply any patches and repost if someone felt inclined to add/improve. - -- Andrew Hammond 416-673-4138 ahammond@ca.afilias.info Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A John DeSoi wrote: | Hi George, | | On Jan 21, 2005, at 9:53 AM, George Weaver wrote: | |> Trying to go to the links you provided results in a Page Not Found |> message. This seems to stem from the Public folder no being accessible. |> | | Thanks for letting me know. I tested the .html file and that worked, but | I did not try the .sql file. There must be some restriction from the | content management system. I changed the extension to pgsql and it is OK | now from here. Let me know if they are still not working for you. | | sql: http://pgedit.com/public/sql/acl_admin/acl_admin.pgsql | PostgreSQL Autodoc: http://pgedit.com/public/sql/acl_admin/acl_admin.html | | | John DeSoi, Ph.D. | http://pgedit.com/ | Power Tools for PostgreSQL | | | ---------------------------(end of broadcast)--------------------------- | TIP 3: 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 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFCAU6Dgfzn5SevSpoRAjdeAJ0fkl3s/nVP4Apyw2vQAk7CxjLeygCfaCd6 YN5U4sHrF1K5/F2p3GOXMyM= =Ku3p -----END PGP SIGNATURE-----