Re: Restricted access to data in remote database

Richard Spitz wrote: > Hi Informixers, > > since there was not a single reply to my last posting about > my problem, I'll try again and rephrase my description. > > I need to give certain users access to some information from > a table to which they have no select (or any other) permission. > > This is the table definition: > > CREATE TABLE contract ( > employee_id integer, > position_id integer, > begin_date date, > end_date date, > c_status char(4) > );> > These are sensitive data, hence the requirement to not > give any permissions on this table to the users in > question. However, they need a certain piece of info > from this table: For a given employee_id and date, > retrieve c_status. > > For added complexity, the users are connected to > another database on another instance of the engine. > They do have connect permission to the database > and select permission on some other tables. > > I know how to write a Stored Procedure that retrieves > the information I need. What I don't know is how I can > let my users call this procedure "remotely" from the > other database they are connected to. Well, I would expect that the following should work - bar the syntax errors. CREATE PROCEDURE remote_contract_status(eid integer, refdate DAETDEFAULT TODAY) RETURNING CHAR(4) {AS contract_status}; DEFINE rv CHAR(4); FOREACH SELECT c_status INTO rv FROM remotedbs@remoteserver:contract c WHERE c.employee_id = eid AND refdate BETWEEN c.begin_date AND c.end_date RETURN rv; END FOREACH; END PROCEDURE; You simply create this procedure in each database where the users need access to the remote database. (The AS clause is optional and could only be uncommented in IDS 9.40; it names the result column.) Did you try this? Did it fail? If so, please tell me why/how it failed? Executing procedures that are in remote databases is much messier - if it is possible at all. I know that back in the days of 5.0x (where x was small - like 0, 1 or 2) I had immense problems with a loosely similar issue. But I think the outline code should be OK. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/