IBM DB2 - Define access rights just for one column of a table


The same user should be able to read and write in all columns excepted in one (e.g. colums X) of a table in a DB2 data base.

How can I restraint its access rights for the column X, so that it may only read data from X and not write in X anymore? It may further write and read in all other columns of the table.

Is it possible to restraint the access rights starting from a certain date (e.g. from 01st July)?

Many thanks in adavance and have a nice day!

Who is Participating?
momi_sabagConnect With a Mentor Commented:
in order to restrain a user from updating a column x,
you must grant him specific update privileges on all other columns, besides x (that is, you can't activly deny a user from a specific column, just allow him to update all the other columns)

regarding the date question,
if you are using db2 for mainframe, it's possible
i don't know about other platforms
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi inversojvo,

DB2 doesn't support column-level permission granularity so you won't be able to implement this restriction "directly".

You should be able to easily accomplish this by removing the users' update permission to the table and creating a view that is all of the table except the key column.  This forces the developers to base their updates on the view instead of the table.

As far as "time based" access, that's a bit unusual.  You should be able to create an UPDATE trigger that throws an error if your time constraint is violated.  Or the trigger can simply ignore the changes by saving the old values instead of the new ones.  That's clumsy and inefficient, but it should work.

Good Luck,
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi Momi,

I stand corrected.  :)  I didn't realize that DB2 had added column-level authorizations....  Pretty cool......

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

wct097Connect With a Mentor Commented:
This probably also varies between host systems.  I don't believe DB2 on the iSeries allows for database level permission setting by column.  I echo Kdo's opinion that you'd probably need to create a logical view of the table in question, granting the user authority over that logical.
tliottaConnect With a Mentor Commented:
Minor note... DB2 on AS/400s (and later iSeries and System i) has had column-level permissions (SQL, but not native) for... ummm... not sure how long, but at least since V5R1.

Basic example from V5R1:


inversojvoAuthor Commented:
Many thanks to all
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.