Security Classification

Industry-aligned security classification, user clearance levels, and access control.

ahgSecurityClearancePlugin

All Schemas

object_access_grant

object_classification_history

object_compartment

object_declassification_schedule

object_security_classification

security_2fa_session

security_access_condition_link

security_access_log

security_access_request

security_audit_log

security_classification

security_clearance_history

security_compartment

security_compliance_log

security_declassification_schedule

security_retention_schedule

security_watermark_log

user_compartment_access

user_security_clearance

user_security_clearance_log

Entity Relationship Diagram

Open Full Size

Foreign Key Relationships

Constraint	Table	Column	References	Referenced Column
`object_access_grant_ibfk_1`	`object_access_grant`	request_id	`access_request`	id
`object_compartment_ibfk_1`	`object_compartment`	compartment_id	`security_compartment`	id
`fk_osc_classification`	`object_security_classification`	classification_id	`security_classification`	id
`fk_osc_classified_by`	`object_security_classification`	classified_by	`user`	id
`fk_osc_object`	`object_security_classification`	object_id	`information_object`	id
`fk_sal_classification`	`security_access_log`	classification_id	`security_classification`	id
`fk_sal_object`	`security_access_log`	object_id	`information_object`	id
`security_access_request_ibfk_1`	`security_access_request`	classification_id	`security_classification`	id
`security_access_request_ibfk_2`	`security_access_request`	compartment_id	`security_compartment`	id
`security_clearance_history_ibfk_2`	`security_clearance_history`	new_classification_id	`security_classification`	id
`security_clearance_history_ibfk_1`	`security_clearance_history`	previous_classification_id	`security_classification`	id
`security_compartment_ibfk_1`	`security_compartment`	min_clearance_id	`security_classification`	id
`security_declassification_schedule_ibfk_1`	`security_declassification_schedule`	from_classification_id	`security_classification`	id
`security_declassification_schedule_ibfk_2`	`security_declassification_schedule`	to_classification_id	`security_classification`	id
`user_compartment_access_ibfk_1`	`user_compartment_access`	compartment_id	`security_compartment`	id
`fk_usc_classification`	`user_security_clearance`	classification_id	`security_classification`	id

`object_access_grant`

14 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`user_id`	int unsigned	NOT NULL	-
FK	`request_id`	int unsigned	NULL	-
FK	`object_type`	varchar(45)	NOT NULL	-		information_object, repository, actor
	`object_id`	int unsigned	NOT NULL	-
	`include_descendants`	tinyint(1)	NULL	`0`
	`access_level`	varchar(28)	NULL	`view`		view, download, edit
	`granted_by`	int unsigned	NOT NULL	-
	`granted_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`expires_at`	datetime	NULL	-
	`revoked_at`	datetime	NULL	-
	`revoked_by`	int unsigned	NULL	-
	`notes`	text	NULL	-
FK	`active`	tinyint(1)	NULL	`1`

`object_classification_history`

8 columns 12 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`object_id`	int	NOT NULL	-
	`previous_classification_id`	int unsigned	NULL	-
	`new_classification_id`	int unsigned	NULL	-
	`action`	varchar(50)	NOT NULL	-
FK	`changed_by`	int	NULL	-
	`reason`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`object_compartment`

7 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`object_id`	int unsigned	NOT NULL	-
FK	`compartment_id`	int unsigned	NOT NULL	-
	`assigned_by`	int unsigned	NULL	-
	`assigned_date`	date	NOT NULL	-
	`notes`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`object_declassification_schedule`

10 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`object_id`	int	NOT NULL	-
	`from_classification_id`	int	NULL	-
	`to_classification_id`	int	NULL	-
FK	`scheduled_date`	date	NOT NULL	-
FK	`processed`	tinyint(1)	NULL	`0`
	`processed_at`	datetime	NULL	-
	`processed_by`	int	NULL	-
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`object_security_classification`

17 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
UQ	`object_id`	int	NOT NULL	-
FK	`classification_id`	int unsigned	NOT NULL	-
FK	`classified_by`	int	NULL	-
	`classified_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
FK	`assigned_by`	int unsigned	NULL	-
	`assigned_at`	datetime	NOT NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`review_date`	date	NULL	-
	`declassify_date`	date	NULL	-
	`declassify_to_id`	int unsigned	NULL	-
	`reason`	text	NULL	-
	`handling_instructions`	text	NULL	-
	`inherit_to_children`	tinyint(1)	NULL	`1`
	`justification`	text	NULL	-
	`active`	tinyint(1)	NOT NULL	`1`
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`security_2fa_session`

8 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`user_id`	int unsigned	NOT NULL	-
UQ	`session_id`	varchar(100)	NOT NULL	-
	`verified_at`	timestamp	NOT NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
FK	`expires_at`	timestamp	NOT NULL	-
	`ip_address`	varchar(45)	NULL	-
	`device_fingerprint`	varchar(255)	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`security_access_condition_link`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`object_id`	int	NOT NULL	-
FK	`classification_id`	int unsigned	NULL	-
	`access_conditions`	text	NULL	-
	`reproduction_conditions`	text	NULL	-
	`narssa_ref`	varchar(100)	NULL	-
	`retention_period`	varchar(50)	NULL	-
	`updated_by`	int	NULL	-
	`updated_at`	datetime	NULL	-

`security_access_log`

11 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`user_id`	int unsigned	NULL	-
FK	`object_id`	int	NOT NULL	-
FK	`classification_id`	int unsigned	NOT NULL	-
	`action`	varchar(50)	NOT NULL	-
	`access_granted`	tinyint(1)	NOT NULL	-
	`denial_reason`	varchar(255)	NULL	-
	`justification`	text	NULL	-
	`ip_address`	varchar(45)	NULL	-
	`user_agent`	varchar(255)	NULL	-
	`created_at`	datetime	NOT NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`security_access_request`

16 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`user_id`	int unsigned	NOT NULL	-
FK	`object_id`	int unsigned	NULL	-
FK	`classification_id`	int unsigned	NULL	-
FK	`compartment_id`	int unsigned	NULL	-
	`request_type`	varchar(74)	NOT NULL	-		view, download, print, clearance_upgrade, compartment_access, renewal
	`justification`	text	NOT NULL	-
	`duration_hours`	int	NULL	-
FK	`priority`	varchar(33)	NULL	`normal`		normal, urgent, immediate
FK	`status`	varchar(51)	NULL	`pending`		pending, approved, denied, expired, cancelled
	`reviewed_by`	int unsigned	NULL	-
	`reviewed_at`	timestamp	NULL	-
	`review_notes`	text	NULL	-
	`access_granted_until`	timestamp	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`security_audit_log`

11 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`object_id`	int	NULL	-
	`object_type`	varchar(50)	NULL	`information_object`
FK	`user_id`	int	NULL	-
	`user_name`	varchar(255)	NULL	-
FK	`action`	varchar(100)	NOT NULL	-
FK	`action_category`	varchar(50)	NULL	`access`
	`details`	json	NULL	-
	`ip_address`	varchar(45)	NULL	-
	`user_agent`	text	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`security_classification`

19 columns 6 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
UQ	`code`	varchar(20)	NOT NULL	-
UQ	`level`	tinyint unsigned	NOT NULL	-
	`name`	varchar(100)	NOT NULL	-
	`description`	text	NULL	-
	`color`	varchar(20)	NULL	-
	`icon`	varchar(100)	NULL	-
	`requires_justification`	tinyint(1)	NOT NULL	`0`
	`requires_approval`	tinyint(1)	NOT NULL	`0`
	`requires_2fa`	tinyint(1)	NOT NULL	`0`
	`max_session_hours`	int	NULL	-
	`watermark_required`	tinyint(1)	NOT NULL	`0`
	`watermark_image`	varchar(255)	NULL	-
	`download_allowed`	tinyint(1)	NOT NULL	`1`
	`print_allowed`	tinyint(1)	NOT NULL	`1`
	`copy_allowed`	tinyint(1)	NOT NULL	`1`
	`active`	tinyint(1)	NOT NULL	`1`
	`created_at`	datetime	NOT NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NOT NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`security_clearance_history`

8 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`user_id`	int unsigned	NOT NULL	-
FK	`previous_classification_id`	int unsigned	NULL	-
FK	`new_classification_id`	int unsigned	NULL	-
	`action`	varchar(86)	NOT NULL	-		granted, upgraded, downgraded, revoked, renewed, expired, 2fa_enabled, 2fa_disabled
	`changed_by`	int unsigned	NOT NULL	-
	`reason`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`security_compartment`

10 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
UQ	`code`	varchar(50)	NOT NULL	-
	`name`	varchar(255)	NOT NULL	-
	`description`	text	NULL	-
FK	`min_clearance_id`	int unsigned	NOT NULL	-
	`requires_need_to_know`	tinyint(1)	NULL	`1`
	`requires_briefing`	tinyint(1)	NULL	`0`
FK	`active`	tinyint(1)	NULL	`1`
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`security_compliance_log`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`action`	varchar(100)	NOT NULL	-
	`object_id`	int	NULL	-
FK	`user_id`	int	NULL	-
	`username`	varchar(255)	NULL	-
	`details`	text	NULL	-
	`ip_address`	varchar(45)	NULL	-
	`hash`	varchar(64)	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`security_declassification_schedule`

12 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`object_id`	int unsigned	NOT NULL	-
FK	`scheduled_date`	date	NOT NULL	-
FK	`from_classification_id`	int unsigned	NOT NULL	-
FK	`to_classification_id`	int unsigned	NULL	-
	`trigger_type`	varchar(30)	NOT NULL	`date`		date, event, retention
	`trigger_event`	varchar(255)	NULL	-
	`processed`	tinyint(1)	NULL	`0`
	`processed_at`	timestamp	NULL	-
	`processed_by`	int unsigned	NULL	-
	`notes`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`security_retention_schedule`

8 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
UQ	`narssa_ref`	varchar(100)	NOT NULL	-
	`record_type`	varchar(255)	NOT NULL	-
	`retention_period`	varchar(100)	NOT NULL	-
	`disposal_action`	varchar(100)	NOT NULL	-
	`legal_reference`	text	NULL	-
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`security_watermark_log`

11 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`user_id`	int unsigned	NOT NULL	-
FK	`object_id`	int unsigned	NOT NULL	-
	`digital_object_id`	int unsigned	NULL	-
	`watermark_type`	varchar(32)	NOT NULL	`visible`		visible, invisible, both
	`watermark_text`	varchar(500)	NOT NULL	-
FK	`watermark_code`	varchar(100)	NOT NULL	-
	`file_hash`	varchar(64)	NULL	-
	`file_name`	varchar(255)	NULL	-
	`ip_address`	varchar(45)	NULL	-
FK	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`user_compartment_access`

12 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`user_id`	int unsigned	NOT NULL	-
FK	`compartment_id`	int unsigned	NOT NULL	-
	`granted_by`	int unsigned	NOT NULL	-
	`granted_date`	date	NOT NULL	-
FK	`expiry_date`	date	NULL	-
	`briefing_date`	date	NULL	-
	`briefing_reference`	varchar(100)	NULL	-
	`notes`	text	NULL	-
	`active`	tinyint(1)	NULL	`1`
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`user_security_clearance`

7 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int unsigned	NOT NULL	-	auto_increment
UQ	`user_id`	int unsigned	NOT NULL	-
FK	`classification_id`	int unsigned	NOT NULL	-
FK	`granted_by`	int unsigned	NULL	-
	`granted_at`	datetime	NOT NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
FK	`expires_at`	datetime	NULL	-
	`notes`	text	NULL	-

`user_security_clearance_log`

7 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int unsigned	NOT NULL	-	auto_increment
FK	`user_id`	int unsigned	NOT NULL	-
	`classification_id`	int unsigned	NULL	-
	`action`	varchar(41)	NOT NULL	-		granted, revoked, updated, expired
FK	`changed_by`	int unsigned	NULL	-
	`notes`	text	NULL	-
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

Notes

Legend

PK Primary Key FK Foreign Key / Index UQ Unique Constraint

Table structures are read live from the database. Row counts reflect current data.