db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Francois Orsini <francois.ors...@gmail.com>
Subject Re: Grant and Revoke, Part I ... DERBY-464...
Date Thu, 22 Dec 2005 10:08:27 GMT
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.
>
>

Mime
View raw message