Trongate Website Homepage
☰
Prev
next
Home
»
Authorization & Authentication
»
Understanding The Security Tables

Understanding The Security Tables

Trongate’s token system utilizes three core relational database tables, collectively referred to as "the security tables." This document outlines each table’s structure and its role in the framework.

Note: If you are using The Trongate Desktop App and have created at least one module, these tables are generated automatically.

Table 1: trongate_user_levels

The trongate_user_levels table defines various user roles within the application. Its structure is as follows:

SQL Statement:

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

Sample Data: Insert entries for each user level, such as 'admin' and 'member':

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

Table 2: trongate_users

The trongate_users table stores user accounts. Its structure includes:

Important: Do not add columns like 'username' or 'password' directly. Instead, link to another table for these details to maintain scalability.

SQL Statement:

CREATE TABLE `trongate_users` (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
code VARCHAR(32) NOT NULL,
user_level_id INT(11) NOT NULL,
FOREIGN KEY (user_level_id) REFERENCES `trongate_user_levels`(id)
);

Sample Data: Add user records, including the 'member' level:

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

Tip: Use the make_rand_str() function to generate a 32-character string:

$code = make_rand_str(32);

The code column in the trongate_users table is designed to enhance user privacy and security. It stores a random, 32-character string used to reference user records securely. This approach prevents direct exposure of sensitive information like user IDs in URLs or API calls, reducing the risk of unauthorized access and maintaining user confidentiality.

For example, instead of using numeric IDs that could reveal internal structure, you use the code in URLs, like so:

https://example.com/profile/7rSvE86mYi2Z8EKwVMrBDHm3FmTBDDkL

This method helps obscure user data and improve security by making it harder to infer user information.

Table 3: trongate_tokens

The trongate_tokens table manages authentication tokens with the following structure:

SQL Statement:

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',
FOREIGN KEY (user_id) REFERENCES `trongate_users`(id)
);

Understanding these tables is crucial for implementing Trongate’s token system effectively. Proper configuration ensures a secure and scalable authentication system.

Interaction of the Three Tables

These tables work together to provide robust authentication and authorization:

1. Table Roles

2. Table Interactions

3. Key Security Considerations

By understanding how trongate_user_levels, trongate_users, and trongate_tokens function together, you can manage authentication and authorization effectively in your Trongate application.

Prev
next