odbc tables

[Guest]

Hi

I have a few databases set up that basically act like a crystal report; they
report information. i have queries, macros, unbounded forms, and reports
(minimal vba). The tables that supply the information are on a sql server,
which i connect to them through an odbc connection.

i have one problem, there are some sensitive information on the tables that
i don't want the users to see. i take them out by limiting them on the first
query. my question is, what is the best way to disallow my users from opening
the table, and only accessing it through the queries.

thanks,

sam

 

[Guest]

Get your SQL database administrator to create account for them a view only
for the fields allowed.

 

[Guest]

thanks karl for your reply. the problem is that i want my users to be able to
read from the table based on a query.

let me describe the situation. i have employees at the corporate level that
need to access the employees at the lower down levels. however, i don't want
them to be able to read the coporate level employees information. so i have a
query that limits the employee information to only those that are not working
at the coporate level. all the information is stored on a sql server, to
which i connect to the tables with an odbc connection. i have a user name
passoword that is stored in access that allows the odbc connection.

now i don't want the users making new queries (this can be done be setting
up security levels). i also don't want them to be able to open up the tables
directly. if i set up the security levels, and i limit the users to access
the tables, then they can't run the queries and reports that feed off these
tables. what do i do?

thanks,

sam

 

[Douglas J. Steele]

As Karl suggested, create a View that lets them see whatever you want them
to see and give them the necessary access to that view. Don't give them any
access to the underlying tables.

 

[Guest]

thanks all for replying. i don't understand why these groups are here. are
you guys serious? please review your answers, do you think you have answered
my question or you just rephrased my question.

ok, i will attempt to clarify.

i need the table available so that the queries can read the information. one
of the objectives of my query is to filter out certain information. i need to
block the table view so that the odbc table can't be seen. i tried creating
the security groups but that would not help my situation. i am currently
trying to see if i can somehow limit it on the server side. in the meantime
that doesn't seem to be the answer either.

thanks again (and sorry for being a bit strong, i am just too frustrated.)

sam

 

[Guest]

A View in a SQL Server is like a query in Access. To them it will be as
though they are linked to a copy of the table that only has the fields you
have placed in the View.

Also the priviledges can can be set for Read Only.

Talk to your SQL Administrator.

 

[Olden Doode]

thanks all for replying. i don't understand why these groups are here. are
you guys serious? please review your answers, do you think you have answered
my question or you just rephrased my question.

ok, i will attempt to clarify.

i need the table available so that the queries can read the information. one
of the objectives of my query is to filter out certain information. i need to
block the table view so that the odbc table can't be seen. i tried creating
the security groups but that would not help my situation. i am currently
trying to see if i can somehow limit it on the server side. in the meantime
that doesn't seem to be the answer either.

thanks again (and sorry for being a bit strong, i am just too frustrated.)

sam

:

Try setting the Access database front ends up as a workgroup. Use the
SQL database data as a separate backend data only. Then as administrator
you can go in to the workgroup and permissions for the database security
and set up the types of access you wnat any one user to have. You can
define whether they can read, insert, edit, dlete data, as well as
change the design of objects like queries and tables etc. Minimize the
number of user groups you want and restrict permissions to these groups.
Then add specific users to the groups as members with only the group's
permissions. Make yourself the owner and administrator and minimize
permissions or disable the Administrator and guest acoounts to prevent
unauthorized access. You as administrator can then deploy the database
to a network share with the custom workgroup ( .mdw) file. Make sure you
HIDE all objects in the database window and use the autostart options
box to prevent unathorized access to the linked table with the fields
you want to protect.

It isn't bullet - proof but is pretty good if you also have domain share
privlages in your network.

 

[Douglas J. Steele]

No, you don't need the tables to be available.

In SQL Server (which you said you were using), users can have access to
views without requiring any access to the underlying tables.

 

[Guest]

hi all


thanks so much. i will set up tomorow a view on the server's end. i think
that should solve the problem. then i will give myself read privliges so that
i can access this view from the server. the rest is history. should be very
simple.

thanks a lot, i have really learnt alot in the last few months. thanks all,
and keep up the good work.

sam

 

[aaron.kempf]

i _REALLLLLLLY_ reccomend that you look at usign Access Data Projects.
They are the best tool for developing SQL Server queries anywhere...
Views, Sprocs-- Functions-- ADP can do it all.

Good luck

-Aaron