DBSECADM

Posted in 2010

Summary

After an in-place upgrade from Informix 10.00.UC8 to 11.50.UC6, a user got error 8229 (no SETSESSIONAUTH privilege) on 'set session authorization to informix', and user informix couldn't grant it, hitting error 8200 (no DBSECADM authority). The poster worked around it by running 'grant dbsecadm to informix' in a database, after which the SETSESSIONAUTH grant succeeded. Discussion covered v11's tightened DBA/DBSA/DBSECADM role separation and whether migration should have auto-granted SETSESSIONAUTH to DBAs, but no definitive explanation or official fix was reached.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Installation, Setup & Upgrades, Server Administration, Security, Permissions & Auditing

MIKE MAGIE — Wed, 05 May 2010 15:26:12 — source: IIUG Forums & Mailing Lists

All - We are in the process of upgrading from v10.00.UC8 to v11.50.UC6. We upgraded our test server to v11 w/o much incident. One problem we had right off the bat was with a tester who received an error when running the following SQL Statement: set session authorization to informix; The error he received was: 8229: User (tigerwoods) does not have SETSESSIONAUTH privilege. Upon seeing the error as user informix I went in and tried to grant the privilege: grant setsessionauth on informix to tigerwoods; And got this error: 8200: User (informix) does not have DBSECADM authority. WTF? As user informix I then selected a random database and issued a 'grant dbsecadm to informix' and it ran fine - afterwards I was able to grant the setsessionauth on informix to tigerwoods. Questions: 1. Why does user informix not have DBSECADM privs by default post-upgrade? 2. Why do dba users not maintain their 'set sessionauth on X' privs after the upgrade? 3. Are we going to have to grant the set sessionauth statement for all of our DBA users when we migrate our prod env? MM

↪ replying to MIKE MAGIE

Art Kagel — Wed, 05 May 2010 15:47:55 — source: IIUG Forums & Mailing Lists

Version 11 tightened up security. By default there is no DBSA user so, if during the install you tell the installer that you want the DBA/DBSA separation, no one has those privileges. User informix as superuser can grant dbsa to anyone, even to itself by default. What should happen is thatonce you've created a DBSA user you should revoke the grant privilege to create a DBSA user or grant setsessionauth from informix and only let the DBSA user(s) do that. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 5, 2010 at 11:26 AM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > All - > > We are in the process of upgrading from v10.00.UC8 to v11.50.UC6. We > upgraded > our test server to v11 w/o much incident. One problem we had right off the > bat > was with a tester who received an error when running the following SQL > Statement: > > set session authorization to informix; > > The error he received was: > > 8229: User (tigerwoods) does not have SETSESSIONAUTH privilege.> > Upon seeing the error as user informix I went in and tried to grant the > privilege: > > grant setsessionauth on informix to tigerwoods;> > And got this error: > > 8200: User (informix) does not have DBSECADM authority.> > WTF? > > As user informix I then selected a random database and issued a 'grant > dbsecadm to informix' and it ran fine - afterwards I was able to grant the > setsessionauth on informix to tigerwoods. Questions: > > 1. Why does user informix not have DBSECADM privs by default post-upgrade? > 2. Why do dba users not maintain their 'set sessionauth on X' privs after > the > upgrade? > 3. Are we going to have to grant the set sessionauth statement for all of > our > DBA users when we migrate our prod env? > > MM > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502ae02926f600485dabf6a

↪ replying to MIKE MAGIE

Fernando Nunes — Wed, 05 May 2010 15:53:10 — source: IIUG Forums & Mailing Lists

How did you upgrade your engine? In place or with data movement? Regards. On Wed, May 5, 2010 at 4:26 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > All - > > We are in the process of upgrading from v10.00.UC8 to v11.50.UC6. We > upgraded > our test server to v11 w/o much incident. One problem we had right off the > bat > was with a tester who received an error when running the following SQL > Statement: > > set session authorization to informix; > > The error he received was: > > 8229: User (tigerwoods) does not have SETSESSIONAUTH privilege.> > Upon seeing the error as user informix I went in and tried to grant the > privilege: > > grant setsessionauth on informix to tigerwoods;> > And got this error: > > 8200: User (informix) does not have DBSECADM authority.> > WTF? > > As user informix I then selected a random database and issued a 'grant > dbsecadm to informix' and it ran fine - afterwards I was able to grant the > setsessionauth on informix to tigerwoods. Questions: > > 1. Why does user informix not have DBSECADM privs by default post-upgrade? > 2. Why do dba users not maintain their 'set sessionauth on X' privs after > the > upgrade? > 3. Are we going to have to grant the set sessionauth statement for all of > our > DBA users when we migrate our prod env? > > MM > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6d7f07a70503d0485dad2e5

↪ replying to Art Kagel

MIKE MAGIE — Wed, 05 May 2010 16:03:09 — source: IIUG Forums & Mailing Lists

Making sure I follow ya here: During the installation I have the option to create a DBSA user? True/False If I chose to not create a DBSA user during install, I need to create the dbsa user by running the 'grant dbsecadm to informix'? True/False If I decide to create a DBSA user you are suggesting I then revoke the 'grant dbsecadm' priv from dba users? True/False Thanks, MM

↪ replying to MIKE MAGIE

Art Kagel — Wed, 05 May 2010 16:12:07 — source: IIUG Forums & Mailing Lists

I'm saying that IIRC if you enable DBSA role separation during the install then informix no longer has those privileges. On the rest: - I don't remember if the install gives you the option to create a DBSA user at that time. - You'd have to research how to enable DBSA role separation if you don't set it up during the install, but it looks to me like you did, so... - The whole idea of the DBA/DBSA role separation is that no one who isn't explicitly DBSA should have DBSA privs and that would include 'informix'. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 5, 2010 at 12:03 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > Making sure I follow ya here: > > During the installation I have the option to create a DBSA user? True/False > > If I chose to not create a DBSA user during install, I need to create the > dbsa > user by running the 'grant dbsecadm to informix'? True/False > > If I decide to create a DBSA user you are suggesting I then revoke the > 'grant > dbsecadm' priv from dba users? True/False > > Thanks, > > MM > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636e0a6c4216a200485db166a

↪ replying to Art Kagel

Fernando Nunes — Wed, 05 May 2010 16:21:44 — source: IIUG Forums & Mailing Lists

I believe you're mixing up two concepts. But maybe it's me... In short: DBSA is a user who manages the instance. It has no data access and no DBSECADM privilege. DBSECADM can be granted only by a DBSA (although he has no privilege). If I recall correctly DBSA 1 cannot give the DBSECADM to himself (I may be confusing this with SET SESSION AUTH...) The question I asked (if it was in place or not) it's because if you upgrade a version pre-DBSECADM to a DBSECADM enabled version, it should automatically grant the DBSECADM and/or SET SESSION AUTH to all the DBAs. On the other hand, a newly installed instance/database will not have it. Regards. On Wed, May 5, 2010 at 5:12 PM, Art Kagel <art.kagel@gmail.com> wrote: > I'm saying that IIRC if you enable DBSA role separation during the install > then informix no longer has those privileges. > > On the rest: > > - I don't remember if the install gives you the option to create a DBSA > > user at that time. > > - You'd have to research how to enable DBSA role separation if you don't > > set it up during the install, but it looks to me like you did, so... > > - The whole idea of the DBA/DBSA role separation is that no one who isn't > > explicitly DBSA should have DBSA privs and that would include 'informix'. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or > by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, May 5, 2010 at 12:03 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > > > Making sure I follow ya here: > > > > During the installation I have the option to create a DBSA user? > True/False > > > > If I chose to not create a DBSA user during install, I need to create the > > dbsa > > user by running the 'grant dbsecadm to informix'? True/False > > > > If I decide to create a DBSA user you are suggesting I then revoke the > > 'grant > > dbsecadm' priv from dba users? True/False > > > > Thanks, > > > > MM > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001636e0a6c4216a200485db166a > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016363ba5bc7ec3fe0485db3861

↪ replying to Fernando Nunes

Art Kagel — Wed, 05 May 2010 16:41:44 — source: IIUG Forums & Mailing Lists

Could be. I don't completely understand the expanded security stuff. We need Jonathan to jump in here. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 5, 2010 at 12:21 PM, Fernando Nunes <domusonline@gmail.com>wrote: > I believe you're mixing up two concepts. But maybe it's me... In short: > > DBSA is a user who manages the instance. It has no data access and no > DBSECADM privilege. > DBSECADM can be granted only by a DBSA (although he has no privilege). If I > recall correctly DBSA 1 cannot give the DBSECADM to himself (I may be > confusing this with SET SESSION AUTH...) > > The question I asked (if it was in place or not) it's because if you > upgrade > a version pre-DBSECADM to a DBSECADM enabled version, it should > automatically grant the DBSECADM and/or SET SESSION AUTH to all the DBAs. > On > the other hand, a newly installed instance/database will not have it. > > Regards. > > On Wed, May 5, 2010 at 5:12 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > I'm saying that IIRC if you enable DBSA role separation during the > install > > then informix no longer has those privileges. > > > > On the rest: > > > > - I don't remember if the install gives you the option to create a DBSA > > > > user at that time. > > > > - You'd have to research how to enable DBSA role separation if you don't > > > > set it up during the install, but it looks to me like you did, so... > > > > - The whole idea of the DBA/DBSA role separation is that no one who isn't > > > > explicitly DBSA should have DBSA privs and that would include 'informix'. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (art@iiug.org) > > > > See you at the 2010 IIUG Informix Conference > > April 25-28, 2010 > > Overland Park (Kansas City), KS > > www.iiug.org/conf > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other > > organization with which I am associated either explicitly, implicitly, or > > by > > inference. Neither do those opinions reflect those of other individuals > > affiliated with any entity with which I am affiliated nor those of the > > entities themselves. > > > > On Wed, May 5, 2010 at 12:03 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > > > > > Making sure I follow ya here: > > > > > > During the installation I have the option to create a DBSA user? > > True/False > > > > > > If I chose to not create a DBSA user during install, I need to create > the > > > dbsa > > > user by running the 'grant dbsecadm to informix'? True/False > > > > > > If I decide to create a DBSA user you are suggesting I then revoke the > > > 'grant > > > dbsecadm' priv from dba users? True/False > > > > > > Thanks, > > > > > > MM > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001636e0a6c4216a200485db166a > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --0016363ba5bc7ec3fe0485db3861 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636e0a6c4257b6d0485db80ae

↪ replying to Fernando Nunes

MIKE MAGIE — Wed, 05 May 2010 18:10:00 — source: IIUG Forums & Mailing Lists

We upgraded a version 10 database to version 11. The doc says: "If you are migrating the database server from a version that does not support label-based access control, users who held the DBA privilege are automatically granted the SETSESSIONAUTH access privilege for PUBLIC during the migration process. For more information on SETSESSIONAUTH, see the IBM Informix Guide to SQL: Syntax. For information on label-based access control, see the IBM Informix Security Guide. " Does this mean that after the migration process the SETSESSIONAUTH priv is revoked? Hope not - but that is what we saw. In summary - A user rcvd an error when trying to execute the 'set session authorization to informix' statement. As user informix I tried to grant it to him but received an error indicating that user informix did not have the dbsecadm priv. As user informix I then selected a database, ran 'grant dbsecadm to informix' after which I was able to grant the user the grant setsessionauth statement... MM

↪ replying to MIKE MAGIE

Fernando Nunes — Wed, 05 May 2010 23:29:21 — source: IIUG Forums & Mailing Lists

So, you upgraded in place... To which version? I recall seeing some issues in first 11.10 releases, but I would have to check. Also you were trying to set session authorization to Informix.... informix is a very important fellow... I'd have to check if it needs a special touch... Did you try SET SESSION AUT... to another user? Regards. On Wed, May 5, 2010 at 7:10 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > We upgraded a version 10 database to version 11. > > The doc says: > > "If you are migrating the database server from a version that does not > support > label-based access control, users who held the DBA privilege are > automatically > granted the SETSESSIONAUTH access privilege for PUBLIC during the migration > process. For more information on SETSESSIONAUTH, see the IBM Informix Guide > to > SQL: Syntax. For information on label-based access control, see the IBM > Informix Security Guide. " > > Does this mean that after the migration process the SETSESSIONAUTH priv is > revoked? Hope not - but that is what we saw. In summary - A user rcvd an > error > when trying to execute the 'set session authorization to informix' > statement. > As user informix I tried to grant it to him but received an error > indicating > that user informix did not have the dbsecadm priv. As user informix I then > selected a database, ran 'grant dbsecadm to informix' after which I was > able > to grant the user the grant setsessionauth statement... > > MM > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6d99b83d28d540485e131e3

↪ replying to Fernando Nunes

MIKE MAGIE — Thu, 06 May 2010 14:17:28 — source: IIUG Forums & Mailing Lists

Your questions are answered in my earlier posts.