Forum Stats

  • 3,733,004 Users
  • 2,246,671 Discussions
  • 7,856,461 Comments

Discussions

MySQL 5.7 Stored Procedure to ALTER USER

theDBAugie
theDBAugie Member Posts: 3
edited November 2018 in MySQL Community Space

The task is programmaticly change the password of service accounts. The stored procedure to do that has been more difficult than I had originally assessed. Sometimes, Oracle skills do not easily translate into MySQL skills.

DELIMITER $$

USE db $$

DROP PROCEDURE IF EXISTS alter_user $$

CREATE

DEFINER = 'DEVADMIN'@'%'

PROCEDURE db.alter_user (userName VARCHAR(100), hostName VARCHAR(100), newWord VARCHAR(100))

COMMENT 'Procedure to change service account passwords'

LANGUAGE SQL

DETERMINISTIC

MODIFIES SQL DATA

SQL SECURITY DEFINER

BEGIN

  SET @s = CONCAT('ALTER USER ', userName, '\@' hostName' IDENTIFIED BY 'newWord';) )

  PREPARE stmt1 FROM @s;

  EXECUTE stmt1;

  DEALLOCATE PREPARE stmt1;

END ;

$$

DELIMITER ; $$

Attempts to run this code and create the stored procedure have failed with the following error code:

ERROR (42000)1583 Incorrect parameters in the call to native function 'concat'

Can anyone see what's wrong here?

Thanks,

Gus

Answers

  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625
    edited November 2018

    mysql> use mysql
    Database changed
    mysql> DELIMITER $$
    mysql> DROP PROCEDURE IF EXISTS alter_user $$
    Query OK, 0 rows affected (0.00 sec)

    mysql> CREATE PROCEDURE alter_user (userName VARCHAR(100), hostName VARCHAR(100), newWord VARCHAR(100))
        -> BEGIN
        ->   SET @s = CONCAT("ALTER USER ", userName, "@, hostName, IDENTIFIED BY '", newWord, "'");
        ->   PREPARE stmt1 FROM @s;
        ->   EXECUTE stmt1;
        ->   DEALLOCATE PREPARE stmt1;
        -> END ;
        -> $$
    Query OK, 0 rows affected (0.00 sec)

    mysql> DELIMITER ; $$
    mysql> call alter_user('root','localhost','root');
    Query OK, 0 rows affected (0.00 sec)

    MySQL>

    theDBAugie
Sign In or Register to comment.