Adding Users via MySQL

Note: To execute any of the commands listed below you will need to access the server and connect to the database as the cBioPortal user. For instructions on how to perform this see the page Connecting to the Database.

Users Table

The users table contains all the users that have authorised access to the instance of the portal. The table requires a user email address, name, and integer flag indicating if the account is enabled. The following snippet shows how the users table has been defined.

mysql> DESCRIBE cbioportal.users;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| EMAIL   | varchar(128) | NO   | PRI | NULL    |       |
| NAME    | varchar(255) | NO   |     | NULL    |       |
| ENABLED | tinyint(1)   | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

An example entry would be:

mysql> SELECT * FROM cbioportal.users WHERE email = "john.smith@gmail.com";
+--------------------------+----------------+---------+
| EMAIL                    | NAME           | ENABLED |
+--------------------------+----------------+---------+
| john.smith@gmail.com     | John Smith     |       1 | 
+--------------------------+----------------+---------+
1 row in set (0.00 sec)

Note, if the ENABLED value is set to 0, the user will not be able to login to the portal.

View existing users

To view all the existing users in the database you can execute:

mysql> SELECT * FROM cbioportal.users;

If there are users within the database the output returned will appear similar to:

+----------------------+------------+---------+
| EMAIL                | NAME       | ENABLED |
+----------------------+------------+---------+
| john.smith@gmail.com | John Smith |       1 |
| fred.smith@gmail.com | Fred Smith |       1 |
| jane.smith@gmail.com | Jane Smith |       0 |
+----------------------+------------+---------+
3 rows in set (0.00 sec)

However if the database does not currently contain any users the output returned will specify that no entries were found.

Empty set (0.00 sec)

To view the record of a particular user based on some attribute you can add a 'WHERE' condition to the select statement. For example, the following statement selects all users from the database who have the specified email address.

mysql> SELECT * FROM cbioportal.users WHERE email = "john.smith@gmail.com";
+----------------------+------------+---------+
| EMAIL                | NAME       | ENABLED |
+----------------------+------------+---------+
| john.smith@gmail.com | John Smith |       1 |
+----------------------+------------+---------+
1 row in set (0.00 sec)

Insert a new user

To insert a new user into the cbioportal database you will need to provide the unique email address of that user, their name and flag whether or not their account is enabled. This can be done with the following statement:

mysql> INSERT INTO cbioportal.users (EMAIL, NAME, ENABLED) VALUES ('john.smith@gmail.com', 'John Smith', 1);

Upon success you should see an output similar to the following:

Query OK, 1 row affected (0.01 sec)

Note, if the ENABLED value is set to 0, the user account will be disabled and they will not be able to login to the portal. Additionally if the EMAIL value is not unique and already exists for another user in the database then this insertion will fail with an error message similar to the following:

ERROR 1062 (23000): Duplicate entry 'john.smith@gmail.com' for key 'PRIMARY'

You can also insert multiple new users at the same time by providing multiple sets of values:

mysql>  INSERT INTO cbioportal.users (EMAIL, NAME, ENABLED) 
        VALUES  ('john.smith@gmail.com', 'John Smith', 1),
                ('fred.smith@gmail.com', 'Fred Smith', 1),
                ('jane.smith@gmail.com', 'Jane Smith', 0);

Update a user

To update the details of an existing user you will need to specify the attributes to update for users that match a certain condition. Since each user in the database must have a unique email address it is recommended to use the email attribute to select the users to update.

For example, the following statement updates the name of a particular user.

mysql> UPDATE cbioportal.users SET name = 'Jane Doe' WHERE email = 'jane.smith@gmail.com';

You can also update multiple attributes of a user in a single statement. For example, the following statement enables the account of the user Jane Smith and also updates her email address.

mysql>  UPDATE cbioportal.users
        SET name = 'Jane Doe',
            email = 'jane_doe@yahoo.com'
        WHERE email = 'jane.smith@gmail.com';

Note, if the email address of a user is updated within the user table, the email address of that user will not be automatically updated within the authorities table. As such if the email address of a user is updated in the user table the update will need to be replicated in the authorities table.

Modify user account access

A user account can be enabled by modifying the ENABLED attribute of a user record. The value of this attribute will determine whether or not the user is able to log into cBioPortal

Enable a user account

To enable a user account so that they are able to log into cBioPortal, the ENABLED attribute must be set to 1. For example,

mysql> UPDATE cbioportal.users SET enabled = 1 WHERE email = 'jane.smith@gmail.com';

Disable a user account

To disable a user account so that they are unable to log into cBioPortal, the ENABLED attribute must be set to 0. For example,

mysql> UPDATE cbioportal.users SET enabled = 0 WHERE email = 'john.smith@gmail.com';