Secured Temp database

  • Thread starter Thread starter robert d via AccessMonster.com
  • Start date Start date
R

robert d via AccessMonster.com

I am creating a temporary database to hold some tables needed to hold the
results of complex and time-consuming calculations.

The temp DB is created from a model temp DB that I include with the
application. Since tables in the instance of the model temp DB, the instance
temp DB, may have sensitive data I want to secure it. I have used RWOP type
security on the model temp DB. All permissions to tables are revoked for all
groups. The groups only have Open/Run permission on the database.

In the front end application, with code I'm able to create the instance temp
DB and link to it. However, when the tables need to be accessed, I'm getting:


Error #3112 Record(s) cannot be read; no read permissions on tblTEMP XXXXX

I don't understand this as I am using RWOP queries. When I double click on
the RWOP queries I get this message for all.

RWOP is run with Owner's permissions. When I look in the Security menu -
User and Group Permissions - it shows the Owner of the linked temp table as
the current user. But this is expected as the Temp database was just created.
Also, this is just the links so I don't know.

Can anyone provide any suggestions, etc.
 
Just a few additional things:

1) I use RWOP on my backends (not temp tables) as well and have no problems.
2) The Owner shown for the backend table links (again the main tables not the
temp tables) is also the user who last linked them successfully. So I don't
think what I mentioned in the first post about the owner of the links has any
bearing on this problem

3) The actual error message that I get when I double-click on the RWOP query
in the dB window is:

Could not read definitions; no read definitions permission for table or query
'tblTEMP XXXX'.

Is having read definitions different than have read permission on the data in
the table that has had had all permissions removed?

Thanks.
 
It is not the owner of the /link/ which is important to an RWOP query.
It is the owner of the /query/. You need to ensure that the owner of
the RWOP /query/ has sufficient permissions (whatever those may be) to
read the tables in question. Then, any other user who has Read Design
and Read Data permision to the RWOP /query/, should be ale to read
those tables, also.

HTH,
TC
 
I guess I don't understand then what RWOP really means. I always assumed
that is was the owner of the tables in the backend. Are you saying it is the
owner of the query in the front end???

If I go to Security --> User and Group Permissions and click on the Change
Owner tab and select Object Type = Query, I see the following:

1) My RWOP queries on the main backend tables all say the Current Owner is
<Unknown>. These queries work just fine with no problems.

2) My RWOP queries on the temporary tables (from the temp table) show the
Current Owner is also <Unknown>.

I still don't know what to do.

Thanks.
 
Scratch what I just wrote. I just realized I was logged on to my front end
with the distribution workgroup file and not the development workgroup file.

All RWOP queries in the front end have the Current Owner equal to the
database owner.

Let's call that Owner: DBOWNER

Now, the workgroup file used to secure the model temporary database is
different from both the distribution workgroup file and the development
workgroup file. I thought if I did it this way and didn't even distribute
that workgroup file, then no one on the client side would have a chance of
getting in because they don't have any workgroup file available to them.
Let's call this workgroup file the NoAccess workgroup file.

However, DBOWNER is not a listed user in the NoAccess workgroup file.

Is this the problem. I need to add DBOWNER as a user to the NoAccess
workgroup file and provide permissions on the table for just that user??

robert said:
I guess I don't understand then what RWOP really means. I always assumed
that is was the owner of the tables in the backend. Are you saying it is the
owner of the query in the front end???

If I go to Security --> User and Group Permissions and click on the Change
Owner tab and select Object Type = Query, I see the following:

1) My RWOP queries on the main backend tables all say the Current Owner is
<Unknown>. These queries work just fine with no problems.

2) My RWOP queries on the temporary tables (from the temp table) show the
Current Owner is also <Unknown>.

I still don't know what to do.

Thanks.
It is not the owner of the /link/ which is important to an RWOP query.
It is the owner of the /query/. You need to ensure that the owner of
Click to expand...
[quoted text clipped - 5 lines]
HTH,
TC
Click to expand...
Click to expand...
 
robert d via AccessMonster.com wrote:

I guess I don't understand then what RWOP really means.
Click to expand...

Makes it kinda' hard to use them properly!

I always assumed that it was the owner of the tables in the backend.
Click to expand...

Not sure I understand what you mean by that sentence. "An RWOP query is
the owner of the tables in the backend"?

Here is what you need to know. A normal (non RWOP) query is executed
with the permissions of the user who is running the query. But an RWOP
query is executed with the permissions of the user who /owns/ the
query, regardless of which user is actually running it. So, an RWOP
query can let the current user do things which he is normally not
entitled to do.

HTH,
TC
 
A lot of what you are saying there is "sorta right", but it's hard to
be sure, because your use of the various terms is not correct. For
example, you say that the clients "don't have any workgroup file
available to them", but it is impossible to run an Access database
without a workgroup file - it simply can't be done.

So let's go back to the actual problem & start again.

Essentially, you have two secured databases db#1 and db#2. You want,
from code that is running in db#1, to write data into db#2 & read data
back from db#2. But you don't want every man & his dog reading data
from db#1 /or/ db#2, except through your application.

Correct?

Also, you want to have a "development" workgroup file, whose Admins
group has full administrative access to both databases, and a
"distribution" workgroup file, whose Admins group does /not/ have any
special access to those databases.

Correct?

HTH,
TC
 
TC I appreciate your patience. Please bear with me as using RWOP is the
basis for my security and I realize now that I don't completely understand it.


1) I thought that the word "Owner" in RWOP meant the owner of the backend
tables not the front-end query.

2) Then my problem is that the owner of my RWOP query (located in the front
end) on the temp tables is not a member of the workgroup used to secure the
model temporary database. If DBOWNER isn't a member he couldn't possibly
have any permissions on those temporary tables. Please confirm that this is
correct.

3) Now I'm wondering about my main backend tables. I used the development
workgroup file to secure both the front end and main backend. So, that
explains why I have no problems with my RWOP queries on the main backend
tables when using the development workgroup file.

When using the distribution workgroup file, however, DBOWNER is, by design,
not a member of that workgroup file. This explains why when looking at the
owner of the front-end queries when using the distribution workgroup file,
that owner is displayed as <Unknown>. But if only the distribution workgroup
file is used, how does Access know who the owner of the RWOP is?

I really want to make sure I understand this, so I would appreciate your
confirming statements that I've made that are correct.

Thanks.
 
robert said:
TC I appreciate your patience.
Click to expand...

No probs :-) I may need to go in 30 minutes or so, but I am happy to
continue tomorrow.

Please bear with me as using RWOP is the basis for my security
and I realize now that I don't completely understand it.
Click to expand...

You would not be the first!

1) I thought that the word "Owner" in RWOP meant the owner of the backend
tables not the front-end query.
Click to expand...

No. It is as I said before. The fundamental characteristic of an RWOP
query is that it is executed with the permissions of the user who owns
the RWOP query, regardless of which user is actually running that
query.

2) Then my problem is that the owner of my RWOP query (located in the front
end) on the temp tables is not a member of the workgroup used to secure the
model temporary database. If DBOWNER isn't a member he couldn't possibly
have any permissions on those temporary tables. Please confirm that this is
correct.
Click to expand...

I haven't fully followed your existing setup since I found the
description a bit hard to follow. However, if the owner of an RWOP
query does /not/ have access to the tables referenced by that query,
then, that RWOP query will not work for anyone - that owner, or anyone
else.

When you refer to a user being (or not being) a member of several
workgroup files, remember that a user /can/ be a member of several
workgroup files. This depends on what Personal Identifier (PID) you
used when you created the user in those workgroup files. (The PID is
not the password.) Google this group for the words PID, and SID, for
yet more information!!

I really want to make sure I understand this,
Click to expand...

It's not easy. Digest the above, then we can continue tomorrow.

Cheers,
TC
 
Didn't get your 2:05 post until well after my last post. Let me state the
current application strategy.

1) Front end - Owner is DBOWNER. Secured by developer workgroup file. Client
gets distribution workgroup file.

2) Main back end - Owner is DBOWNER. Secured by developer workgroup file.
Client gets distribution workgroup file. All permissions revoked on the
tables.

3) Model temp DB - used in the creation of the instantiation of temporary
database. Owner is TEMPOWNER. Secured by NoAccess workgroup file. All
permissions revoked on the tables.

DBOWNER is not a user in NoAccess workgroup file. Client does not get this
workgroup file or any derivative of it. This is what I meant when I said
that the client doesn't have any workgroup file available to them to open
this model temp DB or it's instance during any current session in which the
front end is running (poor wording on my part in the post). I don't want
the client to be able to open either the model temporary database or it's
instance.

Thanks.

robert said:
TC I appreciate your patience. Please bear with me as using RWOP is the
basis for my security and I realize now that I don't completely understand it.

1) I thought that the word "Owner" in RWOP meant the owner of the backend
tables not the front-end query.

2) Then my problem is that the owner of my RWOP query (located in the front
end) on the temp tables is not a member of the workgroup used to secure the
model temporary database. If DBOWNER isn't a member he couldn't possibly
have any permissions on those temporary tables. Please confirm that this is
correct.

3) Now I'm wondering about my main backend tables. I used the development
workgroup file to secure both the front end and main backend. So, that
explains why I have no problems with my RWOP queries on the main backend
tables when using the development workgroup file.

When using the distribution workgroup file, however, DBOWNER is, by design,
not a member of that workgroup file. This explains why when looking at the
owner of the front-end queries when using the distribution workgroup file,
that owner is displayed as <Unknown>. But if only the distribution workgroup
file is used, how does Access know who the owner of the RWOP is?

I really want to make sure I understand this, so I would appreciate your
confirming statements that I've made that are correct.

Thanks.
[quoted text clipped - 14 lines]
HTH,
TC
Click to expand...
Click to expand...
 
OK, I just added DBOWNER to the NoAccess workgroup file while the model
temporary DB was open. I gave DBOWNER, full administer permissions on all of
the tables.

Now, in the front end of my application, the RWOP queries display data
without an error message. I was able to open each RWOP query on the
temporary tables in the database window without error.

So it looks like I got it to work, but I want to test further to make sure.

Any more information you wish to provide is appreciated.

I guess my only concern now is if malicious users are somehow able to
recreate the DBOWNER user ID (along with PID). I guess you can only do so
much. I think I've implemented good security and maybe have to leave it at
that.

BTW:

Here's a synopsis of my security strategy.
1) Revoke all permissions in the back end and use RWOP as described in
previous posts on this thread.
2) I've also unchecked all startup options
3) I use only custom menus
4) I distribute a second workgroup file separate from the development
workgroup file. No workgroup file is distributed for the model temporary
database.
5) Front end is distributed as a .mde file.

Any comments you wish to make concerning my overall security strategy is
appreciated.

Thanks!


robert said:
Didn't get your 2:05 post until well after my last post. Let me state the
current application strategy.

1) Front end - Owner is DBOWNER. Secured by developer workgroup file. Client
gets distribution workgroup file.

2) Main back end - Owner is DBOWNER. Secured by developer workgroup file.
Client gets distribution workgroup file. All permissions revoked on the
tables.

3) Model temp DB - used in the creation of the instantiation of temporary
database. Owner is TEMPOWNER. Secured by NoAccess workgroup file. All
permissions revoked on the tables.

DBOWNER is not a user in NoAccess workgroup file. Client does not get this
workgroup file or any derivative of it. This is what I meant when I said
that the client doesn't have any workgroup file available to them to open
this model temp DB or it's instance during any current session in which the
front end is running (poor wording on my part in the post). I don't want
the client to be able to open either the model temporary database or it's
instance.

Thanks.
TC I appreciate your patience. Please bear with me as using RWOP is the
basis for my security and I realize now that I don't completely understand it.
Click to expand...
[quoted text clipped - 29 lines]
Click to expand...
 
Yes, let's continue this tomorrow.

Even though the RWOP queries on the temp DB tables now function without error,
I'm suddenly having problems with the workgroup files.

I was doing some testing at trying to open the model temporary database with
the development and distribution workgroup files. I didn't think I would be
able to open it, at least that's the way I designed it. Temp DB opened no
problem. I thought I had secured it with the NoAccess workgroup file. I can
now open the Temp DB with these other workgroup files even with users that
are not present in the NoAccess workgroup file (but are present in either the
development or workgroup file). I cannot open the Temp DB with just the
regular system.mdw file (unaltered in any way by me).

So, I don't know what's going on here with this.

Thanks.
 
I guess when I had only one workgroup file (the development file), I thought
I knew what I was doing. But now I realize I don't.

Here's another thing that is happening. I can now logon to the front end
using the NoAccess workgroup file and a user ID = TEMPOWNER. TEMPOWNER is
the owner of the tables in the model temporary database.

TEMPOWNER isn't even a user in the development or distribution workgroup
files. I don't have a clue what is going on. I would have thought there was
no chance that I could get in.

All three workgroup files were created with different Name/Company/Workgroup
ID combination.
 
It's still a bit hard to comment on your setup, because of the way you
phrase your descriptions. For example, you say that "no workgroup file
is distributed for the model temporary database". That /sounds/ like it
makes sense - bit it doesn't. When you start Access, eg. by
double-clicking a database file, it /always/ selects a workgroup file
to use for that run of Access. If code within that database, writes to
or reads from another database, that other database will be accessed
using the workgroup file that Access selected for that run. So there is
no way that you can access the other database "without a workgroup
file" - regardless of which workgroup files you did or didn't
distribute to the user.

But I guess the proof is in the pudding. Try to break your own security
by accessing the confidential data in either database without a valid
username/password. If /you/ can't do that, the odds are that the user
can't either :-)

Good luck!,
TC
 
When I say no workgroup file is distributed, I mean a workgroup file that
will allow someone to open the model temporary database. The workgroup file
that secured this model temporary database or a derivative of this workgroup
file is not included with the files distributed to the user. I've done this
because I don't want anyone to successfully open this model temporary
database or the instance that is created from it.

Is my method here incorrect?

Thanks.
 
I forgot to comment on the linking.

From the front end, I'm linking to the instance of the temporary database
using the method described in the Access Security FAQ 14.3 No Permissions
necessary.
 
Ok, I hear what you say. Let me restate this in my own terms to be sure
I understand it.

- You have a main database db#1.

- You have a copy (db#2) of the model temporary database.

- You start db#1, probably with a shortcut that uses the /wrkgrp switch
to select the proper workgroup file, wg#1.

- The user logs on with a username/password, say, Fred/whatever.

- User Fred of workgroup file wg#1 has sufficient permissions to read &
write data from & to db#2, but he does /not/ have sufficient permision
to open db#2 directly (by double-clicking it)?

Yes? No?

TC
 
Pretty much. Except that on in db#1 I have RWOP queries on the tables in
db#2 and initially the user was not able to access these tables through the
RWOP queries (this was the initial reason for this whole thread). However, I
added the owner of the RWOP queries as a user in db#2 (using the db#2
workgroup file which is not distributed to the user). Now any user in db#1
can access the tables in db#2 via the RWOP queries.

Because the workgroup file used to secure db#2 is not distributed, the user
can't open db#2 by double-clicking on it.

So:

1) What's your opinion of this method for preventing any user of the
distributed db#2 from opening db#2. My goal is to not allow anyone to open
db#2. All the access they need is provided via the front end (db#1)

2) I've since posted that I'm now having problems with the workgroup files
not providing the security I thought they did. See a couple of posts above.
I guess I'm not really sure what the link is between a workgroup file, that
was used to secure the db, and the db itself. In other words, what is it
about that workgroup file that allows one to say that workgroup file secured
this db (besides the obvious that it can open the db). Is it the Owner
that's the key?

Thanks.
 
I guess I'm not really sure what the link is between a workgroup file, that
was used to secure the db, and the db itself. In other words, what is it
about that workgroup file that allows one to say that workgroup file secured
this db (besides the obvious that it can open the db).
Click to expand...

That's a very good question.

The combination of name, organization and workgroup ID (that you choose
when you create a new workgroup file) is mashed together to form a
unique number which is stored within the workgroup file. Then, when you
create a new database, Access takes that unique number from the current
workgroup file, & copies it to the database file. Thus, when Access has
a workgroup file, and a database, it can tell whether that workgroup
file is or is not "the workgroup file which was in effect when the
database was first created" - it just gets the two numbers (one from
each file) & compares them for equality.

These unique numbers are generically called, Security Identifiers
(SIDs). The particular number in question is, the SID of the Admins
group. Each group and user in the workgroup file, has its own SID. But
only the SID of the Admins group is stored within the database file -
for the purpose describned above.

You can find more information on SIDs by googling this group for that
term.

I'll an't answer your other questions right now, since I have to be off
right now, & I
m not sure if I will be on tomorrow. Maybe later, otherwise the day
after.

Cheers,
TC
 
Back
Top