db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: advice sought: introduction of new system table on trunk
Date Fri, 09 Nov 2007 16:28:51 GMT

Hi,

Kristian Waagan <Kristian.Waagan@Sun.COM> writes:

> INCITS/ISO/IEC 9075-11-2003
> Information technology - Database languages - SQL - Part 11:
> Information and Definition Schemas (SQL/Schemata)
>
> Seems the tables/views are defined by SQL queries, so it should be
> reasonably easy to do a quick first assessment of whether we have the
> required information handy or not.

Good question!  I have checked, and I believe the proposed catalog
patch (DERBY-3137) contains what's needed, cf. section 6.34
ROLE_AUTHORIZATION_DESCRIPTORS base table of 9075-11-2003:

(quote):
CREATE TABLE ROLE_AUTHORIZATION_DESCRIPTORS (
       ROLE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
       GRANTEE INFORMATION_SCHEMA.SQL_IDENTIFIER,
       GRANTOR INFORMATION_SCHEMA.SQL_IDENTIFIER,
       IS_GRANTABLE INFORMATION_SCHEMA.CHARACTER_DATA

       CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_IS_GRANTABLE_CHECK
       CHECK ( IS_GRANTABLE IN ( 'YES', 'NO' ) ),

       CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_PRIMARY_KEY
       PRIMARY KEY ( ROLE_NAME, GRANTEE ),

       CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_CHECK_ROLE_NAME
       CHECK ( ROLE_NAME IN ( SELECT AUTHORIZATION_NAME
       FROM AUTHORIZATIONS
       WHERE AUTHORIZATION_TYPE = 'ROLE' ) ),

       CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_FOREIGN_KEY_AUTHORIZATIONS_GRANTOR
       FOREIGN KEY ( GRANTOR )
       REFERENCES AUTHORIZATIONS,

       CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_FOREIGN_KEY_AUTHORIZATIONS_GRANTEE
       FOREIGN KEY ( GRANTEE )
       REFERENCES AUTHORIZATIONS
)

SYS.SYSROLES (see below) has merged in the role definitions, though,
by adding an extra column ISDEF in lieu of the AUTHORIZATIONS table of
9075-11-2003, since there is no catalog today for valid Derby users
either.  The definition always give rise to a role grant (to the
definer, dbo in our case), and that role grant carries the 'true'
value in the ISDEF column, thus serving also to identify the role
definitions.

If we later to decide add a catalog assembling users and roles
(probably a good idea), I think the present design does not preclude
that; the ISDEF column could then be just fall out of use.

The present catalog code patch maintains the constraints
shown, too.

SYSROLES presently  has these columns:

         ROLEID
         GRANTEE
         GRANTOR
         WITHADMINOPTION
         ISDEF

the four first of which map directly to TABLE ROLE_AUTHORIZATION_DESCRIPTORS.

Btw, wish we had those nice recursive views show, e.g. in ENABLED_ROLES.

Thanks,
Dag

Mime
View raw message