Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Talking Unanswered: Easy Db2 question

    Hi all, I'm new to Db2, so this problem is probably really trivial.
    Anyways, I just created a new instance using the db2icrt command, and then I created a new admin user using the following commands.

    db2admin stop
    db2admin drop
    db2admin create /user:NEWUSR
    db2admin setid NEWUSR
    db2admin start


    But, now, when i try to execute commands, such as

    create db SAMPLEDB

    I get the message

    SQL1092N "NEWUSR" does not have the authority to perform the requested command.


    Can someone please help me?????

    Thanks!!!

  2. #2
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Talking

    Try this:

    GRANT CONTROL ON TABLE <SCHEMA>.<TBL> TO USER <USR>;

    GRANT SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,REFERENCES ON TABLE <SCHEMA>.<TBL> TO USER <USR> WITH GRANT OPTION;

    NB: Please do include your DB2 Version and the OS you are on every time you post.

    Newbie

  3. #3
    Join Date
    Jun 2004
    Posts
    5
    I am using DB2 v8.1 on WIN2000

    In order to execute those commands, don't I need to have a database first? And I can't create a database since my user doesn't have the authority!

  4. #4
    Join Date
    Jun 2004
    Posts
    5
    Do I need to add the user to some kind of admin group or something??

  5. #5
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Wink

    Yes, you need to have SYSADM or DBADM authority to grant control.

    You can restrict the user to do any of the below operations:
    SELECT/INSERT/UPDATE/DELETE/ALTER/INDEX/REFERENCES

    The user need not be added to Admin group.

    I am just curious how you were able to

    "db2admin stop
    db2admin drop
    db2admin create /user:NEWUSR
    db2admin setid NEWUSR
    db2admin start"

    because only a SYSADM or DBADM authority user can do the above.

    Newbie

  6. #6
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Exclamation

    I missed out one more thing. The GRANT command in my previous post is at the table level! Sorry.

    At the Database level, this should work

    GRANT
    DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTI NE,QUIESCE_CONNECT
    ON DATABASE TO USER <USR>;

    You can add the user to perform any of these operations
    DBADM/CREATETAB/BINDADD/CONNECT/CREATE_NOT_FENCED_ROUTINE/IMPLICIT_SCHEMA/LOAD/CREATE_EXTERNAL_ROUTINE/QUIESCE_CONNECT

    Please check the DB2 manual for explaination of what each options are.
    Last edited by dsusendran; 06-04-04 at 15:21.

  7. #7
    Join Date
    Jun 2004
    Posts
    5
    Hmm...ok, that's what I thought. Because the accont I 'created' as an admin is the same account I set up as the admin during install. Which is why I'm so confused as to why I can't add a database now.

    Is there anything I should check? Like, are there commands to check the amount of access a user has, or the groups a user belongs to?
    or, the usernames of the db admins?

  8. #8
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Talking

    Hmm... Not sure... i always use the GUI to check that. Since you are using 8.1 on Win 2000, you should have the control center, from where you can just click and find out.

    (Psst. I am new to DB2 too... )

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The DB2 Administrative Server is different than the Instance Owner. I think you wanted to created another user with the same security as the Instance Owner, not the Administrative Server.

    The default id for the instance owner is db2inst1 and the default for the Administrative Server is dasusr1.

    Creating databases and other SQL is not done by the Administrative Server user id, it is done by the Instance Owner (unless you want grant access to the Adminitrative Server user id).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jun 2004
    Posts
    5
    Oh, I see. So, how can I grant my user the security privileges needed to add databases to the instance?

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    To create a database, sysadm or sysctrl is needed. This is explained in the Command Reference where "create database" is described.

    SYSADM authority is determined by the security facilities used in a specific operating environment.

    For the Windows 2000 operating system, the database manager parm called sysadm_group can be set to any local group that has a name of 8 characters or fewer, and is defined in the Windows 2000 security database. If “NULL” is specified for this parameter, all members of the Administrators group have SYSADM authority. Please refer to the Administration: Performance Guide for instructions on how to set database manager parameters.

    You may also grant sysctrl to create a database. The database manager configuration parm for this is called sysctrl_group. This parameter defines the group name with system control (SYSCTRL) authority. SYSCTRL has privileges allowing operations affecting system resources, but does not allow direct access to data.

    Ability to create objects in the database can be controlled with grant of dbadm authority using SQL grants.

    You can download all the DB2 manuals for your reference. See Useful DB2 Stuff thread above for a link to the download site.
    Last edited by Marcus_A; 06-04-04 at 19:20.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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