Grant and Revoke in Derby

Satheesh Bandaram
12 August 2006
Version 5

Introduction

Originally Cloudscape/Derby used a very simple permissions scheme, which is quite suitable for an embedded database system. Embedded database users typically don't see Derby database directly and instead talk to applications that embeds Derby. So Derby left most of the access control work to applications. Under this scheme, Derby limits database access on database or system basis. A user can be granted full, read-only, or no access. This is less suitable for general purpose client-server database configurations. When end users or diverse applications can issue SQL commands directly against the database, Derby must provide more precise mechanisms to limit who can do what with the database.

I propose to implement a subset of SQL2003 access control that deals with tables, views, procedures, and functions to start with. Each database will have the option of operating using the legacy Derby access control system or a SQL2003 compatible access control system. This paper describes a proposal to introduce Derby's SQL2003 compatible access control system. There are many further enhancements possible in access control and security areas. My current itch is to limit the scope to what is proposed here.

I wish to propose a staged development plan with following tasks as mentioned in DERBY-464: (http://issues.apache.org/jira/browse/DERBY-464)

    Submit support for DDL. This would implement grant and revoke statements and recording of permissions in system tables.
    Add run-time support to enforce permission checking.
    Address upgrade, migration and metadata changes.

Grant and Revoke Statements

The GRANT statement is used to grant permissions to users. The REVOKE statement is used to revoke permissions.

grant-statement ::= table-grant-statement
  | routine-grant-statement

table-grant-statement ::=
  GRANT table-privileges TO grantees

routine-grant-statement ::=
  GRANT EXECUTE ON routine TO grantees

revoke-statement ::= table-revoke-statement
  | routine-revoke-statement

table-revoke-statement ::=
  REVOKE table-privileges FROM grantee, ...

routine-revoke-statement ::=
  REVOKE EXECUTE ON routine FROM grantee, ... RESTRICT

table-privileges ::= table-action ON [TABLE] table-or-view-name

grantees ::= { PUBLIC | user-identifier,... }

table-action ::= ALL PRIVILEGES | action, ...

action ::= SELECT  [ ( privilege-column-list ) ]
       |  DELETE
       |  INSERT
       |  UPDATE [ ( privilege-column-list ) ]
      |  REFERENCES [ ( privilege-column-list ) ]
       |  TRIGGER

routine ::= { FUNCTION | PROCEDURE } routine-designator

routine-designator ::= qualified-name [ signature ]

signature ::= ( data-type, ... )

Some examples:

 GRANT SELECT, update(description) ON t TO maria,harry
 GRANT SELECT ON TABLE s.v to PUBLIC
 GRANT EXECUTE ON PROCEDURE p TO george
 REVOKE update(description) ON t FROM maria

The table select privilege is permission to perform a select on the named table or view. If there is a column list then the permission is only on those columns. If there is no column list then the select privilege is on all columns in the table.

The table references privilege is permission to create a foreign key reference to the named table. If there is a column list then the permission is only on foreign key references to the named columns.

The table trigger privilege is permission to create a trigger on the named table.

When a table, view, function, or procedure is created its owner (creator) has full privileges on it. No other user (except for database owner) has any privileges on it until the owner grants privileges.

Privileges may be granted to specific users or to everyone: all current and future users. The word "PUBLIC" denotes everyone.

Privileges granted to PUBLIC and to individual users are independent. Suppose SELECT privilege on table t was granted to both PUBLIC and harry. If SELECT privilege is revoked from harry, harry will still be able to access table t; he uses the PUBLIC privilege.

The REVOKE statement revokes privileges. Revoking a privilege without specifying a column list revokes the privilege for all columns.

RESTRICT is mandatory with routine revoke statements. That means that execute permission on a function may not be revoked if that function is used in a view, trigger, or constraint, and permission is being revoked from the owner of the view, trigger, or constraint.

Only the owner (creator) of an object or database owner can grant or revoke privileges on that object. Attempting to revoke owners own privileges raise an error.  Derby will not allow GRANT or REVOKE statements on synonyms. If a REVOKE statement doesn't actually revoke any privileges, a warning (01006) is raised. Attempts to revoke part of a granted table privilege by only revoking specific column privilege will also raise the same warning.

Permission Checking

Permissions are checked when a statement is executed, not when it is prepared. This allows statement cache to share statements among different users.

Views, Triggers, and Constraints

Views, triggers, and constraints operate with the permissions of the owner of the view, trigger, or constraint.

For example, consider the following view definition created by user jane
 

  CREATE VIEW s.v(vc1,vc2,vc3)
    AS SELECT t1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE t2.c2 = 5

Jane needs the following permissions in order to create the view:

When the view is created only jane has select permission on it. Jane can grant select permission on any or all of the columns of s.v to anyone, even to users who do not have select permission t1 or t2 or execute permission on f. Suppose jane grants select permission on s.v to harry. When Derby executes a select on s.v on behalf of harry, Derby only checks that harry has select permission on s.v; it does not check that harry has select permission on t1, or t2 or execute permission on f.

Similarly with triggers and constraints: a trigger or constraint may operate on columns for which the current user does not have the appropriate permissions. It is only required that the owner of the trigger or constraint have the appropriate permissions.

When a view, trigger, or constraint is created Derby checks that the owner has the required permissions, throwing an SQLException if not. If any of the required permissions are later revoked then the view, trigger, or constraint is dropped as part of the REVOKE statement.

Current User

The permission system relies on Derby authentication to establish the identity of the current user. Permission checking is of little value unless Derby authentication is turned on. By default, Derby's authentication is OFF and can be turned ON by setting derby.connection.requireAuthentication to TRUE. Attempts to set security mode to standard mode without first requiring authentication will raise a warning.

Database Owner

User creating a database is referred to as Database Owner. A database owner has more privileges than a normal user of a database. Database owners alone can create multiple schemas in that database or create a schema to be owned by another user. She can also grant or revoke any object privilege on any database object to any user and can access all objects in the database without any explicit granting of access. It is also not possible to revoke any privilege from database owners. Database owners assume the authorizationId of  other users while operating in their user schemas. Objects created by database owners in other user schemas would be owned by that user.

For an extent database (database created with 10.1 or earlier versions), person attempting to upgrade the database will become its database owner. Database owner has this extra privileges only in SQL standard authorization mode.

Table, View, Trigger, and Schema Creation Permissions

A table may only be created or dropped by the owner of the table's schema. Table creation permission is not grantable. (This is the SQL2003 spec). Only the owner of a table may create or drop indices or constraints on the table. Views and triggers may only be created or dropped by the owner of the view or trigger's schema.

When a schema is created the schema owner is defined using the authorization clause of the CREATE SCHEMA statement:

create-schema-statement ::=
       CREATE SCHEMA schema-name
   |   CREATE SCHEMA schema-name AUTHORIZATION user-name
   |   CREATE SCHEMA AUTHORIZATION user-name

The first form creates a new schema that is owned by the current effective user. The second form creates a schema owned by the named user. The final form creates a new schema that is owned by the named user and that has the same name as its owner.

The owner of the database may create any schema with any owner (authorization). Other users may only create schemas whose name is the same as their user name and that are owned by the user. For example, if user angelica does not own the database then she can only create a schema with name and authorization angelica.

A schema may only be dropped by its owner or the owner of the database.

System Schemata, Functions, Procedures

All of the system built-in schemata (SYS, SYSCAT, SYSIBM, etc) in Derby 10.1 are owned by pseudo user DBA. From Derby 10.2, they would be owned by authorizationID of the user who creates the database. For databases created using Derby 10.1 or earlier versions, full upgrade to Derby 10.2 would change owner of system schemas from pseudo user 'DBA' to the authorizationID of the user invoking upgrade. This pseudo user name is  not being used in Derby till now and will be used when sqlAuthorization is enabled.

The standard builtin functions, ABS, CURRENT_USER, TRIM, etc are treated differently than the builtin functions and procedures in the SYSCS_UTIL schema. When a database is created all users have execute permission on the standard builtin functions. This permission cannot be revoked. However when a database is created only the database owner has execute permission on the many of SYSCS_UTIL functions and procedures. Only the owner of the database may execute these Derby system routines such as SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY and SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE. The owner of the database may grant execute permission on these routines to other users. DERBY-475 has added a new table based mechanism to define functions in SYSFUN schema. These will also be treated as builtin functions for the permission scheme.

One must be particularly careful about granting execute permission on SYSCS_BACKUP_DATABASE, SYSCS_EXPORT_TABLE, SYSCS_EXPORT_QUERY, SYSCS_IMPORT_TABLE, and SYSCS_IMPORT_DATA because can read or write files. The operating system will let them read or write any file accessible to the user who started the JVM. The operating system does not know anything about Derby connections or who Derby thinks the current user is.

One should also be careful about granting execute permission on SYSCS_FREEZE_DATABASE, SYSCS_UNFREEZE_DATABASE, and SYSCS_SET_DATABASE_PROPERTY. SYSCS_FREEZE_DATABASE locks up the database for some time. SYSCS_SET_DATABASE_PROPERTY affects the whole database.

For those routines that everyone can execute, appropriate privileges are still required to access database objects. For instance a user needs to have SELECT privilege on a table to be able to call SYSCS_COMPRESS_TABLE. Here is a list of system routines and who has privilege by default to execute them. Others can be granted this privilege by the database owner.
 
 
Routine(s) type System rouitne(s) System schema Who has  execute privilege by default Other privileges needed
Metadata routines All routines starting with SQL and METADATA SYSIBM Everyone None
JAR handling routines INSTALL_JAR, REPLACE_JAR, REMOVE_JAR SQLJ Database owner None
Backup routines Routines starting with SYSCS_BACKUP_ SYSCS_UTIL Database owner None
Admin routines SYSCS_CHECKPOINT_DATABSE, SYSCS_DISABLE_LOG_ARCHIVE_MODE, SYSCS_FREEZE_DATABSE,  SYSCS_UNFREEZE_DATABASE, SYSCS_CHECKPOINT_DATABASE SYSCS_UTIL Database owner None
Statistics routines SYSCS_SET_RUNTIMESTATISTICS, SYSCS_SET_STATISTICS_TIMING, SYSCS_GET_RUNTIMESTATISTICS SYSCS_UTIL Everyone None
Import/Export SYSCS_EXPORT_QUERY, SYSCS_EXPORT_TABLE, SYSCS_IMPORT_DATA, SYSCS_IMPORT_TABLE,  SYSCS_UTIL Database owner SELECT priv on table for export and INSERT priv for import
Property handling SYSCS_GET_DATABSE_PROPERTY, SYSCS_SET_DATABASE_PROPERTY SYSCS_UTIL Database owner None
Compress routines SYSCS_INPLACE_COMPRESS_TABLE, SYSCS_COMPRESS_TABLE SYSCS_UTIL Everyone Needs to be schema owner, pending DERBY-1062
Check table SYSCS_CHECK_TABLE SYSCS_UTIL Database owner, could be relaxed later None

Derby upgrade and migration

Derby 10.1 authorization is on a database basis. A user can be granted full (read/write), read-only, or no access to a database. This is done through Derby properties. The derby.database.defaultConnectionMode property specifies the default access permission. Its value must be "noAccess", "readOnlyAccess", or "fullAccess" (case insensitive). The derby.database.fullAccessUsers and derby.database.readOnlyAccessUsers properties specify lists of users with full or read-only access. The default for derby.database.defaultConnectionMode is "fullAccess", so if no authorization properties are specified then all users have full access.

I propose to add a new property, derby.database.sqlAuthorization with possible values "false" or "true", with default value being "false". When sqlAuthorization is false, only current legacy database access checks are performed. Attempts to issue grant or revoke statements would raise sqlAuthorization not enforced warning. If sqlAuthorization is true, in addition to legacy access checks, SQL standard access checks are also performed. Required object access permissions are checked.

An extant database may be switched from the legacy authorization model to the SQL2003 standard model. This is done by firstupgrading the database and after upgrading setting database property derby.database.sqlAuthorization value to "true". All tables and views will be owned by database owner schema owners. Until a GRANT statement is issued, only the table owner will have access to a table.

Security mode switching is performed using the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY procedure. In a database operating under the legacy security model any user with fullAccess can call this procedure to switch the security mode by setting sqlAuthorization. A database may not be reverted from the standard security mode to a legacy security mode.

When a database is created, if derby.database.sqlAuthorization system property value is true, the database gets created with SQL standard security mode, enabling grant and revoke. Also this system property value gets stored in the database as a database property. Once a database is enabled in SQL standard mode, it can not be changed back to Derby authorization mode.

It may be good to switch the default connection mode to standard model and hence support grant/revoke by default in future releases. A scheme needs to be evolved to reduce any disruptions to existing users of Derby.

High-level design

Some high-level details about the design of the feature. This is followed by System schema for new system tables added. It would be good to incorporate system schema in Derby manuals.

GRANT/REVOKE Statement Implementation

The GRANT and REVOKE statements are implemented as ConstantActions insted of generated code. DDL statements directly invoke executing mechanism.

The GrantConstantAction and RevokeConstantAction classes take a permission list and a grantee list as constructor parameters. They iterate through the these lists executing the appropriate SQL insert, delete, or update statements on the permission system tables. They remove the corresponding items from the permission cache.

The RevokeConstantAction class is more complex because it must handle cascading deletions of permissions, views, triggers, and constraints. When a permission is revoked Derby checks dependent objects for views, triggers, and constraints that require that permission and drops them.

Permission Checking

Permission checking is done at execution time. At compile time, in the bind phase, Derby constructs a list of all the permissions that the statement needs to execute.

The generated code starts with a call to a permission checking method. The permission checking method is passed the list of required permissions. It calls the data dictionary to see if the user still has the required permissions.

This can be illustrated with an example. For a statement like INSERT INTO TAB1 SELECT * FROM TAB2, bind phase generates two access descriptors, StatementTablePermission(TAB1, insertPriv) and StatementColumnPermission(TAB2, selectPriv, <colList>). At execution time, GenericAuthorizer.authorize() is invoked through generated code. This first checks if authorizationId of the user has required FULLACCESS. If current user is only allowed readOnly access, an error is raised. This is performed in both legacy database or sqlStandard database.

In an sqlStandard database, further checking is required to make sure required object permissions are present. For this statement, two access descriptor generated during bind phase are checked. If required access is present to this authorizationID or to PUBLIC, then authorization succeeds. Statement execution continues.

DDL statements can only be issued by the schema owner currently. All DDL statements would generate access descriptor, StatementSchemaPermission(schema). Since bind phase generates all access descriptors needed for the query, a compiled statement need not be recompiled for any future grant or revoke. Required access checking is performed at statement execution time, just like in legacy databases for readOnly or fullAccess users.

The permission checks could be done at compilation time. PreparedStatements would have to be re-compiled when permissions are revoked, as they are recompiled when tables are altered. The problem with this is that then the statement cache cannot share statements among different users, lessening the value of the statement cache. Previous versions of Cloudscape/Derby did permission checking at execution time.

System Schema

Following new system tables have been added to hold system metadata. The permissions data is stored in several tables in the SYS schema: SYSTABLEPERMISSIONS, SYSCOLPERMISSIONS, and SYSROUTINEPERMISSIONS. The tables are read only.

Some of these system tables currently have a java object, org.apache.derby.iapi.services.io.FormatableBitSet, in them. Although some of Derby's system table have java objects, I think, introducing new java object to hold system data should be discouraged. Since SQL has no way to select individual components of java objects, they make a poor choice in processing metadata. I will try to modify these system tables to avoid using them.

Current implemention of grant/revoke in Derby does not support the SQL standard WITH GRANT OPTION feature. This could be taken up in the future. This feature allows the owner of an object (table, column, function, or procedure) to delegate granting and revoking permissions on an object to other users, who may themself delegate it to other users. So, in the SQL standard, a user may have the same permission granted to him by multiple users. If one of the grantors revokes the permission the user can still perform the operation. The SQL standard also calls for recursive revocation when a permission with grant option is revoked.

In Derby only the owner of an object can grant or revoke permissions on the object. This significantly simplifies the Derby implementation. Nevertheless, the permissions system tables are designed to support the WITH GRANT OPTION feature. The SYSTABLEPERMS, SYSCOLPERMISSIONS, and SYSROUTINEPERMISSIONS tables all have a GRANTOR column. The primary key indices on these tables all include the GRANTOR column as the least significant column. Currently the GRANTOR column value is always the name of the owner of the object; it is not needed to find a unique row in a permissions table. The DataDictionary code can pass a key containing all the key columns but GRANTOR to the TabInfo.getRow to find a permissions table row.

To support the WITH GRANT OPTION feature Derby must change its DataDictionary and TabInfo implementations to handle multiple permission rows for the same type, user and object. It may also have to add secondary indices on the GRANTOR columns to implement recursive revocation. However, it should not have to add or change any columns in the permissions system tables, nor should it have to change the primary indices.

SYSTABLEPERMS

This table stores the table permissions that have been granted but not revoked. To save space all the permissions for one (grantee, tableID) combination are grouped into the same row. The table's schema is as follows.

create table SYS.SYSTABLEPERMS
(
    TABLEPERMSID char(36) not null,
    GRANTEE varchar(128) not null,
    GRANTOR varchar(128) not null,
    TABLEID char(36) not null,
    SELECTPRIV char(1) not null,
    DELETEPRIV char(1) not null,
    INSERTPRIV char(1) not null,
    UPDATEPRIV char(1) not null,
    REFERENCESPRIV char(1) not null,
    TRIGGERPRIV char(1) not null,
    primary key( GRANTEE, TABLEID, GRANTOR),
    unique key( TABLEPERMSID),
    foreign key( TABLEID references SYS.SYSTABLES)
)

The SELECTPRIV column has value "Y" if the SELECT permission was granted, or "N" if the select permission was not granted. Similarly with the DELETEPRIV, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV, and TRIGGERPRIV columns. Other values for these columns are reserved for future use, when withGrant option may be supported.

The GRANTOR column is reserved for future use. It has the name of the user who granted the permission. Currently this is always the name of the owner of the table.

TABLEPERMSID is used by the dependency manager to track view/trigger/constraint's dependency on table level permissions.

SYSCOLPERMS

The SYS.SYSCOLPERMS table has one row for each (grantee, table, type) combination with column permissions that have been granted but not revoked. Its schema is as follows.

create table SYS.SYSCOLPERMS
(
    COLPERMSID char(36) not null,
    GRANTEE varchar(128) not null,
    GRANTOR varchar(128) not null,
    TABLEID char(36) not null,
    TYPE char(1) not null,
    COLUMNS org.apache.derby.iapi.services.io.FormatableBitSet not null,
    primary key( GRANTEE, TABLEID, TYPE, GRANTOR),
    unique key( COLPERMSID),
    foreign key( TABLEID references SYS.SYSTABLES)
)

TYPE is one of 'S', 'U', or 'R', representing SELECT, UPDATE, or REFERENCES column permissions.

The GRANTOR column is for future use. It has the name of the user who granted the permission. Currently this is always the name of the owner of the table.

COLPERMSID is used by the dependency manager to track view/trigger/constraint's dependency on column level permissions.

SYSROUTINEPERMS

The SYS.SYSROUTINEPERMS table has one row for each routine execute permission. The schema is:

create table SYS.SYSROUTINEPERMS
(
    ROUTINEPERMSID char(36) not null,
    GRANTEE varchar(128) not null,
    GRANTOR varchar(128) not null,
    ALIASID char(36) not null,
    GRANTOPTION char(1) not null,
    primary key( GRANTEE, ALIASID, GRANTOR),
    unique key( ROUTINEPERMSID),
    foreign key( ALIASID references SYS.SYSALIASES)
)

The GRANTOR column is for future use. It has the name of the user who granted the permission. Currently this is always the name of the owner of the table.

The GRANTOPTION column is for future use. It always has value 'Y' if the grantee is the owner of the routine, 'N' if not. Other values for this column are reserved for future use, when withGrant option may be supported.

ROUTINEPERMSID is used by the dependency manager to track view/trigger/constraint's dependency on routine level permissions.