Re: Grant and Revoke, Part I ... DERBY-464...

[Satheesh Bandaram]

Francois Orsini wrote: Yes and I will be posting more details very soon. Great... Would love to see more details. If we are proposing combining both authorization models, why not go the whole way and say Grant/Revoke is always enabled in a 10.2 database? If applications want to keep their current authorization model, they don't need to use new fine-grained access control  allowed by grant/revoke. This preserves legacy model for them.  If Grant/Revoke is always enabled but there is no metadata (including legacy one), then I believe a user would not have access by default to anything except his/her own schema (objects). By default, when no legacy permissions are set, the default access for a database is full (read-write) access, which would be different for Grant/Revoke whereas explicit privileges have to be granted for a user to access a table that is not his/her - Again we're dealing with different semantics across modes which IMHO should not be mixed. This is a good point. Applications would see differences so probably not OK. I was trying to combine both authorization models without having to worry about when to switch the model and to remove ambiguities. 10.2 databases could use grant/revoke to get more control  over access to their objects. Hopefully Derby will also have system privileges and roles to complete this model at some point. This proposal removes the need to have another property, like 'derby.database.sqlAuthorization'. One advantage I see with this is that we don't need to handle the case of someone issuing Grant/Revoke with sqlAuthorization set to false and trying to find appropriate time to switch the authorization scheme. This case/scenario would indeed be very good for users to prepare and switch to sqlAuthorization when all the metadata is there - meaning they would have issued all the required Grant/Revoke statements to satisfy what they had with the legacy mode (*assuming* there is support for Roles and System privileges which is something I'm working on) - it is a nice way for people to migrate from legacy to grant/revoke IMO. This matches what Dan proposed as "option A", right? Once issue I see is how to handle EXTERNAL SECURITY clause in this combined authorization model. Current legacy databases have EXTERNAL SECURITY set to INVOKER, where as my proposal calls for changing this to DEFINER. This could be seen as changing the behavior of Derby without sufficient warning. We could address this by one of the following: Automatically moving existing routines to INVOKER security mode during database upgrade to 10.2 and making all new routine creation mandating specifying this security clause. (No default) Would help users to consider one or the other model. We could add a default for this clause to DEFINER (as specified by ANSI) later. Move existing routines to INVOKER during upgrade and by switching default behavior to DEFINER with sufficient warning messages to release notes and by raising SQL warning if a default mode of DEFINER is picked. Any other suggestions? My suggestion would be not to mix 2 different authorization modes unless we absolutely have to, if it does not impact performance and does not add too much complexity to the runtime permission checking logic. As a first phase for Roles and System privileges support (which are independent), am working on implementing what is required at a minimum to support what's there in legacy to be available with Grant/Revoke as well as adding critical and minimal System privileges that have to be supported in Derby. Sounds nice... Would love to see more details. Would this satisfy Dan's concerns? Satheesh It would be good to drive to a decision soon. I am making progress on second part of Grant/Revoke, implementing the permission scheme enforcement. Satheesh