Create user after insert in SQL table

Question

I want to create a new user with the same initial password every time I insert a row with the username in the table.

I tried but it doesn't work:

CREATE TABLE IF NOT EXISTS Professeur (
professeur_id int NOT NULL AUTO_INCREMENT,
prenom varchar(40) COLLATE utf8_bin NOT NULL,
name varchar(30) COLLATE utf8_bin NOT NULL,
titre char DEFAULT NULL,
PRIMARY KEY (professeur_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE trigger trig_prof AFTER INSERT ON Professeur
FOR EACH ROW CREATE OR replace USER NEW.name@localhost identified BY pwd0;

ERROR:

    ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that
 corresponds to your MySQL server version for the right syntax to use near
 'USER NEW.name@localhost identified BY pwd0' at line 2

(1) There is no password column in your table. (2) "Professeur" is an odd name for a user table. (3) I sure hope you are not storing free text passwords. — Gordon Linoff, Aug 24 '20 at 13:51
You should post an error with your " it doesn't work:" statement witch does not mean anything to us. I added it for you. — ikiK, Aug 24 '20 at 14:13
@GordonLinoff. (1) I don't want to store password in my table. (2) "Professeur" means teacher in French (3) No I'm not. I want to initialize each user account with the same password. After that they can choose their own password. — Raoul HATTERER, Aug 24 '20 at 14:48

Georg Richter · Answer 1 · 2020-08-24T15:02:11.363

1

Triggers have several limitations as described in the fine manual:

"Triggers cannot operate on any tables in the mysql, information_schema or performance_schema database."

Since users are stored in mysql schema (either in global_priv or user) this can't work.

edited Aug 24 '20 at 15:02

answered Aug 24 '20 at 14:19

Georg Richter

5,970
2
9
15

OK thank you. So I have to find an other way: cursor and Loop ? – Raoul HATTERER Aug 24 '20 at 14:50
You should execute the CREATE USER from your application code, after the INSERT is done. – Bill Karwin Aug 24 '20 at 23:32
@BillKarwin It's planned. But, for demonstration purposes, with a sql script, I initialize the Teacher table with a list of fictitious teachers to whom I want to give the rights to connect with an sql role of Teacher. – Raoul HATTERER Aug 26 '20 at 00:13

score 0 · Answer 2 · answered Aug 24 '20 at 23:25

0

Do things a different way...

Write a Stored Procedure that both creates the table and adds the user. See SQL SECURITY DEFINER for how to temporarily give an enduser root permission. But be aware of the security implications.

Then, to add the table and the user, it is one CALL statement.

answered Aug 24 '20 at 23:25

Rick James

135,179
13
127
222

The sql script will be executed as root so no problem with that. I figured out how to do this: https://stackoverflow.com/a/12839013/5952631 Anything else to know? – Raoul HATTERER Aug 26 '20 at 00:40

score 0 · Answer 3 · answered Aug 28 '20 at 16:14

Problem solved with a procedure as Rick James has suggested and with Prepared Statements https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

I put the following code in my script and I execute the source command SOURCE initdb_gestiondesnotes.SQL

After that I call the procedure with: CALL create_professeur();

DELIMITER $$  
CREATE PROCEDURE CREATE_PROFESSEUR()

   BEGIN
      DECLARE v_a INT Default 1 ;
      DECLARE v_nom VARCHAR(42);
      DECLARE v_prenom VARCHAR(42);
      DECLARE v_login VARCHAR(84);
      DECLARE v_titre VARCHAR(5);
      DECLARE v_pwd VARCHAR(5);
      SET v_pwd = 'p';
      simple_loop: LOOP
         SET v_nom = CONCAT("prof", LPAD(CAST(v_a AS CHAR), 3, '0'));
         SET v_prenom = CONCAT("prenom", LPAD(CAST(v_a AS CHAR), 3, '0'));
         SET v_titre = CASE WHEN RAND() > .5
                  THEN 'M'
                  ELSE 'F' END;
         INSERT INTO Professeur (prenom, nom, titre) VALUES (v_prenom, v_nom, v_titre);
         SET v_login = CONCAT(v_prenom, v_nom);
         SET @sql1 = CONCAT('CREATE OR REPLACE USER ', v_login, '@localhost identified BY  \'p\' ');
         PREPARE stm1 FROM @sql1;
         EXECUTE stm1;
         SET @sql2 = CONCAT('GRANT role_professeur TO ', v_login, '@localhost');
         PREPARE stm2 FROM @sql2;
         EXECUTE stm2;
         SET @sql3 = CONCAT('SET DEFAULT ROLE role_professeur FOR ', v_login, '@localhost');
         PREPARE stm3 FROM @sql3;
         EXECUTE stm3;
         SET v_a=v_a+1;
         IF v_a=51 THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
   DEALLOCATE PREPARE stm1;
   DEALLOCATE PREPARE stm2;
   DEALLOCATE PREPARE stm3;      
END $$

DELIMITER ;

Now I hove 50 user with prenom001prof001, prenom002prof002... as login and 'p' as password. Again with prepared statements I gave each user the role of role_professeur.

Create user after insert in SQL table

3 Answers3