Using Roles to Control Security

MarketPlace® is compliant with entry- and mid-level SQL92 standards regarding roles. A role is a named group of privileges.

In practice, a company might want to grant a particular collection of privileges to its sales people and different collection of privileges to its accounting staff. The privileges list in each case might be quite complex. Without roles, you would have to use a lengthy and detailed GRANT statement each time a new sales or accounting person joined the company. The role feature avoids that.

Implementing roles is a four-step process:

Step 1: Define the role.

CREATE ROLE role_name

Step 2: Grant privileges to the role

GRANT {one or more of INSERT, UPDATE, DELETE, SELECT, REFERENCES, EXECUTE}

ON table_name to role_name

GRANT UPDATE (col_name1, col_name 2) ON table_name TO role_name

When the access is restricted to certain columns, as in the second line, only UPDATE and REFERENCES can be granted. EXECUTE must always be granted in a separate statement.

Step 3: Grant the role to users

GRANT role_name TO user_name1, user_name2

The users have now have all the privileges that were granted to the role_name role. But there’s an additional step they must take before those privileges are available to them. They must specify the role when the connect to a database.

Step 4: Specify the role when connecting to a database.

Committing your Work

If you have not committed your work in IBConsole lately, do so now (Transaction > Commit). That way, if anything goes astray, you can roll back your work to this point (Transaction > Rollback).

Using a Role to Control Access

This exercise takes you through all four steps of implementing a role. You begin by creating another user and trying to access one of your tables when you are connected as this new user, in order to experience InterBase’s security at work. Then you create the Salesperson role, assign some privileges to it, assign the role to your new user, and finally, repeat the access that failed earlier to experience that your new user now has the necessary permissions.

1. Logout from the server.
2. Disconnect from the Tutorial database by selecting Database > Disconnect.
3. Login to the server as SYSDBA (password "masterkey) by choosing Server > Login.
4. Create a new user called CHRIS with a password of chris4ib. Refer to “Creating a New User” on page 6 if you’ve forgotten how to create a user.
5. Highlight the Tutorial database icon in the left pane and choose Database > Connect As to connect to the database as Chris:
6. Execute the following SELECT statement:

SELECT * FROM SALES

Note: InterBase issues an error statement, because all those tables you’ve created in this tutorial belong to user TUTOR. User CHRIS doesn’t have permission to do anything at all with them.

7. Now disconnect from CHRIS and reconnect to the TUTORIAL database again, this time as TUTOR. Leave the Role field empty.
8. Create a role called SALESPEOPLE.

   CREATE ROLE SALESPEOPLE

9. Execute the following GRANT statements to assign privileges to the SALESPERSON role. Note: Remember that you must execute each GRANT statement before entering the next one.

   GRANT SELECT, UPDATE, INSERT ON Sales to SALESPEOPLE

   GRANT update (contact_first, contact_last, phone_no) ON Customer to SALESPEOPLE

   GRANT SALESPEOPLE TO CHRIS

10. Connect to the TUTORIAL database as user CHRIS.
11. Now enter the same query that failed in Step 5. This time, InterBase retrieves all the rows in the Sales table, because CHRIS now has the required permissions, thanks to the role.
12. Now reconnect to the database as user TUTOR.

Advance To:

Triggers and Stored Procedures