Hello,
I'm facing a complex project to develop a new database to be used both through a web-based user interface (developed by us, later on) and at the same time give ODBC access to some groups of users.
I've come across several isssues, mostly concerning security. The relational database will have some 30+ tables, and we are planning to have some 250 users with something like 30 defined groups. Of course, the permissions for each group are different.
As of now, we have thought of two different alternatives to solve security:
1. Develop different views and rules, and give grants only to views to the users.
This means defining one view and three rules (insert, update & delete) for every table - grant combination... In average, that would mean roughly over 1000 rules... (quite difficult to maintain when we face requirement changes - which will happen) and then both the web interface and ODBC have the same permissions
2. Develop some views that are strictly needed with their respective rules, and limit ODBC access to be read-only for all users.
For any of the options, I would like to receive some comments from the readers of this list, also to get some other ideas on how to proceed. Also, regarding specifically the ODBC access, I have two specific questions:
a. Is it possible to limit which groups / users have access through ODBC?
b. Is it possible to have a read-only ODBC access?
Many thanks for the thoughts and answers.
Regards,
Jose A. Cortina