Browse Prior Art Database

Conservative binding semantics for SQL objects and static DML statements in packages handling for the newly introduced execute privilege of a routine

IP.com Disclosure Number: IPCOM000015840D
Original Publication Date: 2002-Apr-01
Included in the Prior Art Database: 2003-Jun-21
Document File: 3 page(s) / 49K

Publishing Venue

IBM

Abstract

This disclosure relates to privilege associated with the ability to execute a routine (i.e. function, procedure or method), known as Execute privilege as way to do the conservative binding semantics logic. Conservative binding semantics ensures that the function and data type references will be resolved using the same SQL path as when it was bound. We need to add the privilege into the logic. Since we can now have a case where the privilege is granted on a better match routine after the initial routine resolution, the logic will requires a redesign. Grant Timestamp Approach We could add the comparison of the grant timestamp with the original create timestamp of SQL objects or the original bind timestamp of packages. New and better Approach: Routine ID List Approach To support the new privilege and maintain the conservative binding semantics for SQL objects (view, triggers, check constraints, and SQL routines) and static DML statements in packages, we introduce the concept of routine id list. Routine id list is a list of routine id that is stored as part of the packed descriptor for the SQL objects and packages. When we compile the SQL objects or static DML statements in a package, the SQL compiler will gather a list of objects (including routines) that are referenced by the static DML statements in the package or by the SQL objects. The referenced routines id are then collected and stored as part of the packed descriptor of the package or SQL objects. In this release, we have not yet achieved statement level routine id list. We collected every referenced routine id of each statements in a package and merged into one routine id list and attached to the package packed descriptor. We are planning to extend this infrastructure to put it in statement level in the next major release. Once we collected the routine id that we used during compile time routine resolution, at the runtime (execute time) of the SQL objects or packages, we will restrict any routine being used if it is not in the routine id list. We fetch the packed descriptor and pass down the routine id list to the compiler to allow us to perform such restriction checking. Of course, this is in addition to the SQL path and create timestamp checking.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 39% of the total text.

Page 1 of 3

  Conservative binding semantics for SQL objects and static DML statements in packages handling for the newly introduced execute privilege of a routine

   This disclosure relates to privilege associated with the ability to execute a routine (i.e. function, procedure or method), known as Execute privilege as way to do the conservative binding semantics logic. Conservative binding semantics ensures that the function and data type references will be resolved using the same SQL path as when it was bound. We need to add the privilege into the logic. Since we can now have a case where the privilege is granted on a better match routine after the initial routine resolution, the logic will requires a redesign.

Grant Timestamp Approach We could add the comparison of the grant timestamp with the original create timestamp of SQL objects or the original bind timestamp of packages.

New and better Approach: Routine ID List Approach To support the new privilege and maintain the conservative binding semantics for SQL objects (view, triggers, check constraints, and SQL routines) and static DML statements in packages, we introduce the concept of routine id list. Routine id list is a list of routine id that is stored as part of the packed descriptor for the SQL objects and packages. When we compile the SQL objects or static DML statements in a package, the SQL compiler will gather a list of objects (including routines) that are referenced by the static DML statements in the package or by the SQL objects. The referenced routines id are then collected and stored as part of the packed descriptor of the package or SQL objects. In this release, we have not yet achieved statement level routine id list. We collected every referenced routine id of each statements in a package and merged into one routine id list and attached to the package packed descriptor. We are planning to extend this infrastructure to put it in statement level in the next major release.

Once we collected the routine id that we used during compile time routine resolution, at the runtime (execute time) of the SQL objects or packages, we will restrict any routine being used if it is not in the routine id list. We fetch the packed descriptor and pass down the routine id list to the compiler to allow us to perform such restriction checking. Of course, this is in addition to the SQL path and create timestamp checking.

Revoking of execute privilege on a routine is restrict if the specified routine is used in a view, trigger, constraint, index extension, or SQL routine body, or referenced as the source of a sourced function and the loss of the execute privilege would cause the definer of the view, trigger, constraint, index extension, SQL routine body, or sourced function to no longer be able to execute the routine specified in the revoke. A package will becomes inoperative if the execute privilege is lost.

With such restriction, the routine id list always contain the conservative binding semantics a...