Setting the SQL standard authorization mode

If you use NATIVE authentication, SQL standard authorization is automatically enabled. Otherwise, use the derby.database.sqlAuthorization property to enable SQL standard authorization.

If SQL standard authorization mode is enabled, object owners can grant and revoke permission for other users to perform actions on database objects. SQL standard authorization mode also controls users' ability to create, set, and drop roles.

The valid settings for the derby.database.sqlAuthorization property are:

• TRUE
• FALSE

The default setting for the derby.database.sqlAuthorization property is FALSE, unless NATIVE authentication is enabled.

The derby.database.sqlAuthorization property is usable only if the property derby.connection.requireAuthentication is also set to true, since SQL authorization is of no value unless authentication is also enabled. (With NATIVE authentication, both are enabled automatically.)

After you set the derby.database.sqlAuthorization property to TRUE, you cannot set the property back to FALSE.

You can set the derby.database.sqlAuthorization property as a system property or as a database property. If you set this property as a system property before you create the databases, all new databases will automatically have SQL authorization enabled. If the databases already exist, you can set this property only as a database property.

To enable SQL standard authorization for the entire system, set the derby.database.sqlAuthorization property as a system property:

derby.database.sqlAuthorization=true

To enable SQL standard authorization for a specific database, set the derby.database.sqlAuthorization property as a database property:

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.sqlAuthorization',
    'true')

• Using SQL standard authorization
  When the SQL standard authorization mode is enabled, object owners can use the GRANT and REVOKE SQL statements to set the user privileges for specific database objects or for specific SQL actions. They can also use roles to administer privileges.
• Privileges on views, triggers, and constraints
  Views, triggers, and constraints operate with the privileges of the owner of the view, trigger, or constraint.
• Using SQL roles
  When the SQL standard authorization mode is enabled, object owners can use the SQL roles facility to administer privileges.
• Upgrading an old database to use SQL standard authorization
  An old, unprotected database can be shielded with authentication and SQL authorization later on.
• SQL standard authorization exceptions
  SQL exceptions are returned when errors occur with SQL authorization.