$Revision: 3096 $
Copyright 2010-2024 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.
2024-06-01
Table of Contents
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.
There are some pre-defined roles in each database; some defined by the SQL Standard, some by HyperSQL. These roles can be assigned to users (directly or via other, user-defined roles). In addition, there is the initial admin user created with each new database. The initial admin user name and password are defined in the connection properties when the first connection to the database is made. In older versions of HSQLDB, this name was always SA. But in the latest version, the name can be defined as a different string.
Admin User
the Admin user (HyperSQL-specific)
This user is automatically created with a new database and has the DBA role. This user name and its password are defined in the connection properties when connecting to the new database to create the database. This user can change the password, create other users and create new schemas and other objects. The initial admin user can be dropped by another user that has the DBA role. As a result, there is always at least one admin user in the database.
PUBLIC
the PUBLIC role
The role that is assigned to all authorizations (roles and users) in the database. This role has access rights to all objects in the INFORMATION_SCHEMA with limited visibility. Any roles or rights granted to this role, are in effect granted to all users of the database.
_SYSTEM
the _SYSTEM role
This role is the authorization for the pre-defined (system) objects in the database, including the INFORMATION_SCHEMA. This role cannot be assigned to any authorization (user or role).
DBA
the DBA role (HyperSQL-specific)
This is a special role in HyperSQL. A user that has this role can perform all possible administrative tasks on the database. The DBA role can also act as a proxy for all the roles and users in the database. This means it can do everything the authorization for a schema can do, including dropping the schema or its objects, or granting rights on the schema objects to a grantee. All admin users have this role.
CREATE_SCHEMA
the CREATE_SCHEMA role (HyperSQL-specific)
An authorization that has this role, can create schemas. The DBA authorization has this role and can grant it to other authorizations.
CHANGE_AUTHORIZATION
the CHANGE_AUTHORIZATION role (HyperSQL-specific)
A user that has this role, can change the authorization for the current session to another user. The other user cannot have the DBA role (otherwise, the original user would gain DBA privileges). The DBA authorization has this role and can grant it to other authorizations.
SCRIPT_OPS
the SCRIPT_OPS role (HyperSQL-specific)
A user that has this role, can execute the PERFORM EXPORT SCRIPT and PERFORM IMPORT SCRIPT statements. The DBA authorization has this role and can grant it to other authorizations.
Tables in the INFORMATION_SCHEMA contain the list of users and roles for the database. Only admin users can see the full contents of these tables.
The SYSTEM_USERS tables contains the list of users, with some extra settings for each user. The AUTHORIZATIONS table contains a list of both users and roles.
Several other INFORMATION_SCHEMA tables list the privileges granted to users and roles on different database objects. Refer to the Schemas and Database Objects chapter for a list and description of the tables. Example below:
SELECT * FROM INFORMATION_SCHEMA.SYSTEM_USERS SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
The create schema statements has an optional AUTHORIZATION clause: For example:
CREATE SCHEMA mySchema AUTHORIZATION aUserOrRole
If the authorization is not specified, the DBA role becomes the authorization. This authorization is the owner of the schema. By default, the objects in a schema can only be accessed by the schema owner. But the schema owner can grant privileges (access rights) on the objects to other users or roles.
Things can get far more complex because the grant of privileges can be made WITH GRANT OPTION. In this case, the role or user that has been granted the privilege can grant the privilege to other roles and users.
Privileges can also be revoked from users or roles.
The statements for granting and revoking privileges normally
specify which privileges are granted or revoked. However, there is a
shortcut, ALL PRIVILEGES, which means all the privileges that the
<grantor>
has on the specified schema object.
The <grantor>
is normally the CURRENT_USER of
the session that issues the statement.
The user or role that is granted privileges is referred to as
<grantee>
for the granted privileges.
TABLE
For tables, including views, privileges can be granted with different degrees of granularity. It is possible to grant a privilege on all columns of a table, or on specific columns of the table.
The DELETE privilege applies to the table, rather than its columns. It applies to all DELETE statements.
The SELECT, INSERT and UPDATE privileges may apply to all
columns or to individual columns. These privileges determine whether the
<grantee>
can execute SQL data statements on
the table.
The SELECT privilege designates the columns that can be referenced in SELECT statements, as well as the columns that are read in a DELETE or UPDATE statement, including the search condition.
The INSERT privilege designates the columns into which explicit values can be inserted. To be able to insert a row into the table, the user must therefore have the INSERT privilege on the table, or at least all the columns that do not have a default value.
The UPDATE privilege designates the table or the specific columns that can be updated.
A MERGE statement requires SELECT privileges together with INSERT, UPDATE and DELETE privileges when these actions are specified in the statement.
The REFERENCES privilege allows the
<grantee>
to define a FOREIGN KEY constraint on
a different table, which references the table or the specific columns
designated for the REFERENCES privilege.
The TRIGGER privilege allows adding a trigger to the table.
SEQUENCE, TYPE, DOMAIN, CHARACTER SET, COLLATION, TRANSLITERATION
For these objects, only USAGE can be granted. The USAGE privilege is needed when object is referenced directly in an SQL statement.
ROUTINE
For routines, including procedures or functions, only EXECUTE privilege can be granted. This privilege is needed when the routine is used directly in an SQL statement.
OTHER OBJECTS
Other objects such as constraints and assertions are not used directly and there is no grantable privilege that refers to them.
The simplest form of access control is when the DBA user creates a single non-admin user, then creates the tables and other objects in the PUBLIC schema, and grants access to the objects to PUBLIC. A grant to PUBLIC applies to all non-admin users, including any users that are created later.
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
When different users need to have different levels of access, the privileges are granted to individual users as opposed to PUBLIC. In the example below there are 2 users with different access rights to the objects in the schema.
-- 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;
A USER or ROLE that does not own a schema can be granted access to individual columns of a table in the schema.
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
HyperSQL adds a feature that is not part of the SQL Standard to allow access to be granted to a ROLE only for certain rows of a table, based on a FILTER condition.
When the GRANT statement contains a FILTER condition, the condition is applied to each row of the table that a SELECT, INSERT, UPDATE, DELETE or MERGE statement tries to access. Only the rows that satisfy the condition are accessed and all other rows are ignored.
The following is an example of this usage. The table INFO has a
column that determines the geographic region for each row of data and
another column that holds the expiration date for this row. The owner of
the schema can access and change the data in all the rows of this table.
A group of ordinary users with the eu_admin
role is
only allowed to access the data for a certain region. Another group with
the eu_user
role is only allowed to access the rows
before the expiration date.
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
In the above example, the EU_ADMIN and EU_USER roles are granted to the users that are allowed to access the data for the European Union. These users cannot see the rows that are for other regions. Among them, only the EU_ADMIN users can see the rows that have expired. The SELECT grant with FILTER also prevents the users from deleting or updating the rows they cannot access.
The <filter clause>
can be used for other
forms of fine-grained access control. In the example below, the
COMMON_ROLE role is defined and granted access during office hours
only.
CREATE ROLE common_role GRANT SELECT FILTER (WHERE EXTRACT(HOUR FROM CURRENT_TIMESTAMP) BETWEEN 9 AND 17) ON TABLE info TO common_role
Fine-grained data access control can also be used to implement multi-tenancy database solutions.
A separate <filter clause>
can be
declared on each of SELECT, DELETE, INSERT and UPDATE rights on the
table. To change an existing fine-grained right granted to a ROLE on a
table, the existing right must be revoked before a GRANT with FILTER is
made. Use of ALTER TABLE to remove columns that are not referenced in a
FILTER condition, or to add new columns to the table, does not affect
the validity of the FILTER condition. But if any column that is
referenced is removed, you need to REVOKE the filtered rights.
With a MERGE statement, which may contain INSERT, UPDATE, and DELETE clauses, the UPDATE filter is used when selecting the rows to UPDATE, as well as rows to DELETE.
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.
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 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.
$Revision: 6752 $