Trongate Docs
switch to dark modeswitch to dark mode
»
»
The Three Security Tables

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.  

Just To Let You Know
If you are a user of The Trongate Desktop App and you have created at least one module then the database tables described on this page would have been automatically created for you.

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'.

Top Tip
Do not add any additional columns to the trongate_users table (such as 'username' and/or 'password').  Although this may seem tempting, it's not necessary and will make your app less scalable.  It's a much better strategy to link 'trongate_users' to another database table, perhaps via use of a pivot table if necessary.

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);


Top Tip
You can use Trongate's make_rand_str() method to generate a random 32 character string for each Trongate user record.  The code for this would be:

 $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'
)

Just To Let You Know
There is no need to add any sample data to this table.  The business of adding and deleting rows of data on trongate_tokens is handled automatically, by the framework.





HELP & SUPPORT

If you have a question or a comment relating to anything you've see here, please goto the Help Bar.

 
×