In Querona, users can easily manage access rights for users and roles on various levels of granularity. This chapter will shortly describe permissions model and present few usage scenarios.
In general Querona’s permission model is based, as far as possible and practical, on SQL Server’s model.
Before going into details let’s introduce few important terms that will be used in this chapter:
Securables are the resources (like tables, views, connections etc.) to which the Querona authorization system regulates access. Some securables can be nested, for example, tables are nested within schemas.
Principal is an entity that can request some Querona resource, either user or role.
Permission is a characteristic of a usage of some securable that can be granted or denied for a principal. Set of possible permissions is defined for each securable. There are also global permissions not related to any specific securable.
Effective Permission is a result of permission evaluation taking into account all permissions set at a global level and for all relevant securables. To sum up, lower level permissions take precedence over higher level permissions. For example, if a principal has been granted global permission to see any view, but has been denied access to one particular view, he cannot see that view.
Grantee is a principal that was granted or denied with some permission
Grantor is a principal that granted or denied some permission to some principal
Access right is the mode of access that given principal will get to given permission. Each permission can be allowed, denied or granted.
Allow access right gives the principal the privilege to access described resource. Deny is the reverse of Allow, denies the permission of accessing described resource. Grant means that principal will be able to configure access rights for this permission for principals. Not all combinations of access rights for a permission are allowed:
Grant cannot be assigned with Deny
Grant cannot be assigned without Allow
Allow cannot be assigned with Deny
If there are any access rights configured for given permission, you can revoke them. Revoking means that any previously given access right(including Deny) is removed, thus going back to default state.
Here is a summary of permissions found in Querona:
Alter securable - confers the ability to create, alter or drop objects of particular securable
Connect - confers the ability to connect to Querona. By itself, it does not confer any other abilities like viewing or querying.
Control securable - confers the ability to execute all actions on the securable and in nested scopes
Create securable - confers the ability to create objects of particular_securable
Delete/Insert/Select/Update - confers the ability to execute Delete/Insert/Select/Update statements accordingly
Manage any access rights - confers the ability to configure permissions for any user or role
Trigger any job - confers the ability to run any job, by itself does not confer the ability to create, alter or remove jobs
View securable - confers the ability to access metadata of particular securable
View server state - confers the ability to see server related information such as active connections, running jobs, and Spark processes that Querona manages
Global access rights¶
This section can be found under:
Global access rights are those assigned to global permissions, they relate to whole categories of objects and actions. They do not relate to any particular object (like database or connection). For example, if one has Allow permission on Alter any view privilege it means that permission is assigned for all current and future views.
However, lower level privileges (on databases, schemas etc.) override global ones. Continuing the example: if one has Allow permission on Alter any view privilege, one still can have Deny on some particular view and one would not be able to view it.
On the initial screen, you can set a list of users and roles that have any explicit permission for any global access right. Those that do not yet have such permission are not on the list. By default, newly created users and roles, do not have any global access rights configured.
By clicking on any of the users or roles on the list, you will be redirected to next screen on which you can review and edit access rights:
To revoke permissions for single privilege, just uncheck Allow, Deny and Grant for that privilege. To revoke all permissions, you may click on REVOKE ALL button, which does that automatically for all permissions.
To configure permissions for a user or role that do not yet have any permissions configured, on the initial screen click SELECT USER or SELECT ROLE:
After selecting a user or role you will be redirected to a previously described screen where you will be able to edit the permissions.
Object access rights¶
Apart from global access rights, there is a number of securables:
Tables and Views
Columns are scoped within Tables and Views, those in turn within Schemas, and finally, schemas are scoped within Databases.
Permissions for each securable can be configured in respective Access Rights panel.
To manage object’s access rights follow the instructions from Global Access Rights section. User interface and general rules of governing access rights are the same, it is only set of permissions that differ from securable to securable.