Derby
  1. Derby
  2. DERBY-4680

Add documentation for routines running with definer's rights

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.7.1.1
    • Component/s: Documentation
    • Labels:
      None
    1. DERBY-4680-3.zip
      16 kB
      Kim Haase
    2. DERBY-4680-3.diff
      12 kB
      Kim Haase
    3. DERBY-4680-2.zip
      16 kB
      Kim Haase
    4. DERBY-4680-2.stat
      0.2 kB
      Kim Haase
    5. DERBY-4680-2.diff
      11 kB
      Kim Haase
    6. DERBY-4680.zip
      26 kB
      Kim Haase
    7. DERBY-4680.stat
      0.4 kB
      Kim Haase
    8. DERBY-4680.diff
      15 kB
      Kim Haase

      Issue Links

        Activity

        Hide
        Kim Haase added a comment -

        Attaching DERBY-4680.diff, DERBY-4680.stat, and DERBY-4680.zip with changes to the following files:

        M src/devguide/cdevspecial28907.dita
        M src/ref/rrefsqlj42476.dita
        M src/ref/rrefsqlj42324.dita
        M src/ref/rrefsqljrevoke.dita
        M src/ref/rrefsqlj25228.dita
        M src/ref/rrefcreateprocedurestatement.dita
        M src/ref/rrefsistabs28114.dita
        M src/ref/rrefcallprocedure.dita
        M src/ref/rrefcreatefunctionstatement.dita

        This is just a first draft, with questions here and there. I was not quite sure what to do about the REVOKE information, so I just put it in more or less verbatim from the spec. I then repeated the information in the Dev Guide in the topic "Requirements for database-side JDBC routines using nested connections" under "Derby server-side programming." Hope that was more or less what you had in mind for the Dev Guide? If more is needed, let me know.

        Thanks in advance for reviewing!

        Show
        Kim Haase added a comment - Attaching DERBY-4680 .diff, DERBY-4680 .stat, and DERBY-4680 .zip with changes to the following files: M src/devguide/cdevspecial28907.dita M src/ref/rrefsqlj42476.dita M src/ref/rrefsqlj42324.dita M src/ref/rrefsqljrevoke.dita M src/ref/rrefsqlj25228.dita M src/ref/rrefcreateprocedurestatement.dita M src/ref/rrefsistabs28114.dita M src/ref/rrefcallprocedure.dita M src/ref/rrefcreatefunctionstatement.dita This is just a first draft, with questions here and there. I was not quite sure what to do about the REVOKE information, so I just put it in more or less verbatim from the spec. I then repeated the information in the Dev Guide in the topic "Requirements for database-side JDBC routines using nested connections" under "Derby server-side programming." Hope that was more or less what you had in mind for the Dev Guide? If more is needed, let me know. Thanks in advance for reviewing!
        Hide
        Dag H. Wanvik added a comment -

        Thanks, Kim. Great start. I'll have some feedback for you on the questions soon.

        Show
        Dag H. Wanvik added a comment - Thanks, Kim. Great start. I'll have some feedback for you on the questions soon.
        Hide
        Dag H. Wanvik added a comment -

        Hi Kim,

        • rrefcallprocedure.html
        • rrefcreatefunctionstatement.html
        • rrefcreateprocedurestatement.html

        Good. For these three it may be good to call out that the fact that the
        privileges include the right to set the current role to a role for
        which the definer has privileges. A priori, no role is set, i.e. even
        if the invoker has set a current role, the routine running with
        definer's rights has no current role set initially.

        • rrefsistabs28114.html

        The internals for AliasInfo is not public/exposed, so I suggest just
        leave this section unchanged (implementation detail).

        • rrefsqlj25228.html
        • rrefsqlj42324.html
        • rrefsqlj42476.html

        > If used within a function or procedure created with definer's rights,
        > USER and CURRENT_USER return the authorization identifier of the
        > definer, whereas SESSION_USER returns the authorization identifier of
        > the "first" caller, that is, the user of the top level
        > session. [Question: does this mean the invoker?]

        Yes, it's better to use "invoker" I guess. Also the quotes around first look bad.
        Maybe we can reword to something like:

        "When used outside stored routines, USER alias CURRENT_USER and
        SESSION_USER return the same value, i.e. the user identifier of
        the user which created the SQL session.

        SESSION_USER also always returns this value when used inside stored routines.

        USER/CURRENT_USER, however, when used within a routine defined with
        EXTERNAL SECURITY DEFINER, will return the user identifier of the user
        that owns the schema of the routine. This is usually the creating user
        although the database owner could be the creator as well."

        • rrefsqljrevoke.html
        • cdevspecial28907.html

        This verbiage was just a general comment on the fact that we can't track such dependencies.
        Actually, it applies for invoker's rights routines as well. I suggest that we just remove this altogether.
        Thanks!

        Show
        Dag H. Wanvik added a comment - Hi Kim, rrefcallprocedure.html rrefcreatefunctionstatement.html rrefcreateprocedurestatement.html Good. For these three it may be good to call out that the fact that the privileges include the right to set the current role to a role for which the definer has privileges. A priori, no role is set, i.e. even if the invoker has set a current role, the routine running with definer's rights has no current role set initially. rrefsistabs28114.html The internals for AliasInfo is not public/exposed, so I suggest just leave this section unchanged (implementation detail). rrefsqlj25228.html rrefsqlj42324.html rrefsqlj42476.html > If used within a function or procedure created with definer's rights, > USER and CURRENT_USER return the authorization identifier of the > definer, whereas SESSION_USER returns the authorization identifier of > the "first" caller, that is, the user of the top level > session. [Question: does this mean the invoker?] Yes, it's better to use "invoker" I guess. Also the quotes around first look bad. Maybe we can reword to something like: "When used outside stored routines, USER alias CURRENT_USER and SESSION_USER return the same value, i.e. the user identifier of the user which created the SQL session. SESSION_USER also always returns this value when used inside stored routines. USER/CURRENT_USER, however, when used within a routine defined with EXTERNAL SECURITY DEFINER, will return the user identifier of the user that owns the schema of the routine. This is usually the creating user although the database owner could be the creator as well." rrefsqljrevoke.html cdevspecial28907.html This verbiage was just a general comment on the fact that we can't track such dependencies. Actually, it applies for invoker's rights routines as well. I suggest that we just remove this altogether. Thanks!
        Hide
        Kim Haase added a comment -

        Thanks, Dag! That simplifies matters a good deal if some things don't actually need to be documented. I'm attaching DERBY-4680-2.diff, DERBY-4680-2.stat, and DERBY-4680-2.zip, with changes to just six files this time.

        M src/ref/rrefsqlj42476.dita
        M src/ref/rrefsqlj42324.dita
        M src/ref/rrefsqlj25228.dita
        M src/ref/rrefcreateprocedurestatement.dita
        M src/ref/rrefcallprocedure.dita
        M src/ref/rrefcreatefunctionstatement.dita

        Please let me know if more changes are needed.

        Show
        Kim Haase added a comment - Thanks, Dag! That simplifies matters a good deal if some things don't actually need to be documented. I'm attaching DERBY-4680 -2.diff, DERBY-4680 -2.stat, and DERBY-4680 -2.zip, with changes to just six files this time. M src/ref/rrefsqlj42476.dita M src/ref/rrefsqlj42324.dita M src/ref/rrefsqlj25228.dita M src/ref/rrefcreateprocedurestatement.dita M src/ref/rrefcallprocedure.dita M src/ref/rrefcreatefunctionstatement.dita Please let me know if more changes are needed.
        Hide
        Dag H. Wanvik added a comment -

        Thanks, Kim! This looks great now.
        One more thing: in the rrefcallprocedure.dita, we now spell out what happens to current schema and current role when running with definer's right. We are not so explicit about what happens when invoking a routine with invoker's rights (the default) and what happens on return.

        Maybe, to be clear, we should add a sentence or two about that here, too. E.g.

        "When a procedure with invoker's's rights is called, the current default schema, current role is unchanged initially within the procedure.
        Similarly, if SQL authorization mode is not enabled, the current default schema is unchanged initially within the procedure.

        When returning from the call, any changes made inside the procedure to default current schema (and current role if relevant) are reset (popped)."

        It also just struck me that the functions call documentation should also be updated with definer's rights, so we should probably duplicate the information in this section (replacing procedure with function) somwhere...?

        Show
        Dag H. Wanvik added a comment - Thanks, Kim! This looks great now. One more thing: in the rrefcallprocedure.dita, we now spell out what happens to current schema and current role when running with definer's right. We are not so explicit about what happens when invoking a routine with invoker's rights (the default) and what happens on return. Maybe, to be clear, we should add a sentence or two about that here, too. E.g. "When a procedure with invoker's's rights is called, the current default schema, current role is unchanged initially within the procedure. Similarly, if SQL authorization mode is not enabled, the current default schema is unchanged initially within the procedure. When returning from the call, any changes made inside the procedure to default current schema (and current role if relevant) are reset (popped)." It also just struck me that the functions call documentation should also be updated with definer's rights, so we should probably duplicate the information in this section (replacing procedure with function) somwhere...?
        Hide
        Kim Haase added a comment -

        Thanks, Dag! Attaching DERBY-4680-3.diff and DERBY-4680-3.zip, which I hope do what you want.

        The CALL PROCEDURE topic actually just repeats some of the language from the CREATE PROCEDURE topic, and there is no topic for functions equivalent to the CALL PROCEDURE topic because there's no specific statement to call them. So I added your new suggested language to both the CREATE PROCEDURE and CREATE FUNCTION topics and also transferred it to CALL PROCEDURE. So those are the three topics with changes in this patch.

        Hope this language is suitable? Please let me know if anything needs changing.

        Show
        Kim Haase added a comment - Thanks, Dag! Attaching DERBY-4680 -3.diff and DERBY-4680 -3.zip, which I hope do what you want. The CALL PROCEDURE topic actually just repeats some of the language from the CREATE PROCEDURE topic, and there is no topic for functions equivalent to the CALL PROCEDURE topic because there's no specific statement to call them. So I added your new suggested language to both the CREATE PROCEDURE and CREATE FUNCTION topics and also transferred it to CALL PROCEDURE. So those are the three topics with changes in this patch. Hope this language is suitable? Please let me know if anything needs changing.
        Hide
        Dag H. Wanvik added a comment -

        Thanks, Kim. I think this is fine now! +1 to commit.

        Nothing to do with this patch, but I noted that the following part of the section for CURRENT_USER (and similarly for USER and SESSION_USER) is slightly confusing:

        > Syntax
        >
        > CURRENT_USER
        >
        > VALUES CURRENT_USER

        The syntax is correctly stated as plain CURRENT_USER. However, it seems there is a missing header before "VALUES CURRENT_USER":
        This is an example of usage, not part of the syntax explanation. If you think it's worth it, we could fix that in a separate issue, perhaps.

        Show
        Dag H. Wanvik added a comment - Thanks, Kim. I think this is fine now! +1 to commit. Nothing to do with this patch, but I noted that the following part of the section for CURRENT_USER (and similarly for USER and SESSION_USER) is slightly confusing: > Syntax > > CURRENT_USER > > VALUES CURRENT_USER The syntax is correctly stated as plain CURRENT_USER. However, it seems there is a missing header before "VALUES CURRENT_USER": This is an example of usage, not part of the syntax explanation. If you think it's worth it, we could fix that in a separate issue, perhaps.
        Hide
        Kim Haase added a comment -

        Thanks, Dag!

        Committed patch DERBY-4680-3.diff to documentation trunk at revision 1021346.

        The problem of the missing "Example" or "Examples" titles is endemic – I think there are about 85 in the Reference Manual, and some in other books as well. I'll file an issue for this – Rick noticed the problem too. It's the sort of tedious job I enjoy doing when I have spare time ...

        Show
        Kim Haase added a comment - Thanks, Dag! Committed patch DERBY-4680 -3.diff to documentation trunk at revision 1021346. The problem of the missing "Example" or "Examples" titles is endemic – I think there are about 85 in the Reference Manual, and some in other books as well. I'll file an issue for this – Rick noticed the problem too. It's the sort of tedious job I enjoy doing when I have spare time ...
        Hide
        Dag H. Wanvik added a comment -

        Thanks, Kim! Closing this.

        Show
        Dag H. Wanvik added a comment - Thanks, Kim! Closing this.

          People

          • Assignee:
            Kim Haase
            Reporter:
            Dag H. Wanvik
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development