Configuring User Authorization

When you specify user authorizations, SQLFire verifies that a user has been granted permission to access a schema, database object, or a SQL action.


Connection Authorization and SQL Standard Authorization

There are two types of user authorization in SQLFire: connection authorization and SQL standard authorization. Connection authorization specifies the basic access that users have when they connect to the distributed system. SQL authorization controls the permissions that users have on database objects or for SQL actions. You set the user authorization properties in SQLFire as system-level properties, either at the command line or connection string when booting SQLFire members, or in the sqlfire.properties file.

User Authorization Properties

You can set properties to control user authorizations for SQLFire. Some properties set the default access mode for all users. Other properties set the default level of access for specific user IDs.

The properties that affect authorization are:
  • sqlfire.authz-default-connection-mode—Controls the default access mode. Use sqlfire.authz-default-connection-mode to specify the default connection access that users have when they connect to a SQLFIre member. If you do not explicitly set the sqlfire.authz-default-connection-mode property, the default user authorization for a database is fullAccess, which is read-write access.
  • sqlfire.authz-full-access-users and sqlfire.authz-read-only-access-users —These properties specify one or more user IDs that have read-write access and read-only access to the distributed system as a whole.
  • sqlfire.sql-authorization —Enables SQL standard authorization. Use sqlfire.sql-authorization to control whether object owners can grant and revoke permission for other users to perform SQL actions on their database objects. The default setting for sqlfire.sql-authorization is FALSE. When sqlfire.sql-authorization 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.

If you do not configure user authorizations for a specific user ID, the user ID inherits whatever authorization is set as the default user authorization for the SQLFire member (sqlfire.authz-default-connection-mode).

Tip: If you set the sqlfire.authz-default-connection-mode property to noAccess or readOnlyAccess, you should allow at least one user read-write access. Otherwise, depending on the default connection authorization that you specify, your system may contain database objects that cannot be accessed or changed.

How User Authorization Properties Work Together

The sqlfire.authz-default-connection-mode and sqlfire.sql-authorization properties work together. The default settings for these properties allow anyone to access and drop the database objects that they create. You can change the default access mode by specifying different settings for these properties.
  • When the sqlfire.sql-authorization property is FALSE, the ability to read from or write to database objects is determined by the setting for the sqlfire.authz-default-connection-mode property. If sqlfire.authz-default-connection-mode is set to readOnlyAccess, users can access all of the database objects but they cannot update or drop those objects.
  • When sqlfire.sql-authorization is TRUE, the ability to read from or write to database objects is initially restricted to the owner of those database objects. The owner must explicitly grant permission for others to access the database objects. No one but the owner of an object or the JVM owner can drop the object.
  • The access mode specified for the sqlfire.authz-default-connection-mode property overrides 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.

Changing Connection Authorization Settings

Connection authorization properties are fixed for the duration of a connection. Establish a new connection in order to change authorization properties.