user_name
- Last UpdatedNov 20, 2024
- 3 minute read
The user_name table defines all users of the system. Each user has a unique identifier (usually their name), an optional description, and a password (stored in encrypted form) and can be assigned a labor rate. Users are required for the security system and also for assigning labor if the labor collection option is used.
Logging in always requires entry of the user’s ID, but which of ID, description, ID + description, or description + ID is used to display a list of users in other contexts is configurable.
Users are normally assigned to at least one group, which is necessary to obtain any privileges. Users are global. Employees (for recording labor hours) must be defined as users.
"Deleted" users can be kept in the system but removed from all groups to remove any privileges while at the same time maintaining their information for data that may have been recorded against them.
Each user has an email address that can be used in a custom application for sending messages or alerts, and a preferred language so that terminology mapping can be dynamic per user.
The auth_method field is used to allow authentication of the user_id and password using Windows-defined users. This prevents duplicate maintenance of passwords only as users and groups must still be defined within the MES database.
The active field is used to allow users to be deactivated (deleted) without removing them from the database.
The last_login field is used to store the last login time for each user. This enables extracting which users are no longer active users of the system to highlight them for formal deactivation if necessary.
Can’t name this table ‘user’ as it is a reserved word in SQL Server.
|
Field |
Datatype |
PK |
Nulls? |
Description |
|---|---|---|---|---|
|
user_id |
string40 |
Y |
N |
User unique identifier. This is normally the employee number or name. |
|
user_desc |
string80 |
N |
Y |
User description. The description could be the employee name or number. |
|
encrypt_pw |
string40 |
N |
N |
Encrypted password. |
|
salt |
nvarchar |
N |
N |
User salting string. |
|
active |
logical |
N |
N |
Is this user active in the system? Allows for users to be deactivated without deleting them from the database for audit_trail purposes. Default = Yes. |
|
hourly_cost |
tfloat |
N |
Y |
Labor cost per hour |
|
email_address |
string80 |
N |
Y |
External e-mail address |
|
lang_id |
int32 |
N |
N |
Preferred language Default = 1. |
|
def_dept_id |
string40 |
N |
Y |
Default labor department |
|
last_login |
tdatetime |
N |
Y |
Stores the last login time for each user in UTC |
|
last_pw_change |
tdatetime |
N |
Y |
Date of last change of password in UTC |
|
failed_logins |
int32 |
N |
N |
Stores the current number of failed logins for each user. Default = 0. |
|
auth_method |
int32 |
N |
N |
Enumeration of authentication method: 0 = MES Native authentication (default) 1 = Windows Active Directory (AD) OS User authentication 2 = Windows AD OS Group authentication |
|
def_lab_cd |
string40 |
N |
Y |
Default labor code for this user. If defined this takes priority over labor_exec.def_lab_cd. |
|
smtp_server |
string80 |
N |
Y |
Email server details |
|
pop3_server |
string80 |
N |
Y |
Email server details |
|
email_account |
string80 |
N |
Y |
Email server details |
|
email_pw |
string20 |
N |
Y |
Email server details |
|
spare1 |
string1000 |
N |
Y |
Use defined by language term 1078 |
|
spare2 |
string1000 |
N |
Y |
Use defined by language term 1079 |
|
spare3 |
string1000 |
N |
Y |
Use defined by language term 1080 |
|
spare4 |
string1000 |
N |
Y |
Use defined by language term 1081 |
|
last_edit_comment |
string254 |
N |
Y |
Reserved for internal use to indicate why this record was changed |
|
last_edit_by |
string40 |
N |
Y |
Who last changed this record |
|
last_edit_at |
tdatetime |
N |
N |
When this record was last changed |
|
mod_id |
timestamp |
N |
N |
A binary number that increments each time the row is modified. Needed in this table because user can change own password. |
|
row_id |
ID 1 |
N |
N |
Unique row identifier, for audit trail |
FK from def_dept_id to labor_dept. (Set null)
FK from def_lab_cd to labor_cat. (Set null)