Roles

DBCode’s Connection Roles feature provides granular control over database interactions, helping prevent accidental modifications and ensuring appropriate access levels based on your environment context (development, testing, or production).

Key Features

Connection Roles offer two primary security mechanisms:

1. Read-Only Mode: Restricts all connections to read-only operations, preventing any data modifications
2. Environment Roles: Configures statement permissions based on the connection’s intended use (Development, Testing, or Production)

Read-Only Mode

The read-only mode provides a simple safeguard against unintended data modifications.

Feature	Description
Availability	Depends on database driver support
Effect	Restricts connection to SELECT statements only
Override	Not possible without changing connection settings
Best for	Production data exploration, reporting connections

Enabling Read-Only Mode

1. Open the connection settings dialog
2. Find the Role section
3. Check the Read Only option if enabled
4. Save your connection

Note: Not all database drivers support read-only connections. If unsupported, this option will be disabled.

Environment Roles

Environment roles provide a more nuanced approach to connection permissions by defining what types of SQL statements are allowed based on the connection’s purpose.

Available Roles

Role	Typical Use Case	Default Permissions
Development	Local development work	Most permissive; allows all statement types
Testing	Integration/QA environments	Moderately restrictive; prevents schema changes
Production	Live production databases	Most restrictive; limits potentially dangerous operations

Statement Type Permissions

For each role, you can configure permissions for different SQL statement types using three permission levels:

Permission	Behavior
Allowed	Statements execute without confirmation
Ask First	Confirmation prompt appears before execution
Denied	Statements are blocked from execution

Configurable Statement Types

Statement Type	Description	Examples
SELECT	Data retrieval operations	SELECT * FROM table
INSERT	Data addition operations	INSERT INTO table VALUES (...)
UPDATE	Data modification operations	UPDATE table SET col = value
DELETE	Data removal operations	DELETE FROM table WHERE ...
TRUNCATE	Table clearing operations	TRUNCATE TABLE table
EXECUTE	Procedure/function calls	EXEC procedure, CALL function()
CREATE	Object creation operations	CREATE TABLE, CREATE VIEW
ALTER	Object modification operations	ALTER TABLE ADD COLUMN
DROP	Object removal operations	DROP TABLE, DROP DATABASE
OTHER	Other statement types	Database-specific operations

Default Permission Matrix

Statement Type	Development	Testing	Production
SELECT	Allowed	Allowed	Allowed
INSERT	Allowed	Allowed	Ask First
UPDATE	Allowed	Allowed	Ask First
DELETE	Allowed	Allowed	Ask First
TRUNCATE	Allowed	Allowed	Denied
EXECUTE	Allowed	Allowed	Ask First
CREATE	Allowed	Ask First	Denied
ALTER	Allowed	Ask First	Denied
DROP	Allowed	Ask First	Denied
OTHER	Allowed	Ask First	Ask First

Configuring Connection Roles

Setting the Connection Role

1. Open the connection settings
2. Navigate to the Roles section
3. Select the appropriate role (Development, Testing, or Production)
4. Click Save to update the connection

Customizing Statement Permissions

1. Open Settings in VS Code
2. Navigate to Extensions > DBCode > Connection Roles
3. Find the role you want to customize (Development, Testing, or Production)
4. For each statement type, select the desired permission level from the dropdown:
   • Allow: Statements execute without confirmation
   • Ask: Confirmation prompt appears before execution
   • Deny: Statements are blocked from execution
5. Changes are automatically saved and applied to all connections using that role

Use Cases

Safeguarding Production Databases

• Select the Production role for live database connections
• Critical operations require confirmation, preventing accidental data loss
• Destructive operations like DROP and TRUNCATE are blocked entirely

Development Workflow

• Use Development role for local database instances
• Allow unrestricted operations during active development
• Switch to Testing role when working with shared QA environments

Teaching Environments

• Configure custom permissions for student database access
• Allow SELECT but require confirmation for data modifications
• Prevent schema changes entirely

Benefits of Connection Roles

• Mistake Prevention: Adds confirmation steps for potentially destructive operations
• Customizable Security: Tailor permissions to your team’s needs and risk tolerance
• Confidence: Work with production data knowing safeguards are in place

Connection Roles provide an additional layer of security and awareness when working with important databases, helping prevent costly mistakes while maintaining productivity.