Skip to main content

​
Overview

The PermissionService is responsible for ensuring that the connected Oracle database user has all necessary permissions to run OmniView successfully. This service:
  • Deploys a temporary permission-checking package to Oracle
  • Verifies required database privileges
  • Validates Oracle Advanced Queuing (AQ) permissions
  • Stores permission verification results in BoltDB
  • Cleans up by dropping the temporary package after checks complete
The service follows the hexagonal architecture pattern and runs only once on the first application startup, caching results for subsequent runs.

​
Service Structure

type PermissionService struct {
    db   ports.DatabaseRepository
    bolt ports.ConfigRepository
}
​
db
ports.DatabaseRepository
Database repository for Oracle operations including package deployment and permission queries
​
bolt
ports.ConfigRepository
Configuration repository for BoltDB operations to persist permission check results

​
Permission Entities

​
PermissionStatus

type PermissionStatus struct {
    Schema              string
    CreateSequence      bool
    CreateProcedure     bool
    CreateType          bool
    AQAdministratorRole bool
    AQUserRole          bool
    DBMSAQADMExecute    bool
    DBMSAQExecute       bool
    AllValid            bool
}
​
Schema
string
Oracle schema/username being verified
​
CreateSequence
bool
Permission to create sequences in the database
​
CreateProcedure
bool
Permission to create stored procedures and packages
​
CreateType
bool
Permission to create custom Oracle types
​
AQAdministratorRole
bool
Has the AQ_ADMINISTRATOR_ROLE granted (required for queue management)
​
AQUserRole
bool
Has the AQ_USER_ROLE granted (required for queue operations)
​
DBMSAQADMExecute
bool
Has EXECUTE permission on DBMS_AQADM package (queue administration)
​
DBMSAQExecute
bool
Has EXECUTE permission on DBMS_AQ package (queue operations)
​
AllValid
bool
True only if all of the above permissions are granted

​
DatabasePermissions

type DatabasePermissions struct {
    Permissions PermissionStatus
}
Wrapper entity that contains the permission status and is persisted to BoltDB.

​
Constructor

​
NewPermissionService

Creates a new instance of PermissionService with injected dependencies. 
func NewPermissionService(
    db ports.DatabaseRepository,
    bolt ports.ConfigRepository,
) *PermissionService
​
db
ports.DatabaseRepository
required
Database repository interface for Oracle database operations
​
bolt
ports.ConfigRepository
required
Configuration repository interface for BoltDB operations
​
*PermissionService
*PermissionService
Returns a pointer to the newly created PermissionService instance
Example Usage: 
// From cmd/omniview/main.go:60
permissionService := permissions.NewPermissionService(dbAdapter, boltAdapter)

​
Methods

​
DeployAndCheck

The primary method that orchestrates the entire permission verification process. This method:
  1. Checks if this is the first application run
  2. If first run:
    • Deploys the TXEVENTQ_PERMISSION_CHECK_API package
    • Executes permission checks
    • Saves results to BoltDB
    • Marks first run as complete
    • Drops the temporary package
  3. If not first run: Returns immediately (cached results are used)
func (ps *PermissionService) DeployAndCheck(schema string) (bool, error)
​
schema
string
required
Oracle schema/username to verify permissions for (typically the connected user)
​
success
bool
Returns true if all permissions are valid or if checks were previously completed
​
error
error
Returns error with detailed permission report if any checks fail, or if deployment/cleanup fails
Example Usage: 
// From cmd/omniview/main.go:67-69
if _, err := permissionService.DeployAndCheck(appConfig.Username); err != nil {
    log.Fatalf("failed to run permission checks: %v", err)
}
Permission Check Output: When permissions are verified, a formatted table is displayed: 
Permission Status Details:
┌───────────────────────────┬─────────┐
│ Permission                │ Status  │
├───────────────────────────┼─────────┤
│ Create Sequence           │ [OK]    │
│ Create Procedure          │ [OK]    │
│ Create Type               │ [OK]    │
│ AQ Administrator Role     │ [OK]    │
│ AQ User Role              │ [OK]    │
│ Execute DBMS AQADM        │ [OK]    │
│ Execute DBMS AQ           │ [OK]    │
└───────────────────────────┴─────────┘

​
Permission Package: Permission_Checks.sql

The service deploys a temporary Oracle package named TXEVENTQ_PERMISSION_CHECK_API from the embedded asset file Permission_Checks.sql. This package contains:

​
Get_Permission_Report Function

TXEVENTQ_PERMISSION_CHECK_API.Get_Permission_Report(:schema)
This function:
  • Queries Oracle system views (USER_SYS_PRIVS, USER_ROLE_PRIVS, USER_TAB_PRIVS)
  • Checks for each required permission
  • Returns a JSON object with permission status
Query Used: 
// From internal/service/permissions/permissions_service.go:90
query := `SELECT TXEVENTQ_PERMISSION_CHECK_API.Get_Permission_Report(:schema) FROM DUAL`
results, err := ps.db.FetchWithParams(query, map[string]interface{}{
    "schema": schema,
})

​
Deployment Process

​
1. Package Existence Check

exists, err := ps.db.PackageExists("TXEVENTQ_PERMISSION_CHECK_API")
Checks if the permission package is already deployed to avoid redundant deployments.

​
2. Package Deployment

permissionChecksSQLPackage, err := assets.GetSQLFile("Permission_Checks.sql")
if err := ps.db.DeployFile(string(permissionChecksSQLPackage)); err != nil {
    return fmt.Errorf("failed to deploy permission checks package: %w", err)
}
Reads the embedded SQL file and deploys it to Oracle.

​
3. Permission Verification

Executes the Get_Permission_Report function and unmarshals the JSON result into the PermissionStatus struct.

​
4. Result Storage

if err := ps.bolt.SaveClientConfig(*status); err != nil {
    return err
}
Persists the permission check results to BoltDB for future reference.

​
5. Package Cleanup

dropQuery := `BEGIN
    EXECUTE IMMEDIATE 'DROP PACKAGE TXEVENTQ_PERMISSION_CHECK_API';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -4043 THEN
            RAISE;
        END IF;
END;`
Drops the temporary package to:
  • Maintain a clean database state
  • Improve security by removing diagnostic code
  • Prevent namespace pollution
The package is dropped even if it doesn’t exist (SQLCODE -4043 is ignored) to ensure idempotency.

​
Error Handling

​
Permission Failures

If AllValid is false, the service returns a detailed error with a formatted table showing which permissions are missing: 
if !perStatus.Permissions.AllValid {
    return false, fmt.Errorf(
        "permission checks failed for schema %s: %+v",
        schema,
        permStructTable,
    )
}
This causes the application to exit with a fatal error during startup, ensuring OmniView doesn’t run with insufficient permissions.

​
Deployment Errors

Errors during package deployment or dropping return immediately:
  • Package deployment failure: Returns error to caller
  • Package drop failure: Returns error (prevents cleanup)
  • BoltDB save failure: Returns error (prevents marking as complete)

​
First Run Detection

The service uses a “first run” flag stored in BoltDB to determine if permission checks need to be executed: 
isFirstRun, err := ps.bolt.IsApplicationFirstRun()
Run Cycle Status: 
type RunCycleStatus struct {
    IsFirstRun bool
}
After successful permission verification: 
if err := ps.bolt.SetFirstRunCycleStatus(
    domain.RunCycleStatus{IsFirstRun: false},
); err != nil {
    return false, err
}
This ensures:
  • Permission checks only run once
  • Faster subsequent startups
  • Reduced database load
  • Cached results available for auditing

​
Required Oracle Permissions

For OmniView to function correctly, the Oracle user must have:
  • CREATE SEQUENCE: Required for generating unique IDs
  • CREATE PROCEDURE: Required for deploying OmniView packages
  • CREATE TYPE: Required for defining queue payload types
  • AQ_ADMINISTRATOR_ROLE: Required for:
    • Creating queues and queue tables
    • Managing subscriptions
    • Administering AQ objects
  • AQ_USER_ROLE: Required for:
    • Enqueuing messages
    • Dequeuing messages
    • Basic queue operations
  • EXECUTE on DBMS_AQADM: Required for:
    • Queue administration procedures
    • Subscription management
    • Queue table operations
  • EXECUTE on DBMS_AQ: Required for:
    • Enqueue operations
    • Dequeue operations
    • Message handling

​
Granting Permissions

If permission checks fail, a DBA must grant the required permissions: 
-- Object creation permissions
GRANT CREATE SEQUENCE TO your_schema;
GRANT CREATE PROCEDURE TO your_schema;
GRANT CREATE TYPE TO your_schema;

-- AQ roles
GRANT AQ_ADMINISTRATOR_ROLE TO your_schema;
GRANT AQ_USER_ROLE TO your_schema;

-- DBMS package execution
GRANT EXECUTE ON DBMS_AQADM TO your_schema;
GRANT EXECUTE ON DBMS_AQ TO your_schema;

​
Architecture Pattern

PermissionService follows the hexagonal architecture (ports and adapters) pattern:
  1. Core Service (internal/service/permissions): Contains permission verification logic
  2. Ports (internal/core/ports): Define repository interfaces
  3. Adapters:
    • internal/adapter/storage/oracle: Implements DatabaseRepository for Oracle
    • internal/adapter/storage/boltdb: Implements ConfigRepository for BoltDB
This design enables:
  • Separation of concerns
  • Testability with mock repositories
  • Database-agnostic business logic
  • Easy addition of permission checks

​
Usage Flow

​
Security Considerations

  1. Temporary Package: The permission check package is deployed, used, and immediately dropped to minimize security exposure
  2. Read-Only Checks: The package only reads from system views, never modifies data
  3. Cached Results: Permission status is cached to prevent repeated diagnostic queries
  4. Error Details: Permission failures provide detailed output to help DBAs grant correct permissions

Build docs developers (and LLMs) love

Get started for freeTalk to us