Privileges on views, triggers, and constraints

Views, triggers, and constraints operate with the privileges of the owner of the view, trigger, or constraint.

For example, suppose that 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 privileges 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 privilege on column t2.c1 and column t2.c2
• EXECUTE privilege on function f

When the view is created, only user anita has the SELECT privilege on it. User anita can grant the SELECT privilege on any or all of the columns of view s.v to anyone, even to users that do not have the SELECT privilege on t1 or t2, or the EXECUTE privilege on f. User anita then grants the SELECT privilege 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 the SELECT privilege on view s.v. Derby does not check to determine if user harry has the SELECT privilege on t1 or t2, or the EXECUTE privilege on f.

Privileges on triggers and constraints work the same way as privileges on views. When a view, trigger, or constraint is created, Derby checks that the owner has the required privileges. Other users do not need to have those privileges to perform actions on a view, trigger, or constraint.

If the required privileges are revoked from the owner of a view, trigger, or constraint, the object is dropped as part of the REVOKE statement.

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.