X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 616C8DA390 for ; Thu, 6 Oct 2005 10:06:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69249-02 for ; Thu, 6 Oct 2005 13:06:23 +0000 (GMT) Received: from hotmail.com (bay103-dav15.bay103.hotmail.com [65.54.174.87]) by svr1.postgresql.org (Postfix) with ESMTP id B4B69DA085 for ; Thu, 6 Oct 2005 10:06:24 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 6 Oct 2005 06:06:23 -0700 Message-ID: Received: from 69.182.215.169 by BAY103-DAV15.phx.gbl with DAV; Thu, 06 Oct 2005 13:06:23 +0000 X-Originating-IP: [69.182.215.169] X-Originating-Email: [russellsimpkins@hotmail.com] X-Sender: russellsimpkins@hotmail.com From: "Russell Simpkins" To: "Stewart Ben (RBAU/EQS4) *" , References: Subject: Re: Scripting GRANT on functions Date: Thu, 6 Oct 2005 09:06:29 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0074_01C5CA55.3CD2B540" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-OriginalArrivalTime: 06 Oct 2005 13:06:23.0894 (UTC) FILETIME=[C0C3DB60:01C5CA76] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=4.883 required=5 tests=[DNS_FROM_RFC_ABUSE=0.374, DNS_FROM_RFC_POST=1.376, FORGED_MUA_OUTLOOK=3.037, HTML_50_60=0.095, HTML_MESSAGE=0.001, MSGID_FROM_MTA_HEADER=0] X-Spam-Level: **** X-Archive-Number: 200510/53 X-Sequence-Number: 22906 This is a multi-part message in MIME format. ------=_NextPart_000_0074_01C5CA55.3CD2B540 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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.=20 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 -----=20 From: Stewart Ben (RBAU/EQS4) *=20 To: pgsql-sql@postgresql.org=20 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 ------=_NextPart_000_0074_01C5CA55.3CD2B540 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
You could continue with this function, = with an=20 additional cursor to get the parameters for the function. If this is a = one off=20 thing, that you just need to do once, you could use pg_dump to get the = create=20 function statements and then simply alter them with an re in your = favorite=20 editor.
 
You should consider using groups. Then = you could=20 just add the new user to the existing group that has execute=20 access.
 
hope that helps....
 
Russ
----- Original Message -----
From:=20 Stewart Ben (RBAU/EQS4) * =
Sent: Thursday, October 06, = 2005 1:27=20 AM
Subject: [SQL] Scripting GRANT = on=20 functions

Is there any easy way to script granting privileges to = a number=20 of
functions? I've got as far as the following code before = realising=20 that
I'll need to pass in the arguments, and the arguments are = stored as=20 OIDs
in pg_proc.

Is there any easy way, such as GRANT .... = FUNCTION=20 OID 12345?

---CODE---

DECLARE
  curs=20 REFCURSOR;
  funcname VARCHAR;
BEGIN
  OPEN foo=20 FOR
    SELECT proname FROM=20 pg_proc
     WHERE proname LIKE=20 'tr\\_%'
        OR proname LIKE = 'tt\\_%'
        OR proname LIKE = 'v\\_%'
        OR proname LIKE=20 'vui\\_%';

  FETCH curs INTO funcname;

  WHILE = FOUND=20 LOOP
    FETCH curs INTO = funcname;
   =20 EXECUTE 'GRANT EXECUTE ON FUNCTION ' || funcname || ' TO = myuser';
 =20 END LOOP;

  CLOSE curs;
END;

---END=20 CODE---


Best regards,

Ben = Stewart

--
Robert Bosch=20 (Australia) Pty. Ltd.
Engineering Quality Services, Software = Engineer=20 (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - = AUSTRALIA
mailto:ben.stewart@au.bosch.com<= /A>
http://www.bosch.com.au/

---= ------------------------(end=20 of broadcast)---------------------------
TIP 4: Have you searched = our list=20 = archives?

         &n= bsp;    =20 http://archives.postgresql.org
------=_NextPart_000_0074_01C5CA55.3CD2B540--