Stored Procedures and Security
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