Users and roles

Like most DBMS-es Querona uses users and roles for access management. Permissions can be assigned on a per-user or per-role basis.

Any number of users can be assigned to a single role.

The following chapter describes the user, role and permission management.

Authentication

Querona authenticates users with Standard (SQL) authentication, Integrated Windows Authentication, and Microsoft Entra ID. Integrated Windows Authentication is available for both the web interface and the TDS endpoint (the SQL Server–compatible endpoint).

Repeated failures from the same origin are refused for a while - see Failed sign-in lockout. A login can be restricted to the networks it may sign in from - see Trusted networks. A standard login can sign in with a one-time code from an authenticator app in front of its password, and an administrator can require one - see Second factor. An account that has not signed in for a configured number of days can be disabled automatically - see Security policy settings. A disabled account, whether disabled by an administrator or for inactivity, is refused on every sign-in path: a SQL client signing in with a password or with Microsoft Entra ID sees error 18470, Login failed for user. Reason: The account is disabled.; Integrated Windows Authentication, the web interface and the REST API report the general login failure.

User management

This section can be found under ADMINISTER ‣ User management:

Querona comes with a predefined user accounts:

Account name	Description
admin	The default administrative account
spark	The default account used for reverse Spark connections (see: Managing Apache Spark)
BUILTIN\Administrators	Default Windows Administrators group mapping
system (hidden)	The default system account used by Querona

A new user can created using the Add user button:

The following table summarizes the fields:

Field name	Description
Login	The name of the account. If using Windows Authentication, the format should be “DOMAIN\account”.
Integrated authentication	Enabled Integrated Windows Authentication, disables password
Password	Account password when using SQL authentication
Confirm password	Retype password
First name	Optional: user’s first name
Last name	Optional: user’s last name
Description	Optional: free-text note about the account, up to 2500 characters
User type	Type of the account: Power User and End User - standard accounts, Spark reverse account - see: Managing Apache Spark, System - reserved for system account - do not use
Disabled	A disabled account is refused on every sign-in path; a SQL client signing in to it with a password, or with Microsoft Entra ID under its own name, sees error 18470, Login failed for user. Reason: The account is disabled. A disabled group account - BUILTIN\Administrators, a Windows group or an Entra group - admits none of its members

The next screen allows assigning the account to Roles. Every account must be assigned at least to public role.

An account can also be created from T-SQL over the SQL endpoint with CREATE LOGIN, which covers both password accounts and Windows principals.

Once defined, you can use the Access rights functionality to define the actual permissions for the given user.

The description is stored as the MS_Description extended property and can also be read and written over SQL - see Extended properties.

To set the password of an existing account, see Setting a user’s password. Users change their own from their profile, without needing a permission - see Changing your password.

The user list shows when each account last signed in, and the account details add the client of that sign-in (the protocol, and the program the client declared) and how many attempts failed since. An account that existed when the inactivity rule was installed shows a sign-in at that installation or upgrade moment until it next signs in; one created since shows nothing until it first does. For a SQL login the same facts are reported over SQL by LOGINPROPERTY; a Windows or Microsoft Entra ID login’s sign-ins are shown here and by the REST API only, since the function answers NULL for such a login, as it does on SQL Server. See Logon tracking settings for what is recorded and how to turn it off.

Over SQL, sys.sql_logins and LOGINPROPERTY show each login only the SQL logins it may see, as SQL Server does: itself, the login named admin, and every login when it holds ALTER ANY LOGIN or VIEW ANY DEFINITION. VIEW SERVER STATE does not widen this. The rule goes by the name: an installation that renamed its administrator at setup has no login everyone sees, and a login created later under the name admin is visible to everyone. An integration that lists logins as an account without one of those permissions sees at most two rows, itself and admin, and no longer sees the system and spark accounts under any permission. What the function reports about sign-ins - the last one with its client, and the attempts refused since - is narrower still: another login’s, admin’s included, is answered only to a caller holding one of those two permissions, so no account can watch when the administrator signs in. sys.server_principals is not narrowed and still lists every login. The Administration Portal and the REST API are unaffected: they do not read sys.sql_logins.

Role management

This section can be found under ADMINISTER ‣ Role management:

Querona comes with several predefined roles:

Role name	Description
datareader	Members of the datareader built-in server role can query any table in any database
dbcreator	Members of the dbcreator built-in server role can create new databases and connections
public	Default role assigned to all users, any rights granted to the public role are granted to all current and future users
securityadmin	Members of the securityadmin built-in server role manage logins and their properties
sysadmin	Members of the sysadmin built-in server role can perform any activity in the server.
viewer	Members of the viewer built-in server role can see any table in any database but cannot query or modify data

A new role can created using the Add role button: it takes the role name and an optional description of up to 2500 characters.

The subsequent screen allows adding any existing user to the newly created role.

A role’s description is stored as the MS_Description extended property and can also be read and written over SQL - see Extended properties. Built-in roles do not accept extended properties, so their descriptions cannot be changed.

Once defined, you can use the Access rights functionality to define the actual permissions for the given role.

Role membership can also be managed over SQL, on an ordinary TDS connection, with ALTER SERVER ROLE - useful when an external system drives the assignment.