From: Russell Simpkins <russellsimpkins@hotmail.com>
To: Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
To: pgsql-sql@postgresql.org
Subject: Re: Scripting GRANT on functions
Date: Thu, 6 Oct 2005 09:06:29 -0400
Message-ID: <BAY103-DAV15FAE1FCAE7A0A975DBDA9B5850@phx.gbl> (raw)
References: <E253BDD7F008244585AEE87AF8F0224F116C7AA6@cl-mail01.au.bosch.com>
You could continue with this function, with an additional cursor to get the parameters for the function. If this is a one off thing, that you just need to do once, you could use pg_dump to get the create function statements and then simply alter them with an re in your favorite editor.
You should consider using groups. Then you could just add the new user to the existing group that has execute access.
hope that helps....
Russ
----- Original Message -----
From: Stewart Ben (RBAU/EQS4) *
To: pgsql-sql@postgresql.org
Sent: Thursday, October 06, 2005 1:27 AM
Subject: [SQL] Scripting GRANT on functions
Is there any easy way to script granting privileges to a number of
functions? I've got as far as the following code before realising that
I'll need to pass in the arguments, and the arguments are stored as OIDs
in pg_proc.
Is there any easy way, such as GRANT .... FUNCTION OID 12345?
---CODE---
DECLARE
curs REFCURSOR;
funcname VARCHAR;
BEGIN
OPEN foo FOR
SELECT proname FROM pg_proc
WHERE proname LIKE 'tr\\_%'
OR proname LIKE 'tt\\_%'
OR proname LIKE 'v\\_%'
OR proname LIKE 'vui\\_%';
FETCH curs INTO funcname;
WHILE FOUND LOOP
FETCH curs INTO funcname;
EXECUTE 'GRANT EXECUTE ON FUNCTION ' || funcname || ' TO myuser';
END LOOP;
CLOSE curs;
END;
---END CODE---
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: russellsimpkins@hotmail.com, Ben.Stewart@au.bosch.com
Subject: Re: Scripting GRANT on functions
In-Reply-To: <BAY103-DAV15FAE1FCAE7A0A975DBDA9B5850@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox