When the SQL standard authorization mode is enabled, object owners
can use the GRANT and REVOKE SQL statements to set the user privileges 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.
The GRANT statement is used to grant specific privileges to users or to
roles, or to grant roles to users or to roles. The
REVOKE statement is used to revoke privileges and role grants. 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
database owner
have full privileges on the object.
No other users have privileges on the object until the object owner grants
privileges to them.
Another way of saying that privileges on objects belong to the owner is to
call them definer rights, as opposed to invoker rights.
This is the terminology used by the SQL standard.
See the Derby Reference Manual for more
information on the GRANT and REVOKE statements.
Public and individual user privileges
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.
Exception: When you create a view, trigger, or constraint,
Derby 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,
Derby 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 is revoked, the object is automatically dropped.
Derby does not try to determine
if you have other privileges that can replace the privileges that are being
revoked.
- Example 1
- 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 v1 with the statement
SELECT * from zhi.t1. The view depends on the user-level
privilege that user harry has on t1. Subsequently,
user zhi revokes SELECT privileges from user harry on
table t1. As a result, the view harry.v1 is
dropped.
- Example 2
- User anita creates table t1 and grants
SELECT privileges to PUBLIC. User harry creates view v1 with
the statement SELECT * from anita.t1. The view depends on
the PUBLIC level privilege that user harry has on t1, since
user harry does not have user-level privileges on table t1 when
he creates the view harry.v1. Subsequently, user anita grants
SELECT privileges to user harry on table anita.t1.
The view harry.v1 continues to depend on the PUBLIC level privilege
that user harry has on t1. When user anita revokes
SELECT privileges from PUBLIC on table t1, the view harry.v1 is
dropped.
See
Privileges on views, triggers, and constraints for
more information.