You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Francois Orsini <fr...@gmail.com> on 2005/12/22 11:08:27 UTC
Re: Grant and Revoke, Part I ... DERBY-464...
Hi Satheesh,
Regarding the issues you mentioned:
> 1) Should Derby support upgrading a 10.1 database to 10.2 directly into
'sqlStandard' mode (optionally) or not?
I would say no - as this is a new feature of 10.2, we should let the user
configure it on its own after upgrade. Also what does it mean and incur to
upgrade from legacy to 'sqlStandard' mode automatically (say the option has
been selected by user)...
This would mean adding entries to the new system catalogs to match privilege
settings defined in the legacy metadata...In Derby 10.1, a user can select
data from a table that is in a different schema; with traditional
grant/revoke (sqlStandard) I don't think a user can select a table in a
different schema if he is not granted access first and he is not the owner -
hence when sqlStandard is turned on, users would only have access to their
own objects (i.e. tables) in their schema unless system catalogs have been
added entries corresponding to granted privileges coming out of 'legacy'
mode (default is 'fullAccess') - say during upgrade, there are permissions
defined such as 'derby.fullAccessUsers=sa,FRED,mary', will this get
converted into respective sqlStandard mode metadata - could get interesting?
> Would it be sufficient if 10.2 upgrade mechanism moves a legacy database
into 10.2 keeping the current authorization model and if users need to use
'sqlStandard' mode, they have to set it after upgrade?
I would vote +1 for that ;-) (let's keep it simple)
> 2) Current proposal doesn't handle Synonyms. I am wondering if Synonyms
should follow authorization model like views...
> Any opinions?
Sounds like it - views can have synonyms as well.
>3) Dan raised an important question about whether defaultConnectionMode
should be made a database-only property
> and without affecting existing applications. I am currently thinking if
defaultConnectionMode is set to sqlStandard \
> as a database property, it would override system-wide property for that
database only. Once defaultConnectionMode is
> set to sqlStandard as a database property, it cann't be changed to
legacy modes or removed.
What is the rationale for making defaultConnectionMode property a
database-only one? Why not leaving both ways (system & database)?
> Once defaultConnectionMode is set to sqlStandard as a database property,
it can't be changed to legacy modes or removed.
Why? is this technical or pure deliberate choice? (not saying it's wrong,
just curious)
Thanks,
--francois
On 12/2/05, Satheesh Bandaram < satheesh@sourcery.org> wrote:
>
> I would like to start contributing code that implements Grant/Revoke in
> batches. My first patch would include DDL support for both Grant/Revoke.
> This patch has passed 'derbyALL' suite and contains implementation for the
> following. Let me know if anyone would like to join completing the rest. Lot
> more needs to be done. :-(
>
> 1. Grant/Revoke DDL parsing and execution
> 2. Addition of several new system tables to hold the system
> metadata. I will update my spec to include detailed schema for new system
> tables, so that they can be included in 10.2 documentation.
> 3. Enhancing the syntax for routine creation to include
> external-security clause
> 4. Very simple tests to cover only the DDL. I would be expanding on
> the testing in the later submissions, including a JUnit test suite.
> 5. Grant/Revoke DDL is only supported if
> derby.database.defaultConnectionMode property is set to
> 'sqlStandard'.
> 6. I also have some implementation for permission checking for DMLs
> to test what has been implemented so far. But I will submit that in follow
> up patches.
>
> Most of the code will not be visible to users who don't set 'sqlStandard'
> property and hence shouldn't cause any issues.
>
> I am also debating following issues:
>
> 1. Should Derby support upgrading a 10.1 database to 10.2 directly
> into 'sqlStandard' mode (optionally) or not? Would it be sufficient if
> 10.2 upgrade mechanism moves a legacy database into 10.2 keeping the
> current authorization model and if users need to use 'sqlStandard' mode,
> they have to set it after upgrade?
> 2. Current proposal doesn't handle Synonyms. I am wondering if
> Synonyms should follow authorization model like views... Any opinions?
> 3. Dan raised an important question about whether
> defaultConnectionMode should be made a database-only property and without
> affecting existing applications. I am currently thinking if
> defaultConnectionMode is set to sqlStandard as a database property, it would
> override system-wide property for that database only. Once
> defaultConnectionMode is set to sqlStandard as a database property, it
> cann't be changed to legacy modes or removed.
>
> Satheesh
>
> 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.
>
> I am also attaching the spec here and invite anyone interested in joining
> the development. Francois had expressed interest sometime ago.
>
> Satheesh
>
>
> ------------------------------
>
> Grant and Revoke in Derby Satheesh Bandaram
> 24 Oct 2005 Introduction Originally Cloudscape/Derby used a very simple
> permissions scheme, which is quite suitable for an embedded database system.
> Embedded database users typically don't see Derby database directly and
> instead talk to applications that embeds Derby. So Derby left most of the
> access control work to applications. Under this scheme, Derby limits
> database access on database or system basis. A user can be granted *full*,
> *read-only*, or *no access*. This is less suitable for general purpose
> client-server database configurations. When end users or diverse
> applications can issue SQL commands directly against the database, Derby
> must provide more precise mechanisms to limit who can do what with the
> database.
>
> I propose to implement a subset of SQL2003 access control that deals with
> tables, views, procedures, and functions to start with. Each database will
> have the option of operating using the legacy Derby access control system or
> a SQL2003 compatible access control system. This paper describes a proposal
> to introduce Derby's SQL2003 compatible access control system. There are
> many further enhancements possible in access control and security areas. My
> current itch is to limit the scope to what is proposed here.
>
> I wish to propose a staged development plan with following tasks as
> mentioned in DERBY-464 <http://issues.apache.org/jira/browse/DERBY-464>: (
> http://issues.apache.org/jira/browse/DERBY-464)
>
> 1. Submit support for DDL. This would implement grant and revoke
> statements and recording of permissions in system tables.
> 2. Add run-time support to enforce permission checking.
> 3. Address upgrade, migration and metadata changes.
>
> Grant and Revoke Statements The GRANT statement is used to grant
> permissions to users. The REVOKE statement is used to revoke permissions.
>
> *grant-statement ::= table-grant-statement*
> * | routine-grant-statement*
>
> *table-grant-statement ::=*
> * *GRANT* table-privileges *TO* grantees*
>
> *routine-grant-statement ::=*
> * *GRANT EXECUTE ON* routine *TO* grantees*
>
> *revoke-statement ::= table-revoke-statement*
> * | routine-revoke-statement*
>
> *table-revoke-statement ::=*
> * *REVOKE* table-privileges *FROM* grantee, ...*
>
> *routine-revoke-statement ::=*
> * *REVOKE* *EXECUTE ON* routine *FROM* grantee, ... *RESTRICT
>
> *table-privileges ::= table-action* ON [TABLE] *table-or-view-name*
>
> *grantees ::= { *PUBLIC* | user-identifier,... }*
>
> *table-action ::= *ALL PRIVILEGES *| action, ...*
>
> *action ::= *SELECT* [ ( privilege-column-list ) ]*
> * | *DELETE
> * | *INSERT
> * | *UPDATE* [ ( privilege-column-list ) ]*
> * | *REFERENCES* [ ( privilege-column-list ) ]*
> * | *TRIGGER
>
> *routine ::= { *FUNCTION* | *PROCEDURE* } routine-designator*
>
> *routine-designator ::= qualified-name [ signature ]*
>
> *signature ::= ( data-type, ... )*
>
> Some examples:
>
> GRANT SELECT, update(description) ON t TO maria,harry
> GRANT SELECT ON TABLE s.v to PUBLIC
> GRANT EXECUTE ON PROCEDURE p TO george
> REVOKE update(description) ON t FROM maria
>
> The table *select* privilege is permission to perform a select on the
> named table or view. If there is a column list then the permission is only
> on those columns. If there is no column list then the select privilege is on
> all columns in the table.
>
> The table *references* privilege is permission to create a foreign key
> reference to the named table. If there is a column list then the permission
> is only on foreign key references to the named columns.
>
> The table *trigger* privilege is permission to create a trigger on the
> named table.
>
> When a table, view, function, or procedure is created its owner (creator)
> has full privileges on it. No other user has any privileges on it until the
> owner grants privileges.
>
> Privileges may be granted to specific users or to everyone: all current
> and future users. The word "PUBLIC" denotes everyone.
>
> Privileges granted to PUBLIC and to individual users are independent.
> Suppose SELECT privilege on table t was granted to both PUBLIC and harry. If
> SELECT privilege is revoked from harry, harry will still be able to access
> table t; he uses the PUBLIC privilege.
>
> The REVOKE statement revokes privileges. Revoking a privilege without
> specifying a column list revokes the privilege for all columns.
>
> RESTRICT is mandatory with routine revoke statements. That means that
> execute permission on a function may not be revoked if that function is used
> in a view, trigger, or constraint, and permission is being revoked from the
> owner of the view, trigger, or constraint.
>
> Only the owner (creator) of an object can grant or revoke privileges on
> that object.
> Permission Checking Permissions are checked when a statement is executed,
> not when it is prepared. This allows statement cache to share statements
> among different users. Views, Triggers, and Constraints Views, triggers,
> and constraints operate with the permissions of the owner of the view,
> trigger, or constraint.
>
> For example, consider the following view definition created by user jane
>
>
> CREATE VIEW s.v(vc1,vc2,vc3)
> AS SELECT t1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE
> t2.c2 = 5
>
> Jane needs the following permissions in order to create the view:
>
> - ownership of schema s (so that she can create something in it),
> - ownership of table t1 (so that she can allow others to see columns
> in it),
> - select permission on t2.c1 and t2.c1, and
> - execute permission on f.
>
> When the view is created only *jane* has select permission on it. Jane can
> grant select permission on any or all of the columns of s.v to anyone,
> even to users who do not have select permission t1 or t2 or execute
> permission on f. Suppose *jane* grants select permission on s.v to *harry*.
> When Derby executes a select on s.v on behalf of *harry*, Derby only
> checks that *harry* has select permission on s.v; it does not check that *
> harry* has select permission on t1, or t2 or execute permission on f.
>
> Similarly with triggers and constraints: a trigger or constraint may
> operate on columns for which the current user does not have the appropriate
> permissions. It is only required that the owner of the trigger or constraint
> have the appropriate permissions.
>
> When a view, trigger, or constraint is created Derby checks that the owner
> has the required permissions, throwing an SQLException if not. If any of the
> required permissions are later revoked then the view, trigger, or constraint
> is dropped as part of the REVOKE statement.
> Current User The permission system relies on Derby authentication to
> establish the identity of the current user. Permission checking is of little
> value unless Derby authentication is turned on. By default, Derby's
> authentication is OFF and can be turned ON by setting
> derby.connection.requireAuthentication to TRUE.
>
> 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 | EXTERNAL SECURITY INVOKER ]
>
> 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.
>
> For example:
>
> CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER,
> IN S_YEAR INTEGER,
> OUT TOTAL DECIMAL(10,2))
> PARAMETER STYLE JAVA
> READS SQL DATA
> LANGUAGE JAVA
> EXTERNAL NAME 'com.acme.sales.calculateRevenueByMonth'
> EXTERNAL SECURITY INVOKER
>
> This specifies that procedure sales.total_revenue can only read columns
> that the invoker can read directly. If instead the definition of
> sales.total_revenue used EXTERNAL SECURITY DEFINER, or it did not have an
> external security clause, then the procedure can only read columns that the
> creator of sales.total_revenue is permitted to read. It then may be able
> to access data that the invoker of sales.total_revenue is not permitted to
> read directly.
> Table, View, Trigger, and Schema Creation Permissions A table may only be
> created or dropped by the owner of the table's schema. Table creation
> permission is not grantable. (This is the SQL2003 spec). Only the owner of a
> table may create or drop indices or constraints on the table. Views and
> triggers may only be created or dropped by the owner of the view or
> trigger's schema.
>
> When a schema is created the schema owner is defined using the
> authorization clause of the CREATE SCHEMA statement:
>
> *create-schema-statement ::=*
> CREATE SCHEMA *schema-name*
> | CREATE SCHEMA *schema-name* AUTHORIZATION *user-name*
> | CREATE SCHEMA AUTHORIZATION *user-name*
>
> The first form creates a new schema that is owned by the current effective
> user. The second form creates a schema owned by the named user. The final
> form creates a new schema that is owned by the named user and that has the
> same name as its owner.
>
> The owner of the database may create any schema with any owner
> (authorization). Other users may only create schemas whose name is the same
> as their user name and that are owned by the user. For example, if user
> angelica does not own the database then she can only create a schema with
> name and authorization angelica.
>
> A schema may only be dropped by its owner or the owner of the database.
> System Schemata, Functions, Procedures All of the built-in schemata (SYS,
> SYSCAT, APP, etc) are owned by the owner of the database. For databases
> created under the SQL standard security model, the owner is the user who
> opened the connection used to create the database. For databases created
> under the old Derby security model and switched to the standard model the
> owner is the owner of the connection when the security model was changed to
> the standard model.
>
> The standard builtin functions, ABS, CURRENT_USER, TRIM, etc are treated
> differently than the builtin functions and procedures in the SYSCS_UTIL
> schema. When a database is created all users have execute permission on the
> standard builtin functions. This permission cannot be revoked. However when
> a database is created only the database owner has execute permission on the
> SYSCS_UTIL functions and procedures. So initially only the owner of the
> database may execute Derby system functions and procedures such as
> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY and SYSCS_UTIL.SYSCS_EXPORT_TABLE.
> The owner of the database may grant execute permission on these routines to
> other users. 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. DERBY-475<http://issues.apache.org/jira/browse/DERBY-475>has added a new table based mechanism to define functions in SYSFUN schema.
> These will also be treated as builtin functions for the permission scheme.
>
> One must be particularly careful about granting execute permission on
> SYSCS_BACKUP_DATABASE, SYSCS_EXPORT_TABLE, SYSCS_EXPORT_QUERY,
> SYSCS_IMPORT_TABLE, and SYSCS_IMPORT_DATA because can read or write files.
> The operating system will let them read or write any file accessible to the
> user who started the JVM. The operating system does not know anything about
> Derby connections or who Derby thinks the current user is.
>
> One should also be careful about granting execute permission on
> SYSCS_COMPRESS_TABLE, SYSCS_FREEZE_DATABASE, SYSCS_UNFREEZE_DATABASE, and
> SYSCS_SET_DATABASE_PROPERTY. SYSCS_COMPRESS_TABLE and SYSCS_FREEZE_DATABASE
> lock up the database or part of it for some time.
> SYSCS_SET_DATABASE_PROPERTY affects the whole database.
> Derby upgrade and migration Derby 10.1 authorization is on a database
> basis. A user can be granted full (read/write), read-only, or no access to a
> database. This is done through Derby properties. The
> derby.database.defaultConnectionMode property specifies the default access
> permission. Its value must be "noAccess", "readOnlyAccess", or "fullAccess"
> (case insensitive). The derby.database.fullAccessUsers and
> derby.database.readOnlyAccessUsers properties specify lists of users with
> full or read-only access. The default for
> derby.database.defaultConnectionMode is "fullAccess", so if no
> authorization properties are specified then all users have full access. This
> is incompatible with SQL2003.
>
> I propose to add a new derby.database.defaultConnectionMode property value
> to Derby, "sqlStandard". If the derby.database.defaultConnectionModeproperty value for a database is "sqlStandard" then authorizations for the
> database will follow the SQL standard model described in this document. If
> the value is "noAccess", "readOnlyAccess", "fullAccess", or null then the
> Derby legacy authorization model will be used. GRANT and REVOKE statements
> will raise an error when run in a database using the legacy authorization
> model.
>
> 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. Until a GRANT
> statement is issued, only the table owner will have access to a table.
>
> 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.
>
> 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.
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
Right - at least you get a choice...
On 12/22/05, Daniel John Debrunner <dj...@debrunners.com> wrote:
>
> Francois Orsini wrote:
>
>
> > >3) Dan raised an important question about whether
> > defaultConnectionMode should be made a database-only property
> > > and without affecting existing applications. I am currently thinking
> > if defaultConnectionMode is set to sqlStandard \
> > > as a database property, it would override system-wide property for
> > that database only. Once defaultConnectionMode is
> > > set to sqlStandard as a database property, it cann't be changed to
> > legacy modes or removed.
> >
> > What is the rationale for making defaultConnectionMode property a
> > database-only one? Why not leaving both ways (system & database)?
>
> I'm not sure about this, I can't find what Satheesh is refering to when
> he said 'Dan raised an important question ...'.
>
> I found one comment by me in the thread where I was talking about system
> properties in general.
>
> Databases do have an existing way to override system properties, by
> setting the database property derby.database.propertiesOnly
>
> http://db.apache.org/derby/docs/10.1/tuning/rtunproper24390.html
>
> Dan.
>
>
>
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
I did not pick 'ansiAuthMode' because one could easily confuse 'Auth' for
Authentication instead of Authorization (these are 2 different beasts) -
hence why I leaned towards 'ansiAuthoMode' ;)
On 1/10/06, David W. Van Couvering <Da...@sun.com> wrote:
>
> ansiAuthMode (not AuthoMode) sounds good to me, I agree that there is a
> false sense of security around the term secureMode. How secure is
> secure? And this is just about authentication and authorization,
> necessary but not necessarily sufficient in terms of security.
>
> David
>
> Francois Orsini wrote:
> > Agreed - I have had the same reserve but could not really come up with a
> > better name ;)
> >
> > Although I was thinking of 'ansiAuthorizationMode' (for ANSI
> > authorization mode) or 'ansiAuthoMode'
> >
> > On 1/10/06, *Daniel John Debrunner* < djd@apache.org
> > <ma...@apache.org>> wrote:
> >
> >
> > I'm still thinking about this 'secureMode' approach and the
> interaction
> > with the existing authentication model. One issue I do have is the
> name
> > of the attribute, 'secureMode'. I don't believe that the current
> > grant/revoke syntax makes Derby completely secure, thus this
> attribute
> > may mislead people. Note sure I have a better name though. :-(
> >
> > Dan.
> >
> >
>
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
ansiAuthMode (not AuthoMode) sounds good to me, I agree that there is a
false sense of security around the term secureMode. How secure is
secure? And this is just about authentication and authorization,
necessary but not necessarily sufficient in terms of security.
David
Francois Orsini wrote:
> Agreed - I have had the same reserve but could not really come up with a
> better name ;)
>
> Although I was thinking of 'ansiAuthorizationMode' (for ANSI
> authorization mode) or 'ansiAuthoMode'
>
> On 1/10/06, *Daniel John Debrunner* < djd@apache.org
> <ma...@apache.org>> wrote:
>
>
> I'm still thinking about this 'secureMode' approach and the interaction
> with the existing authentication model. One issue I do have is the name
> of the attribute, 'secureMode'. I don't believe that the current
> grant/revoke syntax makes Derby completely secure, thus this attribute
> may mislead people. Note sure I have a better name though. :-(
>
> Dan.
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
Agreed - I have had the same reserve but could not really come up with a
better name ;)
Although I was thinking of 'ansiAuthorizationMode' (for ANSI authorization
mode) or 'ansiAuthoMode'
On 1/10/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
>
> I'm still thinking about this 'secureMode' approach and the interaction
> with the existing authentication model. One issue I do have is the name
> of the attribute, 'secureMode'. I don't believe that the current
> grant/revoke syntax makes Derby completely secure, thus this attribute
> may mislead people. Note sure I have a better name though. :-(
>
> Dan.
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by John Embretsen <Jo...@sun.com>.
Daniel John Debrunner wrote:
> I'm still thinking about this 'secureMode' approach and the interaction
> with the existing authentication model. One issue I do have is the name
> of the attribute, 'secureMode'. I don't believe that the current
> grant/revoke syntax makes Derby completely secure, thus this attribute
> may mislead people. Note sure I have a better name though. :-(
>
> Dan.
I agree, a property/attribute called "secureMode" will most likely
become quite misleading in the long run, as there are other dimensions
to derby security than just GRANT/REVOKE.
I have not followed this discussion closely, but unless the property
values have to be "true" / "false", I propose something like this:
Property/attribute name: "sqlAuth" (or "sqlAuthorization", just
"authorization", or similar).
Legal values: "sqlStandard", "derbyLegacy" (or whatever you see fit).
--
John
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
On 1/16/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
>
> 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?
Yes it does.
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:
> >
> > 1. 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.
> > 2. 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.
> > 3. 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?
Yes it would indeed.
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
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
Comments inlined.
On 1/16/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
>
> Daniel John Debrunner wrote:
>
> Satheesh Bandaram wrote
>
> I think mixing both will lead to confusion to users many already
> familiar with the ansi subset model being proposed. This will also
> create hurdles as we expand authorization scheme to support roles and
> "system privileges" as Francois calls them and other security capabilities.
>
> I'm more proposing this to deal with existing Derby applications and
> finding an easy way to bring them into the new world of grant revoke.
>
> You do raise good points, as usual. I see your argument for ease of
> migration as the most compelling reason to avoid two different security
> models. Since Francois said he is working on adding system privileges and/or
> roles, hopefully your other concerns would be met at some point.
Yes and I will be posting more details very soon.
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.
Combining both modes sounds interesting as long as it does not impact
runtime performance by much (since permission checks would have to be done
in the 2 realms) and it seems to me that runtime permission checking would
get more complicated which we should try to avoid as it is in the main
compilation/execution path - permission resolution check would be more
complicated IMO - I understand the motivation for bringing them to
Grant/Revoke mode but it can also be confusing for users to mix both modes (
i.e. different semantics and syntax being mixed) - 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.
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.
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:
>
> 1. 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.
> 2. 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.
> 3. 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.
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
>
> Users familiar with the ansi subset model would just use that, no need
> to get involved with the defaultConnectionModel. Though until roles and
> system privileges is supported, they might need to to ensure a secure
> system. I haven't seen any proposal on these roles or system privileges
> so I'm looking at how secure Derby will be in its next release given
> what has been proposed and is being worked on. If we have a release
> about 6 months from the last one, it will be around March. I think
> someone was going to set up a wiki page with what "10.3" would include,
> though that hasn't happened yet.
>
> Dan.
>
>
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Daniel John Debrunner <dj...@apache.org>.
Francois Orsini wrote:
> Comments inlined:
>
> On 1/10/06, *Daniel John Debrunner* <djd@apache.org
> <ma...@apache.org>> wrote:
>
>
> I wonder if we should look at grant/revoke augmenting the existing
> authorization model instead of replacing it.
>
>
> Well, it is not completely replaced since legacy would still be
> supported (until sqlStandard is set explicitly)
Yes, the code is still there, but the proposed model is the application
chooses existing mode or grant revoke. Thus if grant revoke is used the
existing authorization is replaced.
Dan.
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
Comments inlined:
On 1/10/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
>
> I wonder if we should look at grant/revoke augmenting the existing
> authorization model instead of replacing it.
Well, it is not completely replaced since legacy would still be supported
(until sqlStandard is set explicitly)
The existing authorization functionality has:
>
> - disallow a user
> - allow a user read-only
> - allow a user full-access
- Disallow a user to access a database or system can be done via a REVOKE
CONNECT statement - it is not part of Satheesh's charter but this is
something am looking at as focusing more on System privileges.
- User read-only can be set via a 'read-only' role (known practice).
- User full-access can be set via a 'full-access' role (again this is where
ROLE(s) shine)
Grant/revoke does not replace this functionality, it could be seen as
> adding fine-grain control to the read only or read-write access.
>
> Then it seems to come down to how does an application selects the old
> model of coarse grain control versus fine grain (grant/revoke).
>
> One way is a property like
>
> derby.database.sqlAuthorization={true|false}
> (derby.language.sqlAuthorization ??)
>
> I would like to be able to set a property in derby.properties that made
> the default mode for new databases to be grant/revoke.
Sounds good..
I think the JDBC attribute (secureMode) is possibly a security risk, if
> upgrade to grant/revoke mode is allowed, it seems to allow any remote
> user to make that change. Which could allow a denial of service attack
> on existing applications. It may be that such an attack could also be
> made through setting a database property, but I don't think that's a
> justification for adding a new (potential) hole.
Unless it is restricted to the database owner - but I agree this is an
issue. I mean without Grant & Revoke *enabled*, there would not be much
system privilege check - kinda being able to shutdown a database or the
whole system as of today.
Another issue is the potential number of security related properties and
> ensuring they are set correctly. If
> derby.database.sqlAuthorization=false and a GRANT statement was
> executed, then it seems there are three choices:
>
> A - allow the GRANT to succeed, but it is not enforced (since
> derby.database.sqlAuthorization=false)
At least a warning should be issued to the user as this last one might not
have realized that the property had not been set (looking at the other way
around) - still the metadata would be updated.
B - throw an exception (warning?) on execution indicating
> derby.database.sqlAuthorization=false and thus grant statements are not
> applicable
This could be restrictive - We could allow users to set-up grant & revoke
privileges and then turning on the sqlStandard authorization mode at a later
stage. Of course there could be some issues against existing and running
queries but this has to be verified based on the current grant&revoke
design.
C - Change the database to grant/revoke mode
I don't think that an automatic change of authorization mode would be very
welcome by users. Especially if the database is running with users against
it.
Maybe B would be the case for a user that was not the database owner
> (creator) and C would only be allowed by the database creator.
>
> Also similar exceptions or warnings are probably needed if grant/revoke
> is enabled but no authentication is set up.
Warnings would be good...
Dan.
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
The idea and goal is not to affect existing applications if they upgrade to
10.2. Others can correct me if this is not the case.
On 1/16/06, Kathey Marsden <km...@sbcglobal.net> wrote:
>
> Satheesh Bandaram wrote:
>
> >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.
> >
> >
> I have not followed this thread at all as I thought it was all
> pertaining to new functionality. Are there changes being proposed that
> might affect existing applications if they upgrade to 10.2?
>
> Thanks
>
> Kathey
>
>
Re: Grant and Revoke, Part II ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
Hi Satheesh,
Please find my comments enclosed below:
On 2/8/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
[...]
>
> I am not sure if previous discussion about migrating a legacy mode database
> to Grant Revoke model was finalized. It seems there were two thoughts:
>
>
> 1. Keep authorization models separate. Legacy mode database can be migrated to
> sqlStandard model by connecting with a URL property. (sqlAuthorization=true)
How to restrict anyone to specify this new URL property? This is a
system privilege by itself that would need to be granted unless it is
a user with an 'ADMIN' role (which we don't have yet). Are we also
going to allow users to move from legacy to grant/revoke during the
database upgrade phase? I thought so.
> 2. Dan proposed combining both models with Grant and Revoke capability being
> seen as adding fine-grain access control on top of current model. While this
> proposal doesn't impact Grant and Revoke work being done currently by much,
> it may have implications on some future work. (like system privileges) This
> does make it easier for existing databases to adapt new capabilities.
I remember we discussed this - This sounds good if Ansi ROLES are not
there but when they are, then equivalent roles for the various legacy
authorization modes can be defined to act the same way. For instance,
a role for a 'read-only' authorization identifier (i.e. user/role) can
be defined to act the same as legacy
'derby.database.readOnlyAccessUsers', etc - you just assign some
defined 'READ_ONLY' role to the users that you reference as part of
the derby.database.readOnlyAccessUsers' derby property...Hence, no
need to mix 2 authorization modes of different standards but until
ROLES are there I agree with Dan this is a *must* to have...
> Satheesh
>
> Daniel John Debrunner wrote:
>
> Satheesh Bandaram wrote:
>
>
> I think mixing both will lead to confusion to users many already
> familiar with the ansi subset model being proposed. This will also
> create hurdles as we expand authorization scheme to support roles and
> "system privileges" as Francois calls them and other security capabilities.
>
> I'm more proposing this to deal with existing Derby applications and
> finding an easy way to bring them into the new world of grant revoke.
> Users familiar with the ansi subset model would just use that, no need
> to get involved with the defaultConnectionModel. Though until roles and
> system privileges is supported, they might need to to ensure a secure
> system. I haven't seen any proposal on these roles or system privileges
> so I'm looking at how secure Derby will be in its next release given
> what has been proposed and is being worked on.
>
> Dan.
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Kathey Marsden <km...@sbcglobal.net>.
Satheesh Bandaram wrote:
>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.
>
>
I have not followed this thread at all as I thought it was all
pertaining to new functionality. Are there changes being proposed that
might affect existing applications if they upgrade to 10.2?
Thanks
Kathey
Re: Grant and Revoke, Part II ... DERBY-464...
Posted by Daniel John Debrunner <dj...@apache.org>.
Satheesh Bandaram wrote:
> I am not sure if previous discussion about migrating a legacy mode
> database to Grant Revoke model was finalized. It seems there were two
> thoughts:
>
> 1. Keep authorization models separate. Legacy mode database can be
> migrated to sqlStandard model by connecting with a URL property.
> (sqlAuthorization=true)
> 2. Dan proposed combining both models with Grant and Revoke
> capability being seen as adding fine-grain access control on top
> of current model. While this proposal doesn't impact Grant and
> Revoke work being done currently by much, it may have implications
> on some future work. (like system privileges) This does make it
> easier for existing databases to adapt new capabilities.
I guess I don't understand how 1) is useful. In this mode by adding
grant/revoke in its current form you are removing key authorization
options. For example if I'm using an LDAP authentication scheme I won't
be able to limt the set of authenticated LDAP users who can connect to
my database. I can do that now, and with 2) I can do that and have more
fine grained authorization.
Dan.
Re: Grant and Revoke, Part II ... DERBY-464...
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
Thanks, Satheesh. FYI, Francois is in France for a family emergency,
so you may not hear from him.
David
Satheesh Bandaram wrote:
> I am getting ready to submit a patch for review, that adds parts of
> Grant and Revoke Part II support. With this patch, I am trying to
> enforce table privileges that are granted to users using Part I patch
> that is already submitted. After this patch, I will work on adding
> upgrade support to upgrade a 10.1 database to 10.2, JDBC metadata
> changes and migration model for legacy database to enable grant and
> revoke functionality. Once all these changes are done, I will then try
> to address other parts of the spec, like routine privileges, views and
> triggers. Let me know if there are any concerns or comments on this plan.
>
> I am not sure if previous discussion about migrating a legacy mode
> database to Grant Revoke model was finalized. It seems there were two
> thoughts:
>
> 1. Keep authorization models separate. Legacy mode database can be
> migrated to sqlStandard model by connecting with a URL property.
> (sqlAuthorization=true)
> 2. Dan proposed combining both models with Grant and Revoke
> capability being seen as adding fine-grain access control on top
> of current model. While this proposal doesn't impact Grant and
> Revoke work being done currently by much, it may have implications
> on some future work. (like system privileges) This does make it
> easier for existing databases to adapt new capabilities.
>
> Satheesh
>
> Daniel John Debrunner wrote:
>
>>Satheesh Bandaram wrote:
>>
>>
>>>I think mixing both will lead to confusion to users many already
>>>familiar with the ansi subset model being proposed. This will also
>>>create hurdles as we expand authorization scheme to support roles and
>>>"system privileges" as Francois calls them and other security capabilities.
>>>
>>>
>>
>>I'm more proposing this to deal with existing Derby applications and
>>finding an easy way to bring them into the new world of grant revoke.
>>Users familiar with the ansi subset model would just use that, no need
>>to get involved with the defaultConnectionModel. Though until roles and
>>system privileges is supported, they might need to to ensure a secure
>>system. I haven't seen any proposal on these roles or system privileges
>>so I'm looking at how secure Derby will be in its next release given
>>what has been proposed and is being worked on.
>>
>>Dan.
>>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Daniel John Debrunner <dj...@apache.org>.
Satheesh Bandaram wrote:
> Daniel John Debrunner wrote:
>
>
>>I wonder if we should look at grant/revoke augmenting the existing
>>authorization model instead of replacing it.
> Why would we want to augment the new authorization model with the old
> one? Is there something that old model provides that new model doesn't
> have?
Yes, the ability to deny a user access to the database and the ability
to make a user's connection read-only. Your current grant/revoke work is
really an extension to the current authorization, it does not replace
the existing functionality.
Note that the property controlling the existing authorization is
'defaultConnectionMode', supporting grant/revoke is not a *connection*
mode, it is a state of the database.
> I think mixing both will lead to confusion to users many already
> familiar with the ansi subset model being proposed. This will also
> create hurdles as we expand authorization scheme to support roles and
> "system privileges" as Francois calls them and other security capabilities.
I'm more proposing this to deal with existing Derby applications and
finding an easy way to bring them into the new world of grant revoke.
Users familiar with the ansi subset model would just use that, no need
to get involved with the defaultConnectionModel. Though until roles and
system privileges is supported, they might need to to ensure a secure
system. I haven't seen any proposal on these roles or system privileges
so I'm looking at how secure Derby will be in its next release given
what has been proposed and is being worked on. If we have a release
about 6 months from the last one, it will be around March. I think
someone was going to set up a wiki page with what "10.3" would include,
though that hasn't happened yet.
Dan.
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Daniel John Debrunner wrote:
>I wonder if we should look at grant/revoke augmenting the existing
>authorization model instead of replacing it.
>
>
>
Why would we want to augment the new authorization model with the old
one? Is there something that old model provides that new model doesn't
have? I think mixing both will lead to confusion to users many already
familiar with the ansi subset model being proposed. This will also
create hurdles as we expand authorization scheme to support roles and
"system privileges" as Francois calls them and other security capabilities.
>The existing authorization functionality has:
>
> - disallow a user
> - allow a user read-only
> - allow a user full-access
>
>Grant/revoke does not replace this functionality, it could be seen as
>adding fine-grain control to the read only or read-write access.
>
>
The new model does allow this access, with fine-grain control. How would
we handle the case of a read-only user having INSERT privilege? Guess we
could make read-only access override INSERT privilege.
>Then it seems to come down to how does an application selects the old
>model of coarse grain control versus fine grain (grant/revoke).
>
>One way is a property like
>
>derby.database.sqlAuthorization={true|false}
>(derby.language.sqlAuthorization ??)
>
>I would like to be able to set a property in derby.properties that made
>the default mode for new databases to be grant/revoke.
>
>I think the JDBC attribute (secureMode) is possibly a security risk, if
>upgrade to grant/revoke mode is allowed, it seems to allow any remote
>user to make that change. Which could allow a denial of service attack
>on existing applications. It may be that such an attack could also be
>made through setting a database property, but I don't think that's a
>justification for adding a new (potential) hole.
>
>
I think we already have a security hole like this... Remote users can
upgrade a database for you when the Sysadm intent may have been to use
the database in soft upgrade mode. This kind of security holes are best
plugged by adding support for roles or system privileges to have Sysadm
privilege. Francois may be working on something in this area...
>Another issue is the potential number of security related properties and
>ensuring they are set correctly. If
>
>
You are proposing to add another one! The list below can be pretty
confusing if not deceiving to new or beginner users to Derby.
Satheesh
>derby.database.sqlAuthorization=false and a GRANT statement was
>executed, then it seems there are three choices:
>
> A - allow the GRANT to succeed, but it is not enforced (since
>derby.database.sqlAuthorization=false)
>
> B - throw an exception (warning?) on execution indicating
>derby.database.sqlAuthorization=false and thus grant statements are not
>applicable
>
> C - Change the database to grant/revoke mode
>
>
>Maybe B would be the case for a user that was not the database owner
>(creator) and C would only be allowed by the database creator.
>
>Also similar exceptions or warnings are probably needed if grant/revoke
>is enabled but no authentication is set up.
>
>
>Dan.
>
>
>
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Daniel John Debrunner <dj...@apache.org>.
I wonder if we should look at grant/revoke augmenting the existing
authorization model instead of replacing it.
The existing authorization functionality has:
- disallow a user
- allow a user read-only
- allow a user full-access
Grant/revoke does not replace this functionality, it could be seen as
adding fine-grain control to the read only or read-write access.
Then it seems to come down to how does an application selects the old
model of coarse grain control versus fine grain (grant/revoke).
One way is a property like
derby.database.sqlAuthorization={true|false}
(derby.language.sqlAuthorization ??)
I would like to be able to set a property in derby.properties that made
the default mode for new databases to be grant/revoke.
I think the JDBC attribute (secureMode) is possibly a security risk, if
upgrade to grant/revoke mode is allowed, it seems to allow any remote
user to make that change. Which could allow a denial of service attack
on existing applications. It may be that such an attack could also be
made through setting a database property, but I don't think that's a
justification for adding a new (potential) hole.
Another issue is the potential number of security related properties and
ensuring they are set correctly. If
derby.database.sqlAuthorization=false and a GRANT statement was
executed, then it seems there are three choices:
A - allow the GRANT to succeed, but it is not enforced (since
derby.database.sqlAuthorization=false)
B - throw an exception (warning?) on execution indicating
derby.database.sqlAuthorization=false and thus grant statements are not
applicable
C - Change the database to grant/revoke mode
Maybe B would be the case for a user that was not the database owner
(creator) and C would only be allowed by the database creator.
Also similar exceptions or warnings are probably needed if grant/revoke
is enabled but no authentication is set up.
Dan.
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Daniel John Debrunner <dj...@apache.org>.
Francois Orsini wrote:
> I think we just *cannot* let anyone override the 'defaultConnectionMode'
> database configuration property - The user would have had to be granted
> a 'system privilege' of some sort. Now as far as migrating a secure
> database to a legacy mode one, there would need to be good reasons for
> that, such as running an embedded application with no need for a secure
> database mode. But then if we allow migration of secure mode to legacy,
> might as well keep the property scheme as you have originally defined it
> and no new connection url property.
>
> --francois
>
> On 1/8/06, *Satheesh Bandaram* < satheesh@sourcery.org
> <ma...@sourcery.org>> wrote:
>
> We could use 'defaultConnectionMode' property to store secureMode
> like you said, but ..
>
> 1. What would happen if a user tries to set the value to
> 'fullAccess' or 'readOnlyAccess' in a secure database? Should
> we catch the case and raise an error since otherwise the
> database would switch to being a legacy database.
> 2. While I am not promising migration from secure database to
> legacy database, overloading this property will make the value
> being lost if someone adds logic to provide this migration
> later and if someone tries to do a roundtrip of a database
> from legacy mode...
>
> Satheesh
>
>
> Francois Orsini wrote:
>
>> Sounds good.
>>
>> Where would you persist the secureMode value?
>>
>> I guess it would then be ok to consider 'defaultConnectionMode' to
>> be legacy only unless you are thinking of still using it to store
>> secureMode value? Could you clarify please.
>>
>> --francois
>>
>> On 1/6/06, *Satheesh Bandaram* <satheesh@sourcery.org
>> <ma...@sourcery.org>> wrote:
>>
>> I have been thinking if use of properties is the right way to
>> chose sqlStandard security mode or legacy mode... Properties
>> are meant to be more dynamic in nature and since I don't yet
>> plan to allow switching between SqlStandard mode (with support
>> for Grant and Revoke) to legacy mode.
>>
>> I think use of URL property to indicate which security mode is
>> wanted during a database create time is more natural. If one
>> wishes to create a database with support for Grant and Revoke,
>> it could be specified by a URL attribute like secureMode.
>>
>> ij> connect 'jdbc:derby:securedb;create=true;*secureMode=true*';
>>
>> If secureMode is not specified, current default of legacy mode
>> database without grant/revoke capability would be created in
>> 10.2 release. If secureMode is true, a database with support
>> for grant/revoke statements is created. In this database,
>> property value of 'defaultConnectionMode' is a no-op.
I'm still thinking about this 'secureMode' approach and the interaction
with the existing authentication model. One issue I do have is the name
of the attribute, 'secureMode'. I don't believe that the current
grant/revoke syntax makes Derby completely secure, thus this attribute
may mislead people. Note sure I have a better name though. :-(
Dan.
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
I think we just *cannot* let anyone override the 'defaultConnectionMode'
database configuration property - The user would have had to be granted a
'system privilege' of some sort. Now as far as migrating a secure database
to a legacy mode one, there would need to be good reasons for that, such as
running an embedded application with no need for a secure database mode. But
then if we allow migration of secure mode to legacy, might as well keep the
property scheme as you have originally defined it and no new connection url
property.
--francois
On 1/8/06, Satheesh Bandaram < satheesh@sourcery.org> wrote:
>
> We could use 'defaultConnectionMode' property to store secureMode like you
> said, but ..
>
> 1. What would happen if a user tries to set the value to
> 'fullAccess' or 'readOnlyAccess' in a secure database? Should we catch the
> case and raise an error since otherwise the database would switch to being a
> legacy database.
> 2. While I am not promising migration from secure database to
> legacy database, overloading this property will make the value being lost if
> someone adds logic to provide this migration later and if someone tries to
> do a roundtrip of a database from legacy mode...
>
> Satheesh
>
> Francois Orsini wrote:
>
> Sounds good.
>
> Where would you persist the secureMode value?
>
> I guess it would then be ok to consider 'defaultConnectionMode' to be
> legacy only unless you are thinking of still using it to store secureMode
> value? Could you clarify please.
>
> --francois
>
> On 1/6/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
> >
> > I have been thinking if use of properties is the right way to chose
> > sqlStandard security mode or legacy mode... Properties are meant to be more
> > dynamic in nature and since I don't yet plan to allow switching between
> > SqlStandard mode (with support for Grant and Revoke) to legacy mode.
> >
> > I think use of URL property to indicate which security mode is wanted
> > during a database create time is more natural. If one wishes to create a
> > database with support for Grant and Revoke, it could be specified by a URL
> > attribute like secureMode.
> >
> > ij> connect 'jdbc:derby:securedb;create=true;*secureMode=true*';
> >
> > If secureMode is not specified, current default of legacy mode database
> > without grant/revoke capability would be created in 10.2 release. If
> > secureMode is true, a database with support for grant/revoke statements is
> > created. In this database, property value of 'defaultConnectionMode' is a
> > no-op.
> >
> > We could also use this mechanism to trigger a legacy database migration
> > to sqlStandard security mode. During booting of a legacy database with
> > secureMode=true could trigger this migration in security mode.
> >
> > Any thoughts or comments?
> >
> > Satheesh
> >
> > Satheesh Bandaram wrote:
> >
> > Let us look at the issues and some assumptions. A solution may follow
> > from it and this definitely needs some debate. The assumptions here are my
> > proposals only.
> >
> > 1. My current proposal (attached to Jira) would allow migrating
> > databases from legacy security mode into sqlStandard mode, but not the
> > otherway.
> > 2. It is preferred to avoid change in behavior to existing
> > applications that may be using defaultConnectionMode.
> > 3. Current default value for defaultConnectionMode is 'fullAccess'
> > and not going to be changed to sqlStandard for 10.2 release. I do
> > think some feedback on how sqlStandard mode is working is needed before any
> > changes.
> > 4. It is possible to have some databases in legacy security mode
> > and some in sqlStandard mode in any installation.
> > 5. sqlStandard mode is likely going to be the default mode at some
> > in the future and likely preferred if not the only mode at long time later.
> >
> > Are these the likely goals for a solution? We could use
> > derby.database.propertiesOnly to override system properties with
> > database properties, but that would change all properties, right?
> >
> > Satheesh
> >
> > Daniel John Debrunner wrote:
> >
> > I'm not sure about this, I can't find what Satheesh is refering to when
> > he said 'Dan raised an important question ...'.
> >
> > I found one comment by me in the thread where I was talking about system
> > properties in general.
> >
> >
> >
> > Databases do have an existing way to override system properties, by
> > setting the database property derby.database.propertiesOnly
> >
> >
> >
> > http://db.apache.org/derby/docs/10.1/tuning/rtunproper24390.html
> >
> > Dan.
> >
> >
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Francois Orsini <fr...@gmail.com>.
Sounds good.
Where would you persist the secureMode value?
I guess it would then be ok to consider 'defaultConnectionMode' to be legacy
only unless you are thinking of still using it to store secureMode value?
Could you clarify please.
--francois
On 1/6/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> I have been thinking if use of properties is the right way to chose
> sqlStandard security mode or legacy mode... Properties are meant to be more
> dynamic in nature and since I don't yet plan to allow switching between
> SqlStandard mode (with support for Grant and Revoke) to legacy mode.
>
> I think use of URL property to indicate which security mode is wanted
> during a database create time is more natural. If one wishes to create a
> database with support for Grant and Revoke, it could be specified by a URL
> attribute like secureMode.
>
> ij> connect 'jdbc:derby:securedb;create=true;*secureMode=true*';
>
> If secureMode is not specified, current default of legacy mode database
> without grant/revoke capability would be created in 10.2 release. If
> secureMode is true, a database with support for grant/revoke statements is
> created. In this database, property value of 'defaultConnectionMode' is a
> no-op.
>
> We could also use this mechanism to trigger a legacy database migration to
> sqlStandard security mode. During booting of a legacy database with
> secureMode=true could trigger this migration in security mode.
>
> Any thoughts or comments?
>
> Satheesh
>
> Satheesh Bandaram wrote:
>
> Let us look at the issues and some assumptions. A solution may follow from
> it and this definitely needs some debate. The assumptions here are my
> proposals only.
>
> 1. My current proposal (attached to Jira) would allow migrating
> databases from legacy security mode into sqlStandard mode, but not the
> otherway.
> 2. It is preferred to avoid change in behavior to existing
> applications that may be using defaultConnectionMode.
> 3. Current default value for defaultConnectionMode is 'fullAccess'
> and not going to be changed to sqlStandard for 10.2 release. I do
> think some feedback on how sqlStandard mode is working is needed before any
> changes.
> 4. It is possible to have some databases in legacy security mode
> and some in sqlStandard mode in any installation.
> 5. sqlStandard mode is likely going to be the default mode at some
> in the future and likely preferred if not the only mode at long time later.
>
> Are these the likely goals for a solution? We could use
> derby.database.propertiesOnly to override system properties with database
> properties, but that would change all properties, right?
>
> Satheesh
>
> Daniel John Debrunner wrote:
>
> I'm not sure about this, I can't find what Satheesh is refering to when
> he said 'Dan raised an important question ...'.
>
> I found one comment by me in the thread where I was talking about system
> properties in general.
>
> Databases do have an existing way to override system properties, by
> setting the database property derby.database.propertiesOnly
>
> http://db.apache.org/derby/docs/10.1/tuning/rtunproper24390.html
>
> Dan.
>
>
Re: Grant and Revoke, Part I ... DERBY-464...
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Francois Orsini wrote:
> >3) Dan raised an important question about whether
> defaultConnectionMode should be made a database-only property
> > and without affecting existing applications. I am currently thinking
> if defaultConnectionMode is set to sqlStandard \
> > as a database property, it would override system-wide property for
> that database only. Once defaultConnectionMode is
> > set to sqlStandard as a database property, it cann't be changed to
> legacy modes or removed.
>
> What is the rationale for making defaultConnectionMode property a
> database-only one? Why not leaving both ways (system & database)?
I'm not sure about this, I can't find what Satheesh is refering to when
he said 'Dan raised an important question ...'.
I found one comment by me in the thread where I was talking about system
properties in general.
Databases do have an existing way to override system properties, by
setting the database property derby.database.propertiesOnly
http://db.apache.org/derby/docs/10.1/tuning/rtunproper24390.html
Dan.