Обсуждение: 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 pronameLIKE '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/
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 -----Sent: Thursday, October 06, 2005 1:27 AMSubject: [SQL] Scripting GRANT on functionsIs 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
"Stewart Ben (RBAU/EQS4) *" <Ben.Stewart@au.bosch.com> writes:
> 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?
Coerce the OID to regprocedure to generate a usable name for the
function.
regression=# select oid from pg_proc limit 5;oid
------124212431244 311245
(5 rows)
regression=# select oid::regprocedure from pg_proc limit 5; oid
------------------boolin(cstring)boolout(boolean)byteain(cstring)byteaout(bytea)charin(cstring)
(5 rows)
regression=#
regards, tom lane