Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 9647 invoked from network); 22 Dec 2005 10:08:53 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 22 Dec 2005 10:08:53 -0000 Received: (qmail 16837 invoked by uid 500); 22 Dec 2005 10:08:52 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 16600 invoked by uid 500); 22 Dec 2005 10:08:50 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 16591 invoked by uid 99); 22 Dec 2005 10:08:50 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Dec 2005 02:08:50 -0800 X-ASF-Spam-Status: No, hits=0.9 required=10.0 tests=HTML_10_20,HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of francois.orsini@gmail.com designates 64.233.162.197 as permitted sender) Received: from [64.233.162.197] (HELO zproxy.gmail.com) (64.233.162.197) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Dec 2005 02:08:48 -0800 Received: by zproxy.gmail.com with SMTP id z6so385131nzd for ; Thu, 22 Dec 2005 02:08:27 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=mu7JGhXSOjzNUAjJy/SDLYb0E49rJCUyw1MsBG6FNxCn8p9jp1EJ+/P+fkAWkKZMzs7U2f/1bjont0w9oQ26KMlGv9Ad0qBF0727t2Kzz+YPHF6jIMgE8OclIvXlFJShYJo42XDQYN4TNpROIcoaiYgIrGFR+bQXDZrEIfy3HTk= Received: by 10.64.251.5 with SMTP id y5mr117853qbh; Thu, 22 Dec 2005 02:08:27 -0800 (PST) Received: by 10.64.209.10 with HTTP; Thu, 22 Dec 2005 02:08:27 -0800 (PST) Message-ID: <7921d3e40512220208u44bb2a54se440d79de684be1@mail.gmail.com> Date: Thu, 22 Dec 2005 02:08:27 -0800 From: Francois Orsini To: derby-dev@db.apache.org Subject: Re: Grant and Revoke, Part I ... DERBY-464... In-Reply-To: <43909A64.5020205@Sourcery.Org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2463_28165686.1135246107646" References: <435FCD22.3050607@Sourcery.Org> <43909A64.5020205@Sourcery.Org> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_2463_28165686.1135246107646 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 privileg= e 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=3Dsa,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 databas= e 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 th= e > 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 sys= tem > 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 fo= llow > 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' mod= e, > 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 with= out > 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 . > 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 syst= em. > 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 wil= l > have the option of operating using the legacy Derby access control system= or > a SQL2003 compatible access control system. This paper describes a propos= al > 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) > > 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 ::=3D table-grant-statement* > * | routine-grant-statement* > > *table-grant-statement ::=3D* > * *GRANT* table-privileges *TO* grantees* > > *routine-grant-statement ::=3D* > * *GRANT EXECUTE ON* routine *TO* grantees* > > *revoke-statement ::=3D table-revoke-statement* > * | routine-revoke-statement* > > *table-revoke-statement ::=3D* > * *REVOKE* table-privileges *FROM* grantee, ...* > > *routine-revoke-statement ::=3D* > * *REVOKE* *EXECUTE ON* routine *FROM* grantee, ... *RESTRICT > > *table-privileges ::=3D table-action* ON [TABLE] *table-or-view-name* > > *grantees ::=3D { *PUBLIC* | user-identifier,... }* > > *table-action ::=3D *ALL PRIVILEGES *| action, ...* > > *action ::=3D *SELECT* [ ( privilege-column-list ) ]* > * | *DELETE > * | *INSERT > * | *UPDATE* [ ( privilege-column-list ) ]* > * | *REFERENCES* [ ( privilege-column-list ) ]* > * | *TRIGGER > > *routine ::=3D { *FUNCTION* | *PROCEDURE* } routine-designator* > > *routine-designator ::=3D qualified-name [ signature ]* > > *signature ::=3D ( 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 onl= y > 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 permissi= on > 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 t= he > 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 acces= s > 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 u= sed > in a view, trigger, or constraint, and permission is being revoked from t= he > 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 =3D t2.c1 WHE= RE > t2.c2 =3D 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 ca= n > 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 appropria= te > permissions. It is only required that the owner of the trigger or constra= int > have the appropriate permissions. > > When a view, trigger, or constraint is created Derby checks that the owne= r > has the required permissions, throwing an SQLException if not. If any of = the > required permissions are later revoked then the view, trigger, or constra= int > 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 lit= tle > 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 functi= on > and procedure element lists. The syntax of external-security-clause is: > > *external-security-clause ::=3D* > [ 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 a= ny > objects created by the routine are owned by the owner of the routine. Thi= s > 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 t= he > 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 t= o > 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 o= f 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 ::=3D* > 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 effectiv= e > 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 th= e > 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 sa= me > 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 t= he > standard builtin functions. This permission cannot be revoked. However wh= en > a database is created only the database owner has execute permission on t= he > 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-475has added a new table based mechanism to d= efine 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 t= he > user who started the JVM. The operating system does not know anything abo= ut > 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_DATABA= SE > 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 t= o a > database. This is done through Derby properties. The > derby.database.defaultConnectionMode property specifies the default acces= s > permission. Its value must be "noAccess", "readOnlyAccess", or "fullAcces= s" > (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. T= his > is incompatible with SQL2003. > > I propose to add a new derby.database.defaultConnectionMode property valu= e > to Derby, "sqlStandard". If the derby.database.defaultConnectionModeprope= rty value for a database is "sqlStandard" then authorizations for the > database will follow the SQL standard model described in this document. I= f > the value is "noAccess", "readOnlyAccess", "fullAccess", or null then the > Derby legacy authorization model will be used. GRANT and REVOKE statement= s > 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 databaseand 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 no= t > 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 an= d > hence support grant/revoke by default in future releases. A scheme needs = to > be evolved to reduce any disruptions to existing users of Derby. > > ------=_Part_2463_28165686.1135246107646 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Satheesh,

Regarding the issues you mentioned:

  > 1) Should Derby support upgrading a 10.1 database to 10.2 direc= tly 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=3Dsa,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 defaultC= onnectionMode 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, j= ust curious)

Thanks,

--francois

On 12/2/05, Satheesh Bandaram < satheesh@sourcery.org> wrote:
=20 =20 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:
=20 =20 Hi

I just attached my proposal to enhance Derby by adding Grant and Revoke capability to DE= RBY-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



=20 =20 =20 =20

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 DER= BY-464: (http://issues.a= pache.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 ::=3D table-grant-statement
  | routine-grant-statement

table-grant-statement ::=3D
  GRANT table-privileges TO grantees

routine-grant-statement ::=3D
  GRANT EXECUTE ON routine TO grantees

revoke-statement ::=3D table-revoke-statement
  | routine-revoke-statement

table-revoke-statement ::=3D
  REVOKE table-privileges FROM grantee, ...

routine-revoke-statement ::=3D
  REVOKE EXECUTE ON routine FROM grantee, ... RESTRICT

table-privileges ::=3D table-action ON [TABLE] table-or-view= -name

grantees ::=3D { PUBLIC | user-identifier,... }

table-action ::=3D ALL PRIVILEGES | action, ...

action ::=3D SELECT  [ ( privilege-column-list ) ]
       |  DELETE
       |  INSERT
       |  UPDATE [ ( privile= ge-column-list ) ]
       |  REFERENCES [ ( privilege-column-list ) ]
       |  TRIGGER

routine ::=3D { FUNCTION | PROCEDURE } routine-designator

routine-designator ::=3D qualified-name [ signature ]

signature ::=3D ( 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 =3D t2.c1 WHERE t2.c2 =3D 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 ::=3D
  [ 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,
           &nb= sp;            =              IN S_YEAR INTEGER,
            &nb= sp;            =             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 ::=3D
       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 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&q= uot;, "readOnlyAccess", or "fullAccess" (case insensitive). T= he 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.defaultConnectionMode property value for a database is "sqlStandard" then authorization= s for the database will follow the SQL standard model described in this document. If the value is "noAccess", "readOnlyAccess", "ful= lAccess", 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 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 standar= d 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.


------=_Part_2463_28165686.1135246107646--