security question
A DBA on IDS 10.00.FC9 (HP-UX) needed to give one user access to only a few specified tables/columns, while the database has many views granted to PUBLIC that a third-party application depends on. Replies: you can REVOKE ALL ON <view> FROM PUBLIC, but the only real way is to strip privileges from PUBLIC and from that user, then grant them explicitly or via a ROLE (default roles exist from IDS 10) so the application still works; alternatively create a separate database with synonyms only to the allowed tables (untested), or use LBAC on 11.10+. The poster didn't report implementing any of these, so no confirmed resolution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 10.00.FC9 on HPUX B.11.23 U ia64 I've been asked if the following is possible security wise in Informix by my boss from our lawyer. We have a employee or potential employee that needs special restricted access to our IDS databases, and this is the way it was explained to me. Employee can have access to a limited number of tables or fields in tables and only these stated ones. No 'Public' access or any other then what is agreed to. Now the database this person needs access to has a number of views that have 'public' access. Granting the limited access is pretty straight forward on the tables and I think I can get that setup per the lawyers requirements once I told what they are, the troubling area is how do I block access to the views that have 'public' to this users? Is it possible to do this? Can I take away public access from a user? Or are we going to have to rebuild the views and not use 'public' in the grant statement? I hope I have translated the legal babble I was told into something resembling DBA ease. :-) John
REVOKE ALL ON <viewname> FROM PUBLIC;
Art
Art S. Kagel
Oninit (www.oninit.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, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Thu, Dec 10, 2009 at 7:48 PM, John Adamski <adamski@graceland.edu> wrote:
> IDS 10.00.FC9 on HPUX B.11.23 U ia64
>
> I've been asked if the following is possible security wise in Informix by
> my
> boss from our lawyer.
>
> We have a employee or potential employee that needs special restricted
> access
> to our IDS databases, and this is the way it was explained to me.
>
> Employee can have access to a limited number of tables or fields in tables
> and
> only these stated ones. No 'Public' access or any other then what is agreed
> to. Now the database this person needs access to has a number of views that
> have 'public' access.
>
> Granting the limited access is pretty straight forward on the tables and I
> think I can get that setup per the lawyers requirements once I told what
> they
> are, the troubling area is how do I block access to the views that have
> 'public' to this users? Is it possible to do this? Can I take away public
> access from a user?
>
> Or are we going to have to rebuild the views and not use 'public' in the
> grant
> statement?
>
> I hope I have translated the legal babble I was told into something
> resembling
> DBA ease. :-)
>
> John
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001517478cce6fb12c047a6ac665
Another way is to create another database, and create synonyms to just the tables that they should have access to... That way - its very tightly controlled - but you dont need to worry about messing it up for current/existing users... On Friday 11 December 2009 00:48:01 John Adamski wrote: > IDS 10.00.FC9 on HPUX B.11.23 U ia64 > > I've been asked if the following is possible security wise in Informix by > my boss from our lawyer. > > We have a employee or potential employee that needs special restricted > access to our IDS databases, and this is the way it was explained to me. > > Employee can have access to a limited number of tables or fields in tables > and only these stated ones. No 'Public' access or any other then what is > agreed to. Now the database this person needs access to has a number of > views that have 'public' access. > > Granting the limited access is pretty straight forward on the tables and I > think I can get that setup per the lawyers requirements once I told what > they are, the troubling area is how do I block access to the views that > have 'public' to this users? Is it possible to do this? Can I take away > public access from a user? > > Or are we going to have to rebuild the views and not use 'public' in the > grant statement? > > I hope I have translated the legal babble I was told into something > resembling DBA ease. :-) > > John > > > *************************************************************************** > **** Forum Note: Use "Reply" to post a response in the discussion forum. > -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ
It seems I did not make myself clear as to what I been asked to find out if it
is possible.
So try two:
We have a number of views that have grant permission set to PUBLIC that the
main 3-rd party administrative application uses. And, this needs to say this
way so the application will function. (I didn't write it I just have to
maintain the DB, nor do I have any influence to change things)
There is an individual that needs access but can only have very selective
access to specified tables and/or fields in tables. The requirement for this
persons access is they can't have PUBLIC access.
Is there a way to do something to this individual's security so they have
access to said select tables but not any PUBLIC tables? I couldn't figure out
a way without doing something to the views.
I hope this is a bit more clear of what I've been asked.
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Thursday, December 10, 2009 8:35 PM
To: ids@iiug.org
Subject: Re: security question [18346]
REVOKE ALL ON <viewname> FROM PUBLIC;
Art
Art S. Kagel
Oninit (www.oninit.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, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Thu, Dec 10, 2009 at 7:48 PM, John Adamski <adamski@graceland.edu> wrote:
> IDS 10.00.FC9 on HPUX B.11.23 U ia64
>
> I've been asked if the following is possible security wise in Informix by
> my
> boss from our lawyer.
>
> We have a employee or potential employee that needs special restricted
> access
> to our IDS databases, and this is the way it was explained to me.
>
> Employee can have access to a limited number of tables or fields in tables
> and
> only these stated ones. No 'Public' access or any other then what is agreed
> to. Now the database this person needs access to has a number of views that
> have 'public' access.
>
> Granting the limited access is pretty straight forward on the tables and I
> think I can get that setup per the lawyers requirements once I told what
> they
> are, the troubling area is how do I block access to the views that have
> 'public' to this users? Is it possible to do this? Can I take away public
> access from a user?
>
> Or are we going to have to rebuild the views and not use 'public' in the
> grant
> statement?
>
> I hope I have translated the legal babble I was told into something
> resembling
> DBA ease. :-)
>
> John
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001517478cce6fb12c047a6ac665
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
The ONLY way to restrict access to data that is available through a VIEW is
to REVOKE all privileges from this individual's login and from the PUBLIC
pseudo login id. In order for the application to continue to work you will
either have to GRANT specific privileges to individual users or create a
ROLE for users who are so privileged and have the application attempt to SET
ROLE to that role immediately after connecting to the database (if you are
running IDS 10.00 or later, you can set each users' DEFAULT ROLE which will
avoid having to modify the application, but if you are running IDS 7 or 9
there is no default role, so you'll have to modify the application). If
this 'underprivileged' user should try to run the application, the SET ROLE
will fail. Then the application can either exit or ignore the error and let
the user continue with his own privileges intact.
There's no other way to do this, except to revoke even CONNECT privilege
from this one user, and as someone suggested, create another database just
for this user to connect with SYNONYMS for all of the tables the user has
legitimate access to with only the desired privileges. However, I haven't
tried this myself and I'm not sure that this will prevent the users from
accessing the underlying data through the PUBLIC privilege. You'll have to
test it yourself and report back.
Art
Art S. Kagel
Oninit (www.oninit.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, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Fri, Dec 11, 2009 at 1:15 PM, John Adamski <adamski@graceland.edu> wrote:
> It seems I did not make myself clear as to what I been asked to find out if
> it
> is possible.
>
> So try two:
>
> We have a number of views that have grant permission set to PUBLIC that the
> main 3-rd party administrative application uses. And, this needs to say
> this
> way so the application will function. (I didn't write it I just have to
> maintain the DB, nor do I have any influence to change things)
>
> There is an individual that needs access but can only have very selective
> access to specified tables and/or fields in tables. The requirement for
> this
> persons access is they can't have PUBLIC access.
>
> Is there a way to do something to this individual's security so they have
> access to said select tables but not any PUBLIC tables? I couldn't figure
> out
> a way without doing something to the views.
>
> I hope this is a bit more clear of what I've been asked.
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Thursday, December 10, 2009 8:35 PM
> To: ids@iiug.org
> Subject: Re: security question [18346]
>
> REVOKE ALL ON <viewname> FROM PUBLIC;>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.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, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. 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 Thu, Dec 10, 2009 at 7:48 PM, John Adamski <adamski@graceland.edu>
> wrote:
>
> > IDS 10.00.FC9 on HPUX B.11.23 U ia64
> >
> > I've been asked if the following is possible security wise in Informix by
> > my
> > boss from our lawyer.
> >
> > We have a employee or potential employee that needs special restricted
> > access
> > to our IDS databases, and this is the way it was explained to me.
> >
> > Employee can have access to a limited number of tables or fields in
> tables
> > and
> > only these stated ones. No 'Public' access or any other then what is
> agreed
> > to. Now the database this person needs access to has a number of views
> that
> > have 'public' access.
> >
> > Granting the limited access is pretty straight forward on the tables and
> I
> > think I can get that setup per the lawyers requirements once I told what
> > they
> > are, the troubling area is how do I block access to the views that have
> > 'public' to this users? Is it possible to do this? Can I take away public
> > access from a user?
> >
> > Or are we going to have to rebuild the views and not use 'public' in the
> > grant
> > statement?
> >
> > I hope I have translated the legal babble I was told into something
> > resembling
> > DBA ease. :-)
> >
> > John
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001517478cce6fb12c047a6ac665
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174c1804f2782b047a781c4b
Thanks. I think I will tell the lawyer pay me your salary and I'll figure out
a way, otherwise it can't be done.
Have a feeling he will go with 'can't be done'. :-)
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Friday, December 11, 2009 12:30 PM
To: ids@iiug.org
Subject: Re: security question [18359]
The ONLY way to restrict access to data that is available through a VIEW is
to REVOKE all privileges from this individual's login and from the PUBLIC
pseudo login id. In order for the application to continue to work you will
either have to GRANT specific privileges to individual users or create a
ROLE for users who are so privileged and have the application attempt to SET
ROLE to that role immediately after connecting to the database (if you are
running IDS 10.00 or later, you can set each users' DEFAULT ROLE which will
avoid having to modify the application, but if you are running IDS 7 or 9
there is no default role, so you'll have to modify the application). If
this 'underprivileged' user should try to run the application, the SET ROLE
will fail. Then the application can either exit or ignore the error and let
the user continue with his own privileges intact.
There's no other way to do this, except to revoke even CONNECT privilege
from this one user, and as someone suggested, create another database just
for this user to connect with SYNONYMS for all of the tables the user has
legitimate access to with only the desired privileges. However, I haven't
tried this myself and I'm not sure that this will prevent the users from
accessing the underlying data through the PUBLIC privilege. You'll have to
test it yourself and report back.
Art
Art S. Kagel
Oninit (www.oninit.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, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Fri, Dec 11, 2009 at 1:15 PM, John Adamski <adamski@graceland.edu> wrote:
> It seems I did not make myself clear as to what I been asked to find out if
> it
> is possible.
>
> So try two:
>
> We have a number of views that have grant permission set to PUBLIC that the
> main 3-rd party administrative application uses. And, this needs to say
> this
> way so the application will function. (I didn't write it I just have to
> maintain the DB, nor do I have any influence to change things)
>
> There is an individual that needs access but can only have very selective
> access to specified tables and/or fields in tables. The requirement for
> this
> persons access is they can't have PUBLIC access.
>
> Is there a way to do something to this individual's security so they have
> access to said select tables but not any PUBLIC tables? I couldn't figure
> out
> a way without doing something to the views.
>
> I hope this is a bit more clear of what I've been asked.
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Thursday, December 10, 2009 8:35 PM
> To: ids@iiug.org
> Subject: Re: security question [18346]
>
> REVOKE ALL ON <viewname> FROM PUBLIC;>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.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, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. 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 Thu, Dec 10, 2009 at 7:48 PM, John Adamski <adamski@graceland.edu>
> wrote:
>
> > IDS 10.00.FC9 on HPUX B.11.23 U ia64
> >
> > I've been asked if the following is possible security wise in Informix by
> > my
> > boss from our lawyer.
> >
> > We have a employee or potential employee that needs special restricted
> > access
> > to our IDS databases, and this is the way it was explained to me.
> >
> > Employee can have access to a limited number of tables or fields in
> tables
> > and
> > only these stated ones. No 'Public' access or any other then what is
> agreed
> > to. Now the database this person needs access to has a number of views
> that
> > have 'public' access.
> >
> > Granting the limited access is pretty straight forward on the tables and
> I
> > think I can get that setup per the lawyers requirements once I told what
> > they
> > are, the troubling area is how do I block access to the views that have
> > 'public' to this users? Is it possible to do this? Can I take away public
> > access from a user?
> >
> > Or are we going to have to rebuild the views and not use 'public' in the
> > grant
> > statement?
> >
> > I hope I have translated the legal babble I was told into something
> > resembling
> > DBA ease. :-)
> >
> > John
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001517478cce6fb12c047a6ac665
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174c1804f2782b047a781c4b
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
What about using LBAC.. I didn't followed the chain complete.. but if you
are on 11.10 or later.. LBAC is another option you can consider
From:
"John Adamski" <adamski@graceland.edu>
To:
ids@iiug.org
Date:
12/11/2009 12:42 PM
Subject:
RE: security question [18360]
Sent by:
ids-bounces@iiug.org
Thanks. I think I will tell the lawyer pay me your salary and I'll figure
out
a way, otherwise it can't be done.
Have a feeling he will go with 'can't be done'. :-)
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Friday, December 11, 2009 12:30 PM
To: ids@iiug.org
Subject: Re: security question [18359]
The ONLY way to restrict access to data that is available through a VIEW
is
to REVOKE all privileges from this individual's login and from the PUBLIC
pseudo login id. In order for the application to continue to work you will
either have to GRANT specific privileges to individual users or create a
ROLE for users who are so privileged and have the application attempt to
SET
ROLE to that role immediately after connecting to the database (if you are
running IDS 10.00 or later, you can set each users' DEFAULT ROLE which
will
avoid having to modify the application, but if you are running IDS 7 or 9
there is no default role, so you'll have to modify the application). If
this 'underprivileged' user should try to run the application, the SET
ROLE
will fail. Then the application can either exit or ignore the error and
let
the user continue with his own privileges intact.
There's no other way to do this, except to revoke even CONNECT privilege
from this one user, and as someone suggested, create another database just
for this user to connect with SYNONYMS for all of the tables the user has
legitimate access to with only the desired privileges. However, I haven't
tried this myself and I'm not sure that this will prevent the users from
accessing the underlying data through the PUBLIC privilege. You'll have to
test it yourself and report back.
Art
Art S. Kagel
Oninit (www.oninit.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, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. 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 Fri, Dec 11, 2009 at 1:15 PM, John Adamski <adamski@graceland.edu>
wrote:
> It seems I did not make myself clear as to what I been asked to find out
if
> it
> is possible.
>
> So try two:
>
> We have a number of views that have grant permission set to PUBLIC that
the
> main 3-rd party administrative application uses. And, this needs to say
> this
> way so the application will function. (I didn't write it I just have to
> maintain the DB, nor do I have any influence to change things)
>
> There is an individual that needs access but can only have very
selective
> access to specified tables and/or fields in tables. The requirement for
> this
> persons access is they can't have PUBLIC access.
>
> Is there a way to do something to this individual's security so they
have
> access to said select tables but not any PUBLIC tables? I couldn't
figure
> out
> a way without doing something to the views.
>
> I hope this is a bit more clear of what I've been asked.
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art
> Kagel
> Sent: Thursday, December 10, 2009 8:35 PM
> To: ids@iiug.org
> Subject: Re: security question [18346]
>
> REVOKE ALL ON <viewname> FROM PUBLIC;>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.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, Oninit, the IIUG, nor any other
organization
> with which I am associated either explicitly or implicitly. 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 Thu, Dec 10, 2009 at 7:48 PM, John Adamski <adamski@graceland.edu>
> wrote:
>
> > IDS 10.00.FC9 on HPUX B.11.23 U ia64
> >
> > I've been asked if the following is possible security wise in Informix
by
> > my
> > boss from our lawyer.
> >
> > We have a employee or potential employee that needs special restricted
> > access
> > to our IDS databases, and this is the way it was explained to me.
> >
> > Employee can have access to a limited number of tables or fields in
> tables
> > and
> > only these stated ones. No 'Public' access or any other then what is
> agreed
> > to. Now the database this person needs access to has a number of views
> that
> > have 'public' access.
> >
> > Granting the limited access is pretty straight forward on the tables
and
> I
> > think I can get that setup per the lawyers requirements once I told
what
> > they
> > are, the troubling area is how do I block access to the views that
have
> > 'public' to this users? Is it possible to do this? Can I take away
public
> > access from a user?
> >
> > Or are we going to have to rebuild the views and not use 'public' in
the
> > grant
> > statement?
> >
> > I hope I have translated the legal babble I was told into something
> > resembling
> > DBA ease. :-)
> >
> > John
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001517478cce6fb12c047a6ac665
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174c1804f2782b047a781c4b
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.