Roles X remote database
Hi , Ifx 11.50 FC9X6 , AIX 6.1 We have two databasesatour system, where we work using ROLEs to manage the users . Today we have a security problem , which is caused by an IBM Security reason (looks like a Paradox...) The problem : We have the user X , databases A, B and the roles "role_full" and "role_read" (into both databases). where : - Role_full : grant of select,update,insert,delete over all tables. - Role_read :grant of selectover all tables. The user X have grant for both roles into both databases and: grant default rolerole_full into database A grant default rolerole_read into database B If they connect into database A and tryexecute an "update B:table_xyz" , got "don't have permission". This is appear working as design , check IBM documentation : http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.ddi.doc/ids_ ddi_051.htm. Our problem is : - Any access with user X over the database B should be "read only" , except if they working with role_full. - Our system was design to user X use the role_full only when connect over database A. - To able user X update few tables of database B, we need to give explicit permissions to user, making useless the roles set as defaul (role_read). Anyone imagine some workaround for this situation? There is some "underground" parameter to enable active roles"migrate" to remote databases access ? We have Oracle here and works fine with similar configuration.... (the role active "migrate" to remote database , what is a remote instance) At our point of view , the way what Informix works, isn't secure. Regards Cesar
On Wed, Nov 28, 2012 at 7:34 PM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Hi , > > Ifx 11.50 FC9X6 , AIX 6.1 > > We have two databasesatour system, where we work using ROLEs to manage > the users . > Today we have a security problem , which is caused by an IBM Security > reason (looks like a Paradox...) > Nice topic... > > The problem : > We have the user X , databases A, B and the roles "role_full" and > "role_read" (into both databases). > where : > - Role_full : grant of select,update,insert,delete over all tables. > > - Role_read :grant of selectover all tables. > > The user X have grant for both roles into both databases and: > grant default rolerole_full into database A > grant default rolerole_read into database B > > If they connect into database A and tryexecute an "update B:table_xyz" , > got "don't have permission". > This is appear working as design , check IBM documentation : > > > http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.ddi.doc/ids_ ddi_051.htm > . > > It is. > Our problem is : > - Any access with user X over the database B should be "read only" , > except if they working with role_full. > - Our system was design to user X use the role_full only when connect > over database A. > - To able user X update few tables of database B, we need to give > explicit permissions to user, making useless the roles set as defaul > (role_read). > > Anyone imagine some workaround for this situation? > See the end... > There is some "underground" parameter to enable active roles"migrate" to > remote databases access ? > > Not that I know of... > We have Oracle here and works fine with similar configuration.... (the > role active "migrate" to remote database , what is a remote instance) > At our point of view , the way what Informix works, isn't secure. > Disagree. The way Informix works is secure, but anoying. What you're doing in Oracle isn't secure... A DBA in database A can gain escalated privileges in database B, just by knowing the remote roles, and creating users/roles that match and as such gaining excessive privileges on the remote database (unless of course there is more to it, than you mentioned). I don't think this is safer than what Informix does... In fact, if you take some time to read about "owner mode" vs "restricted mode" procedures (I have an article about this), you'll see how "paranoid" we are regarding remote operations. This is also the root cause for another annoying thing... this time regarding trusted context... try to use it in a system with several databases and you'll see the limitations... once you change your identity you can't do "remote" (even if remote means same instance) operations. And I fought for this without success... Now that I defended the honor of Informix (feel free to tell me I'm wrong) the main question still holds: How the hell are you able to do what you want... I don't have a good answer... You may consider doing those remote updates through a stored procedure owned by a privilege user (one with the needed privileges in the remote database). But if you have too many queries or ad-hoc requirements this may not fit your needs. > > Regards > Cesar > > Cumprimentos César. É bom ver posts seus! -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b6d88b08915f704cfaa6a40
Hi Fernando, So, I agree (partially) and I understand all this "paranoic" with security , however looking from other point we are working here over a unique environment where the DBA team are responsible for all databases and already have a "controlled" environment over other layers (SO/servers). So , the security is already applied over "who access what".... This way , your theory about the "bad" DBA should not be considered... if the case... we already all f***** because they already have grant to all. (just a note, isn't my case OK... I'm a very good, responsible and nice DBA :) I don't agree the IBM Informix impose security rules over business rules to any company what choose work with Informix... if someone mess something or do a poor configuration this isn't fault of Informix. Off course , the security should be relative strong over as default installation (let's default values over say onconfig.std) ,but with option to us apply that what make sense for our environment. Just like today , we aren't forced to use "role separation" when we install and configure Informix ! Develop procedures to execute the updates will make close of unfeasible the development process today. We need create facilities (with responsibility) instead of include more overhead and steps to our developers and DBAs. ...and create a new point of failure to our system.... Well... I will open a PMR asking for this as new feature... I don't know why , when I sent this , I already imagine a answer from you incoming....:) Changing the subject... This week I update our DW test environment with engine to 11.70 FC6 and we execute partially our batch of tests with Pentaho... all works!! :) No syntax error, no AFs .... now the problem is the pentaho.... :S Regards Cesar On 29/11/2012 21:12, Fernando Nunes wrote: > On Wed, Nov 28, 2012 at 7:34 PM, Cesar Inacio Martins < > cesar_inacio_martins@yahoo.com.br> wrote: > >> Hi , >> >> Ifx 11.50 FC9X6 , AIX 6.1 >> >> We have two databasesatour system, where we work using ROLEs to manage >> the users . >> Today we have a security problem , which is caused by an IBM Security >> reason (looks like a Paradox...) >> > Nice topic... > >> The problem : >> We have the user X , databases A, B and the roles "role_full" and >> "role_read" (into both databases). >> where : >> - Role_full : grant of select,update,insert,delete over all tables. >> >> - Role_read :grant of selectover all tables. >> >> The user X have grant for both roles into both databases and: >> grant default rolerole_full into database A >> grant default rolerole_read into database B >> >> If they connect into database A and tryexecute an "update B:table_xyz" , >> got "don't have permission". >> This is appear working as design , check IBM documentation : >> >> >> > http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.ddi.doc/ids_ ddi_051.htm >> . >> >> > It is. > >> Our problem is : >> - Any access with user X over the database B should be "read only" , >> except if they working with role_full. >> - Our system was design to user X use the role_full only when connect >> over database A. >> - To able user X update few tables of database B, we need to give >> explicit permissions to user, making useless the roles set as defaul >> (role_read). >> >> Anyone imagine some workaround for this situation? >> > See the end... > >> There is some "underground" parameter to enable active roles"migrate" to >> remote databases access ? >> >> Not that I know of... >> We have Oracle here and works fine with similar configuration.... (the >> role active "migrate" to remote database , what is a remote instance) >> At our point of view , the way what Informix works, isn't secure. >> > Disagree. The way Informix works is secure, but anoying. > What you're doing in Oracle isn't secure... A DBA in database A can gain > escalated privileges in database B, just by knowing the remote roles, and > creating users/roles that match and as such gaining excessive privileges on > the remote database (unless of course there is more to it, than you > mentioned). I don't think this is safer than what Informix does... In fact, > if you take some time to read about "owner mode" vs "restricted mode" > procedures (I have an article about this), you'll see how "paranoid" we are > regarding remote operations. > This is also the root cause for another annoying thing... this time > regarding trusted context... try to use it in a system with several > databases and you'll see the limitations... once you change your identity > you can't do "remote" (even if remote means same instance) operations. And > I fought for this without success... > > Now that I defended the honor of Informix (feel free to tell me I'm wrong) > the main question still holds: How the hell are you able to do what you > want... I don't have a good answer... > You may consider doing those remote updates through a stored procedure > owned by a privilege user (one with the needed privileges in the remote > database). But if you have too many queries or ad-hoc requirements this may > not fit your needs. > >> Regards >> Cesar >> >> Cumprimentos César. É bom ver posts seus!
Cutting the story short... I'd vote for some kind of "I'm the only DBA around, let Informix trust me" kind of configuration. With this in mind, I'd like to have: - Roles at the instance level (at least) - Roles across instances - Cross database/instances operations inside a trusted context Problem is the usual... I can't put a business case around this and R&D priorities don't seem to go my way :) Regarding 11.70.FC6. Great...Although strange... Regards. On Fri, Nov 30, 2012 at 12:25 AM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Hi Fernando, > > So, I agree (partially) and I understand all this "paranoic" with > security , however looking from other point we are working here over a > unique environment where the DBA team are responsible for all databases > and already have a "controlled" environment over other layers > (SO/servers). So , the security is already applied over "who access > what".... > This way , your theory about the "bad" DBA should not be considered... > if the case... we already all f***** because they already have grant to > all. > (just a note, isn't my case OK... I'm a very good, responsible and nice > DBA :) > > I don't agree the IBM Informix impose security rules over business rules > to any company what choose work with Informix... if someone mess > something or do a poor configuration this isn't fault of Informix. > Off course , the security should be relative strong over as default > installation (let's default values over say onconfig.std) ,but with > option to us apply that what make sense for our environment. Just like > today , we aren't forced to use "role separation" when we install and > configure Informix ! > > Develop procedures to execute the updates will make close of unfeasible > the development process today. We need create facilities (with > responsibility) instead of include more overhead and steps to our > developers and DBAs. > ....and create a new point of failure to our system.... > > Well... I will open a PMR asking for this as new feature... > > I don't know why , when I sent this , I already imagine a answer from > you incoming....:) > > Changing the subject... This week I update our DW test environment with > engine to 11.70 FC6 and we execute partially our batch of tests with > Pentaho... all works!! :) > No syntax error, no AFs .... now the problem is the pentaho.... :S > > Regards > Cesar > > On 29/11/2012 21:12, Fernando Nunes wrote: > > On Wed, Nov 28, 2012 at 7:34 PM, Cesar Inacio Martins < > > cesar_inacio_martins@yahoo.com.br> wrote: > > > >> Hi , > >> > >> Ifx 11.50 FC9X6 , AIX 6.1 > >> > >> We have two databasesatour system, where we work using ROLEs to manage > >> the users . > >> Today we have a security problem , which is caused by an IBM Security > >> reason (looks like a Paradox...) > >> > > Nice topic... > > > >> The problem : > >> We have the user X , databases A, B and the roles "role_full" and > >> "role_read" (into both databases). > >> where : > >> - Role_full : grant of select,update,insert,delete over all tables. > >> > >> - Role_read :grant of selectover all tables. > >> > >> The user X have grant for both roles into both databases and: > >> grant default rolerole_full into database A > >> grant default rolerole_read into database B > >> > >> If they connect into database A and tryexecute an "update B:table_xyz" , > >> got "don't have permission". > >> This is appear working as design , check IBM documentation : > >> > >> > >> > > > > http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.ddi.doc/ids_ ddi_051.htm > >> . > >> > >> > > It is. > > > >> Our problem is : > >> - Any access with user X over the database B should be "read only" , > >> except if they working with role_full. > >> - Our system was design to user X use the role_full only when connect > >> over database A. > >> - To able user X update few tables of database B, we need to give > >> explicit permissions to user, making useless the roles set as defaul > >> (role_read). > >> > >> Anyone imagine some workaround for this situation? > >> > > See the end... > > > >> There is some "underground" parameter to enable active roles"migrate" to > >> remote databases access ? > >> > >> Not that I know of... > >> We have Oracle here and works fine with similar configuration.... (the > >> role active "migrate" to remote database , what is a remote instance) > >> At our point of view , the way what Informix works, isn't secure. > >> > > Disagree. The way Informix works is secure, but anoying. > > What you're doing in Oracle isn't secure... A DBA in database A can gain > > escalated privileges in database B, just by knowing the remote roles, and > > creating users/roles that match and as such gaining excessive privileges > on > > the remote database (unless of course there is more to it, than you > > mentioned). I don't think this is safer than what Informix does... In > fact, > > if you take some time to read about "owner mode" vs "restricted mode" > > procedures (I have an article about this), you'll see how "paranoid" we > are > > regarding remote operations. > > This is also the root cause for another annoying thing... this time > > regarding trusted context... try to use it in a system with several > > databases and you'll see the limitations... once you change your identity > > you can't do "remote" (even if remote means same instance) operations. > And > > I fought for this without success... > > > > Now that I defended the honor of Informix (feel free to tell me I'm > wrong) > > the main question still holds: How the hell are you able to do what you > > want... I don't have a good answer... > > You may consider doing those remote updates through a stored procedure > > owned by a privilege user (one with the needed privileges in the remote > > database). But if you have too many queries or ad-hoc requirements this > may > > not fit your needs. > > > >> Regards > >> Cesar > >> > >> Cumprimentos César. É bom ver posts seus! > > > > ******************************************************************************* > 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... --047d7b678864d77fc004cfab9185