Stored Procedures and Security
Andrew Rowe found stored procedures let users select from tables they have no privilege on (IDS 7.31.UD8, also 9.40). Rkusenet said this is expected, and Andreas Legner cited the rule that a UDR runs with its owner's privileges. Andrew found privilege checks on the invoking user do apply, giving error -272, if the UDR owner is not a DBA and does not own the tables.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
This is not a bug. It is expected in Stored Procedures. And BTW the same behaviour exists in SQL Server too. Can this be disabled. I am not aware of any mechanism. I am sure there must be some way, otherwise you can always code it in the stored procedure. "Andrew Rowe" <andrew.rowe@manufacturing.demon.co.uk> wrote in message news:PTiMe.10592$JB4.1747@newsfe6-win.ntli.net... > 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@yahoo.com> wrote in message news:3meg2hF167b4gU1@individual.net... > This is not a bug. It is expected in Stored Procedures. > > And BTW the same behaviour exists in SQL Server too. > > Can this be disabled. I am not aware of any mechanism. I am > sure there must be some way, otherwise you can always code > it in the stored procedure. > > > "Andrew Rowe" <andrew.rowe@manufacturing.demon.co.uk> wrote in message > news:PTiMe.10592$JB4.1747@newsfe6-win.ntli.net... >> 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 >> >> >> >
You might be missing http://publib.boulder.ibm.com/infocenter/ids9help/topic/com.ibm.udr.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@weihenstephan.org> wrote in message news:43024D6A.5020700@weihenstephan.org... > You might be missing > http://publib.boulder.ibm.com/infocenter/ids9help/topic/com.ibm.udr.doc/udr198.htm > > esp. the phrase "The user who executes the UDR runs with the privileges of > the owner of the UDR." > > HTH, > Andreas