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 Daniel John Debrunner <dj...@apache.org> on 2006/02/16 22:20:53 UTC
Grant -revoke (464) and future backwards compat
The reason that grant/revoke (DERBY-464) has to add a mode
derby.database.sqlAuthorization={true|false}
is that the grant/revoke requires a different access model to Derby's
current model.
The current model is any full access user has access to any schema object.
The SQL model is that only the creator of the table has access to the
table, but can grant fine grained access to others.
Changing Derby to the default or the SQL model or only supporting the
SQL model would break existing applications. Hence the dual mode.
I'd like to ensure that we don't need to introduce another mode switch
in a future release, related to this area. Ending up several releases
from now with these properties would be bad
10.3 derby.database.sqlAuthorizationWeReallyMeanItThisTime={true|false}
10.4 derby.database.sqlAuthorizationOKMaybeThisTime={true|false}
:-)
So I'm wondering if there is anything more that needs to be done (in
addition to 464 - which is a good incremental step) to prevent mode hell
in the future. (There's a certain other open source database that now
has many mode settings, imagine trying to support that, "ok now try
turning that mode off and this one on", "or which mode settings do you
have on, do you really know?").
A possible free reign for users after 464 is the ability to create a
schema corresponding to the user's name and thus create any SQL objects
you want in the database. So I can create a table and populate it with
100 million rows to bring the database down by filling the disk up.
It almost seems like some create schema permission would avoid us having
a mode in the future that was:
- 10.2 anyone can create a schema and objects within it
- future - support full grant/revoke mode with schema creation permission.
Now I looked briefly in the SQL2003 standard and the does not seem to be
any create schema permission, and the access permission under create
table was the less than obvious:
"If a <table definition> is contained in an <SQL-client module
definition>, then the enabled authorization
identifiers shall include A."
"A" being an authorization identifier, I need to follow that chain more.
Maybe the more general question is: "How do we want permissions to
behave once all the related work is done, and does that model have any
discontinuities with the model currently proposed for 10.2?"
Maybe there's other work that needs to be done to avoid jarring mode
switches in the future.
The alternative is to document, for 10.2, when
derby.database.sqlAuthorization=true
that behaviour may change in the future, be warned (with possible examples).
Dan.
Re: Grant -revoke (464) and future backwards compat
Posted by Daniel John Debrunner <dj...@apache.org>.
Øystein Grøvlen wrote:
> I also note that while easy-to-use and standards-based is covered by the
> Derby Charter, backward-compatibility is not. ;-)
It's one aspect of being easy to use, easy to upgrade, easy to switch
versions. :-)
Dan.
Re: Grant -revoke (464) and future backwards compat
Posted by Francois Orsini <fr...@gmail.com>.
On 2/28/06, Øystein Grøvlen <Oy...@sun.com> wrote:
> Satheesh Bandaram wrote:
>
> > Until these system privileges are ready, current proposal limits
> > accesses that would cause forward compatibility issues. If sqlStandard
> > mode allows unrestricted schema creation now, this would cause issues in
> > the future where existing applications may need to change or we have to
> > introduce another property like what is being done now. Current legacy
> > authorization model is not compatible with standard model or what Derby
> > might really want to support, but at the same time, we can't drop
> > support for it because of existing applications. I believe Dan is try to
> > ensure current proposal doesn't create any future compatibility issues,
> > even if in the short term, Derby's new capabilities are restrictive.
>
> My main concern here is usability. As long as the legacy mode is the
> default, it seems OK to enforce such restrictions in sqlStandard mode
> since it will probably not hit unexperienced users. Will legacy mode
> always be the default? Do we plan to switch to sqlStandard mode at some
> point in time?
>
This would impact Ease of Use which has always been key to the Derby
charter. It is nice to get a choice but it can also be confusing -
Authentication is turned off by default as well and this came from a
ease of use requirement, mostly for embedded environments...
> Another important aspect of usability is that a product behaves in a
> familiar way. That is, the behavior is similar to similar products. I
> am a bit concerned if users will need to know about a specific property
> in order to be able to use GRANT/REVOKE. Also, can we really claim to
> be standards-compliant if one needs to set specific property in order to
> be able to use parts of the standard?
>
Good point but if you have a way to enforce that feature to be used
then I would think it is - If a feature is supported and can be
enforced in some RDBMS then I would think it is compliant but that's
just IMHO.
> I also note that while easy-to-use and standards-based is covered by the
> Derby Charter, backward-compatibility is not. ;-)
>
> --
> Øystein
>
>
Re: Grant -revoke (464) and future backwards compat
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Satheesh Bandaram wrote:
>
> Øystein Grøvlen wrote:
>
>
>>My main concern here is usability. As long as the legacy mode is the
>>default, it seems OK to enforce such restrictions in sqlStandard mode
>>since it will probably not hit unexperienced users. Will legacy mode
>>always be the default? Do we plan to switch to sqlStandard mode at
>>some point in time?
>
>
> Good comments, thanks... Legacy mode is the default now and I think it
> should continue to be the default until Derby supports well-rounded
> sqlStandard authorization model and provided sufficient warnings to
> existing users. Switching default is a decision for the community to
> make... hopefully sooner (like 10.3) than later. Also, I would like to
> see system privileges work completed to some degree.
>
I agree with this.
>>Another important aspect of usability is that a product behaves in a
>>familiar way. That is, the behavior is similar to similar products.
>>I am a bit concerned if users will need to know about a specific
>>property in order to be able to use GRANT/REVOKE. Also, can we really
>>claim to be standards-compliant if one needs to set specific property
>>in order to be able to use parts of the standard?
>
>
> Right... Without Francois's work, sqlStandard authorization is not going
> to be similar to other products anyway. :-) I am also considering
> documenting current limitations of sqlStandard authorization model and
> advise users to evaluate it well before using it. Once the model is more
> complete, these warnings in the docs could go and the community could
> decide on switching the default authorization model.
Sounds like a good approach.
--
Øystein
Re: Grant -revoke (464) and future backwards compat
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Øystein Grøvlen wrote:
> My main concern here is usability. As long as the legacy mode is the
> default, it seems OK to enforce such restrictions in sqlStandard mode
> since it will probably not hit unexperienced users. Will legacy mode
> always be the default? Do we plan to switch to sqlStandard mode at
> some point in time?
Good comments, thanks... Legacy mode is the default now and I think it
should continue to be the default until Derby supports well-rounded
sqlStandard authorization model and provided sufficient warnings to
existing users. Switching default is a decision for the community to
make... hopefully sooner (like 10.3) than later. Also, I would like to
see system privileges work completed to some degree.
> Another important aspect of usability is that a product behaves in a
> familiar way. That is, the behavior is similar to similar products.
> I am a bit concerned if users will need to know about a specific
> property in order to be able to use GRANT/REVOKE. Also, can we really
> claim to be standards-compliant if one needs to set specific property
> in order to be able to use parts of the standard?
Right... Without Francois's work, sqlStandard authorization is not going
to be similar to other products anyway. :-) I am also considering
documenting current limitations of sqlStandard authorization model and
advise users to evaluate it well before using it. Once the model is more
complete, these warnings in the docs could go and the community could
decide on switching the default authorization model.
Satheesh
> I also note that while easy-to-use and standards-based is covered by
> the Derby Charter, backward-compatibility is not. ;-)
>
> --
> Øystein
>
>
>
Re: Grant -revoke (464) and future backwards compat
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Satheesh Bandaram wrote:
> Until these system privileges are ready, current proposal limits
> accesses that would cause forward compatibility issues. If sqlStandard
> mode allows unrestricted schema creation now, this would cause issues in
> the future where existing applications may need to change or we have to
> introduce another property like what is being done now. Current legacy
> authorization model is not compatible with standard model or what Derby
> might really want to support, but at the same time, we can't drop
> support for it because of existing applications. I believe Dan is try to
> ensure current proposal doesn't create any future compatibility issues,
> even if in the short term, Derby's new capabilities are restrictive.
My main concern here is usability. As long as the legacy mode is the
default, it seems OK to enforce such restrictions in sqlStandard mode
since it will probably not hit unexperienced users. Will legacy mode
always be the default? Do we plan to switch to sqlStandard mode at some
point in time?
Another important aspect of usability is that a product behaves in a
familiar way. That is, the behavior is similar to similar products. I
am a bit concerned if users will need to know about a specific property
in order to be able to use GRANT/REVOKE. Also, can we really claim to
be standards-compliant if one needs to set specific property in order to
be able to use parts of the standard?
I also note that while easy-to-use and standards-based is covered by the
Derby Charter, backward-compatibility is not. ;-)
--
Øystein
Re: Grant -revoke (464) and future backwards compat
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Francois Orsini wrote:
>I'll be posting more information soon.
>
>
Great... Do you see this work going into 10.2 release or later versions?
I would like to know so that documentation changes can be made
appropriately for my work on Grant and Revoke.
Satheesh
Re: Grant -revoke (464) and future backwards compat
Posted by Francois Orsini <fr...@gmail.com>.
On 2/21/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Oystein Grovlen - Sun Norway wrote:
>
> > Daniel John Debrunner wrote:
> >
> >> CREATE SCHEMA
> >> - only create schema matching user's name
> >> - good for now, forwards compatible with the
> >> future where permission to create any schema
> >> could be granted explicitly.
> >
> >
> > Does this mean that we will only allow one schema per user? That
> > seems like a severe limitation. I guess I am missing something.
>
> This is where Francois's work on system privileges is needed. Current
> grant/revoke proposal only deals with access privileges to existing
> objects, like ability to grant/revoke select, insert, delete, update or
> allow references/triggers to tables and execute privilege to routines.
> What is sorely needed is ability to grant/revoke system/database access
> and I thought Francois was working on this. Any status Francois?
>
I'll be posting more information soon.
> Until these system privileges are ready, current proposal limits
> accesses that would cause forward compatibility issues. If sqlStandard
> mode allows unrestricted schema creation now, this would cause issues in
> the future where existing applications may need to change or we have to
> introduce another property like what is being done now. Current legacy
> authorization model is not compatible with standard model or what Derby
> might really want to support, but at the same time, we can't drop
> support for it because of existing applications. I believe Dan is try to
> ensure current proposal doesn't create any future compatibility issues,
> even if in the short term, Derby's new capabilities are restrictive.
>
> Satheesh
>
>
Re: Grant -revoke (464) and future backwards compat
Posted by Francois Orsini <fr...@gmail.com>.
On 2/21/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
>
> Daniel John Debrunner wrote:
>
> Satheesh Bandaram wrote:
>
>
> Until these system privileges are ready, current proposal limits
> accesses that would cause forward compatibility issues.
>
> Except that it doesn't, I believe we need additional restrictions on
> table and routine creation.
>
> In sqlStandard mode, the proposal only allows for creating tables and
> routines in their own Schema and no where else. I thought this is too
> restrictive already :-) , but makes sense that unless someone grants ability
> to create tables in their schema, Derby shouldn't allow that. Currently
> there is no way to grant privilege to create tables...
>
> What future scenario do you see where schema owners don't have ability to
> create tables or routines in their own schema, by default? It may be
> possible that Derby would allow granting/revoking table or routine
> privileges in the future, but default behavior for a schema owner would be
> to have this privilege by default?
>
I need to check again and I thought object creation privileges were
granted to some schema owner if that schema had been created explictly
with the CREATE SCHEMA AUTHORIZATION DDL.
>
> If sqlStandard
> mode allows unrestricted schema creation now, this would cause issues in
> the future where existing applications may need to change or we have to
> introduce another property like what is being done now.
>
>
>
> Current legacy
> authorization model is not compatible with standard model or what Derby
> might really want to support, but at the same time, we can't drop
> support for it because of existing applications.
>
> I'm not sure why "legacy" mode keeps being dragged into this discussion,
> or why folks see it as "not compatible".
> If current or legacy mode is compatible with sqlStandard mode, would there
> be a need to add new property, derby.database.sqlAuthorization? I understand
> going forward defaultConnectionMode can be seen as compatible with standard
> model as additional layer of authorization, but I see there is a break from
> 10.1 model to 10.2.
>
Agreed - this additional layer has to be flagged in order to be turned
on with its new semantics, no? I thought we had that new property
(derby.database.sqlAuthorization) for that reason (and it can still be
a layer on top of legacy)
> I see it as compatible, it's an
> additional layer of authorization at the incoming connection level.
>
> - full-access - Access limited to granted privs.
>
> The way I see it, Derby is essentially changing full-access meaning... from
> "read/write access to every object in the database" to "read/write access to
> objects owned or been granted limited access to" in sqlStandard mode. This
> would force existing applications to change to adapt to sqlStandard mode,
> right? If so, we have an incompatibility.
>
That is correct. Once sqlStandard is enabled then legacy defined
privileges become more like (specific) Roles for some users to be
granted too - that's the way I see it.
> Satheesh
>
>
Re: Grant -revoke (464) and future backwards compat
Posted by Kathey Marsden <km...@sbcglobal.net>.
Daniel John Debrunner wrote:
>Daniel John Debrunner wrote:
>
>
>
>>Creating an external routine would have all sorts of security concerns
>>for a database owner, it's allowing a remote user to execute code on
>>their system.
>>
>>
>
>I thought of a fairly clean solution for this in the 10.2 timeframe:
>
>
Thanks Dan,
Your solution sounds good. Thank you so much for your careful
consideration of the user community's future backward compat needs!
Kathey
Re: Grant -revoke (464) and future backwards compat
Posted by Daniel John Debrunner <dj...@apache.org>.
Daniel John Debrunner wrote:
> Daniel John Debrunner wrote:
>
>
>>Creating an external routine would have all sorts of security concerns
>>for a database owner, it's allowing a remote user to execute code on
>>their system.
>
>
> I thought of a fairly clean solution for this in the 10.2 timeframe:
>
> Allow creation of Java routines (functions & procedures).
>
> 2) Allow execution of Java routines that map into a public static method
> loaded from a jar file installed into the database using
> sqlj.install_jar. This is ok because the database owner must have
> granted permission to install the jar and set the database classpath so
> they showed they trusted the user already.
This one may need to be more specific, maybe only if the jar file was
installed in a schema owned by the routine owner. I found out that there
is a USAGE permission on jar files, so if we want to support that in the
future we would need to restrict access now to jars owned by the user. I
need to look at this some more.
Dan.
Re: Grant -revoke (464) and future backwards compat
Posted by Daniel John Debrunner <dj...@apache.org>.
Daniel John Debrunner wrote:
> Creating an external routine would have all sorts of security concerns
> for a database owner, it's allowing a remote user to execute code on
> their system.
I thought of a fairly clean solution for this in the 10.2 timeframe:
Allow creation of Java routines (functions & procedures).
1) Allow execution of Java routines that that map directly to a public
static method in the Java system libraries, package in EXTERNAL NAME
clause starting with 'java.' or 'javax.'. This is fine because the Java
libraries are assumed to be secure and the class invoking the method
will have no permissions so nothing bad can be done by a remote user.
2) Allow execution of Java routines that map into a public static method
loaded from a jar file installed into the database using
sqlj.install_jar. This is ok because the database owner must have
granted permission to install the jar and set the database classpath so
they showed they trusted the user already.
3) Disallow execution of any other Java routines. This would be routines
that map to Java methods from the JVM's classpath or other non-database
class loading mechanism. Maps to the 'external routine' concept in other
databases. Future system/database wide privileges could enable this
selectively.
I'm going to be making changes in this area anyway, so I'll spend some
time seeing how easy this is.
Dan.
Re: Grant -revoke (464) and future backwards compat
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Daniel John Debrunner wrote:
>I looked at some other databases (Oracle, DB2, Postgres) and the typical
>model is to require a database level privilege to create tables or call
>an external routine.
>
>Create table I could possibly go either way on, but if we followed the
>de-facto standard model of other databases then we need a database level
>privilege.
>
>Creating an external routine would have all sorts of security concerns
>for a database owner, it's allowing a remote user to execute code on
>their system.
>
>
I will look into what other databases do and see if I can propose some
additional changes...
Satheesh
Re: Grant -revoke (464) and future backwards compat
Posted by Daniel John Debrunner <dj...@apache.org>.
Satheesh Bandaram wrote:
>
>
> Daniel John Debrunner wrote:
>
>>Satheesh Bandaram wrote:
>>
>>
>>>Until these system privileges are ready, current proposal limits
>>>accesses that would cause forward compatibility issues.
>>>
>>>
>>
>>Except that it doesn't, I believe we need additional restrictions on
>>table and routine creation.
>>
>>
> In sqlStandard mode, the proposal only allows for creating tables and
> routines in their own Schema and no where else. I thought this is too
> restrictive already :-) , but makes sense that unless someone grants
> ability to create tables in their schema, Derby shouldn't allow that.
> Currently there is no way to grant privilege to create tables...
>
> What future scenario do you see where schema owners don't have ability
> to create tables or routines in their own schema, by default? It may be
> possible that Derby would allow granting/revoking table or routine
> privileges in the future, but default behavior for a schema owner would
> be to have this privilege by default?
I looked at some other databases (Oracle, DB2, Postgres) and the typical
model is to require a database level privilege to create tables or call
an external routine.
Create table I could possibly go either way on, but if we followed the
de-facto standard model of other databases then we need a database level
privilege.
Creating an external routine would have all sorts of security concerns
for a database owner, it's allowing a remote user to execute code on
their system.
Dan.
>>>Current legacy
>>>authorization model is not compatible with standard model or what Derby
>>>might really want to support, but at the same time, we can't drop
>>>support for it because of existing applications.
>>>
>>>
>>
>>I'm not sure why "legacy" mode keeps being dragged into this discussion,
>>or why folks see it as "not compatible".
>>
> If current or legacy mode is compatible with sqlStandard mode, would
> there be a need to add new property, derby.database.sqlAuthorization? I
> understand going forward defaultConnectionMode can be seen as compatible
> with standard model as additional layer of authorization, but I see
> there is a break from 10.1 model to 10.2.
>
>> I see it as compatible, it's an
>>additional layer of authorization at the incoming connection level.
>>
>> - full-access - Access limited to granted privs.
>>
>>
> The way I see it, Derby is essentially changing full-access meaning...
> from "read/write access to every object in the database" to "read/write
> access to objects owned or been granted limited access to" in
> sqlStandard mode. This would force existing applications to change to
> adapt to sqlStandard mode, right? If so, we have an incompatibility.
I think we are in agreement, but talking about different things.
Introducing grant/revoke causes behaviour incompatible (when enabled)
with previous releases.
The connection based authorization model (noAccess, readOnly or full) by
itself is not incompatible with grant/revoke.
Dan.
Re: Grant -revoke (464) and future backwards compat
Posted by Daniel John Debrunner <dj...@apache.org>.
Satheesh Bandaram wrote:
> Oystein Grovlen - Sun Norway wrote:
>
>
>>Daniel John Debrunner wrote:
>>
>>
>>>CREATE SCHEMA
>>> - only create schema matching user's name
>>> - good for now, forwards compatible with the
>>> future where permission to create any schema
>>> could be granted explicitly.
>>
>>
>>Does this mean that we will only allow one schema per user? That
>>seems like a severe limitation. I guess I am missing something.
>
>
> This is where Francois's work on system privileges is needed. Current
> grant/revoke proposal only deals with access privileges to existing
> objects, like ability to grant/revoke select, insert, delete, update or
> allow references/triggers to tables and execute privilege to routines.
> What is sorely needed is ability to grant/revoke system/database access
> and I thought Francois was working on this. Any status Francois?
>
> Until these system privileges are ready, current proposal limits
> accesses that would cause forward compatibility issues.
Except that it doesn't, I believe we need additional restrictions on
table and routine creation.
> If sqlStandard
> mode allows unrestricted schema creation now, this would cause issues in
> the future where existing applications may need to change or we have to
> introduce another property like what is being done now.
> Current legacy
> authorization model is not compatible with standard model or what Derby
> might really want to support, but at the same time, we can't drop
> support for it because of existing applications.
I'm not sure why "legacy" mode keeps being dragged into this discussion,
or why folks see it as "not compatible". I see it as compatible, it's an
additional layer of authorization at the incoming connection level. It
has three modes in sqlStandard mode:
- no access - No connection acceess, regardless of any granted privs.
- read-only access - Connection made read only, read-only access
limited to granted privs. No write access regardless of granted privs.
It is similar to the application calling conn.setReadOnly(true).
- full-access - Access limited to granted privs.
> I believe Dan is try to
> ensure current proposal doesn't create any future compatibility issues,
> even if in the short term, Derby's new capabilities are restrictive.
Yep, exactly what I'm trying to ensure.
Thanks,
Dan.
Re: Grant -revoke (464) and future backwards compat
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Oystein Grovlen - Sun Norway wrote:
> Daniel John Debrunner wrote:
>
>> CREATE SCHEMA
>> - only create schema matching user's name
>> - good for now, forwards compatible with the
>> future where permission to create any schema
>> could be granted explicitly.
>
>
> Does this mean that we will only allow one schema per user? That
> seems like a severe limitation. I guess I am missing something.
This is where Francois's work on system privileges is needed. Current
grant/revoke proposal only deals with access privileges to existing
objects, like ability to grant/revoke select, insert, delete, update or
allow references/triggers to tables and execute privilege to routines.
What is sorely needed is ability to grant/revoke system/database access
and I thought Francois was working on this. Any status Francois?
Until these system privileges are ready, current proposal limits
accesses that would cause forward compatibility issues. If sqlStandard
mode allows unrestricted schema creation now, this would cause issues in
the future where existing applications may need to change or we have to
introduce another property like what is being done now. Current legacy
authorization model is not compatible with standard model or what Derby
might really want to support, but at the same time, we can't drop
support for it because of existing applications. I believe Dan is try to
ensure current proposal doesn't create any future compatibility issues,
even if in the short term, Derby's new capabilities are restrictive.
Satheesh
Re: Grant -revoke (464) and future backwards compat
Posted by Oystein Grovlen - Sun Norway <Oy...@Sun.COM>.
Daniel John Debrunner wrote:
> CREATE SCHEMA
> - only create schema matching user's name
> - good for now, forwards compatible with the
> future where permission to create any schema
> could be granted explicitly.
Does this mean that we will only allow one schema per user? That seems
like a severe limitation. I guess I am missing something.
--
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Technology Group
Trondheim, Norway
Re: Grant -revoke (464) and future backwards compat
Posted by Daniel John Debrunner <dj...@apache.org>.
Daniel John Debrunner wrote:
> Maybe the more general question is: "How do we want permissions to
> behave once all the related work is done, and does that model have any
> discontinuities with the model currently proposed for 10.2?"
This is, as David said, the question that needs to be answered.
This is what I believe is proposed for 10.2
(excluding the owner of the database who can do anything)
CREATE FUNCTION
- allowed within schemas owned by the user
- probably not compatible with future requirement,
would like to restrict access on creating
external functions.
CREATE INDEX
- allowed only by owner of table
- good for now, forwards compatible with the
future where permission to create an index on
a table could be granted explicitly.
CREATE PROCEDURE
- allowed within schemas owned by the user
- probably not compatible with future requirement,
would like to restrict access on creating
external procedures.
CREATE SCHEMA
- only create schema matching user's name
- good for now, forwards compatible with the
future where permission to create any schema
could be granted explicitly.
CREATE SYNONYM
- allowed within schemas owned by the user
- Good, just creates an alias for a table.
No security risk to allowing SYNONYMs, since
user of SYNONYM would require privileges to
underlying table.
CREATE TABLE
- allowed within schemas owned by the user
- probably not compatible with future requirement,
would like to restrict access on creating tables.
CREATE TRIGGER
- allowed within schemas owned by the user
- require TRIGGER privilege on table
- Good, probably completely matches future requirements.
CREATE VIEW
- allowed within schemas owned by the user
- requires access on objects referenced in view
I guess this is ok, seems a little like grant with grant though, if
I have permission to read X, then I can grant that to everyone through a
view. But I guess if I have access to read X, I can distribute it to
others in many ways.
ALTER TABLE
- not sure what is proposed here. Patial mention in specs
for constraints.
- best forward compatibile approach would be to only
allow ALTER to be executed by the table's owner.
Any other DDL?
Thus it seems, to me, that ideally 10.2 should go out with some
restriction on how routines and tables are created.
Dan.
Re: Grant -revoke (464) and future backwards compat
Posted by Daniel John Debrunner <dj...@apache.org>.
David W. Van Couvering wrote:
> I think we need to answer your question
>
>> Maybe the more general question is: "How do we want permissions to
>> behave once all the related work is done, and does that model have any
>> discontinuities with the model currently proposed for 10.2?"
>>
>
> before we consider
>
>> The alternative is to document, for 10.2, when
>>
>> derby.database.sqlAuthorization=true
>>
>> that behaviour may change in the future, be warned (with possible
> examples).
>
> If I were an app developer, reading something like this would make me
> pretty uncomfortable, as I have no clear safety net to avoid unexpected
> authorization errors in the future.
I agree, I think it's better to throw all options out there and see what
happens in the dicussion. One of the great things about open source
development in the archiving of all discussions, since all discussions
are meant to happen on the list. This then captures why a direction
(such as this) was rejected. That's important when a future dicussion
re-visits the topic and needs to see why certain paths were taken and
*not* taken. Summarizing off-list discussions is never the same, as in
general, folks summarize fairly well the path forward, but not the
reasons for the not-taken paths.
Dan.
Re: Grant -revoke (464) and future backwards compat
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
I think we need to answer your question
> Maybe the more general question is: "How do we want permissions to
> behave once all the related work is done, and does that model have any
> discontinuities with the model currently proposed for 10.2?"
>
before we consider
> The alternative is to document, for 10.2, when
>
> derby.database.sqlAuthorization=true
>
> that behaviour may change in the future, be warned (with possible
examples).
If I were an app developer, reading something like this would make me
pretty uncomfortable, as I have no clear safety net to avoid unexpected
authorization errors in the future.
David
Daniel John Debrunner wrote:
> The reason that grant/revoke (DERBY-464) has to add a mode
>
> derby.database.sqlAuthorization={true|false}
>
> is that the grant/revoke requires a different access model to Derby's
> current model.
>
> The current model is any full access user has access to any schema object.
>
> The SQL model is that only the creator of the table has access to the
> table, but can grant fine grained access to others.
>
> Changing Derby to the default or the SQL model or only supporting the
> SQL model would break existing applications. Hence the dual mode.
>
> I'd like to ensure that we don't need to introduce another mode switch
> in a future release, related to this area. Ending up several releases
> from now with these properties would be bad
>
> 10.3 derby.database.sqlAuthorizationWeReallyMeanItThisTime={true|false}
>
> 10.4 derby.database.sqlAuthorizationOKMaybeThisTime={true|false}
>
> :-)
>
> So I'm wondering if there is anything more that needs to be done (in
> addition to 464 - which is a good incremental step) to prevent mode hell
> in the future. (There's a certain other open source database that now
> has many mode settings, imagine trying to support that, "ok now try
> turning that mode off and this one on", "or which mode settings do you
> have on, do you really know?").
>
> A possible free reign for users after 464 is the ability to create a
> schema corresponding to the user's name and thus create any SQL objects
> you want in the database. So I can create a table and populate it with
> 100 million rows to bring the database down by filling the disk up.
>
> It almost seems like some create schema permission would avoid us having
> a mode in the future that was:
>
> - 10.2 anyone can create a schema and objects within it
> - future - support full grant/revoke mode with schema creation permission.
>
> Now I looked briefly in the SQL2003 standard and the does not seem to be
> any create schema permission, and the access permission under create
> table was the less than obvious:
>
> "If a <table definition> is contained in an <SQL-client module
> definition>, then the enabled authorization
> identifiers shall include A."
>
> "A" being an authorization identifier, I need to follow that chain more.
>
> Maybe the more general question is: "How do we want permissions to
> behave once all the related work is done, and does that model have any
> discontinuities with the model currently proposed for 10.2?"
>
>
> Maybe there's other work that needs to be done to avoid jarring mode
> switches in the future.
>
> The alternative is to document, for 10.2, when
>
> derby.database.sqlAuthorization=true
>
> that behaviour may change in the future, be warned (with possible examples).
>
> Dan.
>
>
>
Re: Grant -revoke (464) and future backwards compat
Posted by Francois Orsini <fr...@gmail.com>.
The legacy 'defaultConnectionMode' access mode was implemented as a
simplistic authorization model, mostly aimed at embedded applications
back then. It was also meant to ease application development as by
default a user is in full-access mode.
Legacy access mode needs to be kept because if not, application out
there using it would fail - even if we had a substitute (ANSI Grant &
Revoke) and some logic to emulate what legacy did (by adding
predifined ROLES corresponding to the various legacy access modes), we
would still need to keep around as end-users may have script using the
legacy syntax - I know this is not a discussion about removing legacy
access mode or not but as it is going to be there (until it gets
deprecated and if it ever happens) for now, we need to have an access
mode switch property - we can make both access modes work at runtime,
but we still need a property to tell derby that sqlStandard mode is
now in effect and it's no longer a default of fullAccess mode.
We need to document "activation" of the ANSI Grant & Revoke mode very
carefully with the impact on existing application if it were to be
turned ON by some user. This leads to another issue with the lack of
System Privileges at the moment to ensure that you're not going to
have (any) user (re)setting a database property in your back...At
least the sqlAuthorization property cannot be reset as Satheesh
mentioned already in the specs.
What we could do is raise a big Warning message the first time this
property ('sqlAuthorization ') is set to true and have the user
execute it a second time to confirm the action. I've seen this done
elsewhere as a confirmation mechanism to important configuration
properties.
As far as CREATE SCHEMA, a user would need to be granted a specific
system privilege to allow a user to create some schema...same way as
CREATE TABLE, etc....or have some kind of DBA role (when ANSI SQL
ROLES are available)...
--francois
On 2/16/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Daniel John Debrunner wrote:
>
> The reason that grant/revoke (DERBY-464) has to add a mode
>
> derby.database.sqlAuthorization={true|false}
>
> is that the grant/revoke requires a different access model to Derby's
> current model.
>
> I am still not satisfied this is the right way to switch authorization
> models. Could having a system procedure to switch mode from legacy
> authorization to standard authorization be better? This property can only be
> changed from false to true and need special code to ensure this.
>
> I'd like to ensure that we don't need to introduce another mode switch
> in a future release, related to this area. Ending up several releases
> from now with these properties would be bad
>
> 10.3
> derby.database.sqlAuthorizationWeReallyMeanItThisTime={true|false}
>
> 10.4
> derby.database.sqlAuthorizationOKMaybeThisTime={true|false}
>
> :-)
>
> This is a problem... I think it would be wise to advise users to follow SQL
> standard mode of operation or any other that we may enhance derby to in the
> future.
>
>
> Maybe there's other work that needs to be done to avoid jarring mode
> switches in the future.
>
> The alternative is to document, for 10.2, when
>
> derby.database.sqlAuthorization=true
>
> that behaviour may change in the future, be warned (with possible examples).
>
> It should be ok to document recommended practices... no?
>
> Satheesh
>
>