Using Roles to Grant Privileges

Go Up to Planning Security

In InterBase, you can assign privileges through the use of roles. Acquiring privileges through a role is a four-step process.

Create a role using the CREATE ROLE statement.
```
CREATE ROLE rolename;
```
Assign one or more privileges to that role using GRANT.
```
GRANT privilegelist ON tablename TO rolename;
```
Use the GRANT statement once again to grant the role to one or more users.
```
GRANT rolename TO userlist;
```
The role can be granted WITH ADMIN OPTION, which allows users to grant the role to others, just as the WITH GRANT OPTION allows users to grant privileges to others.
At connection time, specify the role whose privileges you want to acquire for that connection.
```
CONNECT 'database' USER 'username' PASSWORD 'password' ROLE 'rolename';
```

Use REVOKE to remove privileges that have been granted to a role or to remove roles that have been granted to users.

See the Language Reference Guide for more information on CONNECT, CREATE ROLE, GRANT, and REVOKE.

Granting Privileges to a Role

Once a role has been defined, you can grant privileges to that role, just as you would to a user.

The syntax is as follows:

GRANT <privileges>
 ON [TABLE] {tablename | viewname}
 TO rolename;
<privileges> = ALL [PRIVILEGES] | <privilege_list>
<privilege_list> = {
SELECT
| DELETE
| INSERT
| UPDATE [(col [, col …])]
| REFERENCES [(col [, col …])]
} [, <privilege_list> …]

See the following section Granting a Role to Users for an example of creating a role, granting privileges to it, and then granting the role to users.

Granting a Role to Users

When a role has been defined and has been granted privileges, you can grant that role to one or more users, who then acquire the privileges that have been assigned to the role.

To permit users to grant the role to others, add WITH ADMIN OPTION to the GRANT statement when you grant the role to the users.

The syntax is as follows:

GRANT {rolename [, rolename …]} TO {PUBLIC
 | {[USER] username [, [USER] username …]} } [WITH ADMIN OPTION];

The following example creates the DOITALL role, grants ALL privileges on DEPARTMENTS to this role, and grants the DOITALL role to RENEE, who then has SELECT, DELETE, INSERT, UPDATE, and REFERENCES privileges on DEPARTMENTS.

CREATE ROLE DOITALL;
GRANT ALL ON DEPARTMENTS TO DOITALL;
GRANT DOITALL TO RENEE;

Advance To:

Granting Users the Right to Grant Privileges