Hi All,

I'm finding that stored procedures seem to bypass table-level privileges and
allow data to be selected from tables that the user has no select permission
on. Is this a bug?

This has surprised me given the discussion entitled "Using Stored Procedures
to Control Access to Data" in the "Informix Guide to Database Design and
Implementation" (4364.pdf). This section discusses creating a DBA procedure
to read from a table that the user does not have select permission on. Why
put this section in the manual if any procedure can do this?

OS: UnixWare 7.1.1
IDS: 7.31.UD8

Regards,
Andrew
PS Also tried this on 9.40.TC1E1 on Windows XP with the same result

It seems odd that there would be a discussion on the use of DBA procedures
in the manual to
get around security if you don't require a DBA procedure to do so.

(Digression) This begs the question: what are DBA procedures for?

Unfortunately, I don't think I can code around this as we will be using
roles to enforce security.

I can easily determine if a particular user has select permissions on a
table and also if any of the roles
that they have been granted has select permissions.

The kicker is how to find out if a particular role has been invoked with SET
ROLE...



"rkusenet" <rkusenet (AT) yahoo (DOT) com> wrote

You might be missing http://publib.boulder.ibm.com/infoce...doc/udr198.htm

esp. the phrase "The user who executes the UDR runs with the privileges of the owner of the UDR."

HTH,
Andreas

Hmmm yes, but the same article also states " The database server checks the
existence of any referenced objects and verifies that the user who invokes
the UDR has the necessary privileges to access the referenced objects "

It then goes on to say "For example, if a user executes a UDR that updates
data in a table, the user must have the Update privilege for the table or
columns referenced in the UDR."

This is pretty clear - but was not the behaviour I was seeing.

After much fiddling I have determined that the above statements ARE correct
provided two conditions are met :-

1 - The owner of the UDR in not a DBA at the time the UDR is invoked.
2 - The owner of the UDR is not the owner of the tables(s) to which the UDR
refers.

Provided these conditions are true, you can elicit a -272 from procedure
based on the invoking user's privileges if such is your desire.

"Andreas Legner" <andreas.legner (AT) weihenstephan (DOT) org> wrote