T3.2: Manages SQL Security

Knowledge Review - InterSystems IRIS SQL Specialist

1. Assigns SQL privileges to users and roles (GRANT/REVOKE)

Key Points

  • GRANT command: Assigns administrative or object privileges to users or roles
  • REVOKE command: Removes previously granted privileges from users or roles
  • Administrative privileges: Namespace-specific rights for creating, altering, dropping objects (e.g., %CREATE_TABLE, %ALTER_TABLE, %DROP_VIEW)
  • Object privileges: Specific access rights to tables, views, stored procedures (%ALTER, DELETE, SELECT, INSERT, UPDATE, EXECUTE, REFERENCES)
  • Column-level privileges: Granular control allowing access to specific columns rather than entire tables
  • WITH GRANT OPTION: Allows grantees to grant the same privileges to other users, creating cascading permissions

Detailed Notes

Overview

InterSystems IRIS SQL security operates through a comprehensive privilege system managed primarily via GRANT and REVOKE commands.

  • GRANT command: Assigns privileges to users or roles, enabling specific database operations
  • Privilege scope: Granted on a per-namespace basis, immediately exercisable
  • Multiple grants: Multiple users can grant the same privilege; single REVOKE removes it entirely

Administrative Privileges

Administrative privileges are namespace-specific and include rights to create, alter, and drop database objects:

  • Tables, views, triggers, and procedures
  • %DB_OBJECT_DEFINITION: Grants all 16 data definition privileges in one command

Special administrative privileges:

  • %NOCHECK, %NOINDEX, %NOLOCK, %NOTRIGGER: Control whether users can apply these keyword restrictions during INSERT, UPDATE, or DELETE
  • Note: TRUNCATE TABLE requires %NOTRIGGER privilege

Object Privileges

Object privileges provide access to specific database objects including tables, views, stored procedures, and cubes.

Table-level object privileges:

  • %ALTER, DELETE, SELECT, INSERT, UPDATE, EXECUTE, REFERENCES, CANCEL
  • Grant access to all columns in table or view, including subsequently added columns

Column-level privileges:

  • Can be assigned to specific columns only for more granular control

Wildcard and schema grants:

  • Asterisk (*): Grants privileges to all objects in current namespace
  • SCHEMA keyword: Grants privileges to all objects within a named schema, including future objects

WITH GRANT OPTION

  • Enables privilege delegation
  • Allows grantees to grant the same privileges to other users
  • REVOKE with CASCADE can reverse cascading series of granted privileges

Methods for Granting Privileges

  • SQL GRANT statements
  • ObjectScript methods: `$SYSTEM.SQL.Security.GrantPrivilege()`
  • Management Portal interface

Object Ownership

The owner of an SQL object automatically holds all privileges on that object in all namespaces to which the object is mapped.

Documentation References

2. Understands SQL privilege checking versus application security

Key Points

  • Privilege checking interfaces: SQL privileges enforced through ODBC, JDBC, Dynamic SQL, and SQL Shell only
  • Embedded SQL exemption: Does not perform privilege checking; assumes application-level security controls
  • Class query invocation: Direct class query calls without %SQL.Statement objects bypass SQL privilege checks
  • SQL versus system privileges: SQL privileges more granular than system-level protections; can target specific tables, views, columns
  • Implicit privilege granting: Holding SQL privilege automatically grants related system privileges needed for the SQL action
  • Roles shared across models: Single role can include both SQL and system-level privileges

Detailed Notes

Overview

InterSystems IRIS implements a dual-layer security model that distinguishes between SQL privilege checking and application-level security.

  • Purpose: Different access patterns have different security requirements and trust models
  • Critical knowledge: Understanding when and where privilege checking occurs is essential for designing secure database applications

SQL Privilege Checking Enforcement

SQL privilege checking is enforced exclusively through specific interfaces:

  • ODBC connections
  • JDBC connections
  • Dynamic SQL (%SQL.Statement objects)
  • SQL Shell interface

Behavior:

  • Comprehensive privilege validation before allowing any operation
  • SQLCODE -99: Privilege Violation error if user lacks required privilege
  • Ensures external database access and interactive SQL queries respect security boundaries

Interfaces That Bypass Privilege Checking

Embedded SQL:

  • Deliberately does not perform privilege checking
  • Assumes applications will implement their own security controls
  • Typically used within ObjectScript routines where application logic controls access

Direct class query invocation:

  • Without %SQL.Statement objects is considered application access
  • Bypasses SQL privilege checks
  • Allows developers to build custom security models

SQL vs System Privileges

The relationship between SQL privileges and system-level privileges is asymmetric:

  • SQL privilege grants system access: Holding SQL privilege implicitly grants related system privileges required for the SQL action
  • System privileges do NOT grant SQL access: System-level privileges do not automatically imply table-level or object-level SQL privileges
  • Granularity: SQL protections are more granular, allowing privileges for specific tables, views, columns, and stored procedures

Roles:

  • Serve as bridge between both security models
  • Single role can include both SQL and system-level privileges
  • Shared between both security subsystems

SQLSecurity System Setting

Enforcement boundary can be controlled system-wide:

  • Method: `$SYSTEM.SQL.Util.SetOption("SQLSecurity")`
  • Value 0: Disables SQL Security for new processes (suppresses privilege-based table/view security)
  • Value 1 (default): Privilege enforcement enabled (recommended for production)

Documentation References

3. Enables and understands the impact of SQL auditing

Key Points

  • Three audit event types: EmbeddedStatement, DynamicStatement, and XDBCStatement (ODBC/JDBC)
  • Two-step enablement: Enable system audit event AND add code directive or use auditable interface
  • Embedded SQL auditing: Requires %System/%SQL/EmbeddedStatement event enabled AND #sqlcompile audit directive in code
  • Dynamic SQL auditing: Automatic when %System/%SQL/DynamicStatement event enabled for all %SQL.Statement executions
  • Audit data captured: SQL statement text, parameter values, user, timestamp, process ID, and statement type
  • Privilege failure auditing: %System/%SQL/PrivilegeFailure event records SQLCODE -99 errors when privilege checks fail

Detailed Notes

Overview

InterSystems IRIS provides comprehensive SQL auditing capabilities that record SQL statement execution for compliance, security monitoring, and troubleshooting purposes.

  • Mechanism: Operates through the system's Audit Database
  • Content: Records detailed information about SQL operations across different execution interfaces

Audit Event Types

SQL auditing supports three distinct event types:

  • %System/%SQL/EmbeddedStatement: Audits Embedded SQL statements within ObjectScript routines
  • %System/%SQL/DynamicStatement: Audits Dynamic SQL operations through %SQL.Statement objects
  • %System/%SQL/XDBCStatement: Audits SQL statements executed through ODBC and JDBC connections

Note: Each event type is disabled by default; must be enabled via Management Portal: System Administration > Security > Auditing > Configure System Events

Embedded SQL Auditing (Two-Step Enablement)

1. Enable system event: %System/%SQL/EmbeddedStatement 2. Add code directive: `#sqlcompile audit` preprocessor directive in routines containing Embedded SQL

  • Setting to ON causes Embedded SQL following it to generate audit records
  • Gives fine-grained control over which routines are audited
  • Avoids performance overhead on non-sensitive operations

Dynamic SQL Auditing

  • Simpler implementation: When %System/%SQL/DynamicStatement event is enabled, system automatically audits every %SQL.Statement execution
  • No code changes required: Applies to all Dynamic SQL operations system-wide
  • Best for: Monitoring external access or interactive queries requiring comprehensive coverage

Audit Record Content

The Audit Database stores:

  • Timestamp (local time)
  • Username
  • Process ID (PID)
  • Description specifying statement type (e.g., "SQL SELECT Statement")
  • Event Data (via Details link): Complete SQL statement and input argument/parameter values
  • Maximum Event Data length: 3,632,952 characters for Dynamic SQL (longer statements truncated)

Privilege Failure Auditing

  • Event: %System/%SQL/PrivilegeFailure
  • Records: Every SQLCODE -99 (Privilege Violation) error
  • Purpose: Detect unauthorized access attempts, identify permission configuration errors, maintain security audit trails
  • Status: Disabled by default; must be explicitly enabled

Performance Considerations

  • Embedded SQL auditing: Minimizes overhead with selective #sqlcompile directives
  • Dynamic SQL and XDBC auditing: May introduce measurable latency in high-transaction environments
  • Balance: Audit coverage requirements against performance considerations

Documentation References

Exam Preparation Summary

Critical Concepts to Master:

  1. GRANT/REVOKE Syntax: Understand the syntax for granting administrative privileges, object privileges, roles, and column-level privileges
  2. Privilege Types: Differentiate between administrative privileges (%CREATE_TABLE, %ALTER_TABLE) and object privileges (SELECT, INSERT, UPDATE, DELETE, EXECUTE)
  3. Privilege Enforcement Scope: Know which interfaces enforce SQL privileges (ODBC, JDBC, Dynamic SQL, SQL Shell) versus which bypass them (Embedded SQL, direct class queries)
  4. WITH GRANT OPTION: Understand cascading privilege grants and the CASCADE/RESTRICT keywords for REVOKE
  5. Audit Event Types: Distinguish between EmbeddedStatement, DynamicStatement, XDBCStatement, and PrivilegeFailure audit events
  6. Audit Enablement: Know the two-step process for Embedded SQL auditing versus automatic Dynamic SQL auditing
  7. Column-level vs Table-level: Understand when to use column-level privileges for granular access control

Common Exam Scenarios:

  • Granting SELECT privilege on a specific table to a user
  • Granting all privileges on a schema to a role
  • Revoking privileges with CASCADE to remove delegated grants
  • Identifying whether privilege checking occurs for a given SQL interface
  • Enabling SQL auditing for Dynamic SQL statements
  • Troubleshooting SQLCODE -99 privilege violation errors
  • Understanding the difference between SQL privileges and system-level resources

Hands-On Practice Recommendations:

  • Practice GRANT and REVOKE commands with different privilege types and scopes
  • Create roles and assign multiple privileges to test privilege inheritance
  • Test privilege enforcement by executing SQL through different interfaces (ODBC vs Embedded SQL)
  • Enable SQL audit events and review audit records in the Management Portal
  • Use %CHECKPRIV to verify privilege assignments
  • Grant column-level privileges and test INSERT behavior on restricted columns
  • Experiment with WITH GRANT OPTION and CASCADE revocation

Report an Issue