Security

Security & Permissions

SQLServerNIO provides complete programmatic control over Microsoft SQL Server's security hierarchy, including Server-level and Database-level principals, as well as granular permissions.

---

1. Database Security

The SQLServerSecurityClient allows you to manage users, database roles, and object-level permissions within a specific database.

let dbSec = SQLServerSecurityClient(client: client)

// 1. Create a database user
try await dbSec.createUser(name: "app_user", options: UserOptions(defaultSchema: "dbo"))

// 2. Create a custom role and add the user
try await dbSec.createRole(name: "data_analysts")
try await dbSec.addUserToRole(user: "app_user", role: "data_analysts")

// 3. Grant table permissions to the role
try await dbSec.grantPermission(permission: .select, on: "Financials", to: "data_analysts")

// List all permissions for a user
let permissions = try await dbSec.listPermissions(principal: "app_user")
for perm in permissions {
    print("\(perm.stateDescription) \(perm.permissionName) ON \(perm.objectName ?? "DATABASE")")
}

Granular Securables

You can GRANT/REVOKE/DENY permissions natively using robust Securable enums, allowing you to target entire schemas, single tables, or even specific columns.

// GRANT schema-level permission
try await dbSec.grant(permission: .alterAnySchema, on: .schema("dbo"), to: "app_user")

// GRANT column-level SELECT on an object
let tableId = ObjectIdentifier(schema: "dbo", name: "Employees", kind: .table)
try await dbSec.grant(
    permission: .select, 
    on: .column(tableId, ["id", "public_name"]), 
    to: "app_user"
)

Application Roles

// Create an application role (often used to secure mid-tier applications)
try await dbSec.createApplicationRole(name: "AppRole", password: "StrongPassword!", defaultSchema: "dbo")

---

2. Server Security

The SQLServerServerSecurityClient interacts with the master database concepts like Server Logins, Server Roles, and Server Credentials.

let srvSec = SQLServerServerSecurityClient(client: client)

// 1. Create a SQL Login
try await srvSec.createSqlLogin(
    name: "external_service",
    password: "<your_password>",
    options: .init(defaultDatabase: "MyDb", checkPolicy: true, checkExpiration: false)
)

// 2. Assign to a Server Role
try await srvSec.addMemberToServerRole(role: "securityadmin", principal: "external_service")

// 3. Grant a Server-wide permission
try await srvSec.grant(permission: .viewServerState, to: "external_service")

Advanced Logins and Credentials

// Logins via external Keys and Certificates
try await srvSec.createCertificateLogin(name: "cert_login", certificateName: "MyCert")
try await srvSec.createAsymmetricKeyLogin(name: "asym_login", asymmetricKeyName: "MyKey")

// Windows/Azure AD Logins
try await srvSec.createExternalLogin(name: "aad_login")

// Server Credentials (often used by Agent Jobs or external scripts)
try await srvSec.createCredential(
    name: "s3_backup_cred", 
    identity: "DOMAIN\\svc_user", 
    secret: "secret_token"
)