Create Role as db2inst1 Gives Error SQL0552N
in [DB2]
|
Prev: Create Role as db2inst1 Gives Error
Next: Backup DB
From: mohammed bhatti on 14 Jun 2010 16:03 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
From: The Boss on 14 Jun 2010 16:56 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 -- Jeroen
From: The Boss on 14 Jun 2010 17:23 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=/com.ibm.db2.luw.admin.sec.doc/doc/c0021054.html Cheers! -- Jeroen
From: mohammed bhatti on 15 Jun 2010 12:16 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
From: Frederik Engelen on 15 Jun 2010 14:35
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 |