Hi Folks,

New to DB2 but current Oracle DBA so please bear with me.

System is RHAS 4u7 64-bit with DB2/LINUXX8664 9.5.1

This questions refers to a DB2 install for a Tivoli NetCool
application.

I have the following groups:
dasadm1:x:102:db2inst1
db2iadm1:x:103:
db2fadm1:x:104:
itmuser:x:503:

And the following users:
dasusr1:x:500:102::/home/dasusr1:/bin/bash
db2inst1:x:501:103::/home/db2inst1:/bin/bash
db2fenc1:x:502:104::/home/db2fenc1:/bin/bash
itmuser:x:503:503::/home/itmuser:/bin/bash

db2inst1 has the following groups assigned to it:
db2inst1 : db2iadm1 dasadm1

..bashrc for the db2inst1 user is:
# The following three lines have been added by UDB DB2.
if [ -f /home/db2inst1/sqllib/db2profile ]; then
. /home/db2inst1/sqllib/db2profile
fi

I connect to db2 follows:
su - db2inst2

This is output from the following command:
db2 get dbm cfg | grep -i sysad

SYSADM group name (SYSADM_GROUP) = DB2INST1

Once connected as db2inst1, I start a CLI session as follows:
db2
connect to teps

Database Connection Information

Database server = DB2/LINUXX8664 9.5.1
SQL authorization ID = DB2INST1
Local database alias = TEPS

get authorizations as db2inst1 gives me the following:

db2 => get authorizations

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = YES
Direct CREATETAB authority = YES
Direct BINDADD authority = YES
Direct CONNECT authority = YES
Direct CREATE_NOT_FENC authority = YES
Direct IMPLICIT_SCHEMA authority = YES
Direct LOAD authority = YES
Direct QUIESCE_CONNECT authority = YES
Direct CREATE_EXTERNAL_ROUTINE authority = YES
Direct SYSMON authority = NO

Indirect SYSADM authority = YES
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = YES
Indirect BINDADD authority = YES
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = YES
Indirect LOAD authority = NO
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = NO

I then try to create a role as follows:
db2 create role test

Which gives the following error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0552N "DB2INST1" does not have the privilege to perform operation
"CREATE
ROLE". SQLSTATE=42502

Any ideas what I'm doing wrong or what do I need to create a role?

BTW, sorry about the partial message earlier, hit Send too soon.

Thanks

--
mohammed

mohammed bhatti wrote:
I don't have a system at hand right now to check, but my first guess would
be that you should change the SYSADM_GROUP parameter (in dbm cfg) from
db2inst1 to db2iadm1.

HTH

--
Jeroen

The Boss wrote:
On second thought, while my previous remark might be valid, it probably
isn't the reason for the error.

To be able to create a role, you need SECADM authority.
The SECADM privilege can only be granted to a user, not to a group or a
role.
The instance owner doesn't have this privilege by default, so it should be
granted by someone with SYSADM authority (= a user in the SYSADM_GROUP).
For obvious reasons (Separation of Duties), a SYSADM user can not grant
himself (or another SYSADM) the SECADM authority, so you should create an
extra user for this.

For a complete explanation see the DB2 Info Center:
http://publib.boulder.ibm.com/infoce.../c0021054.html

Cheers!

--
Jeroen

On Jun 14, 5:23*pm, "The Boss" <use... (AT) No (DOT) Spam.Please.invalid> wrote:
Jeroen,

Thank you for your guidance. I got it working and here is my
solution:

Create an OS user as follows:
useradd -d /home/secadmin -g db2iadm1 -G dasadm1 -s /bin/bash -m -p
password secadmin

Now connect as db2inst1:
su - db2inst1

Start CLP and connect to teps:
db2
connect to teps

Grant the necessary privileges:
grant connect on database to secadmin
grant dbadm on database to secadmin
grant secadm on database to secadmin

Now connect to teps as secadmin:
connect to teps user secadmin using password

And create the role...
create role test

This is very interesting and very different from creating roles in
Oracle. I like the fact that there is a separate security admin and
the separation of roles and duties.

Obviously, I have a lot of reading to do and hopefully I'll be asking
fewer questions. Once again, thanks for your help and guidance.

--
mohammed

On 15 jun, 18:16, mohammed bhatti <mohammed.bhat... (AT) gmail (DOT) com> wrote:
Mohammed,

I like the separation too, but if you don't need it, which is still
often the case, you can just create a user in the SYSADM_GROUP and use
it to grant SECADM to your instance owner (db2inst1). You can delete
this user afterwards.

--
Frederik

On Jun 15, 2:35*pm, Frederik Engelen <engelenfrede... (AT) gmail (DOT) com>
wrote:
Frederik,

Ah, yes I see. That worked also. Now I can drop the user I just
created.

Beginning to make much more sense.

Thanks