# Using 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. 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 permissions to users or to roles, or to grant roles to users or to roles. The REVOKE statement is used to revoke permissions 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.

#### 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 on 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 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.

#### Permissions on views, triggers, and constraints

Views, triggers, and constraints operate with the permissions of the owner of the view, trigger, or constraint. For example, user `anita` wants to create a view using the following statement:

    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 = 5

User `anita` needs the following permissions to create the view:

- Ownership of the schema `s`, so that she can create something in the schema
- Ownership of the table `t1`, so that she can allow others to see columns in the table
- SELECT permission on column `t2.c1` and column `t2.c2`
- EXECUTE permission on function `f`

When the view is created, only user `anita` has SELECT permission on it. User `anita` can grant SELECT permission on any or all of the columns of view `s.v` to anyone, even to users that do not have SELECT permission on `t1` or `t2`, or EXECUTE permission on `f`. User `anita` grants SELECT permission on view `s.v` to user `harry`. When user `harry` issues a SELECT statement on the view `s.v`, Derby checks to determine if user `harry` has SELECT permission on view`s.v`. Derby does not check to determine if user `harry` has SELECT permission on `t1`, or `t2`, or EXECUTE permission on `f`.

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 Java DB Reference Manual for more information on the GRANT and REVOKE statements.

Parent topic: Setting the SQL standard authorization mode
Related concepts
Using SQL roles
Related reference
SQL standard authorization exceptions