Re: Create Roles

Harold, > Does anybody know the syntax for the create role statement? I have looked > through all of our informix books and I can't find any references to the > exact syntax to use. > > Also any comments on using roles to limit access to the databse would be > appreciated. Currently all of our security on the databse is built into > the 4GL applications and the database itself is pretty much unrestricted. > However, we would like to put some of the information on the web without > compromising security, which means the database will have to become > protected. > > Any comments would be appreciated. > The following is an articule I did on roles for our user group newsletter. I hope it helps. Make sure you revoke all public access to the database and tables if you want to limit access and put it on the web. Regards - Lester ############################################################################# # Lester Knutsen lester@advancedatatools.com # # Advanced DataTools Corporation Voice: 703-256-0267 # # Grant group privileges for Informix databases with DB Privileges # # Visit our Web page: www.advancedatatools.com # # Washington Area Informix User Group: www.iiug.org/~waiug/ # ############################################################################# 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 st