Need help with security

[erick-flores]

Hello all

I need to secure my database. This is the first time I am doing this,
so I have no clues oh how to do this. Basically, I have a Expense
Report Interface, I want only the manager to be able to look at all
employees and access to all the reports. On the other hand the
employees only access to THEIR OWN employee ID and no access to some of
the reports and of course no access to others employee's
information....

I've been doing some reading but I am very confused...Can somebody tell
me where to start of where can I find a GOOD guide so I can follow and
setup all that security for my database.

Thanks in advance

 

[erick-flores]

I am using Access 2003

I think I changed some things...and now all the time I open the
freaking Access is asking for the Logon..plase help I am going crazy!

 

[erick-flores]

I am using Access 2003

I think I changed some things...and now all the time I open the
freaking Access is asking for the Logon..plase help I am going crazy!

Ok, Got that Logon problem solved...

 

[Joan Wild]

You can implement user-level security and accomplish what you want. You
should take the time to study up on it, and not just run the wizard.
Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

I've also outlined the detailed steps at
www.jmwild.com/AccessSecurity.htm

You would need to add a field to your tables (to hold the Access uesrname)
to indicate who 'owned' the record. You can then use queries with criteria
on this field of CurrentUser(). This will restrict them to just their
records. For the manager, you can set the recordsources of forms and
reports to show all records.

You can hide access to some/all reports based on who the currentuser is or
based on their membership in some group.

You'll find examples in the security FAQ.

 

[erick-flores]

Thanks for your answer, I am going to take some time now reading those
files

 

[erick-flores]

Hello

I have been reading those files...So if I am right, I will first create
the new workgroup and set all their preferences I can understand all
that. BUT what about the CurrentUser() option, I guess thats a lil more
complicated. I was trying to follow the secfaq.doc but I cant see a
clear explanation to it.

I am still kind a confused, here is what I think I need to do ( can you
please tell me if I am in the right direction):
1. I already have my database with all the forms and reports
2. Create a new workgroup to secure my database
3. Now I supposed to find a way to: whenever a user login he can only
see his records, here is where I am lost, right now my I have an
"Employees" table that has a field name Employee ID...you said
something about creating adding a new field to a table, can I use this
Employee ID as a reference for the users to only see their records?

I guess this is more complicated than I though...Can you please help me
here? I am going to still doing some readings to see if I can find more
help

Thank you

 

[erick-flores]

Hello, I am having a lil problem here

I created a new workgroup, add a new user, add this user to Admins
group...remove the Admin user from the Admins group. Open my unsecure
database, logon with my new user and its saying: "You dont have the
necessary permissions to use the 'C:\...' object. Have your system
admnistrator or the person who created this object establish the
appropriate permissions for you" I follow step-by-step and is giving
this problem all the time. I tryed redoing again everything and got the
same problem...I dont know whats the problem if I am following each
step... I am going crazy here....any ideas of whats happening???

 

[Joan Wild]

Hello

I have been reading those files...So if I am right, I will first
create the new workgroup and set all their preferences I can
understand all that. BUT what about the CurrentUser() option, I guess
thats a lil more complicated. I was trying to follow the secfaq.doc
but I cant see a clear explanation to it.

The CurrentUser() function returns the Access username of the person
currently logged in to the session of Access.

I am still kind a confused, here is what I think I need to do ( can
you please tell me if I am in the right direction):
1. I already have my database with all the forms and reports
2. Create a new workgroup to secure my database

Yes (you are using Access 2003, so the wizard does a decent job of securing
including creating a new workgroup).

3. Now I supposed to find a way to: whenever a user login he can only
see his records, here is where I am lost, right now my I have an
"Employees" table that has a field name Employee ID...you said
something about creating adding a new field to a table, can I use this
Employee ID as a reference for the users to only see their records?

If the Employee ID values are the same as their Access username then yes,
but somehow I doubt that the Employee ID is a text value. So add a field to
your table to hold the Access username.

I assume you have other tables related to the Employees table, and these
other tables are joined based on the Employee ID? If so you can ensure that
all your record sources for forms/reports are based on queries that include
the field that contains the Access username from the Employees table. You
can set a criteria in these queries under the Access username of
=CurrentUser and the query will only return records where the Access
username is that of the person currently logged in.

I

 

[Joan Wild]

Hello, I am having a lil problem here

I created a new workgroup, add a new user, add this user to Admins
group...remove the Admin user from the Admins group.

I assume that you are now joined by default to this new workgroup?
Double-check this in Tools, Security, Workgroup Administrator. Then close
Access.

Open my unsecure

database, logon with my new user and its saying: "You dont have the
necessary permissions to use the 'C:\...' object. Have your system
admnistrator or the person who created this object establish the
appropriate permissions for you" I follow step-by-step and is giving
this problem all the time. I tryed redoing again everything and got
the same problem...I dont know whats the problem if I am following
each step... I am going crazy here....any ideas of whats happening???

It is essential that you follow each step, every phrase exactly. Skipping
one thing can throw things off.

Since you are using 2003, you could try starting from scratch and using the
Security Wizard.

 

[erick-flores]

I assume you have other tables related to the Employees table, and these

other tables are joined based on the Employee ID?

I can perfectly understand what you are saying, and I think thats what
I need. But I am confused of how to implement all that. I do have more
tables linked based on the Employee ID.

If so you can ensure that
all your record sources for forms/reports are based on queries that include
the field that contains the Access username from the Employees table.

Ok, I add a field for all my tables "Usernamehold". How do I based on
queries that include the field that contains the access username from
the employees table? can you guide me in more details of how to do
this, please...How do I do the query? where do I put the code of the
query?

You

can set a criteria in these queries under the Access username of
=CurrentUser and the query will only return records where the Access
username is that of the person currently logged in.

I do understand this part

 

[erick-flores]

Right now I have all my forms/reports based on tables. Do I need to
redo all my forms/reports with the new queries, or can I just add a
query, maybe using some VBA, and everything will work fine?

 

[Joan Wild]

You said you added a field to each table to hold the username of the Access
user who 'owns' the record.

Open a form in design view. In the RecordSource property is currently a
table name. Click on the build button to the right of that property.

This will open the Query designer. Add all the fields from the table and
under the Usernamehold field in the criteria row put
=CurrentUser(). Close the window and respond with Yes. This will put a SQL
statement in the recordsource in place of your table.

Note that you'll need to populate existing records with a username in the
Usernamehold field.

In addition, you'll need to set a default for the textbox that holds the
Usernamehold field on the form to =CurrentUser(). This will automatically
add the username when a new record is created. You might want to change the
locked property of this control to yes, so that users don't edit it.

 

[erick-flores]

I found a way to do it, if you can please see my other thread and help
me please...here is the link:
http://groups.google.com/group/micr...52380/9528dbffcde9618d?hl=en#9528dbffcde9618d

Thank you

 

[erick-flores]

Thank you very much, thats what I have done. But now I have this lil
problem. I want the Admin group to see ALL THE RECORDS. They, right
now, can only see the records that they have created. But obviously I
want then to see ALL THE RECORDS...they are the ones that will be
looking at the reports and all that fun stuff

Do you know how to do this?

 

[Joan Wild]

Answered in the other thread where you asked this.