Understanding security and permissions
Hi, we have a vendor supplied database. I am attempting to create a read only
user for one table on one database. My attempts to revoke permissions and
access are not working as expected. I assumed this would be quick and easy and
am wondering if I should attempt a role for this purpose.
Informix 10.00 FC8 on hp-ux 11.23.
Schema build:
grant dba to "vendorname";
grant resource to "public";
create table as "vendorname".mytable
(
col1 char(35)
);
revoke all on "vendorname".mytable from "public" as "vendorname" ;
grant select on "vendorname".mytable to "public" as "vendorname";
grant update on "vendorname".mytable to "public" as "vendorname";
grant delete on "vendorname".mytable to "public" as "vendorname";
I create a unix account userviewonly. At that point they can access any data
and perform any update, insert, delete. Implying to me they are part of the
group public and have resource priv at the database level even though no grant
stmts have been made at the database level or the table level.
I assumed I could execute statements to block access using the grant-revoke
syntax as below either via database level privileges or table level.
grant resource to userviewonly;
revoke dba from userviewonly ;
revoke connect from userviewonly ;
revoke resource from userviewonly ;The userviewonly account has update, insert, delete priv's to the tables in
the database.
At the table level I tried this:
grant select on "vendorname".mytable to "userviewonly" as "vendorname";
revoke select on "vendorname".mytable from "userviewonly" as "vendorname" ;
Regardless of order and execution of the above, the user can query the table
in question. Clearly I am missing something and need to understand what it is
via the above example.
Thanks for you help,
Doug
Revoke all privileges from the pseudo user PUBLIC except for CONNECT (NOTresource) and SELECT. Or revoke all privileges from PUBLIC altogether and
just privilege user 'userviewonly' with CONNECT and SELECT privileges.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
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, Aug 27, 2009 at 7:43 PM, Doug Fossmeyer
<DougF@spokaneschools.org>wrote:
> Hi, we have a vendor supplied database. I am attempting to create a read
> only
> user for one table on one database. My attempts to revoke permissions and
> access are not working as expected. I assumed this would be quick and easy
> and
> am wondering if I should attempt a role for this purpose.
>
> Informix 10.00 FC8 on hp-ux 11.23.
>
> Schema build:
> grant dba to "vendorname";
> grant resource to "public";
> create table as "vendorname".mytable
> (
> col1 char(35)
> );
> revoke all on "vendorname".mytable from "public" as "vendorname" ;
> grant select on "vendorname".mytable to "public" as "vendorname";
> grant update on "vendorname".mytable to "public" as "vendorname";
> grant delete on "vendorname".mytable to "public" as "vendorname";>
> I create a unix account userviewonly. At that point they can access any
> data
> and perform any update, insert, delete. Implying to me they are part of the
> group public and have resource priv at the database level even though no
> grant
> stmts have been made at the database level or the table level.
>
> I assumed I could execute statements to block access using the grant-revoke
> syntax as below either via database level privileges or table level.
> grant resource to userviewonly;
> revoke dba from userviewonly ;
> revoke connect from userviewonly ;
> revoke resource from userviewonly ;> The userviewonly account has update, insert, delete priv's to the tables in
> the database.
>
> At the table level I tried this:
> grant select on "vendorname".mytable to "userviewonly" as "vendorname";
> revoke select on "vendorname".mytable from "userviewonly" as "vendorname" ;>
> Regardless of order and execution of the above, the user can query the
> table
> in question. Clearly I am missing something and need to understand what it
> is
> via the above example.
>
> Thanks for you help,
> Doug
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174760aa49789b04722874f4
Every user is part of "public", this can not be changed. So if you do not
revoke public access from a table then even though you revoke an individual
user, the user would be able to gain access via public.
grant select on t1 to public;revoke select on t1 to "john";
Even though "john" select permission was removed "john".
still has access to select because "john" is part of public.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 08/27/2009 04:43:26 PM:
> [image removed]
>
> Understanding security and permissions [16796]
>
> Doug Fossmeyer
>
> to:
>
> ids
>
> 08/27/2009 04:47 PM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi, we have a vendor supplied database. I am attempting to create a read
only
> user for one table on one database. My attempts to revoke permissions and
> access are not working as expected. I assumed this would be quick
> and easy and
> am wondering if I should attempt a role for this purpose.
>
> Informix 10.00 FC8 on hp-ux 11.23.
>
> Schema build:
> grant dba to "vendorname";
> grant resource to "public";
> create table as "vendorname".mytable
> (
> col1 char(35)
> );
> revoke all on "vendorname".mytable from "public" as "vendorname" ;
> grant select on "vendorname".mytable to "public" as "vendorname";
> grant update on "vendorname".mytable to "public" as "vendorname";
> grant delete on "vendorname".mytable to "public" as "vendorname";>
> I create a unix account userviewonly. At that point they can access any
data
> and perform any update, insert, delete. Implying to me they are part of
the
> group public and have resource priv at the database level even
> though no grant
> stmts have been made at the database level or the table level.
>
> I assumed I could execute statements to block access using the
grant-revoke
> syntax as below either via database level privileges or table level.
> grant resource to userviewonly;
> revoke dba from userviewonly ;
> revoke connect from userviewonly ;
> revoke resource from userviewonly ;> The userviewonly account has update, insert, delete priv's to the tables
in
> the database.
>
> At the table level I tried this:
> grant select on "vendorname".mytable to "userviewonly" as "vendorname";
> revoke select on "vendorname".mytable from "userviewonly" as"vendorname" ;
>
> Regardless of order and execution of the above, the user can query the
table
> in question. Clearly I am missing something and need to understand what
it is
> via the above example.
>
> Thanks for you help,
> Doug
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>