SEO

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



--
+-------------------------------+-------------------------------+
| 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 | |
+-------------------------------+-------------------------------+

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

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 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;

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/

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 | |
+-------------------------------+-------------------------------+

malc_p@btinternet.com (Malc P) wrote:

>You could just allow selects on the column in question with
>"GRANT SELECT(employee_id, begin_date, c_status) ON contract TO
><xyz>".

If I granted these privileges, the users could query the table
directly and retrieve information about contract duration of
other employees.

With the stored procedure, getting this info would be more
difficult and very cumbersome, but not totally impossible. I
am aware of that, but it would still offer more protection
of this information than opening the table for direct queries.

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 | |
+-------------------------------+-------------------------------+