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.The role can be grantedGRANT rolename TO userlist;
WITH ADMIN OPTION
, which allows users to grant the role to others, just as theWITH 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;