Create Role as db2inst1 Gives Error SQL0552N
in [DB2]
|
Prev: Create Role as db2inst1 Gives Error
Next: Backup DB
From: mohammed bhatti on 15 Jun 2010 16:13 On Jun 15, 2:35 pm, Frederik Engelen <engelenfrede...(a)gmail.com> wrote: > On 15 jun, 18:16, mohammed bhatti <mohammed.bhat...(a)gmail.com> wrote: > > > > > On Jun 14, 5:23 pm, "The Boss" <use...(a)No.Spam.Please.invalid> wrote: > > > > The Boss wrote: > > > > mohammed bhatti wrote: > > > >> 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 > > > > > 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 > > > > 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/infocenter/db2luw/v9r5/index.jsp?topic=... > > > > Cheers! > > > > -- > > > Jeroen > > > 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 > > 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 Frederik, Ah, yes I see. That worked also. Now I can drop the user I just created. Beginning to make much more sense. Thanks |