no idea how to proceed

  • Thread starter Thread starter Josh
  • Start date Start date
J

Josh

I've got a FE/BE .mdb, for three users. One BE, all users have a FE. Now, I have
to figure out how to let a remote company access *some* of the information.

The mdb tracks people who've applied for a certain status, and the remote
company will check to see whether that status has been approved yet, and will
input some data of their own (I'm not clear on what that will be).

The rest of the mdb cannot be accessed by the remote company.

Can security be set up on the one BE mdb, so that the remote company can access
some tables, but not all?

Just looking for a direction to go.

Thanks
 
If you want to give them access to some *tables*, that is fairly
straightforward using User Level Security. If you want to give them
access to some *records* within the tables, that is somewhat more
complex.

In the first case, you would apply ULS to your database and give
permission to the various tables according to GROUPS. At least two
groups are required -- one for your home users and one for your remote
users. Give different permissions to the various tables to the
different groups.

With ULS, you must launch Access with a particular Workgroup File. The
workgroup file contains the different groups. I would create two
workgroup files, one containing both groups and one containing just
the remote group. Send the "remote only" workgroup file to the remote
site. The easiest way to accomplish this is to create the dual-group
workgroup file first, then copy it, then delete the home group from
one of them.

None of this stuff is immediately obvious, and it is easy to get
tripped up. You *must* get a good set of instructions, and follow them
to the letter -- ON A COPY of your database. Links to several
different documents are on the website in my signature.

To give permissions to selected records, you will need to implement
Read With Owners Permission (RWOP) queries. Basic concepts are in the
Microsoft Security FAQ.



I've got a FE/BE .mdb, for three users. One BE, all users have a FE. Now, I have
to figure out how to let a remote company access *some* of the information.

The mdb tracks people who've applied for a certain status, and the remote
company will check to see whether that status has been approved yet, and will
input some data of their own (I'm not clear on what that will be).

The rest of the mdb cannot be accessed by the remote company.

Can security be set up on the one BE mdb, so that the remote company can access
some tables, but not all?

Just looking for a direction to go.

Thanks
Click to expand...

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
How do you plan on letting the remote company access your db? You do
realize that Access/Jet is a file/server database - not a client/server
one like SQL*Server - so its response time could be absolutely terrible
(perhaps completely unusable) over the internet?

HTH,
TC
 
To give permissions to selected records, you will need to implement
Read With Owners Permission (RWOP) queries. Basic concepts are in the
Microsoft Security FAQ.
Click to expand...
I thought that Read With Owners Permission was really only a method in which
security can be enforced on a column (field basis) not a record basis. Am I
mistaken? I would like to find a convenient way to enforce record based
security.
 
I will be giving them access to some *tables*, not certain *records*.

This is all just in the beginning stages, I'm just trying to get some feel for
for what is possible. I just wanted to make sure I could allow them to access
only certain tables.

I think I can (after reading up and testing) get the security set up ok, but if
they have read rights to the DB, could they copy the DB and crack the password
at their leisure?

I know, I 'm getting ahead of myself.

Thanks
 
I was either going to use PcAnywhere, or a VPN connection. If PcAnywhere over
the phone line, then performance will be throttled by being over phone.

Possibly PcAnywhere (or remote desktop, or VNC) thru VPN/Internet DSL
connection. Where they would be actually using a "local" computer.

Since this all in the beginning stages, I'm not really sure what we're going to
do. For instance, I'm not sure how they would be able to print a report if
connecting to a "local" computer, etc.......I'm not even sure if they will need
to print, or just to take a quick glance to see what the status is.

Not sure what you mean by file/server vs client/server, can you elaborate on
that a little?

Thanks
 
Tom, you can accomplish record level security in a RWOP query by setting
appropriate criteria on the query, that only returns the records you want
available to the user.
 
PCAnywhere is acceptable, if somewhat sluggish response depending on
your connection method. VPN connection is not recommended. Opening the
Access database over a remote connection will likely lead to
corruption of your database.

IIRC, PCAnywhere allows you to designate your locally-connected
printer as the printer for your host computer.

In a file server system, the server acts like a remote hard disk, and
all the computing power is handled by the computer that you are
sitting at. All the "guts" of the database analysis pass over the
network in "raw" format. In a client server system, there is an active
component on the server that connects to the database. Your local
computer sends requests to that active component on the server, which
acts upon the request, and sends the results back to the client. Only
requests and results pass over the network.

In a file-server system, users have direct access to the file system,
and can copy the database to their own computer, and play with it
outside your desired environment. In a client server system, they are
prevented from having direct access to the database files.

In a file-server system, a knowledgeable person could take your Access
database file, and hack away at it to their hearts content.

The security system in Jet/Access is strong enough to keep everyday
users from mucking with your database. It is not strong enough to keep
out a determined hacker.

I was either going to use PcAnywhere, or a VPN connection. If PcAnywhere over
the phone line, then performance will be throttled by being over phone.

Possibly PcAnywhere (or remote desktop, or VNC) thru VPN/Internet DSL
connection. Where they would be actually using a "local" computer.

Since this all in the beginning stages, I'm not really sure what we're going to
do. For instance, I'm not sure how they would be able to print a report if
connecting to a "local" computer, etc.......I'm not even sure if they will need
to print, or just to take a quick glance to see what the status is.

Not sure what you mean by file/server vs client/server, can you elaborate on
that a little?

Thanks
Click to expand...

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
As Joan says, RWOP queries respond to whatever criteria you place in
them. By using the CurrentUser() function to determine who is the
logged-on user, and matching it with appropriate values in certain
fields in your tables, your queries can be made to return only
selected records. When you create/edit the records in the first place,
you use the CurrentUser() function to populate the field in the table.


I thought that Read With Owners Permission was really only a method in which
security can be enforced on a column (field basis) not a record basis. Am I
mistaken? I would like to find a convenient way to enforce record based
security.
Click to expand...

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Jack MacDonald said:
As Joan says, RWOP queries respond to whatever criteria you place in
them. By using the CurrentUser() function to determine who is the
logged-on user, and matching it with appropriate values in certain
fields in your tables, your queries can be made to return only
selected records. When you create/edit the records in the first place,
you use the CurrentUser() function to populate the field in the table.
Click to expand...
Thanks for clarifying that. I've done some more reading and understand
exactly what you're saying. My challenge is that I can't just use the
UserName as criteria for my queries.

My application handles customer pricing. I want sales people to have access
to their customers' pricing, I want managers to have access to all
customers' pricing, and I want certain coordinators to have access to more
than one sales rep's customers' pricing but not necessarily all sales rep's
customers' pricing.

I think I can accomplish this by writing a function that returns a criteria
string based on the user's permissions and using the function in the query
grid. Does that make sense?
 
My preference would be to use ULS Groups to control the record
selection. If I recall correctly, the Microsoft Security FAQ contains
a function to determine if a particular CurrentUser() is a member of a
certain group. You would move your people in and out of groups as
appropriate.

Thanks for clarifying that. I've done some more reading and understand
exactly what you're saying. My challenge is that I can't just use the
UserName as criteria for my queries.

My application handles customer pricing. I want sales people to have access
to their customers' pricing, I want managers to have access to all
customers' pricing, and I want certain coordinators to have access to more
than one sales rep's customers' pricing but not necessarily all sales rep's
customers' pricing.

I think I can accomplish this by writing a function that returns a criteria
string based on the user's permissions and using the function in the query
grid. Does that make sense?
Click to expand...

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Further to Jack's good comments:

In a client/server database like SQL*Server, the client (ie. the user's
PC) can send an SQL statement to the server, to get the exact data it
wants. For example, the client might send the statement: SELECT CUST_ID
FROM CUSTOMERS WHERE NOT ACTIVE_FLAG, to get the customer IDs of all
Inactive customers. The server (ie. central PC) gets that SQL
statement, locates the relevant records, extracts the customer IDs from
those records, and then sends /just the requested customer IDs/ back
down the line to the client.

But in a file/server database like Access/Jet, the SQL is executed on
the client PC. Then the client works out which data blocks are
required, and asks the server for those blocks. "Give me blocks #13,
28, 99, 207 and 405 of table CUSTOMERS". Typically, each block can be
fairly large, eg. 4Kb, and can contain significantly more data than
what the user actually wants. When those blocks come back down the
line, the client then extracts the specific data required (ie. the
customer IDs).

A file/server database therefore uses much more network bandwidth
(usually) than a client/server one. The former pulls down much more
data than the user actually asked for. The result can be, significantly
slower response times.

Look up the two terms on the web - there'll be thousands of
explanations.

HTH,
TC
 
Ouch! Not a function returning a criteria string, please! That would
defeat the automatic optimization of your queries. You would probably
find that it would run very slowly. And you couldn't update the user
permissions, unless you recoded the function accordingly.

All those problems are avoided by creating a new table to defines each
user's data permissions. Then join to that table, in your RWOP queries.

For example, say tblBlah has a field 'ID'. Tom can only access records
where 'ID' is 1 or 3. Mary can only access records where 'ID' is 2 or
6.

Create a new table to define those permissions:

tblPerms:
username ID
Tom 1
Tom 3
Mary 2
Mary 6

then write the RWOP query like this:

SELECT b.*
FROM tblBlah AS b, tblPerms AS p
WHERE p.username = Currentuser()
AND b.ID = p.ID
...

Now, each user can only see the rows that you've defined for them.

Of course, you'd have to:

- add the RWOP option as desired;

- change the above to use INNER JOIN, if you wanted the result to be
updatable, and

- make other changes depending on exactly how you wanted to define the
rows accessible to each user.

HTH,
TC
 
Thanks for preventing me from making a huge design error. I probably would
have realized it myself but not until I tried it that way and wasted a bunch
of time trying to make it work. I had thought about the scheme that you
propose and that's probably the way I'll have to go but I was hoping there
was an easier way.

Basically, I now have to create my own permission schemes, create database
objects (tables and queries) around them and maintain those objects as users
come and go. I have to find a way to maintain the permissions table and
possibly enable others to do the same. Whenever I add a new user I have to
determine which IDs they should have access to and who should have access to
their ID. I'm sure it can be done but it gets very complicated very quickly.
I can see already that I will need to create a user interface with which to
manage this process.

Somehow I knew there wasn't going to be a quick solution to my problem. If I
was desinging this solution from scratch I guess it wouldn't be so bad but
that's not the case. This is an application which I wrote for my own use a
while back and have now started to share it with others. It's forced me to
learn more about replication and now security. I have a lot of changes to
make.

Thanks for you help.



My problem with doing it this way is that I'm basically writing the security
for the application. Access's security does very little to help me.
 
No probs, glad I could help!

My example was a little suboptimal, in having the permissions table
give people permission to individual records identified by record ID
number. Generally, you'd want to do this at a higher level. For
example, you might make each form store, in each new record, the ID or
username of the user who created that record. Then, your permissions
table would just say which users could see which other users' records.
That table would not need to state the permissions right down to the
individual record ID level.

Cheers,
TC
 
PCAnywhere is acceptable, if somewhat sluggish response depending on
your connection method. VPN connection is not recommended. Opening the
Access database over a remote connection will likely lead to
corruption of your database.
Click to expand...

Good to know, although I was planning on them not having a front-end
at their location, just to remotely connect, and then using a local
computer to 'work' on.
IIRC, PCAnywhere allows you to designate your locally-connected
printer as the printer for your host computer.
Click to expand...

I thought I'd heard that somethwere (pcanywhere).

reference below, ok, I think I understand now.
In a file server system, the server acts like a remote hard disk, and
all the computing power is handled by the computer that you are
sitting at. All the "guts" of the database analysis pass over the
network in "raw" format. In a client server system, there is an active
component on the server that connects to the database. Your local
computer sends requests to that active component on the server, which
acts upon the request, and sends the results back to the client. Only
requests and results pass over the network.

In a file-server system, users have direct access to the file system,
and can copy the database to their own computer, and play with it
outside your desired environment. In a client server system, they are
prevented from having direct access to the database files.

In a file-server system, a knowledgeable person could take your Access
database file, and hack away at it to their hearts content.

The security system in Jet/Access is strong enough to keep everyday
users from mucking with your database. It is not strong enough to keep
out a determined hacker.
Click to expand...

Thanks!

Josh
 
Thanks for the clarification..........clear now (or at least, as clear
as anything ever is for me)

Network bandwidth won't be consideration here, as the remote company
will only check in every so often, just to check the status, then log
off.

Thanks to TC and Jack for their help.

Josh
 
TC,

I've followed your direction on this one and I'm having trouble figuring out
how to make my query updatable. I have a Customers table, SalesReps table,
and a Permissions table. The permissions table has usernames and SaleRepIDs
stored in it. The SalesReps table has a one-to-many relationship with the
Customers table. I've written a query that goes something like this:

Select * From tblCustomers Inner Join tblPermissions On
tblCustomers.SalesRepID = tblPermissions.SalesRepID Where
tblPermissions.UserName = CurrentUser()

It displays the correct results but they are not updateable. I want users to
be able to edit, add and delete any records that they have access to. How do
I accomplish this?

Thanks
 
Back
Top