Privileges Granted Through Roles

Access privileges that a user holds through the current role can support operations on database objects for which the user does not hold sufficient access privileges as an individual or as PUBLIC.

Code examples that follow illustrate the new behavior for access privileges that are granted to roles. The following example shows SQL statements issued by user admin who holds the DBA privilege in a database called ruv:

{ Example 1: WITH GRANT OPTION, user admin }

DATABASE ruv;
CREATE ROLE schaden;
GRANT schaden TO user1;

CREATE TABLE "admin".test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
GRANT SELECT ON test TO schaden WITH GRANT OPTION;

After successful execution of the SQL statements in this example, an entry for the test table in the systabauth system catalog table shows user admin as grantor and the schaden role as grantee. A capital S in the systabauth.tabauth column indicates the WITH GRANT OPTION attribute for schaden. When schaden is the active role, user1 now can grant the Select privilege to other users.

In the previous example, the Select privilege held by the role schaden can be revoked by user admin as the grantor of the role. In the next example, user admin grants the Select privilege on the test table to user1, but includes the AS clause in the GRANT statement to make the schaden role the grantor:

{ Example 2: Role AS grantor, user admin }

DATABASE Ruv;
CREATE ROLE schaden;

CREATE TABLE "admin".test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
GRANT SELECT ON test TO schaden WITH GRANT OPTION;
GRANT SELECT ON test TO user1 AS schaden;

In Example 2, role schaden is granted the Select privilege with grant option on table test. In the last statement, the DBA grants the Select privilege as schaden to another user (user1). To get around the restriction mentioned in the section above, a DBA can specify a role as a grantor of privileges.

We have a role schaden, which was granted the Select privilege on test. In the last statement here, the DBA is able to grant the privileges in the name of schaden to another user (user1). To get around the restriction mentioned in the section above, a DBA is able to have a role as a grantor of privileges The reason is that user admin does not attempt to grant privileges as a user in the schaden role. In this example, user admin grants the privileges in the name of a specific role in the AS clause of the GRANT statement. The server is able to identify the schaden role as the grantor of the privilege.

The next example shows code from two sessions, in one of which user1 creates a view on the test table by using the Select privilege on that was acquired by receiving and activating the schaden role:

{Example 3: Creating a view based on role privileges, User admin }

DATABASE ruv;
CREATE ROLE schaden;
GRANT schaden TO user1;

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
GRANT SELECT ON test TO schaden WITH GRANT OPTION;

{User user1}

DATABASE ruv;
SET ROLE schaden;
CREATE VIEW v1 AS SELECT * FROM test;

After the vi view is created, the systabauth system catalog table shows the owner of the view as user1 and that user1 has the Select privilege on the view with grant option.

In the following example, user1 creates the view that required the privileges of the role schaden. This creation succeeds, even though the neither user1 nor active role leben were granted the necessary Select privilege on the table test, role schaden was granted that privilege, and role schaden was granted to role leben, the active role of user1.

The Select privilege that user1 holds on the test table is called a cascading role privilege, because user1 holds it through a role that was granted to another role.

{Example 3.1:  Create a view based on Cascading Role privileges} 

{ User admin }

DATABASE ruv;
CREATE ROLE schaden;
GRANT schaden TO user1;
CREATE ROLE leben;
GRANT schaden TO leben;
GRANT leben TO user1; 

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
GRANT SELECT ON test TO schaden WITH GRANT OPTION;

{ User user1 }

DATABASE ruv;
SET ROLE leben;
CREATE VIEW v1 AS SELECT * FROM test;;

In Example 4 below, two tables have different owners. User admin grants Select privileges on both tables only to the role schaden, but grants this role to user1. By setting this role, user1 obtains sufficient privileges to create a view on both tables.

{Example 4:  Create a view based on 2 tables with different owners}

{User admin } 

DATABASE ruv;
CREATE ROLE schaden;
GRANT schaden TO user1;

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
CREATE TABLE "user2".test1 ( bb INTEGER );
REVOKE ALL ON test1 FROM PUBLIC AS user2;

GRANT SELECT ON test TO schaden WITH GRANT OPTION;
GRANT SELECT ON test1 TO schaden WITH GRANT OPTION AS user2;


{ User user1 }

DATABASE ruv;
SET ROLE schaden;
CREATE VIEW v1 AS SELECT * FROM test , test1 WHERE aa=bb;

In Example 4.1 user admin similarly creates two tables with different owners, and grants to user1 a role that holds Select privileges on those tables only through another role.

{ Example 4.1:  Create a view on 2 tables with different owners with  
                cascading roles}

{ User admin }

DATABASE ruv;
CREATE ROLE schaden;
CREATE ROLE leben;;
GRANT schaden TO leben;
GRANT leben TO user1;


CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
CREATE TABLE "user2".test1 ( bb INTEGER );
REVOKE ALL ON test1 FROM PUBLIC AS user2;

GRANT SELECT ON test TO schaden WITH GRANT OPTION;
GRANT SELECT ON test1 to schaden WITH GRANT OPTION as user2;


{ User user1 }

DATABASE ruv;
SET ROLE leben;;
CREATE VIEW v1 AS SELECT * FROM test , test1 WHERE aa=bb;

As in the previous examples, setting a role that holds the schaden role provides user1 with the Select privileges needed to create a view on both tables.

{Example 5:  Create a View in deputy for another user with a default role set}

{User admin }

DATABASE ruv;
CREATE ROLE schaden;
GRANT schaden TO user1;
GRANT DEFAULT ROLE schaden TO user1

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
CREATE TABLE "user2".test1 ( bb INTEGER ));
REVOKE ALL ON test1 FROM PUBLIC AS user2;

GRANT SELECT ON test TO schaden WITH GRANT OPTION;
GRANT SELECT ON test1 TO schaden WITH GRANT OPTION AS user2;

CREATE VIEW 'user1'.v1 AS SELECT * FROM test , test1 WHERE aa=bb;

The CREATE VIEW statement in Example 5 succeeds because on the additional check of the user privileges, if there is a default role and if this role exists, the role privileges are combined with the user privileges. If the default role did not exist, however, this command would return an error -302.

In the next example, user1 holds a default role (leben) to which another role (schaden) is granted.

{ Example 5.1:  Create a View in deputy for another 
  user with cascading role as default role  }

{ User admin }

DATABASE ruv;
CREATE ROLE schaden;
CREATE ROLE leben;
GRANT schaden TO leben;

GRANT DEFAULT ROLE leben TO user1

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
CREATE TABLE "user2".test1 ( bb INTEGER );
REVOKE ALL ON test1 FROM PUBLIC AS user2;

GRANT SELECT ON test TO schaden WITH GRANT OPTION;
GRANT SELECT ON test1 TO schaden WITH GRANT OPTION AS user2;


CREATE VIEW 'user1'.v1 AS SELECT * FROM test , test1 WHERE aa=bb;

This provides user1 with sufficient Select privileges for user admin to create a view and make user1 the owner of that view.

In the next example, the user user1 does not have any privileges on the table test. The Select privilege is not available, because the system does not consider the current or default role when determining privileges in the GRANT statement. When user1 attempts to grant the Select privilege to another user, this leads to error -302.

Example 6: Grant of privileges in the name of a Role

{User admin }

DATABASE ruv;
CREATE ROLE schaden;
GRANT schaden TO user1

CREATE TABLE 'admin'.test ( aa INTEGER );
REVOKE ALL ON test FROM PUBLIC;
GRANT SELECT ON test TO schaden WITH GRANT OPTION;

{User user1:}

SET ROLE Schaden;
GRANT ALL ON test TO ebach;

–Error -302