Chapter 8. Access Control

This chapter is about controlling access to database objects such as tables and routines. Other topics related to security include user authentication, password complexity and secure connections. These topics are covered in the System Management chapter and the HyperSQL Network Listeners (Servers) chapter.

Apart from schemas and their object, each HyperSQL catalog has USER and ROLE objects. These objects are collectively called authorizations. Each AUTHORIZATION has some access rights on some of the schemas and the database objects such as tables and routines contained in those schemas. Only USER authorizations can be used to connect to the database and start a session.

Authorization names are stored in the database in case-normal form. When a user is created with the CREATE USER statement, if the user name is enclosed in double quotes, the exact name is used as the case-normal form. But if it is not enclosed in double quotes, the name is converted to uppercase and this uppercase version is stored in the database as the case-normal form. When connecting to a database via JDBC, the user name and password must match the case of the stored form.

Each database has at least one admin user. When the first connection to a non-existent database is made, the admin user is created with the the user name for the connection. The user name SA is suggested in the documentation but you can use any name.

In general, ROLE and USER objects simply control access to schema objects. There is the built-in DBA role that allows full access to all possible operations on the database, including the creation of USER and ROLE objects and schemas. There are other built-in roles that allow some special operations on the database as a whole. Admin users have the DBA role.

A ROLE has a name, a collection of zero or more other roles, plus some privileges (access rights). A USER has a name and a password. It also has a collection of zero or more roles plus some privileges.

USER objects existed in SQL-92, but ROLE objects were introduced in SQL:1999. The co-existence of ROLE and USER objects results in complexity. With the addition of ROLE objects, there is no rationale, other than legacy support, for granting privileges to USER objects directly. It is better to create roles and grant privileges to them, then grant the roles to USER objects.

The Standard effectively defines a special ROLE, named PUBLIC. All authorizations have the PUBLIC role, which cannot be removed from them. Therefore, any access right assigned to the PUBLIC role applies to all authorizations in the database. For many simple databases, it is adequate to create one or more non-admin users, then assign access rights to the tables and sequences to the PUBLIC role.

The PUBLIC role is separate from the default PUBLIC schema. Like any user-created schema, the contents of the PUBLIC schema are not visible to non-admin users unless access is granted by the DBA role.

Access to INFORMATION_SCHEMA views is automatically granted to PUBLIC; therefore these views are accessible to all. However, the contents of each view depend on the ROLE or USER (AUTHORIZATION) that is in force while accessing the view. This means a user cannot even see the existence of tables and other objects when it has no access rights on those objects.

Each schema has a single AUTHORIZATION. This is commonly known as the owner of the schema. All the objects in the schema inherit the schema owner. The schema owner can add objects to the schema, drop them or alter them. By default, the objects in a schema can only be accessed by the schema owner. The schema owner can grant access rights on the objects to other users or roles.

authorization identifier

authorization identifier

<authorization identifier> ::= <role name> | <user name>

Authorization identifiers share the same name-space within the database. The same name cannot be used for a USER and a ROLE.

 SELECT * FROM INFORMATION_SCHEMA.SYSTEM_USERS
 SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

 CREATE SCHEMA mySchema AUTHORIZATION aUserOrRole

CREATE USER generalUser PASSWORD 'aPassword'

-- the objects are created one by one in the PUBLIC schema 
SET SCHEMA PUBLIC
CREATE TABLE aTable ...
CREATE TABLE anotherTable ...
...
CREATE SEQUENCE aSequence ...
CREATE PROCECURE aProc ...
'''

-- access rights are granted to PUBLIC, which includes the generalUser
GRANT ALL ON ALL TABLES IN SCHEMA public TO PUBLIC
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO PUBLIC
GRANT ALL ON ALL ROUTINES IN SCHEMA public TO PUBLIC

-- two users are created first
CREATE USER generalUser PASSWORD 'aPassword'
CREATE USER auditUser PASSWORD 'anotherPassword'

-- the schema and its objects are created with a single compound statement 
CREATE SCHEMA mySchema AUTHORIZATION DBA
    CREATE TABLE aTable ...
    CREATE TABLE anotherTable ...
    ...
    CREATE SEQUENCE aSequence ...
    CREATE PROCECURE aProc ...
    ''';

-- different access rights are granted to the users
GRANT ALL ON ALL TABLES IN SCHEMA mySchema TO generalUser
GRANT USAGE ON ALL SEQUENCES IN SCHEMA mySchema TO generalUser
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA mySchema TO generalUser
GRANT SELECT ON ALL TABLES IN SCHEMA mySchema TO auditUser;

CREATE TABLE info(id INT PRIMARY KEY, information VARCHAR(100), region VARCHAR(32) NOT NULL, expires DATE NOT NULL)
CREATE ROLE eu_admin
CREATE ROLE eu_user
// the eu_admin can insert, update and delete all rows 
GRANT ALL PRIVILEGES ON TABLE info TO eu_admin
// the eu_user cannot delete any rows
// the eu_user cannot see the contents of the expires columns
GRANT SELECT(id, information, region) ON TABLE info TO eu_user
// the eu_user can only update the contents of the information column
GRANT UPDATE(information) ON TABLE info TO eu_user
GRANT eu_admin TO peter, wendy
GRANT eu_user TO emma, john

CREATE TABLE info(id INT PRIMARY KEY, information VARCHAR(100), region VARCHAR(32) NOT NULL, expires DATE NOT NULL)
-- there is also a foreign key constraint on the REGION column to reference a list of valid region names.
INSERT INTO info VALUES 2, 'inserted data for EU current', 'European Union', CURRENT_DATE + 1 DAY
INSERT INTO info VALUES 3, 'inserted data for SA current', 'South America', CURRENT_DATE + 1 DAY
INSERT INTO info VALUES 4, 'inserted data for EU expired', 'European Union', CURRENT_DATE - 1 DAY
CREATE ROLE eu_admin
CREATE ROLE eu_user
// the eu_admin cannot see the rows for regions other than the EU
GRANT SELECT FILTER (WHERE region = 'European Union') ON TABLE info TO eu_admin
// the eu_user cannot see the rows past the expires date
GRANT SELECT FILTER (WHERE region = 'European Union' AND expires > CURRENT_DATE) ON TABLE info TO eu_user
GRANT eu_admin TO peter, wendy
GRANT eu_user TO emma, john

CREATE ROLE common_role
GRANT SELECT FILTER (WHERE EXTRACT(HOUR FROM CURRENT_TIMESTAMP) BETWEEN 9 AND 17) ON TABLE info TO common_role

The statements listed below allow creation and destruction of USER and ROLE objects. The GRANT and REVOKE statements allow roles to be assigned to other roles or to users. The same statements are also used in a different form to assign privileges on schema objects to users and roles.

CREATE USER

user definition (HyperSQL)

<user definition> ::= CREATE USER <user name> PASSWORD <password> [ ADMIN ]

Define a new user and its password. <user name> is an SQL identifier. If it is double-quoted it is case-sensitive, otherwise it is turned to uppercase. <password> is a string enclosed with single quote characters and is case-sensitive. If ADMIN is specified, the DBA role is granted to the new user. Only a user with the DBA role can execute this statement.

DROP USER

drop user statement (HyperSQL)

<drop user statement> ::= DROP USER <user name>

Drop (destroy) an existing user. If the specified user is the authorization for a schema, the schema is destroyed.

Only a user with the DBA role can execute this statement.

ALTER USER ... SET PASSWORD

set the password for a user (HyperSQL)

<alter user set password statement> ::= ALTER USER <user name> SET PASSWORD <password>

Change the password of an existing user. <user name> is an SQL identifier. If it is double-quoted it is case-sensitive, otherwise it is turned to uppercase. <password> is a string enclosed with single quote characters and is case-sensitive.

Only a user with the DBA role can execute this command.

ALTER USER ... SET INITIAL SCHEMA

set the initial schema for a user (HyperSQL)

<alter user set initial schema statement> ::= ALTER USER <user name> SET INITIAL SCHEMA <schema name> | DEFAULT

Change the initial schema for a user. The initial schema is the schema used by default for SQL statements issued during a session. If DEFAULT is used, the default initial schema for all users is used as the initial schema for the user. The SET SCHEMA command allows the user to change the schema for the duration of the session.

Only a user with the DBA role can execute this statement.

ALTER USER ... SET LOCAL

set the user authentication as local (HyperSQL)

<alter user set local> ::= ALTER USER <user name> SET LOCAL { TRUE | FALSE }

Sets the authentication method for the user as local. This statement has an effect only when external authentication with role names is enabled. In this method of authentication, users created in the database are ignored and an external authentication mechanism, such as LDAP is used. This statement is used if you want to use local, password authentication for a specific user.

Only a user with the DBA role can execute this statement.

SET PASSWORD

set password statement (HyperSQL)

<set password statement> ::= SET PASSWORD <password>

Set the password for the current user. <password> is a string enclosed with single quote characters and is case-sensitive.

SET INITIAL SCHEMA

set the initial schema for the current user (HyperSQL)

<set initial schema statement> ::= SET INITIAL SCHEMA <schema name> | DEFAULT

Change the initial schema for the current user. The initial schema is the schema used by default for SQL statements issued during a session. If DEFAULT is used, the default initial schema for all users is used as the initial schema for the current user. The separate SET SCHEMA command allows the user to change the schema for the duration of the session. See also the Sessions and Transactions chapter.

SET DATABASE DEFAULT INITIAL SCHEMA

set the default initial schema for all users (HyperSQL)

<set database default initial schema statement> ::= SET DATABASE DEFAULT INITIAL SCHEMA <schema name>

Sets the initial schema for new users. This schema can later be changed with the <set initial schema statement> command.

CREATE ROLE

role definition

<role definition> ::= CREATE ROLE <role name> [ WITH ADMIN <grantor> ]

Defines a new role. Initially the role has no rights, except those of the PUBLIC role. Only a user with the DBA role can execute this command.

DROP ROLE

drop role statement

<drop role statement> ::= DROP ROLE <role name>

Drop (destroy) a role. If the specified role is the authorization for a schema, the schema is destroyed. Only a user with the DBA role can execute this statement.

GRANTED BY

grantor determination

GRANTED BY <grantor>

<grantor> ::= CURRENT_USER | CURRENT_ROLE

The authorization that is granting or revoking a role or privileges. The optional GRANTED BY <grantor> clause can be used in various statements that perform GRANT or REVOKE actions. If the clause is not used, the authorization is CURRENT_USER. Otherwise, it is the specified authorization.

GRANT

grant privilege statement

<grant privilege statement> ::= GRANT <privileges> TO <grantee> [ { <comma> <grantee> }... ] [ WITH GRANT OPTION ] [ GRANTED BY <grantor> ]

Assign privileges on schema objects to roles or users. Each <grantee> is a role or a user. If [ WITH GRANT OPTION ] is specified, then the <grantee> can assign the privileges to other <grantee> objects.

<privileges> ::= <object privileges> ON <object name> [ <filter clause> ]

<object privileges> ::= ALL PRIVILEGES | <action> [ <filter clause> ] [ { <comma> <action> }... ]

<action> ::= SELECT [ <left paren> <privilege column list> <right paren> ] | DELETE | INSERT [ <left paren> <privilege column list> <right paren> ] | UPDATE [ <left paren> <privilege column list> <right paren> ] | REFERENCES [ <left paren> <privilege column list> <right paren> ] | TRIGGER | USAGE | EXECUTE

<object name> ::= { <single object name> | <schema object set name> }

<simple object name> ::= [ TABLE ] <table name> | DOMAIN <domain name> | COLLATION <collation name> | CHARACTER SET <character set name> | TRANSLATION <transliteration name> | TYPE <user-defined type name> | SEQUENCE <sequence generator name> | <specific routine designator> | ROUTINE <routine name> | FUNCTION <function name> | PROCEDURE <procedure name>

<schema object set name> ::= ALL { TABLES | SEQUENCES | ROUTINES } IN SCHEMA <schema name>

<privilege column list> ::= <column name list>

<filter clause> ::= FILTER <left paren> WHERE <search condition> <right paren>

<grantee> ::= PUBLIC | <authorization identifier>

The <object privileges> that can be used depend on the type of the <object name>. These are discussed in the previous section. For a table or view, if <privilege column list> is not specified, then the privilege is granted on the table, which includes all of its columns and any column that may be added to it in the future.

For routines, the name of the routine can be specified in two ways, either as the generic name, or as the specific name for a signature. HyperSQL allows referencing the generic name which covers all overloaded versions of a routine at the same time. This is an extension to the SQL Standard, which requires the use of <specific routine designator> to grant privileges separately on each different signature of the routine.

HyperSQL also adds the <schema object set name> option as an extension to the SQL Standard. This form grants the privileges to all the tables of the schema with a single GRANT statement.

The <filter clause> can be specified after the <object name> to limit access for all the granted privileges, or it can be used after individual privileges.

Each <grantee> is the name of a role or a user. Examples of GRANT statement are given below:

 GRANT ALL ON SEQUENCE aSequence TO roleOrUser 
 GRANT SELECT ON aTable TO roleOrUser
 GRANT SELECT(col3, col5) ON aTable TO aRole  
 GRANT SELECT, UPDATE FILTER(WHERE aColumn > 2) ON aTABLE TO role1 -- filter only for update
 GRANT SELECT, UPDATE ON aTable FILTER(WHERE aColumn > 2) TO role2 -- filter for both select and update
 GRANT SELECT(columnA, columnB), UPDATE(columnA, columnB) ON TABLE aTable TO user1, role1, role2
 GRANT EXECUTE ON SPECIFIC ROUTINE aroutine_1234 TO roleOrUser
 GRANT SELECT ON ALL TABLES IN SCHEMA mySchema TO PUBLIC

As mentioned in the general discussion, it is better to define a role for a collection of all the privileges required by an application. This role is then granted to any user. If further changes are made to the privileges of this role, they are automatically reflected in all the users that have the role. Fine-grained privileges (those with a FILTER clause) can be granted to roles only.

GRANT

grant role statement

<grant role statement> ::= GRANT <role name> [ { <comma> <role name> }... ] TO <grantee> [ { <comma> <grantee> }... ] [ WITH ADMIN OPTION ] [ GRANTED BY <grantor> ]

Assign roles to roles or users. One or more roles can be assigned to one or more <grantee> objects. A <grantee> is a user or a role. If the [ WITH ADMIN OPTION ] is specified, then each <grantee> can grant the newly assigned roles to other grantees. An example of user and role creation with grants is given below:

 CREATE USER appuser
 CREATE ROLE approle
 GRANT approle TO appuser
 GRANT SELECT, UPDATE ON TABLE atable TO approle
 GRANT USAGE ON SEQUENCE asequence to approle
 GRANT EXECUTE ON ROUTINE aroutine TO approle

REVOKE privilege

revoke statement

<revoke privilege statement> ::= REVOKE [ GRANT OPTION FOR ] <privileges> FROM <grantee> [ { <comma> <grantee> }... ] [ GRANTED BY <grantor> ] RESTRICT | CASCADE

Revoke privileges from a user or role. The syntax elements are similar to the GRANT statements.

REVOKE role

revoke role statement

<revoke role statement> ::= REVOKE [ ADMIN OPTION FOR ] <role revoked> [ { <comma> <role revoked> }... ] FROM <grantee> [ { <comma> <grantee> }... ] [ GRANTED BY <grantor> ] RESTRICT | CASCADE

<role revoked> ::= <role name>

Revoke a role from users or roles.