Greenlight - Adding user via MySQL (outside of the web UI)

[Alban S]

Hi,

I'm trying to pre-set available users in Big Blue Button greenlight via MySQL commands.
The purpose is simple: based on a list of email addresses, I pre-create the users and request them to reset their password first to gain access to greenlight. This allows to disable the registration and enable control over which users can access greenlight.

So far I tried this - creating the user, UID being uniquely generated:

INSERT INTO `users` (`id`, `room_id`, `provider`, `uid`, `name`, `username`, `email`, `social_uid`, `image`, `password_digest`, `accepted_terms`, `created_at`, `updated_at`, `email_verified`, `language`, `reset_digest`, `reset_sent_at`, `activation_digest`, `activated_at`, `trial669`) VALUES (NULL, NULL, 'greenlight', 'gl-peaiglkrhsxq', 'My User', NULL, 'us...@domain.com', NULL, NULL, NULL, '0', '', '', '0', NULL, NULL, NULL, NULL, NULL, NULL)

Then adding the user to the default role (not admin):

INSERT INTO `users_roles` (`user_id`, `role_id`, `trial682`) VALUES ('3', '1', NULL);

I think the room_id might be mandatory... Anything else I shall think of?

Thank you. 

[Ahmad Farhat]

Hi Alban,

Why not just set your Registration Method to Invite, and then invite the specific users that you want to join? (http://docs.bigbluebutton.org/greenlight/gl-admin.html#registration-methods)

As for your issues now, you'll need to create the users in Rails inorder for the after_create methods to run. (Which is the issue you're having with room_id).

The commands you'll need are:

- rails console

- User.create(name: '___', email: '___', password: '___', provider: 'greenlight', email_verified: true)

[Alban S]

I'm actually looking at syncing users from a list - hence would need to call greenlight from a cron task (key reason that I was initially looking at doing this via SQL).

Is there any existing rake task to create new users? (doesn't look like it)

Thx for your suggestions, much appreciated.

[Ahmad Farhat]

Unfortunately there is no rake task that creates users.

You're very welcome!

[Alban S]

Alright, then I have put together the following PHP script using BBB PHP API to create the home meeting room.

https://github.com/bigbluebutton/bigbluebutton-api-php

It does its job - surely can be enhanced - $bbbUserName & $bbbUserEmail have to be set accordingly, and could be fed through a CSV file for instead.

<?php

require_once './vendor/autoload.php';

$mysqlServer = $_SERVER['DB_HOST'];

$mysqlUser = $_SERVER['DB_USERNAME'];

$mysqlPassword = $_SERVER['DB_PASSWORD'];

$mysqlDB = $_SERVER['DB_NAME'];

$bbbUserProvider = 'greenlight';

$bbbUserUIDprefix = 'gl-';

$bbbUserUIDsuffixPermittedChar = 'abcdefghijklmnopqrstuvwxyz';

$bbbUserName = '';

$bbbUserEmail = '';

$bbbUserDbRoleID = 1;

$bbbRoomUIDsuffixPermittedChar = 'abcdefghijklmnopqrstuvwxyz1234567890';

$bbbRoomPasswordPermittedChar = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

$bbbRoomAttendeePassword = generate_string($bbbRoomPasswordPermittedChar, 12);

$bbbRoomModeratorPassword = generate_string($bbbRoomPasswordPermittedChar, 12);

$bbbRoomBbbID = sha1(time());

 

if ((strlen($bbbUserEmail) == 0) || (strlen($bbbUserName) == 0)) {

die('User Name and/or User Email are note defined.');

}

 

$dbh = mysqli_connect($mysqlServer, $mysqlUser, $mysqlPassword); 

    

if (!$dbh)    

die("Unable to connect to MySQL: " . mysqli_error($dbh)); 

    

if (!mysqli_select_db($dbh,$mysqlDB))     

die("Unable to select database: " . mysqli_error($dbh));

$sql_stmt = "SELECT COUNT(id) as IsUnique FROM users where email = '$bbbUserEmail'";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Database access failed: " . mysqli_error($dbh)); 

$row = mysqli_fetch_array($result);

if ($row['IsUnique'] != 0)

die('Email address already in use - not creating new user'); 

$bbb = new BigBlueButton\BigBlueButton();

$createMeetingParams = new BigBlueButton\Parameters\CreateMeetingParameters($bbbRoomBbbID, 'Home Room');

$createMeetingParams->setAttendeePassword($bbbRoomAttendeePassword);

$createMeetingParams->setModeratorPassword($bbbRoomModeratorPassword);

$response = $bbb->createMeeting($createMeetingParams);

var_dump($response);

if ($response->getReturnCode() == 'FAILED')

die('BBB API call to create home room failed.');

$isUserUIDsuffixUnique = false;

while (!$isUserUIDsuffixUnique) {

$bbbUserUIDsuffix = generate_string($bbbUserUIDsuffixPermittedChar, 12);

$sql_stmt = "SELECT COUNT(id) as IsUnique FROM users where uid = '$bbbUserUIDprefix$bbbUserUIDsuffix'";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Database access failed: " . mysqli_error($dbh)); 

$row = mysqli_fetch_array($result);

if ($row['IsUnique'] == 0) {

$isUserUIDsuffixUnique = true;

}

}

$sql_stmt = "INSERT INTO users (id, room_id, provider, uid, name, username, email, social_uid, image, password_digest, accepted_terms, created_at, updated_at, email_verified, language, reset_digest, reset_sent_at, activation_digest, activated_at, trial669) VALUES (NULL, NULL, '$bbbUserProvider', '$bbbUserUIDprefix$bbbUserUIDsuffix', '$bbbUserName', NULL, '$bbbUserEmail', NULL, NULL, NULL, '0', '', '', '0', NULL, NULL, NULL, NULL, NULL, NULL)";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Adding Users record failed: " . mysqli_error($dbh));

$bbbUserDbID = mysqli_insert_id($dbh);

if (!is_int($bbbUserDbID))

die("Fetching DB User ID failed: " . mysqli_error($dbh));

$sql_stmt = "INSERT INTO users_roles (user_id, role_id, trial682) VALUES ('$bbbUserDbID', '$bbbUserDbRoleID', NULL)";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Adding Users Roles record failed: " . mysqli_error($dbh));

$isbbbRoomUIDsuffixUnique = false;

while (!$isbbbRoomUIDsuffixUnique) {

$bbbRoomUID = substr(strtolower($bbbUserName), 1, 3).'-'.generate_string($bbbRoomUIDsuffixPermittedChar, 3).'-'.generate_string($bbbRoomUIDsuffixPermittedChar, 3);

$sql_stmt = "SELECT COUNT(id) as IsUnique FROM rooms where uid = '$bbbRoomUID'";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Database access failed: " . mysqli_error($dbh)); 

$row = mysqli_fetch_array($result);

if ($row['IsUnique'] == 0) {

$isbbbRoomUIDsuffixUnique = true;

}

}

$sql_stmt = "INSERT INTO rooms (id, user_id, name, uid, bbb_id, sessions, last_session, created_at, updated_at, room_settings, moderator_pw, attendee_pw, trial639) VALUES (NULL, $bbbUserDbID, 'Home Room', '$bbbRoomUID', '$bbbRoomBbbID', 0, NULL, NOW(), NOW(), NULL, '$bbbRoomModeratorPassword', '$bbbRoomAttendeePassword', NULL)";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Adding Rooms record failed: " . mysqli_error($dbh));

$bbbRoomDbID = mysqli_insert_id($dbh);

if (!is_int($bbbRoomDbID))

die("Fetching DB User ID failed: " . mysqli_error($dbh));

$sql_stmt = "UPDATE users SET room_id = $bbbRoomDbID where id = $bbbUserDbID";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Updating user with room id failed: " . mysqli_error($dbh));

echo("Generated User UID: $bbbUserUIDprefix$bbbUserUIDsuffix");

echo("Generated Room UID: $bbbRoomUID");

echo("Generated Room UID BBB: $bbbRoomBbbID");

mysqli_close($dbh);

function generate_string($input, $strength = 16) {

$input_length = strlen($input);

$random_string = '';

for($i = 0; $i < $strength; $i++) {

$random_character = $input[random_int(0, $input_length - 1)];

$random_string .= $random_character;

}

 

return $random_string;

}

?>

[Anton Meyburgh]

Hi Alban

Just to make sure I understand what you are saying

With the above you create a room via SQL. You have enhanced it so that a User and a Room can be added via SQL if you provide a Username and Password and it doesn't exist already in the database?

Regards

--
You received this message because you are subscribed to the Google Groups "BigBlueButton-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bigbluebutton-...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/bigbluebutton-dev/84548469-3af1-4b4b-98ed-748958018899%40googlegroups.com.

[Alban S]

Hi Anton,

The provided PHP script will create a user in Greenlight with the required "Home Room".
It's aligned with the way users are being created manually in Greenlight (as far as I could tell).

Its purpose is to enable automated user creation into Greenlight.

Regards

[Alban S]

Quick update in order to fix the logout url of the Home Room meeting.

<?php

require_once './vendor/autoload.php';

$mysqlServer = $_SERVER['DB_HOST'];

$mysqlUser = $_SERVER['DB_USERNAME'];

$mysqlPassword = $_SERVER['DB_PASSWORD'];

$mysqlDB = $_SERVER['DB_NAME'];

$bbbGreenlightRootURL = $_SERVER['GREENLIGHT_URL'];

$bbbUserProvider = 'greenlight';

$bbbUserUIDprefix = 'gl-';

$bbbUserUIDsuffixPermittedChar = 'abcdefghijklmnopqrstuvwxyz';

$bbbUserName = 'UserName';

$bbbUserEmail = 'Us...@Domain.Ext';

$bbbUserDbRoleID = 1;

$bbbRoomUIDsuffixPermittedChar = 'abcdefghijklmnopqrstuvwxyz1234567890';

$bbbRoomPasswordPermittedChar = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

$bbbRoomAttendeePassword = generate_string($bbbRoomPasswordPermittedChar, 12);

$bbbRoomModeratorPassword = generate_string($bbbRoomPasswordPermittedChar, 12);

$bbbRoomBbbID = sha1(time());

 

if ((strlen($bbbUserEmail) == 0) || (strlen($bbbUserName) == 0)) {

die('User Name and/or User Email are note defined.');

}

 

$dbh = mysqli_connect($mysqlServer, $mysqlUser, $mysqlPassword); 

    

if (!$dbh)    

die("Unable to connect to MySQL: " . mysqli_error($dbh)); 

    

if (!mysqli_select_db($dbh,$mysqlDB))     

die("Unable to select database: " . mysqli_error($dbh));

$sql_stmt = "SELECT COUNT(id) as IsUnique FROM users where email = '$bbbUserEmail'";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Database access failed: " . mysqli_error($dbh)); 

$row = mysqli_fetch_array($result);

if ($row['IsUnique'] != 0)

die('Email address already in use - not creating new user'); 

$isUserUIDsuffixUnique = false;

while (!$isUserUIDsuffixUnique) {

$bbbUserUIDsuffix = generate_string($bbbUserUIDsuffixPermittedChar, 12);

$sql_stmt = "SELECT COUNT(id) as IsUnique FROM users where uid = '$bbbUserUIDprefix$bbbUserUIDsuffix'";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Database access failed: " . mysqli_error($dbh)); 

$row = mysqli_fetch_array($result);

if ($row['IsUnique'] == 0) {

$isUserUIDsuffixUnique = true;

}

}

$sql_stmt = "INSERT INTO users (id, room_id, provider, uid, name, username, email, social_uid, image, password_digest, accepted_terms, created_at, updated_at, email_verified, language, reset_digest, reset_sent_at, activation_digest, activated_at, trial669) VALUES (NULL, NULL, '$bbbUserProvider', '$bbbUserUIDprefix$bbbUserUIDsuffix', '$bbbUserName', NULL, '$bbbUserEmail', NULL, NULL, NULL, '0', '', '', '0', NULL, NULL, NULL, NULL, NULL, NULL)";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Adding Users record failed: " . mysqli_error($dbh));

$bbbUserDbID = mysqli_insert_id($dbh);

if (!is_int($bbbUserDbID))

die("Fetching DB User ID failed: " . mysqli_error($dbh));

$sql_stmt = "INSERT INTO users_roles (user_id, role_id, trial682) VALUES ('$bbbUserDbID', '$bbbUserDbRoleID', NULL)";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Adding Users Roles record failed: " . mysqli_error($dbh));

$isbbbRoomUIDsuffixUnique = false;

while (!$isbbbRoomUIDsuffixUnique) {

$bbbRoomUID = substr(strtolower($bbbUserName), 0, 3).'-'.generate_string($bbbRoomUIDsuffixPermittedChar, 3).'-'.generate_string($bbbRoomUIDsuffixPermittedChar, 3);

$sql_stmt = "SELECT COUNT(id) as IsUnique FROM rooms where uid = '$bbbRoomUID'";

$result = mysqli_query($dbh,$sql_stmt);

if (!$result)     

die("Database access failed: " . mysqli_error($dbh)); 

$row = mysqli_fetch_array($result);

if ($row['IsUnique'] == 0) {

$isbbbRoomUIDsuffixUnique = true;

}

}

$bbb = new BigBlueButton\BigBlueButton();

$createMeetingParams = new BigBlueButton\Parameters\CreateMeetingParameters($bbbRoomBbbID, 'Home Room');

$createMeetingParams->setAttendeePassword($bbbRoomAttendeePassword);

$createMeetingParams->setModeratorPassword($bbbRoomModeratorPassword);

$createMeetingParams->setLogoutUrl($bbbGreenlightRootURL.'/'.$bbbRoomUID);

$response = $bbb->createMeeting($createMeetingParams);

var_dump($response);

if ($response->getReturnCode() == 'FAILED')

die('BBB API call to create home room failed.');

$sql_stmt = "INSERT INTO rooms (id, user_id, name, uid, bbb_id, sessions, last_session, created_at, updated_at, room_settings, moderator_pw, attendee_pw, trial639) VALUES (NULL, $bbbUserDbID, 'Home Room', '$bbbRoomUID', '$bbbRoomBbbID', 0, NULL, NOW(), NOW(), '{}', '$bbbRoomModeratorPassword', '$bbbRoomAttendeePassword', NULL)";

[Fred Dixon]

Hi Alban,

Thanks for sharing this.  I'm sure it will help out others who are integrating Greenlight with PHP applications.

Regards,... Fred

--
You received this message because you are subscribed to the Google Groups "BigBlueButton-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bigbluebutton-...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/bigbluebutton-dev/efb43a92-c380-49d2-8dcc-9215786d883d%40googlegroups.com.

--

BigBlueButton Developer

http://bigbluebutton.org/

@bigbluebutton

[Anton Meyburgh]

Thanks a lot Alban

Regards

--
You received this message because you are subscribed to the Google Groups "BigBlueButton-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bigbluebutton-...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/bigbluebutton-dev/efb43a92-c380-49d2-8dcc-9215786d883d%40googlegroups.com.