Re: Access: Protect data in tables from copy and export...

From: TC (no_at_email.here)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 11:44:28 +0930

Erm, I doubt it! :-)

Say that user NO has no direct access to table T, but user YES does have
access to that table.

User NO will not be able to run this query, under any circumstances, afaik:
   SELECT * FROM T

If you add WITH OWNERACCESS OPTION (or whatever it is), user NO >will< be
able to run that query, >>but only if the query is owned by user YES - not
if the query is still owned by user NO<<.

So, adding WITH OWNERACCESS OPTION to a query, is not a sneaky way of
defeating the access restrictions of the user who owns that query. It is,
conversely, a way for a higher-level user, to delegate his authority to a
lower-level user, in a manner completely controlled by the higher-level
user.

So in our example, when user YES (>>not NO<<) creates the WITH OWNERACCESS
OPTION query, he is delegating his read authority to table T, to owner NO,
who does not have that authority directly. User NO is not grabbing that
authority "out of thin air", as it were.

IMO, the ownership issue is sadly neglected when people discuss these
queries. They say: "add a WITH OWNER ACCESS OPTION and it will all work!" -
neglecting that the >ownership< of that query must often be changed as well.

Cheers,
TC

"Adrian Jansen" <qqv@noqqwhere.com> wrote in message
news:40836bb8$2@duster.adelaide.on.net...
> Yes, that what I thought too. But I am sure I created a situation where I
> had a user with only read/write permissions able to create a query and
view
> data in a table ( to which they did not have access ), merely by adding
the
> "With Owner Access" clause to the SQL in the query. I will have to check
> further.
>
> --
> Regards,
>
> Adrian Jansen
> J & K MicroSystems
> Microcomputer solutions for industrial control
> "TC" <no@email.here> wrote in message
news:4083300c_4@news.chariot.net.au...
> > Adrian, I don't follow you there. If a user has "no permissions directly
> on
> > the tables", they definitely should not be able to create & run a query
on
> > those tables, no?
> >
> > Cheers,
> > TC
> >
> >
>
>

Relevant Pages

  • Re: Access: Protect data in tables from copy and export...
    ... query owned by someone else. ... > If you add WITH OWNERACCESS OPTION, ... > OPTION query, he is delegating his read authority to table T, to owner NO, ... >> had a user with only read/write permissions able to create a query and ...
    (microsoft.public.access.forms)
  • Re: Column authority in STRQMQRY
    ... regard to ability to access a table referenced by the query, since there is no adopted authority feature for that object type except as established by a program object from which the query is invoked. ... query running under that profile the sql bombs out. ... The users have *ALL authority over the files. ... preventing the access to the file, review for column authorities. ...
    (comp.sys.ibm.as400.misc)
  • Finding Recently Logged on Users
    ... I can query on event log ID 528 in the security log, ... but I want to exclude the nt authority \ network service, ... to query and exclude the other user ID's. ...
    (microsoft.public.windows.server.scripting)
  • Re: Dynamic WHERE statement or criteria
    ... When you have a parameter in a query, SET the .PARAMETER object variable before opening the RecordSet. ... The field name is 'Authority Type', ... Dim stDocName As String ... MsgBox thisAuthority ...
    (microsoft.public.access.modulesdaovba)
  • Re: tough query (for me)
    ... WITH OWNERACCESS OPTION; ... > Suppose we have a table with two columns Col1 and Col2. ... > Query result: ... > Let's say the there is a table with a key field and another table with the ...
    (microsoft.public.access.queries)