The following users have specific privileges:
The universal user has all special privileges for a database, except for USAGE ON STOGROUP.
The owner of a storage group has the special privilege USAGE ON STOGROUP for that storage group.
The owner of a schema has all table privileges for the base table in his or her schema.
A view owner's access rights to his or her view are described on "Granting privileges with the SQL statement GRANT".
The universal user, all schema owners and the owners of storage groups and views who were granted their privileges with “WITH GRANT OPTION”, can grant their respective privileges to other users with the SQL statement GRANT.
The SQL statement GRANT can be represented schematically as follows:
GRANT privilege(s) ON object TO grantee(s) [WITH GRANT OPTION]
GRANT can be specified as an independent SQL statement or as part of the CREATE SCHEMA statement.
The “grantor” is the user who grants the privilege with GRANT; the “grantee” is the user to whom this privilege is granted. Depending on the privilege, the “object” can be a table, a database, or a storage group.
Users can only pass on a privilege they have been granted with GRANT to other users if the privilege was granted “WITH GRANT OPTION”. In other words, grantees can act in turn as grantors and grant the same privilege with or without “WITH GRANT OPTION”. This feature can be used to construct a hierarchically organized access protection scheme.
A grantor can specify either the authorization identifiers of selected users or the keyword PUBLIC for the grantee in the GRANT statement. PUBLIC grants the privilege to all users entitled to work with the database.
If a grantor assigns a grantee the same privilege twice – once with and once without the “WITH GRANT OPTION” clause – then “WITH GRANT OPTION” always applies.
A grantor can also use the GRANT statement to grant a table privilege to the owner of a different schema. This allows schema owners, for example, to define views of their schemas that are based on tables that have a different owner.
Special privileges and table privileges should be assigned on the basis of a well-planned data-protection scheme. Special privileges in particular should only be granted to a select group of individuals, and the “WITH GRANT OPTION” should be used with extreme caution.
Special aspects of granting privileges for the creation of views
Users only receive the right to create a view with CREATE VIEW via the table privilege SELECT for the table on which the view is based. Here, there are two specific cases to differentiate between:
The view is updatable.
In this case the view is based on just one base table. If the view's owner has the table privileges INSERT, UPDATE or DELETE for the underlying base table, he or she automatically also receives the relevant table privilege for the view.The view is not updatable.
The owner automatically receives the table privilege SELECT for the view.
One can imagine the automatic granting of privileges for a view as follows: SESAM/SQL grants the relevant table privilege for the view with an implicit GRANT statement.
The owner of the view can only pass on a table privilege for the view with GRANT if he or she himself has the “WITH GRANT OPTION” for the base table on which the view is based.
Example
The example that follows outlines the granting of privileges to the owner of a view and the passing on of privileges to another user.
User A owns the base table T and therefore has the table privileges SELECT, INSERT, DELETE, UPDATE and REFERENCES. A is entitled to pass theses privileges on to other users.
A passes the table privileges DELETE “WITH GRANT OPTION” and SELECT to user B.
GRANT DELETE ON T TO B WITH GRANT OPTION
GRANT SELECT ON T TO B
Because user B has SELECT authorization for base table T, he or she can create a view of T:
CREATE VIEW V AS SELECT * FROM T
B thus automatically, i.e. from SESAM/SQL, obtains the table privileges granted for table T (SELECT and DELETE "WITH GRANT OPTION") for view V, too. The following table privileges for T and V therefore exist for B:
Grantor | Grantee | Object | Table | WITH GRANT |
A | B | T | DELETE | yes |
A | B | T | SELECT | no |
SESAM/SQL | B | V | DELETE | yes |
SESAM/SQL | B | V | SELECT | no |
Table 43: Table privileges for base table T and view V
A then grants the following table privileges with GRANT:
GRANT UPDATE ON T TO B,C
GRANT INSERT ON T TO B WITH GRANT OPTION
B grants the following table privilege:
GRANT DELETE ON V TO D WITH GRANT OPTION
This means that the following new table privileges have been granted:
Grantor | Grantee | Object | Table | WITH GRANT |
A | B | T | UPDATE | no |
A | C | T | UPDATE | no |
SESAM/SQL | B | V | UPDATE | no |
A | B | T | INSERT | yes |
SESAM/SQL | B | V | INSERT | yes |
B | D | V | DELETE | yes |
Table 44: New table privileges for the base table T and view V