1.1

If a user with read-only access attempts to write to the system, SQLException 08004 - connection refused
is returned.
Set User Permissions for Database Objects in 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 permissions for specific database objects or for specific SQL actions.
The SQL standard authorization mode is a SQL2003 compatible access control system. You enable the SQL
standard authorization mode by setting the sqlfire.sql-authorization property to TRUE.
While SQLFire has a simpler database access mode which can be set to provide users with full, read-only, or no
access authorization, this simpler access mode is less appropriate for most client-server database configurations.
When users or applications issue SQL statements directly against the database, the SQL authorization mode
provides a more precise mechanism to limit the actions that users can take on the database.
• GRANT and REVOKE Privileges on page 249
• Set Public and Individual User Privileges on page 249
• Set Permissions on Views, Triggers, and Constraints on page 250
GRANT and REVOKE Privileges
The GRANT statement is used to grant specific permissions to users. The REVOKE statement is used to revoke
permissions. The grant and revoke privileges are:
• DELETE
• EXECUTE
• INSERT
• SELECT
• REFERENCES
• TRIGGER
• UPDATE
When a table, view, function, or procedure is created, the person that creates the object is referred to as the owner
of the object. Only the object owner and the SQLFire Member JVM Owner on page 246 have full privileges on
the object. No other users have privileges on the object until the object owner grants privileges to them.
Set Public and Individual User Privileges
The object owner can grant and revoke privileges for specific users or for all users. The keyword PUBLIC is
used to specify all users. When PUBLIC is specified, the privileges affect all current and future users. The
privileges granted and revoked to PUBLIC and to individual users are independent. For example, a SELECT
privilege on table t is granted to both PUBLIC and to the user harry. The SELECT privilege is later revoked
from user harry, but user harry has access to table t through the PUBLIC privilege.
Exception: When you create a view, trigger, or constraint, SQLFire first checks to determine if you have
the required privileges at the user-level. If you have the user-level privileges, the object is created and is
dependent on that user-level privilege. If you do not have the required privileges at the user-level, SQLFire
checks to determine if you have the required privileges at the PUBLIC level. If you have the PUBLIC
level privileges, the object is created and is dependent on that PUBLIC level privilege. After the object
is created, if the privilege on which the object depends on is revoked, the object is automatically dropped.
SQLFire does not try to determine if you have other privileges that can replace the privileges that are
being revoked.
User zhi creates table t1 and grants SELECT privileges to user harry on table t1.
User zhi grants SELECT privileges to PUBLIC on table t1. User harry creates view
Example 1
v1 with the statement SELECT * from zhi.t1. The view depends on the user-level
249
Configuring Authentication and Authorization