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
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)
- Submit support for DDL. This would implement grant and revoke
statements
and recording of permissions in system tables.
- Add run-time support to enforce permission checking.
- 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
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.defaultConnectionMode
property 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 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.
