Cloudscape and Security
Page 5 of 10

User Authorization

Cloudscape provides user authorization, which is a means of granting users permission to access a database (or system). Cloudscape allows you to distinguish between full (read-write) access and read-only access. (Cloudscape Version 3.6 does not support the SQL GRANT and REVOKE features, which allow you to set permissions to specific database objects or specific SQL actions.)

Setting User Authorization

Database or System Access

Typically, you configure user authorization for a particular database. However, Cloudscape also allows you to configure user authorization for the system, which is useful during development and for specifying a secure default access for all databases in the system.

To control access to a particular database, set database-level properties that specify which users have full (read-write) access to the database and which users have read-only access to the database. Users not specified by either property inherit the default access for the database (none, read-only, or full read-write access). When not explicitly set, the default access for a database is full (read-write) access.

Setting the Default Access Mode

To specify the default access mode for the database, use the cloudscape.database.defaultConnectionMode property. You can set the property to the following values:

  • noAccess
  • readOnlyAccess
  • fullAccess (the default)

Cloudscape validates the authorization configuration properties when users set them. It raises an exception if a user attempts to set the properties to invalid values (see Exceptions).

Setting the Access Mode for Particular Users

To specify which particular users have full (read-write) access to a database, use the cloudscape.database.fullAccessUsers property. For example:

CALL PropertyInfo.setDatabaseProperty(
    'cloudscape.database.fullAccessUsers', 'sa,mary')

To specify which particular users have read-only access to a database, use the cloudscape.database.readOnlyAccessUsers property. For example:

CALL PropertyInfo.setDatabaseProperty(
    'cloudscape.database.readOnlyAccessUsers', 'guest,"Fred!"')

For these properties, you specify users as a comma-separated list (no spaces between the comma and the next user).

For users not specified with either property the access is specified by the cloudscape.database.defaultConnectionMode property.

NOTE: It is possible to configure a database so that it cannot be changed (or even accessed) using the cloudscape.database.defaultConnectionMode property. If you set this property to noAccess or readOnlyAccess, be sure to allow at least one user full access.

NOTE: Cloudscape provides a utility to make it easier to create and maintain the lists of read-only and full-access users. This utility is COM.cloudscape.database.UserUtility (aliased as UserUtility). For example, instead of having to retype the full list of cloudscape.database.fullAccessUsers when you add a full access user, you can simply use UserUtility's add method to add a user. For example:

CALL UserUtility.add(
    'mary', UserUtility->FULL_ACCESS_PERMISSION)

See the Javadoc for the utility or Examples for more details.

Notes on User Authorization

All the authorization properties are set for a connection when it is created. Changing any of the authorization properties does not affect existing connections. However, all future connections are affected by the change.

For more information about authorization identifiers, see Users and Authorization Identifiers.

Exceptions

If a user is not authorized to connect to the database specified in the connection request, SQLException 04501 is raised.

If a user with readOnlyAccess attempts to write to a database, SQLException 22502 is raised.

About Read-Only and Full Access

Table 8-2 shows which actions read-only and full-access users are permitted to perform on regular or source databases and on target databases (source and target databases are part of a synchronization system).

Table 8-2 Permissions for Read-Only and Full-Access Users

Action

Read-Only
Users

on Non-Target Databases

Full-Access
Users on Non-Target Databases

Read-Only
Users
on Targets

Full-Access
Users
on Targets

Executing SELECT statements

X

X

X

X

Reading database properties

X

X

X

X

Loading database classes from jar files

X

X

X

X

Creating a copy file from a publication


X
(only if a source)



Performing a refresh



X

X

Executing INSERT, UPDATE, or DELETE statements


X


X

Executing DDL statements


X



Adding or replacing jar files


X



Setting database properties


X


X (new in Version 3.0)

For more information about security in synchronization systems, see the Cloudscape Synchronization Guide.

Examples

This example shows the property settings to configure a database to support:

  • full access for a single user named sa
  • read-only access for anyone else who connects to the database

    CALL PropertyInfo.setDatabaseProperty(
        'cloudscape.database.defaultConnectionMode',
        'readOnlyAccess')

    CALL PropertyInfo.setDatabaseProperty(
        'cloudscape.database.fullAccessUsers', 'sa')

This example shows the settings to configure a database to support:

  • full access for a single user named "Fred!" (case-sensitive) with full (read-write) access
  • Read-only access for mary and guest
  • No access for other users.

(The example demonstrates the use of delimited identifiers for user names.)

CALL PropertyInfo.setDatabaseProperty(
    'cloudscape.database.defaultConnectionMode',
    'noAccess')

CALL PropertyInfo.setDatabaseProperty(
    'cloudscape.database.fullAccessUsers', '"Fred!"')

CALL PropertyInfo.setDatabaseProperty(
    'cloudscape.database.readOnlyAccessUsers', 'mary,guest')

This example uses UserUtility to create and then modify lists of read-only and full-access users.

-- first, make the default access mode noAccess
CALL PropertyInfo.setDatabaseProperty(
    'cloudscape.database.defaultConnectionMode',
    'noAccess')

-- then add users to the read-access user list
-- add bill
CALL UserUtility.add(
    'bill', UserUtility->READ_ACCESS_PERMISSION)

-- add jane
CALL UserUtility.add(
    'jane', UserUtility->READ_ACCESS_PERMISSION)

-- now add users to the full-access user list
-- add george
CALL UserUtility.add(
    'george', UserUtility->FULL_ACCESS_PERMISSION)

-- add felipe
CALL UserUtility.add(
    'felipe', UserUtility->FULL_ACCESS_PERMISSION)

-- now change jane's permission to full-access
CALL UserUtility.set(
    'jane', UserUtility->FULL_ACCESS_PERMISSION)

-- check on felipe's permissions:
VALUES UserUtility.getPermission('felipe')

-- take felipe out of all lists
CALL UserUtility.drop('felipe')