Hi All

I'm very new to DB2, I normally work with MySQL but my project needs to use data in a DB2 database.

The table on the DB2 database holds lots of customer data, including some sensitive information I don't want to put at risk. As I'm using PHP/ODBC to connect to DB2 I'll be storing the password in plain text. I need to find a way of limiting the DB2 userid so that it can't perform a full SELECT on the customer table (therefore revealing all the customer data).

I was led to believe that a User Defined function would allow me to run a select query on a table, and only return a single column in the result - hiding the select completely and allowing my DB2 user to have no SELECT privileges on the table - just the UDF.

This is probably the only time I'm likely to use DB2, and I'm in quite a hurry now (it took a long time to get our security guys to agree to my server having DB2 connectivity - it's that sensitive!). I'm struggling to find any tutorials, examples or recipes showing the equivalent of what I'm trying to do - perform a select statement on a table with given variables within a UDF and return the result as a table.

The select is basically:

SELECT CUST_ID FROM CUST_INFO WHERE SENSITIVE_FIELD LIKE 'partOfSensitiveFieldIKnow%' AND CUST_TYPE = custType

I'd need to create the UDF so I could use it like:

SELECT CUST_ID FROM myUdf(partofSensitiveFieldIKnow, custType);

Is this even possible? Any suggestions / examples about how to implement this would be appreciated.

Yes, you can do this. You create the UDF to return a table, like this:

CREATE FUNCTION MySchema.UDF_Security(Security_level int)
RETURNS table (col1 int, col2 int, ...)
SPECIFIC MySchema.UDF_Security
LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
RETURN
<full-select>


Then you select statement would look like this:

select * from table (MySchema.UDF_Security(0)) as x where ...

Andy

I'd rather create a view (defined by the query you state) and give SELECT authority on that view to the selected user(s), but not on the underlying table(s).

Peter's suggestion has merit if there are only a few different "security" levels, because you need a different view for each level. If there are lots of different security levels, then having a separate view for each of those levels can be a maintenance issue, not to mention the issues on the programming side.

I took the OP's request as there will be lots of security levels and that their idea of using a UDF was a valid one.

Andy

Agreed... with two additional remarks:

- A view may be user-dependent: by using the USER special register in the WHERE condition, one could e.g. limit the rows visible to a particular user by the presence of his UserID in a certain security column.
(E.g., have an additional table T with 2 columns: UserID and security level; join your base table with this additional table with an "<=" in the join condition for the two security level columns. Then filter rows with the condition "T.UserId = USER".)

- Also have a look at the "multi-level security" possibilities of DB2 for zOS (new since version 8). (Search for the "AS SECURITY LABEL" keyword of CREATE TABLE.)
If I'm not mistaken, DB2 for LUW has a similar functionality through the datatype "DB2SECURITYLABEL". (Search for "security policy" in the SQL manuals.)

Wow that's some good stuff. Thanks folks.

I think there may have been a misunderstanding (who would think it, a misunderstanding on a web forum?)

The problem is that I want to prevent the DB2 user that my webserver is using from seeing certain fields in the customer table.

The customer table stores the customer account number. This is highly sensitive banking information. More than one customer can be tied to this account number, so we also use an indicator to show if the person concerned is the 1st signatory, 2nd, and so on. I need to differentiate between individuals on an account, as well as confirming that the account details entered by the web-app user are correct, without being able to use a SELECT to gain the customer account number. The flow of account number information needs to be one-way.

what I do want is for the webserver to be able to find the primary key field of that table, by providing part of the customer account number, and an indicator that shows which signatory.

They need to be able to provide those fields (or at least part of the field value - hence the LIKE) to a query, and get the customer ID field back. This verifies the information the webserver has about the customer relates to a genuine customer, but doesn't allow someone who has access to my PHP code (and the plaintext password) being able to log in to the DB2 database via another client and dump a list of my entire customer table.

I'll also be passing the customer ID gained by this method back to the DB2 database, so it can be used to tie information collected by the webserver to the customer record.

So, bearing that in mind, I originally looked at the idea of a view - but a view seems to insist on returning the same fields you use to query it - e.g I make a view using a select based on account_number and _signatory_number, and i have to return those values.

As I understand it, that could allow anyone who has access to the webserver (and can read my PHP to gain the DB2 password) to select all records and gain a list of account numbers.

So - to make sure - is UDF the way to go? And can I use the format described by ARWinner (thanks!) to achieve this?

First of all, views, just like tables, do not have to have all the columns returned. Even if the columns has hundreds of columns, a query can return only one of them. This works great if you have absolute control of the queries to the table or view. If you do not, then you are vulnerable to sensitive information being accessed. The only ways I can see around this is a UDF or a Stored Procedure. Direct access to the table is REVOKED, and you only allow the access through the UDF or SP.

Andy

I think I know what he is saying. The documentation does not make it fully clear how views work.

A view is defined as:
create view myschema.myview(cola, colb, colc) as
select id, name, amount from tablea;

In use, you call the view as:
select colb, colc
from myschema.myview
where cola = 123;

The optimizer will rewrite the view to include your predicate before it issues the query. This only works with predicates that are declared columns of the view, and there are limitations (a derived column won't work, a column of declared literals won't work, etc.).

However, your discussion makes it seem like a table function or a stored procedure would be more secure.

Thanks chaps - having tried out the code a little more, yes, a UDF is the way to go. Revoking access to the table is exactly my goal, and this has worked brilliantly for me.

Thanks for all the help.

I am not completely sure I understand what you mean by that, but a column in a view whether derived or defined by literals is like any other column and you can apply predicates to it. There is no limitation here.