Add users directly in SQL

Management and Administration
,
#1

Hi

I’m setting up an emergy EMR for the covid-patients.
I need to add many users. I thought to be smart and was going to write directly in the mysql-DB.
I do an insert into ‘users’, ‘users_secure’ and ‘gacl_aor’.
I made my own salt and blowfish password for each user, based on the code of the openemr-project.
But… it doesn’t work when I do a login (No valid username or pass).
I guess still a problem with the acl? in which table should I add something else? Or another problem?
(No problems with creating blowfish hash or salt when testing manually).

Many thanks!
Pieter

INSERT INTO users ( id , username , password , authorized , info , source , fname , mname , lname , suffix , federaltaxid , federaldrugid , upin , facility , facility_id , see_auth , active , npi , title , specialty , billname , email , email_direct , url , assistant , organization , valedictory , street , streetb , city , state , zip , street2 , streetb2 , city2 , state2 , zip2 , phone , fax , phonew1 , phonew2 , phonecell , notes , cal_ui , taxonomy , calendar , abook_type , pwd_expiration_date , pwd_history1 , pwd_history2 , default_warehouse , irnpool , state_license_number , weno_prov_id , newcrop_user_role , cpoe , physician_type , main_menu_role , patient_menu_role ) VALUES (‘15’, 'drtesttest, ‘NoLongerUsed’, ‘0’, NULL, NULL, ‘Test’, NULL, ‘Test’, NULL, NULL, NULL, NULL, ‘Triage’, ‘3’, ‘3’, ‘1’, NULL, NULL, NULL, NULL, ‘test@test.com’, ‘test@test.com’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,‘058513923’, NULL, ‘0496522965’, NULL, ‘1’, ‘207Q00000X’, ‘0’, ‘’, ‘0000-00-00’, NULL, NULL, ‘’, ‘’,‘1-33293-82-004’, NULL, ‘erxdoctor’, NULL, ‘specialized_physician’, ‘standard’, ‘standard’);
NSERT INTO users_secure ( id , username , password , salt , last_update , password_history1 , salt_history1 , password_history2 , salt_history2 , last_challenge_response , login_work_area , login_fail_counter ) VALUES (‘15’, ‘drtesttest’, ‘$2a$05$jL0PXMHMVh1ThoEkufanxOETYC.JUpw9AW5nyXDBXxxeHyJXnQinS’, ‘$2a$05$jL0PXMHMVh1ThoEkufanxV$’, current_timestamp(), NULL, NULL, NULL, NULL, NULL, NULL, ‘0’);
INSERT INTO gacl_aro ( id , section_value , value , order_value , name , hidden ) VALUES (15, ‘users’, ‘drtesttest’, 10, ‘Test Test’, 0);

1 Like
#2

Hi Pieter, that is a really interesting question.

If I recall correctly there are some length and complexity requirements for a username and password. The username and password might also need to be different. “Password does not meet minimum requirements and should contain at least each of the following items: A number, a lowercase letter, an uppercase letter, a special character (not a letter or number).”

When I use the add user prompt you need to be logged in as an admin and type your admin password. Maybe try setting the authorized value to 1 and see what happens? Also the calendar field you may want to set to 1 as well.

A good place to start might be to look into /openemr/interface/usergroup/usergroup_admin_add.php for clues.

It takes me to user_info_ajax.php. Which appears to call a function from $library/authentication/password_change.php"

Which led me to open file openemr/library/authentication/common_operations.php

So maybe try comparing the initializePassword function in it.

If you look at login_operations.php when login fails it generates several “newEvent” options. Can you check the logs?

#3

Nevermind on the authorized value. I’m able to login without it.

After a new user added this way isn’t able to log in can you check the logs table in the database? When I type an incorrect password intentionally I see the below under the comments column: failure: 10.0.2.2. user password incorrect where 10.0.2.2 is the IP address I’m attempting to log in from.

A successful login looks like:
|19|2020-03-30 06:33:07|login|login|admin|Default|success: 10.0.2.2|NULL|NULL|1|NULL|NULL|open-emr|NULL|NULL|

#4

Hi Rachel
Thx for the responses!
This is in the log:
failure: 81.240.22.101 (81.240.22.101). user not in group: Default
So, in which table do I need to a change? I can’t figure it out at the moment.

#5

Problem solved.
You need to add a row into tables
-users
-users_secure
-gacl_aro
-gacl_groups_aro_map
-groups

Thx for the support!

$sqlusers .= “INSERT INTO users (id, username, password, authorized, info, source, fname, mname, lname, suffix, federaltaxid, federaldrugid, upin, facility, facility_id, see_auth, active, npi, title, specialty, billname, email, email_direct, url, assistant, organization, valedictory, street, streetb, city, state, zip, street2, streetb2, city2, state2, zip2, phone, fax, phonew1, phonew2, phonecell, notes, cal_ui, taxonomy, calendar, abook_type, pwd_expiration_date, pwd_history1, pwd_history2, default_warehouse, irnpool, state_license_number, weno_prov_id, newcrop_user_role, cpoe, physician_type, main_menu_role, patient_menu_role) VALUES (”.$id.", ‘".$ar[$i]->{‘username’}."’, ‘NoLongerUsed’, ‘0’, NULL, NULL, ‘".$ar[$i]->{‘fname’}."’, NULL, ‘".$ar[$i]->{‘lname’}."’, NULL, NULL, NULL, NULL, ‘".$ar[$i]->{‘facility’}."’, ‘".$ar[$i]->{‘facility_id’}."’, ‘3’, ‘1’, NULL, NULL, NULL, NULL, ‘".$ar[$i]->{‘email’}."’, ‘".$ar[$i]->{‘email’}."’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,‘0".$ar[$i]->{‘phonew1’}."’, NULL, ‘0".$ar[$i]->{‘phone’}."’, NULL, ‘1’, ‘207Q00000X’, ‘0’, ‘’, ‘0000-00-00’, NULL, NULL, ‘’, ‘’,’".$ar[$i]->{‘state_license_number’}."’, NULL, ‘erxdoctor’, NULL, ‘specialized_physician’, ‘standard’, ‘standard’);
";

$sqlpass .= "INSERT INTO `users_secure` (`id`, `username`, `password`, `salt`, `last_update`, `password_history1`, `salt_history1`, `password_history2`, `salt_history2`, `last_challenge_response`, `login_work_area`, `login_fail_counter`) VALUES (".$id.", '".$ar[$i]->{'username'}."', '".crypt($ar[$i]->{'password'},$salt[$i])."', '".$salt[$i]."', current_timestamp(), NULL, NULL, NULL, NULL, NULL, NULL, '0');
";

$sqlacl .= "INSERT INTO `gacl_aro` (`id`, `section_value`, `value`, `order_value`, `name`, `hidden`) VALUES (".$id.", 'users', '".$ar[$i]->{'username'}."', 10, '".$ar[$i]->{'fname'}." ".$ar[$i]->{'lname'}."', 0);
";

$sqlmap .="INSERT INTO `gacl_groups_aro_map` (`group_id`, `aro_id`) VALUES ('13', '".$id."');
";

$sqlgroup .="INSERT INTO `groups` (`id`, `name`, `user`) VALUES (NULL, 'Default', '".$ar[$i]->{'username'}."');
";
4 Likes
Batch user creation
#6

Great, glad you were able to get this figured out. Maybe we should consider making it easier to batch add new users. I imagine doing it one at a time can get pretty tedious.

Feel free to ping the community on slack chat if you’d like to discuss further support for batch addititions of users.
https://www.open-emr.org/wiki/index.php/OpenEMR_Slack_Chat

#7

thank you for this, I was struggling with this and this post helped!

#8

3 posts were merged into an existing topic: Restting password for a user with and Administrator account