Context

Definition

Use

SQL identifiers

User identifier is a SQL identifier, section 5.4 SQL 2003. A regular identifier is upper-cased to represent an authorization identifier, e.g. eve, eVe and EVE all represent the authorization identifier EVE. A delimited identifier (with double quotes) does not have any case conversion to its authorization identifier, e.g. "eVe" represents the authorization identifier eVe. Note that the delimited identifier "EVE" represents the authorization identifier EVE which is the same user as the regular identifiers eve, EVE and EvE etc. The authorization identifier represents how a user name is stored/processed by the SQL engine in a SQL context, e.g. the system tables representing granted permissions. Note that derby.* properties are not in the SQL context.

grantee in GRANT statement

grantee in REVOKE statement

CURRENT USER expression

Returns the authorization identifier for the current user.

VALUES CURRENT USER

VALUES SESSION_USER

VALUES {fn user()}

SQL Routine

A system SQL routine taking a USERNAME parameter requires the caller pass in the authorization identifier, this is to align with the value being passed in from CURRENT USER or a value obtained from a system table representing a user.

SYSCS_UTIL.SYSCS_SET_USER_ACCESS DERBY-3095

SYSCS_UTIL.SYSCS_GET_USER_ACCESS DERBY-3095

JDBC Connection request

Follows the rules of SQL identifiers including support for delimited identifiers.

User named passed into method call, e.g. DataSource.setUser, DriverManager.getConnection

User set as user property in DriverManager connection request

User name on JDBC URL

JDBC DatabaseMetaData.getUserName()

Returns the user name used to make the JDBC connection request which is a SQL identifier and not the authorization identifier.

conn.getMetaData().getUserName()

Derby BUILTIN authentication

Follows the rules of SQL identifiers including support for delimited identifiers. DERBY-3150

System property derby.user.username=password

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.user.username', 'password')

Connection authorization

User names set in Java or database properties. Documented as following the rules of SQL identifiers including support for delimited identifiers. Thus derby.database.fullAccessUsers=eVe and derby.database.fullAccessUsers=EVE have identical meaning.

derby.database.fullAccessUsers

derby.database.readOnlyAccessUsers

GRANT SELECT ON t TO eve

REVOKE SELECT ON T FROM EvE

GRANT SELECT ON t TO EVE

GRANT SELECT ON t TO "EVE"

VALUES CURRENT_USER will return EVE

CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS('EVE', NULL)

VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS('EVE')

jdbc:derby:db;user=eve

jdbc:derby:db;user=EVE

ds.getConnection("eve", password); // Java code DatabaseMetaData.getUserName() will return eve

ds.getConnection("EVE", password); // Java code DatabaseMetaData.getUserName() will return EVE

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.EVE', 'password')

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.eve', 'password')

derby.database.readOnlyAccessUsers=eve

GRANT SELECT ON t TO "eve"

REVOKE SELECT ON T FROM "eve"

VALUES CURRENT_USER will return eve

CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS('eve', NULL)

VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS('eve')

jdbc:derby:db;user="eve"

ds.getConnection("\"eve\"", password); // Java code DatabaseMetaData.getUserName() will return "eve"

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user."eve"', 'password')

derby.database.readOnlyAccessUsers="eve"