Re: help about the function

Patrick.FICHE@xxxxxxxxxxx · Thu, 23 Jun 2005 10:18:06 +0200

Hi,
Why 
not :

SELECT 
INTO l_validity "VALIDITY"."VALIDITY" FROM ( SELECT 
min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY" GROUP 
BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;

Regards,
Patrick

------------------------------------------------------------------------------------------- 

Patrick Fiche 
email : patrick.fiche@xxxxxxxxxxx 
tél : 01 69 29 36 
18 
------------------------------------------------------------------------------------------- 

  -----Original Message-----
From: 
  pgsql-general-owner@xxxxxxxxxxxxxx 
  [mailto:pgsql-general-owner@xxxxxxxxxxxxxx]On Behalf Of Zlatko 
  Matić
Sent: jeudi 23 juin 2005 09:59
To: 
  btober@xxxxxxxxxxxxxxxx
Cc: 
  pgsql-general@xxxxxxxxxxxxxx
Subject:  help about the 
  function
Importance: High

  Hello!

  I have implemented solution for enabling regular 
  user (from group "ADMINS") to create new users in predefined groups, by your 
  modified function:

  CREATE OR REPLACE FUNCTION "public"."alter_group" 
  (name, boolean, name, varchar, timestamp) RETURNS boolean 
  AS
$body$
DECLARE
  l_group ALIAS FOR $1;

  l_create_user ALIAS FOR $2;
  l_username ALIAS FOR $3;

  l_password ALIAS FOR $4;
  l_validity ALIAS FOR $5;
  CMD 
  VARCHAR;
  MIN_SUPER_USER INTEGER := 1;
BEGIN
IF (l_create_user 
  NOTNULL) THEN
    IF (l_create_user) 
  THEN
      CMD := 'CREATE USER "' || l_username || 
  '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID 
  UNTIL ''' || l_validity || '''';
      EXECUTE 
  CMD;
    ELSE
      CMD := 'DROP 
  USER "' || l_username || '"';
      EXECUTE 
  CMD;
    END IF;
    IF (SELECT COUNT(*) 
  FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER 
  THEN
      RAISE EXCEPTION 'At least % admin(s) in 
  group ADMINS must be defined in order to create new user accounts.', 
  MIN_SUPER_USER;
    END IF;
  END IF;

  RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL 
  INPUT SECURITY DEFINER;

  Validity is set in the table 
  public."VALIDITY" in the field "VALIDITY" timestamp. There is also a view 
  called "VALIDITY_VIEW" which reads the actual validity value from the table. 
  It returns only one row-one field:

CREATE OR REPLACE VIEW "VALIDITY_VIEW" AS 
 SELECT 
  "VALIDITY"."VALIDITY"
   FROM ( SELECT min("VALIDITY"."RV_ID") AS 
  "RV_ID", 
  "VALIDITY"."VALIDITY"

  FROM "VALIDITY"

  GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;

  ALTER TABLE "VALIDITY_VIEW" OWNER TO zmatic;

  MS Access front-end calls this function through 
  VBA code. Access creates a recordset on the base of the VALIDITY_VIEW to 
  inspect the value of validity time, assigns it to a variable and 
  then passes as the parameter l_validity to the server function 
  "alter_group". It works nice, but I realized that it is not safe, because 
  someone from the group "ADMINS" could create its own query in Access with 
  different "validity" and execute it without restriction. Therefore I think 
  that "l_validity" timestamp ($5) should not be input parameter for the 
  function "alter_group", but rather declared variable that reads the value of 
  validity from the table directly.

  I tried to modify the function into something 
  like this:

  CREATE OR REPLACE FUNCTION "public"."alter_group" 
  (name, boolean, name, varchar) RETURNS boolean 
  AS
$body$
DECLARE
  l_group ALIAS FOR $1;

  l_create_user ALIAS FOR $2;
  l_username ALIAS FOR $3;

  l_password ALIAS FOR $4;
  l_validity timestamp;
  CMD 
  VARCHAR;
  MIN_SUPER_USER INTEGER := 1;
BEGIN

  l_validity := SELECT "VALIDITY"."VALIDITY" FROM ( 
  SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM 
  "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") 
  sve;

IF (l_create_user NOTNULL) 
  THEN
    IF (l_create_user) 
  THEN
      CMD := 'CREATE USER "' || l_username || 
  '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID 
  UNTIL ''' || l_validity || '''';
      EXECUTE 
  CMD;
    ELSE
      CMD := 'DROP 
  USER "' || l_username || '"';
      EXECUTE 
  CMD;
    END IF;
    IF (SELECT COUNT(*) 
  FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER 
  THEN
      RAISE EXCEPTION 'At least % admin(s) in 
  group ADMINS must be defined in order to create new user accounts.', 
  MIN_SUPER_USER;
    END IF;
  END IF;

  RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL 
  INPUT SECURITY DEFINER;

  ..but it seems that I can't assign the value 
  returned by query to l_validity directly (I could do it in Access by 
  recordset)...How to modify the following in order to work ?

  l_validity := SELECT "VALIDITY"."VALIDITY" FROM ( 
  SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM 
  "VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") 
  sve;

  Thank you in advance.

  Zlatko