Query Database Security

I am currently evaluating Client/Server ad-hoc query and reporting tools running against our Commercial System SE databases. Some of the products being evaluated include Cognos Impromptu/Powerplay, Intersolve Data Direct Explorer, Brio Query, IQ Objects, and Informix ViewPoint Pro. We are also looking at using MS Access and/or Excel as front end tools with ODBC connections. Almost all of the products reviewed use some form of meta-data to allow, at the minimum, custom presentation to the business user. Some products carry this further into various levels of security, complex joins, job scheduling, etc. Although I haven't looked at all the products yet, I am finding that I can replicate a significant amount of the meta-data structure using the following method. 1. Create a new database on the same system as the Commercial information. 2. Define tables as needed with the same definitions as the physical tables in the Commercial System. 3. Modify the systables records by changing the 'dirpath' column to point to the physical tables of the same name. (The table in the new database does not have to have the same name, and therefore I can use more user friendly nomenclature for both the table name and the column names) 4. Create views of the tables to join and summarize data where required. 5. Revoke all priviledges for all tables. Grant select only priviledges for all tables to appropriate users. This is a query only database! We do not want to interfere with the locking of rows during regular update in the production database. The above strategy works well in environments where we don't want to create a physical data warehouse. The number of tables available to the business user is controlled, friendlier terms are used, and summarized data is provided where appropriate. The only real problem I am having is in defining security to the level we require. SE does not support group level security. a) Has anyone got any ideas which would help me in this area? We also require some column value security. I would like to impliment security on certain tables with the following syntax (which, of course, will not work)- grant select to gl_values to user20 where department = '20' I could create separate views of the data for each department with appropriate permissions, but this really is not practical in our environment. b) Any suggestions? c) Anyone have experience with the above mentioned products or methods? Our environment is exclusively PC compatible clients (no MACs) connected via TCP/IP to HP 9000/7xx servers. Some of our sites have Novell LANs, and some are using NT to provide desktop applications in addition to the Commercial System information. Thanks. Roy Knive Senior Analyst Manalta Coal Ltd. kniver@cuug.ab.ca