Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3886

SQL roles: ij show enabled and settable roles

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.5.1.1
    • Component/s: SQL, Tools
    • Labels:
      None

      Description

      Rick suggested it would be nice to have something to allow inspection of enabled roles, cf the
      ENABLED_ROLES view of the information schema, SQL 2003, vol 11, section 5.29.

      I enclose patch suggestion for doing this via a new VTI table and syntactic sugar in ij:
      SYSCS_DIAG.ENABLED_ROLES and and ij command "show enabled_roles".
      Not for commit at this point, we may want to handle to general question of information schemata
      in another way. What say thee?

      1. enabled-roles.diff
        10 kB
        Dag H. Wanvik
      2. enabled-roles.stat
        0.4 kB
        Dag H. Wanvik
      3. enabled-roles-2.diff
        23 kB
        Dag H. Wanvik
      4. enabled-roles-2.stat
        1 kB
        Dag H. Wanvik
      5. enabled-roles-3.diff
        23 kB
        Dag H. Wanvik
      6. enabled-roles-3.stat
        1 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          dagw Dag H. Wanvik added a comment -

          Committed as svn 702266, closing.

          Show
          dagw Dag H. Wanvik added a comment - Committed as svn 702266, closing.
          Hide
          rhillegas Rick Hillegas added a comment -

          Thanks, Dag. Looks good. Cheers-Rick

          Show
          rhillegas Rick Hillegas added a comment - Thanks, Dag. Looks good. Cheers-Rick
          Hide
          dagw Dag H. Wanvik added a comment -

          Thanks, Rick, for the proposed simplification; I believe there is no difference,
          I found I could simplify the last part of the query in the same way.
          Uploading revision #3 with that fix.

          Show
          dagw Dag H. Wanvik added a comment - Thanks, Rick, for the proposed simplification; I believe there is no difference, I found I could simplify the last part of the query in the same way. Uploading revision #3 with that fix.
          Hide
          rhillegas Rick Hillegas added a comment -

          Hi Dag. What's the difference between the SQL in the patch:

          select roleid from sys.sysroles s, (values current_user) c(usr)
          where s.grantee = c.usr or s.grantee = 'PUBLIC'

          and the following query:

          select roleid from sys.sysroles s
          where s.grantee = current_user or s.grantee = 'PUBLIC'

          Thanks!

          Show
          rhillegas Rick Hillegas added a comment - Hi Dag. What's the difference between the SQL in the patch: select roleid from sys.sysroles s, (values current_user) c(usr) where s.grantee = c.usr or s.grantee = 'PUBLIC' and the following query: select roleid from sys.sysroles s where s.grantee = current_user or s.grantee = 'PUBLIC' Thanks!
          Hide
          dagw Dag H. Wanvik added a comment -

          Thanks for your reply on this approach, Rick. Unless others have
          objections I will commit this patch in a couple of days, then.

          Show
          dagw Dag H. Wanvik added a comment - Thanks for your reply on this approach, Rick. Unless others have objections I will commit this patch in a couple of days, then.
          Hide
          dagw Dag H. Wanvik added a comment -

          Uploading version 2 of this patch which:

          • took Rick's two suggestions
          • added "show settable_roles" command to ij (impl. as a SQL query)
          • modified the tools/ij_show_roles script by spitting it into
            two: ij_show_roles_dbo and ij_show_roles_usr to
            be able to test the settable_roles (which typically differ for the data base
            owner and an ordinary db user)
          • added help text for the ij roles show commands

          Regressions ran ok.

          Show
          dagw Dag H. Wanvik added a comment - Uploading version 2 of this patch which: took Rick's two suggestions added "show settable_roles" command to ij (impl. as a SQL query) modified the tools/ij_show_roles script by spitting it into two: ij_show_roles_dbo and ij_show_roles_usr to be able to test the settable_roles (which typically differ for the data base owner and an ordinary db user) added help text for the ij roles show commands Regressions ran ok.
          Hide
          rhillegas Rick Hillegas added a comment -

          Hi Dag,

          I think this is a good use of vtis. SYSCS_DIAG sounds like a good home for your vti. Your vti seems to be compatible with the charter of that schema as described in the Reference Guide: "Derby provides a set of system table expressions which you can use to obtain diagnostic information about the state of the database and about the database sessions."

          Show
          rhillegas Rick Hillegas added a comment - Hi Dag, I think this is a good use of vtis. SYSCS_DIAG sounds like a good home for your vti. Your vti seems to be compatible with the charter of that schema as described in the Reference Guide: "Derby provides a set of system table expressions which you can use to obtain diagnostic information about the state of the database and about the database sessions."
          Hide
          dagw Dag H. Wanvik added a comment -

          Thanks for looking at this, Rick!

          1) I was under the impression I had to provide a (dummy)
          implementation of VTICosting, thank for letting me know I don't.

          2) It will perhaps be good to add an ORDER BY clause to the
          implementation of ij "show enabled_roles". Presently, the current role
          is returned first; the rest of the rows do not have a defined order.

          Another thing; the present "show role" command shows all roles defined
          in the database (similar to show tables etc). Perhaps it would be
          useful to have a command to show the roles which are settable for the
          session (union of those granted to the current user and to PUBLIC)? I don't
          find that information as a predefined view in the "definition schema"
          of volume 11, though.

          But there are many other views pertaining to roles in the definition
          schema: ROLE_COLUMN_GRANTS, ROLE_ROUTINE_GRANTS, ROLE_TABLE_GRANTS,
          ROLE_TABLE_METHOD_GRANTS, ROLE_USAGE_GRANTS, ROLE_UDT_GRANTS,
          APPLICABLE_ROLES, and ADMINISTRABLE_ROLE_AUTHORIZATIONS, and the
          *_PRIVILEGES views also consider the current role.

          I am not sure I have an itch to add all of these now, but it would be nice
          to get a feeling for if the approach of adding such views as VTIs is
          sound. Is the schema SYSCS_DIAG ok or do we need a new one?

          Show
          dagw Dag H. Wanvik added a comment - Thanks for looking at this, Rick! 1) I was under the impression I had to provide a (dummy) implementation of VTICosting, thank for letting me know I don't. 2) It will perhaps be good to add an ORDER BY clause to the implementation of ij "show enabled_roles". Presently, the current role is returned first; the rest of the rows do not have a defined order. Another thing; the present "show role" command shows all roles defined in the database (similar to show tables etc). Perhaps it would be useful to have a command to show the roles which are settable for the session (union of those granted to the current user and to PUBLIC)? I don't find that information as a predefined view in the "definition schema" of volume 11, though. But there are many other views pertaining to roles in the definition schema: ROLE_COLUMN_GRANTS, ROLE_ROUTINE_GRANTS, ROLE_TABLE_GRANTS, ROLE_TABLE_METHOD_GRANTS, ROLE_USAGE_GRANTS, ROLE_UDT_GRANTS, APPLICABLE_ROLES, and ADMINISTRABLE_ROLE_AUTHORIZATIONS, and the *_PRIVILEGES views also consider the current role. I am not sure I have an itch to add all of these now, but it would be nice to get a feeling for if the approach of adding such views as VTIs is sound. Is the schema SYSCS_DIAG ok or do we need a new one?
          Hide
          rhillegas Rick Hillegas added a comment -

          Hey Dag, this is pretty slick. Some small questions:

          1) Does the VTI need to implement VTICosting? I think that getEstimatedCostPerInstantiation() is probably returning a number that isn't tuned to any particular machine. I don't think that the optimizer has a lot of choice here and it doesn't seem to me that the VTICosting implementation will help the optimizer pick a better plan.

          2) Will the closure iterator return the roles in some reasonable sort order? For someone just selecting from the VTI it wouldn't matter because they could just slap an ORDER BY on the SELECT. But once the SELECT is hidden inside a system procedure, the user can't inject an ORDER BY clause.

          These are just nits. Looks like a great approach.

          Show
          rhillegas Rick Hillegas added a comment - Hey Dag, this is pretty slick. Some small questions: 1) Does the VTI need to implement VTICosting? I think that getEstimatedCostPerInstantiation() is probably returning a number that isn't tuned to any particular machine. I don't think that the optimizer has a lot of choice here and it doesn't seem to me that the VTICosting implementation will help the optimizer pick a better plan. 2) Will the closure iterator return the roles in some reasonable sort order? For someone just selecting from the VTI it wouldn't matter because they could just slap an ORDER BY on the SELECT. But once the SELECT is hidden inside a system procedure, the user can't inject an ORDER BY clause. These are just nits. Looks like a great approach.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development