Re: how to grant select on table using trigger

Hi
i want to use trigger because my one of application connects to databse and creates tables in database dynamically , other user want to acces it . as when application creates table in databse it does not GRANT select to other users. thats why i thought to grant permission by trigger.

i know the newly created table name is listed in one Table called queries. can you pls write sample code so that i can grant permission on tables created by application
thanks in advance
santosh dwivedi

Michael Fuhr <mike(at)fuhr(dot)org> wrote:
On Fri, Jan 07, 2005 at 12:30:16AM -0800, santosh dwivedi wrote:

> i want to grant select permission to a group on table that will
> be created by me in future. so i used trigger and a function

Why use a trigger? Why not just issue a GRANT statement when you
create the table? What problem are you trying to solve?

> CREATE FUNCTION permitselect () RETURNS opaque AS '

What version of PostgreSQL are you using? In 7.3 and later triggers
should return TRIGGER, not OPAQUE.

> DECLARE

You don't need a DECLARE section if you don't declare anything.

> BEGIN
> GRANT SELECT ON NEW to GROUP wp;

NEW is the row being inserted or updated; GRANT needs a table name.
Are you trying to grant permissions on a table named in one of the
row's fields? Maybe you want something like this:

EXECUTE ''GRANT SELECT ON '' || quote_ident(NEW.tablename) || '' TO GROUP wp'';

(If you're using 8.0 then you could dollar-quote the function body
and avoid the need to escape the single quotes.)

> CREATE TRIGGER permit_select
> AFTER INSERT OR UPDATE
> ON queries FOR EACH ROW
> EXECUTE PROCEDURE permitselect();
>
> queries is table where meta data of tables created by me is stored.
> so new table created will be listed in table queries.

What kind of metadata are you storing? Hopefully you're not
duplicating anything that's already in the system catalogs.

> but on execution it gives error:
> NEW used in non-rule query
> Error occurred while executing PL/pgSQL function permit

The GRANT statement needs to reference a table name, not a row being
inserted or updated. If the row contains a field that names the
table, then use NEW.fieldname. Also, you'll need to use EXECUTE
as shown above (see "Executing Dynamic Commands" in the PL/pgSQL
documentation).

> Can any body help me how i can write a trigger for granting
> permission to select a table as soon it is created

Apparently you're already doing an INSERT after CREATE TABLE -- why
not issue a GRANT statement as well? Why use a trigger?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------------
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.