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:

• permission - Some permission required to perform an action (e.g. execute on a function)

• user - The user executing an action

• grantee - A user who has been granted a permission to perform an action

• grantor - A user who has granted a permission to a grantee

• privilege - A triplet of {permission*,grantor,grantee} that generated by a GRANT statement and stored in a system table.

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*}

• PrivilegeInfo {permission*} - Created as compile time and used like constant action

• List<grantee> - Created at compile time

• current user {grantor} - used at execution time

• PermissionsDescriptor {permission*,grantor,grantee} - Created at execution time and passed into DataDictionary for storage.

Issues

• PermissionsDescriptor object passed in is reset each time with a different grantee - does this match the intended purpose of such descriptors which are TupleDescriptors and match a single row in the database?

• PrivilegeInfo looks very much like a constant action, the constant action for grant/revoke is more or less just an empty object that calls the execute method on PrivilegeInfo. Should this code be re-factored to have all the logic in a constant action, to match the pattern of other DDL statements.

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*}

• Same as GRANT

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:

• {select C1 on T, <owner T>, fred}

• {select C1 on T, <owner T>, PUBLIC}

• {select * on T, <owner T>, fred}

• {select * on T, <owner T>, PUBLIC}

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

Objects:

• Compile

  • HashMap<rotuine UUID> - For permission to execute routine (key UUID value constant 1)

  • HashMap<StatementSchemaPermission> - For permission to create/modify schema (key object, value same as key)

  • HashMap<StatementTablePermission> - Table level permissions (key object, value same as key)

  • HashMap<StatementTablePermission> - Column level permissions (key object, value StatementColumnPermission)

  • Permissions represented by the four HashMaps are converted into a List<StatementPermission> for execution.

• Execution

  • List<StatementPermission> - The set of permissions needed to execute all the actions for this statement

  • StatementPermission {permission*} - Created at compile time

Issues

• Why HashMaps in the three cases a HashSet would do?

• Why use a UUID for routine and not a StatementPermission?

• Why the Iterator based code to convert to a List, aren't there methods to add the contents of one collection to another?

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:

• List<<StatementPermission> - The complete set of permissions needed to execute the CREATE statement

• The list is walked to determine the sub-set of the permissions that the object will be dependent on for REVOKE processing

• For each required StatementPermission a PermissionsDescriptor is created to be used as Provider for the dependency system

  • StatementPermission {permission}

  • Object owner {grantee}

  • Existing granted privilege for permission {grantor}

• Thus once the CREATE is executed the resultant object (e.g. ViewDescriptor) will have persistent dependencies to a collection of PermissionsDescriptor each identified by its unique UUID corresponding to the row it represents.

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

• `REVOKE SELECT ON T FROM <creator of V>

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

• GRANT SELECT ON T TO PUBLIC

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