# GBS SMART User Creation

## Overview

This manual details the procedure for creating GBS SMART users.

* Every user must have an LDAP account to log in.
* All GBS Smart configurations and data are inside the schema *SMART*.
* The table `User` contains basic information about the user.
* The table `UserPermission` relates users to their permissions through the fields `UserId` and `PermissionId` (see [List of Available Permissions](#lista-de-permissoes-disponiveis)).
* The table `UserStation` relates users to stations through the fields `UserId` and `StationId`.

## Procedure

### Create a new workstation in the table *Station*

To create a new workstation, or station, insert a record into the table `SMART.Station` filling the following columns:

* `StationId`: Fill with an incremental ID for the station.
* `Description`: Fill with the station name.
* `MandatoryConference`: Indicate whether the station's transactions should go to mandatory conference (`1` for yes, `0` for no).
* `CityId`: Indicate the ID of the city where the station is located.

{% hint style="info" %}
The station ID (`StationId`) will be used in the next steps.
{% endhint %}

### Create a new user in the table *User*

To create a new user, insert a record into the table `SMART.User` filling the following columns:

* `CPF`: Fill with the user's CPF.
* `Username`: Fill with the username.
* `Admin`: Indicate whether the user is an administrator (`1` for yes, `0` for no).
* `Active`: Indicate whether the user is active (`1` for active, `0` for inactive). The default value is `1`.
* `Pguid` (optional): Fill with the user's PGUID in the biometric database.

{% hint style="info" %}
The column `UserId` is auto-increment. The user ID will be used in the next steps.
{% endhint %}

### Assign permissions to the user in the table *UserPermission*

After creating the user, you must assign their permissions. To do this, insert a record into the table `SMART.UserPermission` for each permission the user should have, relating the `UserId` with the `PermissionId`.

#### List of available permissions

The `PermissionId` is specified in the following table:

<table><thead><tr><th width="120">PermissionId</th><th>Description</th><th width="150">PermissionName</th></tr></thead><tbody><tr><td>1</td><td>Permission to list civil registration, 2nd copy, etc.</td><td>CIVIL</td></tr><tr><td>2</td><td>Permission to list criminal records.</td><td>CRIMINAL</td></tr><tr><td>3</td><td>Permission to view the layout screen.</td><td>LAYOUT</td></tr><tr><td>4</td><td>Permission to view the advanced search screen.</td><td>SEARCH</td></tr><tr><td>5</td><td>Permission to perform biometric capture.</td><td>CAPTURE</td></tr><tr><td>6</td><td>Permission to download biometrics, print protocol and record.</td><td>DOWNLOAD</td></tr><tr><td>7</td><td>Permission to perform biographical verification.</td><td>CONFERENCE</td></tr><tr><td>8</td><td>Permission to perform package check-in.</td><td>PACKAGE</td></tr></tbody></table>

### Associate the user to a station in the table *UserStation*

Insert a record into the table `UserStation` relating the `UserId` with the `StationId`. The station must be previously configured in the table `SMART.Station`, as explained above.

* `UserId`: Use the `UserId` of the user, as shown in the table `SMART.User`.
* `StationId`: Fill with the ID of the station to which the user will be associated, as shown in the table `SMART.Station`.

### *Procedure* for creating User, assigning station and permissions

```sql
DELIMITER //
CREATE PROCEDURE InsertNewUser(
	IN p_CPF VARCHAR(14),
	IN p_Username VARCHAR(64),
	IN p_Admin TINYINT(1),
	IN p_Active TINYINT(1),
	IN p_Pguid VARCHAR(100),
	IN p_StationId INT,
	IN p_Permissions VARCHAR(100) -- Permissions separated by comma (ex: '1,4,5,6,7')
)
BEGIN
	DECLARE user_id INT;
	DECLARE perm_pos INT DEFAULT 1;
	DECLARE perm_length INT;
	DECLARE current_permission VARCHAR(10);
	DECLARE done INT DEFAULT 0;

	-- Inserts a new user into the User table
	INSERT INTO `User` (`CPF`, `Username`, `Admin`, `Active`, `Pguid`)
	VALUES (p_CPF, p_Username, p_Admin, p_Active, p_Pguid);

	-- Gets the last inserted ID in the User table
	SET user_id = LAST_INSERT_ID();

	-- Loop to insert permissions
	read_loop: LOOP
		SET perm_length = LOCATE(',', p_Permissions, perm_pos) - perm_pos;
		IF perm_length < 0 THEN
			SET perm_length = LENGTH(p_Permissions) - perm_pos + 1;
			SET done = 1;
		END IF;
		SET current_permission = SUBSTRING(p_Permissions, perm_pos, perm_length);

		-- Insert permission into the UserPermission table
		INSERT INTO `UserPermission` (`UserId`, `PermissionId`)
		VALUES (user_id, current_permission);

		IF done = 1 THEN
			LEAVE read_loop;
		END IF;

		SET perm_pos = LOCATE(',', p_Permissions, perm_pos) + 1;
	END LOOP read_loop;

	-- Inserts association of the new user with a station into the UserStation table
	INSERT INTO `UserStation` (`UserId`, `StationId`)
	VALUES (user_id, p_StationId);
END //
DELIMITER ;
```

#### Example call of the *procedure*

**Arguments:**

* **CPF** (numbers only)
* **Username** (same as LDAP)
* **Admin** (`0`: false, `1`: true)
* **Active** (`0`: false, `1`: true)
* **PGUID** of GBDS (search CPF in GBDS)
* **Station ID**
* **Permissions** separated by comma

Call the stored procedure to insert a new user:

```sql
CALL InsertNewUser('12345678900', 'new_user', 0, 1, NULL, 1,'1,4,5,6,7');
```

The example above creates a new user with CPF `123.456.789-00`, username `new_user`, not an administrator, active, without PGUID, associated to the station with ID `1` and with the permissions `1,4,5,6,7`.