Skip to main content

Users, Roles and Databases

The MSSQL Provider gives every Instance one administrator, sa. For the applications and people that use it, install the open-source User Management plugin for OpenEverest. With it you create SQL Server logins, grant them roles and create databases, so teams stop sharing sa:

  • from the Access tab of the instance page in the OpenEverest UI, or
  • as Kubernetes resources, InstanceUser and InstanceDatabase, applied with kubectl or from Git.

Either way the plugin keeps SQL Server in sync with the resources, stores each user's password in a Secret that applications can mount, and drops the login when the resource is deleted.

Requirements​

User Management pluginv0.2.4 or later
MSSQL Providerv0.1.18 or later for Availability Groups (it bundles MSSQL Operator v0.1.19, which copies logins to every replica and coordinates database changes). Standalone Instances work with earlier versions.
TopologyavailabilityGroup, or standalone with 1 replica

A standalone Instance with more than one replica runs independent servers, and a contained Availability Group keeps its own logins. Users of those stay Pending with the reason.

Install the plugin​

Install the plugin into the namespace where OpenEverest runs:

helm install plugin-user-management \
oci://ghcr.io/openeverest/charts/plugin-user-management \
--version 0.2.4 \
--namespace everest-system

The Access tab then appears on the instance page of SQL Server Instances, with a Users and a Databases section. See the plugin's README for upgrades and chart values.

Users​

Each InstanceUser is a SQL Server login:

apiVersion: plugin-user-management.plugins.openeverest.io/v1alpha1
kind: InstanceUser
metadata:
name: reporting
namespace: team-a # the Instance's namespace
spec:
instanceRef:
name: orders-db # the Instance; immutable
username: reporting # the login name; immutable
roles:
- role: read
database: orders # an InstanceDatabase of the same Instance
- role: monitor # server-wide, takes no database
# passwordSecretRef: # optional: bring your own password
# name: reporting-password
# key: password
  • Without passwordSecretRef, the plugin generates a password into the Secret <name>-credentials, with the keys username, password and uri (sqlserver://<user>:<password>@<host>:1433). Changing password in the Secret changes it in SQL Server.
  • Logins are SQL logins with CHECK_POLICY = ON, so SQL Server's password policy applies to your own passwords too. Generated passwords meet it.
  • status.phase is Ready once the login exists with exactly the declared roles. While SQL Server doesn't match yet, it's Pending with the reason.
  • Deleting an InstanceUser ends the login's sessions, drops its database users and drops the login. A user that owns a database stays Deleting until the database is dropped or gets another owner.
  • sa, the ##...## principals, Windows (NT ...) and certificate logins can't be managed. Usernames are at most 128 characters, without \.

Roles​

RoleScopeIn SQL Server
readone databasea database user in db_datareader
readWriteone databasea database user in db_datareader and db_datawriter
dbOwnerone databasea database user in db_owner
monitorserverVIEW SERVER STATE and VIEW ANY DEFINITION

Database roles are granted on an InstanceDatabase of the same Instance. sysadmin and the other fixed server roles can't be granted: use sa for administration.

The plugin restores these role memberships and permissions on every change, and every 5 minutes, so if they are changed by hand in T-SQL they are put back. Permissions granted outside these roles aren't touched.

Databases​

Each InstanceDatabase is a database owned by one of the Instance's users:

apiVersion: plugin-user-management.plugins.openeverest.io/v1alpha1
kind: InstanceDatabase
metadata:
name: orders
namespace: team-a
spec:
instanceRef:
name: orders-db # immutable
name: orders # the database name; immutable
owner:
instanceUserRef:
name: orders-owner # an InstanceUser of the same Instance
deletionPolicy: Retain # Retain (default) | Delete
  • The plugin runs CREATE DATABASE and makes the owner's login the database owner (dbo), which holds every permission in it. The owner needs no role.
  • In the UI, Create database can create the owner too: pick New user… as the owner and the plugin creates the user, with no roles, and then the database, and shows the new user's credentials.
  • The plugin never takes over a database it didn't create: a database that already exists with that name makes the InstanceDatabase Failed.
  • Only the owner and the users granted a role on the database can use it. The Connect setting of other engines doesn't apply to SQL Server and isn't shown.
  • Deleting an InstanceDatabase with Retain stops managing the database and keeps it with its data. With Delete the plugin ends idle sessions, waits for running requests to finish, and drops the database.

Availability Groups​

On an Availability Group, the plugin connects to the primary through <name>-primary, and the operator does the rest:

  • Logins are copied to every secondary with the same SID and password, so applications log in to the new primary after a failover without any change. See Logins, server roles and permissions.
  • Databases the plugin creates are added to the Availability Group and seeded to the secondaries. The InstanceDatabase is Pending until the database is in the group. Database users and role memberships move with the database.
  • Dropping a database removes it from the group and drops it under the operator's lock, so the operator doesn't add it back in between, and the operator drops the copies left on the secondaries. See Adding and removing databases.

Example​

The owner of an orders database, and a reporting user that reads it and monitors the server:

apiVersion: plugin-user-management.plugins.openeverest.io/v1alpha1
kind: InstanceUser
metadata:
name: orders-owner
namespace: team-a
spec:
instanceRef:
name: orders-db
username: orders_owner
---
apiVersion: plugin-user-management.plugins.openeverest.io/v1alpha1
kind: InstanceDatabase
metadata:
name: orders
namespace: team-a
spec:
instanceRef:
name: orders-db
name: orders
owner:
instanceUserRef:
name: orders-owner
---
apiVersion: plugin-user-management.plugins.openeverest.io/v1alpha1
kind: InstanceUser
metadata:
name: reporting
namespace: team-a
spec:
instanceRef:
name: orders-db
username: reporting
roles:
- role: read
database: orders
- role: monitor

Connect as a user with the credentials from its Secret:

kubectl -n team-a get secret reporting-credentials -o jsonpath='{.data.password}' | base64 -d

See Connecting for the Services to use.