First published in the Washington Area Informix User Group Newsletter
Volume 5, No. 4 - October 1995
INFORMIX OnLine 7.10.UD1 was released with a few surprises in the form of new features. One of the features I was most interested in is Roles. Roles provide a way to grant and revoke privileges to a function, rather than to individual users. A user is granted the privilege to use one or more Roles. When a user needs access to the privileges of a Role, the user or application sets the current access levels to the Role. And, when the user is finished performing the functions for which the Role was granted, the Role can be unset and the privileges are no longer in effect. This article will take a look at a few examples of using Roles to improve your security, and discuss some of the limits. The examples for this article were developed using the Stores database in OnLine 7.10.UD1 running on a Sun Sparc.
We will start with a quick example of using Roles. The stores database has a table called orders. For this example, we will restrict insert, update, and delete access to a group of users in the Orders Department. First, we must revoke all privileges from everyone on this table. Then, instead of granting the select, insert, update and delete privileges to each individual, we will create three Roles. One Role for select-only access, which we will call "read_ord". The next will be for select, update and insert access, which we will call "upd_ord", and the final one will include delete privileges, which we will call "del_ord". Then we will grant individuals the privilege to use these Roles. Finally, we will set-up the applications to use these Roles.
Creating Roles
To create a Role, we begin with the "CREATE ROLE role_name" statement, where role_name is an eight character name for the Role. The role_name cannot be the name of a user on the system because it is stored in the system table sysusers. To create a Role you must have dba privileges in the database. The following statements create our three Roles:
create role read_ord; create role upd_ord; create role del_ord;
After creating the Roles we can perform the following query on the system table sysusers to see the Roles:
select * from sysusers where usertype = "G";
This returns the following data:
username usertype priority password read_ord G 5 upd_ord G 5 del_ord G 5
In the sysusers table, a usertype of "G", a new usertype in 7.10.UD1, indicates a Role definition.
Privileges for a Role
Granting privileges to a Role is the same as granting privileges to a user, and uses the same syntax. For our example to work, we must first revoke all privileges on the orders table. The following SQL statement will show all privileges that have been granted on the orders table:
select * from systabauth where tabid in (select tabid from systables where tabname = "orders" );
To revoke privileges from public, we use the following SQL statement:
revoke all on orders from public;
This command will need to be repeated for every user with privileges to the orders table.
Next, we will use SQL to grant the privileges to each Role:
grant select on orders to read_ord; grant select, insert, update on orders to upd_ord; grant select, delete on orders to del_ord;
After granting the privileges, we can run our query against the system tables to see the results:
select * from systabauth where tabid in ( select tabid from systables where tabname = "orders" );
Results:
grantor grantee tabid tabauth lester del_ord 101 s---d--- lester read_ord 101 s------- lester upd_ord 101 su-i----
This shows that the user lester granted the privileges, the grantee column shows the Role name, and the tabauth column shows the privileges.
Adding Users to a Role
Now we need to add our users to the appropriate Roles. Let's say we have five users in the orders department: Abby, Joe, Ron, Jack, and Linda. We want everyone to read orders, linda and abby to add and update orders, and abby to be able to delete orders. To accomplish this, we need to use the following SQL statements.
grant read_ord to abby, joe, ron, jack, linda; grant upd_ord to abby, linda; grant del_ord to abby ;
In 7.1 there is a new system table called sysroleauth that stores the information about users' access to Roles. If we perform a select on that table, we get back the following information:
rolename grantee is_grantable read_ord abby n read_ord joe n read_ord ron n read_ord jack n read_ord linda n upd_ord abby n upd_ord linda n del_ord abby n
This shows the Role name, the users that have access to that Role and an N (No, cannot grant this Role to someone else) or Y (Yes, can grant this Role to someone else).
Using a Role - SET ROLE Statement
Once a user has been granted the privilege to use a Role, they do not yet have automatic access to the privileges of the Role. The user, or the application executed by the user, must first execute the SET ROLE statement. (Any user with SQL knowledge and connect privilege to the database can use the SET ROLE command to activate a role.)
If Joe tries to select data from the orders table before the SET ROLE statement has been executed, he will see the following error message:
select * from orders; # ^ # 272: No SELECT permission.
However, when Joe, or the application he is using, sets the current Role to one that has proper privileges, he will be able to read data. The following SQL command will set the Role and select all the data from the orders table:
set role read_ord ; select * from orders;
When a user is finished with a Role, the Role can be set to NONE or NULL. This has the effect of taking away the privileges of the Role. In your application, when a user is done with a Role, use the SET ROLE NONE or NULL statement to end the use of the Role's privileges. The following is what happens when we do this for Joe.
set role none; select * from orders; # ^ # 272: No SELECT permission.
Roles in Your Applications
Roles are designed to be used in your applications. The application would set a Role, perform the tasks, and then unset the Role. This way a user only has the privileges while the application is running. Once the application is complete, the user has no privileges.
In order to use a Role in an application, you will need to prepare and execute a statement setting the Role for the application. The following statements are examples of what will be needed in a 4GL program to set the Role to "read_ord":
prepare role_stmt from "set role read_ord"
execute role_stmt
if ( sqlca.sqlcode != 0 ) then
error "Cannot use this role"
fi
After executing the statement, check to make sure it was successful. Otherwise, the user will attempt to perform functions without the proper privileges, which will generate many other SQL errors.
There are several new error messages in 7.10.UD1 to handle Roles. For example, if a user does not have permission to use a Role, the sqlca.sqlcode is "19805: No privilege to set to the Role."
Conclusion
Roles are a great security feature, and when you have many users, this will enable you to more effectively control your database privileges. The only drawback, which is often true with a products new features, is that you will need to change your applications to take advantage of Roles. Unfortunately, Roles are not [yet?] available for INFORMIX SE 7.10.UD1. But I see nothing unique to OnLine in the implementation of Roles to prevent them from being implemented in SE.
I intend to start using Roles as a means for security in all new applications we develop. One question I keep getting asked is what is the impact of Roles on our security product DB Privileges? DB Privileges allows you to create groups of users and grant and revoke privileges to a group with an easy-to-use menu interface. The next version will include support for creating and controlling privileges through Roles with a menu interface. The current version of DB Privileges works well with Roles, since Roles are displayed on the Users screen. Table and column privileges can be granted to, and revoked from, Roles by entering a Y or N on the data entry screen.