Обсуждение: change user password

hi, is it possible to change the current user's password from a
function/stored procedure , I mean,  is there a system function/stored
procedure to do it? like the dbo.sp_password found in adaptive server
anywhere.

thanks in advance

Hugo

On Thu, Feb 17, 2005 at 02:30:33PM -0400, Hugo Takada wrote:
>
> hi, is it possible to change the current user's password from a
> function/stored procedure , I mean,  is there a system function/stored
> procedure to do it? like the dbo.sp_password found in adaptive server
> anywhere.

See the documentation for ALTER USER.

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

> hi, is it possible to change the current user's password from a
> function/stored procedure , I mean,  is there a system function/stored
> procedure to do it? like the dbo.sp_password found in adaptive server
> anywhere.
>


CREATE OR REPLACE FUNCTION public.alter_password(name, name)
  RETURNS "varchar" AS
'
DECLARE
  l_user ALIAS FOR $1;
  l_pwd ALIAS FOR $2;
  CMD VARCHAR;
BEGIN
  CMD := \'ALTER USER "\' || l_user || \'" WITH ENCRYPTED PASSWORD \' ||
\'\'\'\' || l_pwd || \'\'\'\' || \' VALID UNTIL \' || \'\'\'\' ||
CURRENT_DATE+INTERVAL \'30 days\' || \'\'\'\';
  EXECUTE CMD;
  RETURN \'ALTER USER\';
END;
'
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;


-- Of course, you'ld want to be careful about whom was granted execute
permission on this.

-- BMT