Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    104

    Unanswered: setting up access controls for applications

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •