Re: Grant and Revoke ... DERBY-464...

[Satheesh Bandaram]

Thanks for the quick review and great comments... More below... Daniel John Debrunner wrote: <> 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? OK. I will think about this some more and add more details... It may be best to leave the external-security-clause to INVOKER for existing routines after a database being changed to sqlStandard mode... Otherwise, their behavior could change by switching the 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. I will make a list to see if any of them needs different mode. I haven't thought of all of them, for sure. :-) 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. Right... Security mode switching is performed using the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY procedure. In a database 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. Good point. Needs more work here... 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. Right... If we do want to make sqlStandard mode the default, it might need a major version number change. Let us see how the implementation progresses... Satheesh