The Three Security Tables

Trongate's token system requires your web app to have access to three relational database tables, hereby referred to as 'the three security tables'.  Below is an explanation of the structure and purpose of each table.  

Table 1: trongate_user_levels

The first of our tables is trongate_user_levels.  This table has the following columns:

• id INT(11)
  
• user_level VARCHAR(125)

The 'id' column is a primary key that should automatically increment for new records.

The 'user_level' column is for storing user level titles. 

The SQL code for generating the user_levels table is shown below:

CREATE TABLE `trongate_user_levels` (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_level varchar (125) NOT NULL
)

SAMPLE DATA

A row should be added to the trongate_user_levels table for each user level that will be accessing your app.  Below is some sample SQL code for adding data to the trongate_user_levels table.  For this example, we're adding two table rows - a row for site administrators called 'admin' and a row for a second user level called 'member':

INSERT INTO `trongate_user_levels` (`id`, `level_title`) VALUES
(1, 'admin'),
(2, 'member');

Table 2: trongate_users

Our second database table is trongate_users.  This table has the following columns:

• id INT(11)
  
• code VARCHAR(32)
• user_level_id INT (11)

The 'id' column is a primary key that should automatically increment for new records.

The 'code' column should be a random alphanumeric string of 32 characters in length. This column is not involved in the token authentication process.  It was added for the benefit of site owners have a desire to grant access to user records without revealing how many users are registered on a system (for example, by exposing numeric user IDs via a URL).

The 'user_level_id' column is used for connecting trongate_users to trongate_user_levels.  Each value stored in this column should match a corresponding 'id' from 'trongate_user_levels'.

SAMPLE DATA

A row should be added onto the trongate_users table for every user who will require token based authorization and authentication.  Below is some sample SQL code for adding data to the trongate_users table.  For this example, we're adding one table row for a user who will have a user level of 'member'.  We know that the user record is for a member because the user_level_id is set to a value of 2:

INSERT INTO `trongate_users` (`id`, `code`, `user_level_id`) VALUES
(1, '7rSvE86mYi2Z8EKwVMrBDHm3FmTBDDkL', 2);

 $code = make_rand_str(32);

Table 3: trongate_tokens

The third security table is trongate_tokens.  This table has the following columns:

• id INT(11)
  
• token VARCHAR(125)
• user_id INT(11)
• expiry_date INT(11)
• code VARCHAR(3)

The 'id' column is a primary key that should automatically increment for new records.

The 'token' column is for storing security tokens.  Each token will be made up of a random string of characters that are generated automatically by the framework. 

The 'user_id' will be an integer that matches an 'id' from the trongate_users table.

The 'expirty_date' column will be used for storing Unix timestamps.  The values here will represent the expiry date and time for token records.

The 'code' column can contain a string of up to three characters in length.  This column is used internally on Trongate's API manager.  

The SQL code for generating the trongate_tokens table is shown below:

CREATE TABLE trongate_tokens (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  token varchar(125) NOT NULL,
  user_id int(11) DEFAULT 0,
  expiry_date int(11) NOT NULL,
  code varchar(3) DEFAULT '0'
)

​