Security & Permission Architecture
Complete Documentation of the V.E.T.S. Security System
This comprehensive guide explains the security and permission infrastructure that protects
data and controls access throughout the V.E.T.S. Livestock Management System.
Multi-Layer Permission Architecture
V.E.T.S. implements a sophisticated 6-layer permission system. ALL layers must grant access
for an operation to succeed.
🔐 The Six Permission Layers
Permissions are evaluated in order. This "defense in depth" approach ensures robust security.
Layer 1: Table-Level Permissions
Source: sviw_System_MyPermissions |
Grants: S (SELECT), I (INSERT), U (UPDATE), D (DELETE)
Who Grants: System administrators |
Scope: Entire table
Note: Required but NOT sufficient - must also pass Layers 2-6
Layer 2: TeamDoc Permissions
Source: sviw_TeamDoc_MembersWithTDandName |
Levels: Reader, Editor, Manager
Scope: Per-item (each Item/TreeList has its own TeamDoc)
Creator Rule: Item creator automatically gets Manager access
Layer 3: TreeList Group Permissions
Pattern: TreeList name → Group name (spaces → underscores)
Examples: "Professional Services" → VETS_Professional_Services_Editor
Scope: All items within a TreeList category
Layer 4: Animal Ownership Permissions
Source: stbl_AnimalOwnership + User Groups
Logic: User must own animal OR be in veterinarian group
Scope: Individual animals (36,553 active animals)
Layer 5: Row-Level Permissions (Criteria-Based Filtering)
Source: stbl_System_TableRowsPermissions +
stbl_Database_Objects
How It Works: Filters rows based on field value patterns
(NOT per-record by PrimKey, but by LIKE pattern matching)
Configuration: Define filtering fields in stbl_Database_Objects
(CField1, CField2), set criteria patterns in TableRowsPermissions
Real-World Examples (Active):
Example 1: Personal Mailboxes
-- Configuration (stbl_Database_Objects):
Table: stbl_Mail_MailBoxes
CField1 = "Login" -- Filter on this field
-- Permission (stbl_System_TableRowsPermissions):
User: user@example.com
Criteria1 = "user@example.com" -- Show records matching this
-- Generated View (sstp_Database_CreateView):
SELECT * FROM stbl_Mail_MailBoxes
WHERE EXISTS (SELECT * FROM sviw_System_MyPermissions
WHERE TableID = 'stbl_Mail_MailBoxes'
AND stbl_Mail_MailBoxes.Login LIKE Criteria1)
-- Result: User sees ONLY their mailbox (201 users configured)
Example 2: Confidential Risk Records
-- Configuration:
Table: atbl_RiskMgmt_Risks
CField1 = "Confidential" -- Boolean/flag field
-- Managers see all:
Criteria1 = "%" (wildcard matches all values)
-- Staff see non-confidential only:
Criteria1 = "0" (matches FALSE/non-confidential)
-- Result: Data classification based on field value
Current Usage Statistics:
- ~10 tables actively use criteria-based filtering (~1% of 830+ tables)
- 23,297 total permission records in system
- Primary use: Personal data (mailboxes: 201, calendars, image folders, contacts)
- Secondary use: Confidential data filtering (risk management)
- Tertiary use: Multi-tenant domain isolation (groups, codes)
NOT Used For Items/Animals:
- atbl_Items_Items: Has 30 permission records but CField1 = NULL (not enforced in views)
- atbl_VETS_Animals: Has 29 permission records but CField1 = NULL (not enforced in views)
-
Why: TeamDoc Permissions (Layer 2), TreeList Groups (Layer 3), and Animal Ownership (Layer 4)
provide better access control
?? When to Use: Personal/user-specific data (each user sees only their records),
simple flag-based filtering (confidential vs. non-confidential), pattern matching on field values.
When NOT to use: Items, Animals, TeamDoc content - other permission layers (2-4)
handle these better with more flexibility.
Implementation: Views generated by sstp_Database_CreateView
automatically include criteria filtering. Permissions managed via
sstp_Security_TableRowsPermissions_Save
Limitations: Can only filter on 2 fields (CField1, CField2), only LIKE pattern matching
(no complex Boolean logic), requires view regeneration when configuration changes
Layer 6: UI-Level Security (HTML-in-SQL)
Methodology: HTML-in-SQL - Interface elements are conditionally rendered at database level
Impact: Unauthorized actions are never generated, not just hidden
Advantage: Security enforced at UI creation, eliminating attack surface
Innovation: Pioneered server-side UI generation for enterprise security
Real-World Scenarios
Dr. Smith (Vet): ✅✅✅✅✅✅ All 6 layers pass → Full access, SOAP notes visible
Jane (Tech): ✅✅❌✅✅✅ Layer 3 fails → Can VIEW only, simplified UI
Dr. Johnson (Specialist): ✅✅❌✅✅✅ Temporary custody → Consult access only
Sarah (Owner): ❌❌❌❌✅✅ Only Layer 4 → VIEW her horse, client UI
AI Integration & Best Practices
🤖 AI: Permission optimization, smart suggestions (respects all 6 layers),
security audit, automated provisioning
✅ Do: Grant minimum necessary, use TeamDoc for collaboration,
regularly review ActivityLog
❌ Don't: Blanket Layer 1 permissions, over-use row-level,
mix professional groups
Security & Permissions documentation last updated: November 14, 2025