Security Policy

Table of Contents


Overview
Security in Katzebase is enforced through roles and account membership of those roles. Roles come in two varieties: administrators and non-administrators.

paperclip Note that when granting/denying/revoking any permission, they can be individually applied, such as: GRANT READ, and GRAND WRITE, GRANT MANAGE, or you can use the special ALL permission to grant/deny/revoke all applicable permissions. An example of this can be found in REVOKE.

Accounts and Roles
Administrators
Administrators have unrestricted access to the database, its schema, data and all features and functions. In addition to implied schema access, administrators can create accounts, roles, manage memberships, and access all system functions, many of which are restricted for non-administrators, as well as manage configuration, cache, threading, etc. If any account is added to a role which is marked as IsAdminstrator, then that account is granted administrators rights.

round-pushpin Accounts, roles and memberships can be viewed by querying the master database.
SELECT * FROM Master:Account
SELECT * FROM Master:Role
SELECT * FROM Master:Membership

Non-Administrators

Schema Policy Rollup.
Schema permissions are applied from the root down using recursive policy and can be overridden at any level, either with a role policy for a specific schema or by another recursive policy.

When determining the permissions an account has on any given schema, first the exact schema is checked for role policy. If a policy is found, that policy is used for the given permission type. If there are any remaining undefined permissions, then the schemas parent is checked for recursive policies. This continues up the tree until either the root is reached or each permission type has a matching rule.
In this way, you could deny any given permission type from one schema level recursively then then override one or more denial, with a grant, at a lower level.

paperclip Note that all of these checks are omitted for accounts which are a memeber of any administrators role.

Create Account
round-pushpin To create an account, execute CREATE ACCOUNT.
CREATE ACCOUNT JohnDoe WITH (Password = '123abc!')

Create Role
round-pushpin To create a role, execute CREATE ROLE:
CREATE ROLE Salespersons

round-pushpin To create a role for administrators, execute CREATE ROLE:
CREATE ROLE Salespersons WITH (IsAdministrator = true)

Add to Role
round-pushpin To add an account to a role, execute ALTER ROLE: 
ALTER ROLE Salespersons ADD JohnDoe

Remove from Role
round-pushpin To remove an account from a role, execute ALTER ROLE: 
ALTER ROLE Salespersons REMOVE JohnDoe


Schema Security Policy
Schema security is enforced by granting or denying permissions to roles.

Read Permission
Read permission on a schema allows an account to select from a schema and optionally the schemas under it.
round-pushpin To allow a user to read from the Sales:Customers schema, execute GRANT READ.
GRANT READ ON Sales:Customers to Salespersons

round-pushpin To allow a user to read from the Sales:Customers schema and all schemas beneath it, execute GRANT READ with recursion.
GRANT READ ON Sales:Customers to Salespersons WITH (Recursive = True)

round-pushpin To disallow a user from reading from the Sales:Customers schema, execute DENY READ.
DENY READ ON Sales:Customers to Salespersons

round-pushpin To disallow a user from reading from the Sales:Customers schema nd all schemas beneath it, execute DENY READ. with recursion.
DENY READ ON Sales:Customers to Salespersons WITH (Recursive = True)

Write Permission
Write permission on a schema allows an account to insert/update and delete rows from a schema and optionally the schemas under it.
GRANT WRITE and DENY WRITE follows the same convention as allowing/disallowing read permissions.
round-pushpin To allow a user to write to the Sales:Customers schema and all schemas beneath it, execute GRANT WRITE with recursion.
GRANT WRITE ON Sales:Customers to Salespersons WITH (Recursive = True)

Manage Permission
Manage permission allows an account to perform various management tasks on a schema and optionally the schemas beneath it.
These permissions include the ability to create/drop/rebuild indexes, analyze indexes, create sub-schemas, grant/deny permission for other roles to the schema.
Much like write, MANAGE follows the same convention as allowing/disallowing read permissions.
round-pushpin To allow a user to manage the Sales:Customers schema and all schemas beneath it, execute GRANT MANAGE with recursion.
GRANT MANAGE ON Sales:Customers to Salespersons WITH (Recursive = True)

Revoke Permission
Revoking a permission does not deny that permission, but it removes the explicitly set policy on a schema. For instance, the role 'Salespersons' has been granted and/or denied various permissions on the `Sales:Customers` schema, all of these permissions can be removed by executing REVOKE.
round-pushpin To revoke all explicit permissions for Salespersons from the Sales:Customers schema, execute REVOKE.
REVOKE ALL ON Sales:Customers FROM Salespersons

round-pushpin To revoke explicit MANAGE permissions for Salespersons from the Sales:Customers schema, execute REVOKE.
REVOKE MANAGE ON Sales:Customers FROM Salespersons


Management Functions
ShowMySchemaPolicy
Users can check their permissions on a given schema by executing ShowMySchemaPolicy and passing the schema name. This function will return the schema name along with each permission and which role and recursive schema from which the policy was derived. 
exec ShowMySchemaPolicy('Sales:Customers')

RefreshMyRoles
When a user logs into their account, their roles are cached. If a users role membership changes, then they can refresh their roles by executing RefreshMyRoles without logging out and back in.
exec RefreshMyRoles()


See Also

Related