When you specify user authorizations, Derby verifies
that a user has been granted permission to access a system, database, object,
or SQL action.
There are two types of user authorization in
Derby, connection
authorization and SQL authorization:
- Connection authorization specifies the coarse-grained access
that users have to connect to a system or database.
- SQL authorization controls the fine-grained permissions that
users have on database objects or for SQL actions.
You can set the user authorization properties in
Derby as system-level
properties or database-level properties.
Set system-level user authorizations when you are developing applications,
or when you want to specify a secure default authorization for all users to
connect to all of the databases in the system.
Attention: If you use NATIVE authentication, fine-grained SQL
authorization is automatically enabled, and by default, all users enjoy full
coarse-grained access to the database. In this situation, fine-grained SQL
authorization cannot be turned off. However, you can still adjust coarse-grained
access to the database.
User authorization properties
There are several properties that you can set to control database-level
user authorizations. Some of the properties are general properties that set the
access mode for all users. Other properties are user specific properties that
set the type of access for specific user IDs.
The following properties affect authorization:
- The derby.database.sqlAuthorization property enables
SQL standard authorization. Use the derby.database.sqlAuthorization property
to specify if object owners can grant and revoke permission for users to perform
SQL actions on database objects. The default setting for the derby.database.sqlAuthorization property
is FALSE. When the derby.database.sqlAuthorization property
is set to TRUE, object owners can use the GRANT and REVOKE
SQL statements to set the user permissions for specific database objects or
for specific SQL actions.
- The derby.database.defaultConnectionMode property controls
the default coarse-grained access mode. This property specifies the
default connection access that users have when they connect to the database. If
you do not explicitly set the
derby.database.defaultConnectionMode property, the default
coarse-grained connection authorization for a database is
fullAccess, which is read-write access.
- The derby.database.fullAccessUsers and
derby.database.readOnlyAccessUsers properties
are additional coarse-grained access properties. These properties can be used to
specify the user IDs that have read-write access and read-only access to a
database.
If you do not specify the coarse-grained user authorizations for a specific
user ID, that user ID inherits the database's default coarse-grained connection
authorization.
Tip: If you set the derby.database.defaultConnectionMode property
to noAccess or readOnlyAccess, you should
allow at least one user read-write access. Otherwise, depending on the default
connection authorization you specify, you will configure the database so that
it cannot be accessed or changed.
How user authorization properties work together
The
derby.database.defaultConnectionMode property
and the
derby.database.sqlAuthorization property work together.
The default settings for these properties allow anyone to access and drop
the database objects that you create. You can change the default access mode
by specifying different settings for these properties.
- When the derby.database.sqlAuthorization property is
FALSE,
the ability to read from or write to database objects is determined by the
setting for the derby.database.defaultConnectionMode property.
If the derby.database.defaultConnectionMode property is set
to readOnlyAccess, users can access all of the database
objects but they cannot update or drop the objects.
- When the derby.database.sqlAuthorization property is
TRUE,
the ability to read from or write to database objects is further restricted
to the owner of the database objects. The owner must grant permission for
others to access the database objects. No one but the owner of an object or
the
database owner
can drop the object.
- The coarse-grained access mode specified for the
derby.database.defaultConnectionMode property
supplements the permissions that are granted by the owner of a database object.
For example, if a user is granted INSERT privileges on a table but the user
only has read-only connection authorization, the user cannot insert data into
the table.
Changes to connection authorization settings
Connection
authorization properties are fixed for the duration of a connection. If you
change the connection authorization properties during a connection, those
changes are not in affect until you establish a new connection.