Re: Restricted access to data in remote database

Richard Spitz <Richard.Spitz@med.uni-muenchen.de> wrote in message news:<f1gq20h7iktaskcrc6jjmgficfj94t8e2j@4ax.com>... > 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. > > Regards, Richard You could just allow selects on the column in question with "GRANT SELECT(employee_id, begin_date, c_status) ON contract TO <xyz>". You could even create a role with those permissions, grant that role to a subset of users and then you can enable/disable the role as necessary for that group. The user then runs: SET ROLE xyz; SELECT employee_id, c_status FROM database@remotesoc:contract WHERE begin_date <= (TODAY) Or something like that