Re: Increase Security ( A review of ROLES )

Someone mentioned ROLES. I just wrote an articule on ROLES for our User Group Newsletter. The following is the articule. I hope this is helpful. Regards - Lester ----------------------------------------------------------------- From the Washington (DC) Area Informix Users Group Newsletter: Roles - A New Security Feature in INFORMIX OnLine 7.10.UD1 by Lester Knutsen 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 t