limiting access to reports

In my employee database, supervisors will need the ability to run reports on their employees. This reporting can begin at any level of the organizational hierarchy. The Director will need to run reports on any of her supervisors and their employees; her supervisors will need to run reports on their employees; employees will need to run reports on themselves. Each employee record has a field which gives that person’s supervisor number.

I have the reports working for supervisors, but I haven’t figured out how to limit the supervisors report to only their employees. As it is right now, any db user can run a report on anyone. (This db has not been released and is still in the development stages.) Data and tables are on a SQL Server and users have the front end forms, reports, etc. We’re using SQL logon authentication for access to the tables.

Where can I find information about how to limit a user’s access to only their employee’s records? I know the answer lies in a select statement, but how do I identify the current logged on user?

tia,

--
JMorrell

JMorrell wrote:
In my employee database, supervisors will need the ability to run
reports on their employees. This reporting can begin at any level of
the organizational hierarchy. The Director will need to run reports
on any of her supervisors and their employees; her supervisors will
need to run reports on their employees; employees will need to run
reports on themselves. Each employee record has a field which gives
that person's supervisor number.

I have the reports working for supervisors, but I haven't figured out
how to limit the supervisors report to only their employees. As it
is right now, any db user can run a report on anyone. (This db has
not been released and is still in the development stages.) Data and
tables are on a SQL Server and users have the front end forms,
reports, etc. We're using SQL logon authentication for access to the
tables.

Where can I find information about how to limit a user's access to
only their employee's records? I know the answer lies in a select
statement, but how do I identify the current logged on user?

tia,

I suggest that first, if you have not already, you need to study up on
user level security and split databases.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can lock
everyone including God out.

Practice on some copies to make sure you know what you are doing.

Next I suggest that you set the criteria in a query or report to limit
the results to the employees they should include. Of course you need a
table or other source for the query to know which employees. If the
employees never change or can be identified by some characteristic like
department you can hard code that if you like.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

I think before you dig too deeply into the security issue why not try something like this. If each employee has a supervisor id in their record, then restrict the query running the report based on the supervisor login. Each employee record could have a level indicated also so that if the current user is not a supervisor, the data would be for that login id only.

Might be worth a try.


"Joseph Meehan" wrote:

JMorrell wrote:
In my employee database, supervisors will need the ability to run
reports on their employees. This reporting can begin at any level of
the organizational hierarchy. The Director will need to run reports
on any of her supervisors and their employees; her supervisors will
need to run reports on their employees; employees will need to run
reports on themselves. Each employee record has a field which gives
that person's supervisor number.

I have the reports working for supervisors, but I haven't figured out
how to limit the supervisors report to only their employees. As it
is right now, any db user can run a report on anyone. (This db has
not been released and is still in the development stages.) Data and
tables are on a SQL Server and users have the front end forms,
reports, etc. We're using SQL logon authentication for access to the
tables.

Where can I find information about how to limit a user's access to
only their employee's records? I know the answer lies in a select
statement, but how do I identify the current logged on user?

tia,

I suggest that first, if you have not already, you need to study up on
user level security and split databases.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can lock
everyone including God out.

Practice on some copies to make sure you know what you are doing.

Next I suggest that you set the criteria in a query or report to limit
the results to the employees they should include. Of course you need a
table or other source for the query to know which employees. If the
employees never change or can be identified by some characteristic like
department you can hard code that if you like.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math

Thanks for both replies. We intend to eventually open this up to all users in the dept. via an asp/web front end. Given that, we're certain of the logged on user. Security concerns at that point will also be somewhat mitigated. For the time being, only 3 users will have access.

I should have added that in addition to each employee's record having their supervisor's ID, there is a check box to indicate if that employee is a supervisor. There is also a "Supervisor" table with a supervisory level field; 1=Director, 2=Asst. Director, 3=Supervisor. If a logged on user isn't in the Supervisor table, that user is a worker. If that logged on user is in the table, I'll know her level and be able to pull up a list of "children" employees (in a heirarchical relationship).

Conceptually, it should work. I just haven't figured out how to:
1) check logon ID against the Supervisor table (to capture supervisory level),
2) check logon ID against Employee table (to get their supervisor's ID),
3) build a dynamic query to capture the emloyee data I need (based on data from 1 and 2 above).

Does anyone know of any examples of this that I could look at?

tiaA,
--
JMorrell


"Jackie L" wrote:

I think before you dig too deeply into the security issue why not try something like this. If each employee has a supervisor id in their record, then restrict the query running the report based on the supervisor login. Each employee record could have a level indicated also so that if the current user is not a supervisor, the data would be for that login id only.

Might be worth a try.


"Joseph Meehan" wrote:

JMorrell wrote:
In my employee database, supervisors will need the ability to run
reports on their employees. This reporting can begin at any level of
the organizational hierarchy. The Director will need to run reports
on any of her supervisors and their employees; her supervisors will
need to run reports on their employees; employees will need to run
reports on themselves. Each employee record has a field which gives
that person's supervisor number.

I have the reports working for supervisors, but I haven't figured out
how to limit the supervisors report to only their employees. As it
is right now, any db user can run a report on anyone. (This db has
not been released and is still in the development stages.) Data and
tables are on a SQL Server and users have the front end forms,
reports, etc. We're using SQL logon authentication for access to the
tables.

Where can I find information about how to limit a user's access to
only their employee's records? I know the answer lies in a select
statement, but how do I identify the current logged on user?

tia,

I suggest that first, if you have not already, you need to study up on
user level security and split databases.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can lock
everyone including God out.

Practice on some copies to make sure you know what you are doing.

Next I suggest that you set the criteria in a query or report to limit
the results to the employees they should include. Of course you need a
table or other source for the query to know which employees. If the
employees never change or can be identified by some characteristic like
department you can hard code that if you like.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math