Notes taken during understanding the GRANT/REVOKE implementation as part of understanding the changes for DERBY-1539

Terminology

Some general terminology that is used by the code:

Statements

GRANT statement

Input - {permision*,grantor,grantee*} - Grants permission on one or more actions to one or more grantess by a single grantor.

Operations

  1. Store input in system tables

Objects representing {permision,grantor,grantee*}

Issues

REVOKE statement

Input - {permision*,grantor,grantee*} - Revoke of permissions to one or more grantees on a single permission by a single grantor

Operations

  1. Notify dependents of removal of permission
  2. Remove input from system tables

Objects representing {permision,grantor,grantee*}

Arbitrary Statement Execution

Input {permission*,user} - Set of permissions required by user for execution.

Operations:

  1. Determine if the set of granted permissions provides a complete intersect with the required set.

Complicated by the fact that a permission required by be covered by one of several granted permissions, e.g. {select C1 on T, fred} is covered by any of:

assuming the current scheme where on the owner of an object can GRANT permissions on it.

Objects:

Issues

Creation of object with privilege dependencies

Objects such as views, triggers and constraints have underlying actions that require that the permissions be granted to the creator at CREATE execution. E.g. with CREATE VIEW V AS SELECT * FROM T the creator of V must have select permission on T. If sometime later this select permission is revoked from the creator of V then V is dropped. This linkage is implemented using the dependency system, V depends on the privilege {select T,owner of T,creator of V}.

Objects:

Situation is complicated by the fact that if V depends on {select T,owner of T,creator of V} a REVOKE of the form:

will drop V, but not if it was proceeded by a GRANT of the form:

Issues

  1. Too many ways of representing permissions, PrivilegeInfo, StatementPermission, PermissionDescriptor, UUID. Possible solution:

    • Use StatementPermission everywhere

    • Change/Merge PrivilegeInfo into the grant revoke constant action, and have the data pased into the constant action consist of a (or set of) StatementPermission object(s).

    • Re-work/clarify PermissionsDescriptor

      • Ensure consistently it refers to a single row
      • Remove it from being a public-api, only use within the DataDictionaryImpl.

  2. Permissions cache within DataDictionaryImpl - Not sure how this ties into the scheme, how does it support locking etc.

  3. Is it clear as to which module has which responsibilities? Three possible models:
    1. DataDictionary is responsible for storage of rows representing granted permissions only, no logic about what to do with those rows. That logic (such as searching for PUBLIC &fred and the relationship between table & columns permissions) is handled by the StatementPermission objects

    2. DataDictionary is repsonsible for everything, has methods which are passed StatementPermission and all logic below the covers. E.g. isGranted(StatementPermission), addGrantedPermission(StatementPermission, String grantor, String grantee). Is this pushing too much in to the data dictonary, whose role is really just storing SQL catalog objects.

    3. Logic on how to handle permissions is spread across the data dictionary and other code (e.g. CompilerContextImpl). This seems to be where to code is today.

Two modes

  1. derby.database.sqlAuthorization=false - No fine grained authorization, any user can access any object
  2. derby.database.sqlAuthorization=true - Fine-grained authorization, non-owner of an object must be granted

derby.database.sqlAuthorization

authentication enabled

CREATE SCHEMA

ACCESS OTHER OBJECTS

SECURE

Comments

false (traditional Derby)

No

Yes

Yes

No

Yes

Yes

Yes

No

Authenticated users can modify any table etc.

true (GRANT/REVOKE)

No

limited to single schema matching username for non-database owner

controlled by GRANT

No

No authentication means user can log in as database owner if database owner name can be determined. DERBY-1543 will add a warning if GRANT is executed without authentication enabled.

Yes

limited to single schema matching username for non-database owner

controlled by GRANT

Yes