db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: Grant and Revoke ... DERBY-464...
Date Wed, 26 Oct 2005 19:39:11 GMT
Satheesh Bandaram wrote:
> Hi
> I just attached my proposal to enhance Derby by adding Grant and Revoke
> capability to DERBY-464
> <http://issues.apache.org/jira/browse/DERBY-464>. Hope this leads to
> many other enhancements to Derby in the access-control and security
> areas to make Derby much more capable in client-server configurations.

Looks great, thanks Satheesh, some minor comments on extracted portions.

> When a routine (function or procedure) is created you can specify
> whether the routine should execute with the permissions of the routine
> owner or those of the invoker. This done in the external-security-clause
> of function and procedure element lists. The syntax of
> external-security-clause is:
> /external-security-clause ::=/
> EXTERNAL SECURITY DEFINER means that the owner of the routine is the
> effective user as long as the routine executes. That is, the routine
> executes with the permissions of the owner (creator) of the routine and
> any objects created by the routine are owned by the owner of the
> routine. This is the default, as specified by SQL2003. EXTERNAL SECURITY
> INVOKER means that the routine executes with the permissions of the
> invoker of the routine.

The default is the opposite of the current behaviour, this probably
needs to be addressed in the upgrade section. Ie. after an existing
database is changed to sqlStandard, what is the setting of the
external-security-clause for existing routines?

Would using this clause cause an exception in the legacy mode?

>  All the built in
> functions and procedures have EXTERNAL SECURITY INVOKER. So, for
> instance a user cannot call SYSCS_EXPORT_TABLE to see tables on which he
> has no SELECT permission. 

Not sure that is true, or the required behaviour. It may be some of the
routines need to be EXTERNAL SECURITY DEFINED. Need to think about it
more, probably with an explicit list of all the builtin routines.

AT least Suresh's backup approach (and probably the existing approach)
will cause issues, with EXTERNAL SECURITY INVOKER, since the backup
bypasses the SQL layer. Just something to address.

>     Derby upgrade and migration

> An extant database may be switched from the legacy authorization model
> to the SQL2003 standard model. This is done by upgrading the database
> <http://mail-archives.apache.org/mod_mbox/db-derby-dev/200503.mbox/%3c423896FC.6070200@debrunners.com%3e>
> and changing derby.database.defaultConnectionMode property value to
> "sqlStandard". All tables and views will be owned by the database owner.

Rather than "All tables and views" I think you mean 'All database objects'.

> Until a GRANT statement is issued, only the table owner will have access
> to a table.

This matches an newly created database and populated database, correct?
I.e. there is nothing special about upgrade here.

> Security mode switching is performed using the
> operating under the legacy security model any user with fullAccess can
> call this procedure to switch the security mode to "sqlStandard". A
> database may not be reverted from the standard security mode to a legacy
> security mode.

The not reverting it may cause issues, especially if the default is
switched. Though I can see it is the preferred way.

I think you are also implicitly stating here that
derby.database.defaultConnectionMode becomes a property that can only be
set at the database level, ie. not as a system property or in
derby.properties. May need to think that through for existing applications.

> It may be good to switch the default connection mode to standard model
> and hence support grant/revoke by default in future releases. A scheme
> needs to be evolved to reduce any disruptions to existing users of Derby.

Agree that can be a separare project, though we may want to consider it
before 10.2, and if we do it then 10.2 becomes 11.0.

View raw message