Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unhappy Unanswered: DB2 Authorization Problem

    Hi friends,
    I am novice in the field of DB2. Here is my problem-I have DB2 installed on Windows 2000 Server and my client machines are XP professional. I have created some regular user accounts on Windows 2000 Servers and a database for each user.Now the issue when I try to connect a user to his respective database from remote machines it connects to it.But when I try to connect him to another users database even that works fine though he is not added as a user to that database.He can perform all kind of operations on that database.

    Any help in this matter will be highly appreciated...

    Thank you in advance,

    Regards,

    Asha Gada.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 Authorization Problem

    By default, CONNECT authority on a database is granted to public ... You'll have to revoke it from public and grant CONNECT to the user who is supposed to access that database ..

    Connect to the database and issue :

    db2 revoke connect on database from public
    db2 grant connect on database to user user1


    BTW, I'm curious to know why you have created one database/user .... there are other ways of allowing multiple users to share the same database without them having access to others data ...

    Cheers
    Sathyaram



    Originally posted by ashagada
    Hi friends,
    I am novice in the field of DB2. Here is my problem-I have DB2 installed on Windows 2000 Server and my client machines are XP professional. I have created some regular user accounts on Windows 2000 Servers and a database for each user.Now the issue when I try to connect a user to his respective database from remote machines it connects to it.But when I try to connect him to another users database even that works fine though he is not added as a user to that database.He can perform all kind of operations on that database.

    Any help in this matter will be highly appreciated...

    Thank you in advance,

    Regards,

    Asha Gada.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2003
    Posts
    5

    Number of databases

    Thank you so much for your timely reply...It worked.Actually DB2 here is needed for a class of approx 100 students.The professor has assigned a project to design a database for which each students needs to have his own database.So what I plan to do is create a database for each student under a common instance.I am not sure if this is the most efficient way to do it or is there any other better way to handle this.Any help in the regard will be highly appreciated...
    Thanks again,
    Waiting for a reply,
    Asha.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't think creating 100 databases on one instance is a good idea. Each DB2 database uses its own memory for certain things (especially bufferpools), and you will probably run out.

    You might try creating one database and create 100 schemas and set up security so that each user only has access to their own schema (and can only create objects in their own schema).

    You could also create 100 tablespaces, one for each user to use, if you wanted them to use the backup/restore command (but with the tablespace specified). Make sure no one restores the entire database unless it is you who are doing it.

    If you create only one database, I strongly suggest that you use the Performance Wizard to optimize the required number of user connections, bufferpool size, etc. You may want to give DB2 about 50% of system memory when prompted by the Performance Wizard.

  5. #5
    Join Date
    Sep 2003
    Posts
    5

    Security issue

    Hi,
    Thanks a lot for the prompt reply.Now here is another issue.
    1.I created a single test database.I created 3 regular users on the DB2 server.

    2. Next I created 3 schemas on the test database and each user name as the authorization
    name for each schema.

    3. Then I created 3 table spaces each stored in a different container on the hard drive.

    4.Then using the control center I opened the User and Group objects for the test database
    and gave each user the following autorities and privileges...

    Database - COnnect database,Create tables, create packages, Access to the load utility.

    One of the 3 schemas with creatin alterin and droppin privileges

    One of the table space with use privilege on it.

    Now when I tested the user, he could still created tables with schema name as his user name
    and table space as userspace1.Will this be a problem as far as security is concerned?

    Also what I need to know is are there any loop holes in the kind of authorities and privileges
    that I have given to each user ?

    Any help in this regard will be highly appreciated...

    Waiting for a reply,
    Asha.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Security issue

    By default, PUBLIC can connect to the database and create a table in any schema, whether existing or not (implicit_schema auth) in userspace1(use of tablespace granted to PUBLIC)

    Revoking these privileges should ideally solve the problem:

    1) Right-click on the database name in the control centre and click on authorities ... Cross all columns shown against the group PUBLIC

    2) Right click on the Tablespace object userspace 1 and cross the USE column against PUBLIC

    HTH

    Sathyaram

    Originally posted by ashagada
    Hi,
    Thanks a lot for the prompt reply.Now here is another issue.
    1.I created a single test database.I created 3 regular users on the DB2 server.

    2. Next I created 3 schemas on the test database and each user name as the authorization
    name for each schema.

    3. Then I created 3 table spaces each stored in a different container on the hard drive.

    4.Then using the control center I opened the User and Group objects for the test database
    and gave each user the following autorities and privileges...

    Database - COnnect database,Create tables, create packages, Access to the load utility.

    One of the 3 schemas with creatin alterin and droppin privileges

    One of the table space with use privilege on it.

    Now when I tested the user, he could still created tables with schema name as his user name
    and table space as userspace1.Will this be a problem as far as security is concerned?

    Also what I need to know is are there any loop holes in the kind of authorities and privileges
    that I have given to each user ?

    Any help in this regard will be highly appreciated...

    Waiting for a reply,
    Asha.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Sep 2003
    Posts
    5

    Re: Security issue

    Hi,
    Thanks a lot for the reply.It works.This forum is helping me a lot in exploring DB2.I have some other issues
    to resolve...
    1. I have installed only runtime client for students to do their projects. Now if I install application
    development client then the students get an access to Control Center also. Using this control Center
    they can still the structure of the schemas for other students.Is there any way that I can prevent this.

    2. Say if I want to the students to use only runtime clients then how do they specify in their commands
    as to which schema to work with because when we use the command center to create tables it tries creating
    a schema with the name same as user name.

    Any help will be highly appreciated.

    Waiting for a reply,
    Asha.











    Originally posted by sathyaram_s
    By default, PUBLIC can connect to the database and create a table in any schema, whether existing or not (implicit_schema auth) in userspace1(use of tablespace granted to PUBLIC)

    Revoking these privileges should ideally solve the problem:

    1) Right-click on the database name in the control centre and click on authorities ... Cross all columns shown against the group PUBLIC

    2) Right click on the Tablespace object userspace 1 and cross the USE column against PUBLIC

    HTH

    Sathyaram

Posting Permissions

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