Creating User in IBM DB2

Users! It’s impossible to hear this word and not think of TRON. I love it when some program screams “User!!!!” as if a user were something infernal. And, well, it is. Some even associate it with drugs. In any case, “user” is rarely a good thing.

When we talk about DB2, the first thing to keep in mind is: DB2 doesn’t have CREATE USER. DB2 doesn’t have CREATE LOGIN. Forget it. You will not create a user in DB2. Just let it go!

DB2 uses the operating system’s user as a credential. Therefore, to access data and objects in DB2, the first step is creating a user in the operating system. In this example, I’ll use Linux CentOS. Keep in mind you need root privileges on the OS for this task to succeed.

In this example, we’ll create a user called dbuser70 with the password SaiDeBaixo80. This user will be given administrator privileges.

On CentOS:

Step 1: Create the user in the OS and set the password:

bashCopyEdit[root@db2server ~]# useradd dbuser70
[root@db2server ~]# passwd dbuser70
Changing password for user dbuser70.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

Step 2: Access your DB2 instance locally using sudo and the instance owner, usually db2inst1. Then launch the DB2 CLI (in my case the path was already set; otherwise, it’s usually in /opt/ibm/db2/V10.5/bin/, where V10.5 is your DB2 version):

bashCopyEdit[root@db2server ~]# sudo su db2inst1

In DB2:

bashCopyEdit[db2inst1@db2server root]$ db2

(c) Copyright IBM Corporation 1993,2007

Command Line Processor for DB2 Client 10.5.3

You can issue database manager commands and SQL statements from the command
prompt. For example:

    db2 => connect to sample
    db2 => bind sample.bnd

Step 3: Connect to the database and grant the desired privileges:

sqlCopyEditdb2 => CONNECT TO baseteste

Database Connection Information
Database server        = DB2/LINUXX8664 10.5.3
SQL authorization ID   = DB2INST1
Local database alias   = BASETESTE

db2 => GRANT CONNECT, DBADM ON DATABASE TO USER dbuser70

DB20000I  The SQL command completed successfully.

Step 4: You’re good to go! Let’s test the new user:

sqlCopyEditdb2 => CONNECT TO baseteste USER dbuser70 USING SaiDeBaixo80

Database Connection Information
Database server        = DB2/LINUXX8664 10.5.3
SQL authorization ID   = DBUSER70
Local database alias   = BASETESTE

Yoda’s Tip:
Do not create usernames with accents, special characters, or more than 8 characters. Avoid starting with numbers, SYS, SQL, or IBM. Basically—don’t get too creative. And of course, avoid reserved words.

Schedule a meeting here

Visit our Blog

Learn more about databases

Learn about monitoring with advanced tools

Have questions about our services? Visit our FAQ

Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!

Discover the History of HTI Tecnologia