૭( ᵕ•̀ᵕ•́૭)

Lab 11

3 min read

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Security Analysis Requirements

Data Protection Gaps

Sensitive Data Export Risks

  • MISSION includes operation ids, mission names, and priorities which may reveal strategic intent or priority rankings.
  • PERSONNEL includes names, gender, rank, wages, and supervision. Wages are potentially private information, as well as supervisor chains may expose the hierarchy and/or private info related to personnel.
  • STARSHIP includes registry, class, and mission assignments. Location and mission assignment links deployment data with ship identity, which may be potentially risky.
  • PLANET includes sector and civilization metric, which means that strategic planetary intelligence could be exploited.

Mitigation

  • To mitigate these issues, it would be good to create restricted views for general access as well as using data masking and RLS wherever applicable for the database.

Tables Requiring Row-Level Security

  • MISSION → officers should only see missions they’re assigned/authorized to
  • PERSONNEL → personnel should not see others’ wages or gender unless their job requires it
  • STARSHIP → visibility should be based on clearance level or role
  • PLANET → planet data should be redacted for civilians and non-analysts

Data Masking Requirements

  • PERSONNEL.WAGES → mask salaries from non-HR personnel
  • PERSONNEL.GENDER → mask unless user has clearance
  • MISSION.NAME → mask mission names unless clearance is granted
  • STARSHIP.LOCATION → mask current location for unauthorized users

Example for PERSONNEL

ALTER TABLE PERSONNEL
ALTER COLUMN WAGES ADD MASKED WITH (FUNCTION = 'default()');
 
ALTER TABLE PERSONNEL
ALTER COLUMN GENDER ADD MASKED WITH (FUNCTION = 'partial(1,"*",1)');

Security Plan Development

Data Protection Protocol

Row-Level Security Implementation

-- example session context
EXEC sp_set_session_context @key = 'UserID', @value = 'P1357';
EXEC sp_set_session_context @key = 'UserRank', @value = 'Ltnt';
 
-- filter function for `PERSONNEL`
CREATE FUNCTION fn_rls_personnel(@ID char(5))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS access_granted
    WHERE 
        @ID = CAST(SESSION_CONTEXT(N'UserID') AS char(5))
        -- allow 'admr' ranks to view PERSONNEL data
        OR CAST(SESSION_CONTEXT(N'UserRank') AS char(4)) = 'Admr';
 
-- apply rls
CREATE SECURITY POLICY PersonnelRLS
ADD FILTER PREDICATE dbo.fn_rls_personnel(ID)
ON dbo.PERSONNEL
WITH (STATE = ON);

Secure View Architecture

This view can be granted to general users since WAGES, GENDER, and SUPER are hidden

-- restricted view of PERSONNEL without sensitive fields
CREATE VIEW View_PublicPersonnel AS
SELECT ID, NAME, RANK, PLANET
FROM PERSONNEL;

Data Masking Strategy

Data masking only affects non-priviledged users. 

-- mask mission names
ALTER TABLE MISSION
ALTER COLUMN NAME ADD MASKED WITH (FUNCTION = 'partial(1,"***",1)');
-- 'SomeMissionName' -> 'S***e'
 
-- mask starship location
ALTER TABLE STARSHIP
ALTER COLUMN LOCATION ADD MASKED WITH (FUNCTION = 'default()');
-- 'Some Location' -> 'XXXX'

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Graph View