Re: Restricted access to data in remote database
Jonathan Leffler <jleffler@earthlink.net> wrote:
>Well, I would expect that the following should work - bar the syntax
>errors.
>
>CREATE PROCEDURE remote_contract_status(eid integer, refdate DAET>DEFAULT 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;
This doesn't work since the calling user doesn't have SELECT
(or any other) permission on the remote table. So I get
"-272: No SELECT permission".
Creating the procedure as "DBA" procedure (as a user with
DBA privileges) and granting execute to the non-privileged
users doesn't work either.
What DOES work is creating the procedure as DBA procedure
in the remote database and calling this procedure in a
direct connection to the database. If I don't find any
other solution, I will have to find a way to open a parallel
connection to the "remote" database and use this connection
for calling the procedure.
Regards, Richard
--
+-------------------------------+-------------------------------+
| Dr. med Richard Spitz | Tel : +49-89-7095-6110 |
| Klinik f'r Anaesthesiologie | FAX : +49-89-7095-6420 |
| Klinikum der Univ. M'nchen | Page: +49-89-7095-789-2116 |
| 81366 M'nchen, Germany | |
+-------------------------------+-------------------------------+