Re: User Access, security setting

Syed wrote: > > Hi everybody, > > My team(Dev. Team) and me are new to Informix and Relational Database > system. Traditionally we use to store the User details and password as > well as access level in a data file(table) and in each module we, check > whether a user have access to certain column(fields) to enable or > disable access to each field at runtime. However i am not sure how such > a validation can be performed in Informix, whether such validation can > be built into the Database Server itself or at each Module and is there > a need to create a separate table for users as we used to do or the DBMS > provide such facilities? Otherwise such a validation for individual > fields(columns) will become tedious. > > For example, let say, i want to allow user with access level 1 to be > able to access a table and all its columns, for update, delete and > insert operation, where as those with level 2 access may only perform > update to all columns and insert but not delete. Where as for those with > level 3, they may only read(display data only) selected columns, may be > column no 1 , 2 and 3, but they may not view column no 4. The first > level of checking is whether they have permission to read, update, > delete or insert, the next level of validation is which columns they may > access for update, read and insert operations. > > If such a validation need to be done in the application what is the best > solution? This can be done, in newer versions of ODS, with roles. Create a role for each class of access (1,2,3,4...) and grant table and column level permissions to those roles. Then add users to the roles as appropriate. The database will enforce the roles and your application just has to catch and process the appropriate error codes and inform the users of the security violation. To change functionality based on role you can query the sysroleauth table joined to sysusers to determine roles. Art S. Kagel