Hi,

We use db2 8.2.6 on windows (soon to be on db2 9.5).

We have a single schema for the database. We need to give selective access control to applications. The way in which currently we implement this is by:

creating db2 users
granting permissions table by table to these users.

The issue with this set-up is each time a table is added or deleted, we need to maintain the access controls to the various users.

Is there a better way give access control to selected db tables. Would like to hear suggestions/experiences from other people regarding this. We have all tables created under single schema. Is iut better to group tables under various schema? Then can DB2 allow different permissions to different users. That is userA can only read tables in SchemaX, userB can update tables in SChemaY but cannot delete entries and so on

Thanks in advance

Instead of grouping objects by schemas (which won't give you any advantage), you will be better off grouping users and granting appropriate permissions to groups.

Granting access at the USER level should be avoided at all cost unless you like to work hard. As Ivan said grant to the GROUPS and have correct users assigned to the correct group.

You will still need to grant access to a new object as you create or recreate. There is just only way around it and that is to grant admin access which you do not want to do.

There are ways to totally automate granting process and remove DBA from it, but it requires scripting and other people, not DBA, to do their share.

like???

Curious to know


Sathyaram

Security responsibility should not be on the DBA shoulders. DBA should not decide who gets to see the data or who doesn't. That said DBA is simply a tool to implement the access and should be treated as such.

DBA writes a script that seats on the cron and runs twice a day. All that script does is hunts for objects that do not have grants i.e. new objects. Or new trigger for a new GOUP to get a new access or to revoke it.

there is a single file that contains GPOUP and what access it should have. This file is what drives the script.

This File is maintained by only one or two people who have been trained and knows the consequences for a screw up. When new GROUP needs access that person changes this file by appending new line for this group.

This is just a scatch. I have tried to implement this at a couple of palces but no one wanted to take a responsibility for maintaining those files. They think that it is cheaper for DBA to deal with it. Near sighted people.

If it was implemented you will never get a call saying that I have no access to a new object. No more security request for those new objects. everybody happy.