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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kent OlsenDBACommented:
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 OlsenDBACommented:
Hi Momi,

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

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.