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. They can also use roles to administer privileges.
The SQL standard authorization mode is a SQL2003 compatible access control system. You enable the SQL standard authorization mode by setting the derby.database.sqlAuthorization property to TRUE.
While Derby 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 Derby SQL authorization mode provides a more precise mechanism to limit the actions that users can take on the database.
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 database owner have full privileges on the object. No other users have privileges on the object until the object owner grants privileges to them.
The object owner can grant and revoke privileges for specific users, for specific roles, 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 or roles 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.
CREATE VIEW s.v(vc1,vc2,vc3) AS SELECT t1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE t2.c2 = 5User anita needs the following permissions to create the view:
Permissions on triggers and constraints work the same way as permissions on views. When a view, trigger, or constraint is created, Derby checks that the owner has the required permissions. Other users do not need to have those permissions to perform actions on a view, trigger, or constraint.
If the required permissions are revoked from the owner of a view, trigger, or constraint, the object is dropped as part of the REVOKE statement.
See the Derby Reference Manual for more information on the GRANT and REVOKE statements.