473,836 Members | 1,449 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access Security without using the Security Wizard

Hi,

I have created an access db that I would like to secure.

The database will exist on a shared drive and be used at a number of
different locations hence the reason to secure the database.

The users of the database also use other databases therefore I want to
secure this database without using the wizard so it does not effect
their other databases.

So far I have created a "frmLogin" used for logging into the database
which gives the user access to two forms
"frmNewReco rd" and "frmSearch"
>From the "frmSearch" the user can access "frmEditRecord" .
What I would like to do now is:
1. Track when a user creates a new record. I have created text box
within the "frmNewReco rd" named "Created_by " how can I have this
populated automatically by the user who created it?

2. I'd also like to track when a user edits an existing record within
"frmEditRecord" . I have created a text box named "Last_Updated_b y".
How can I have this text box automatically updated by the user who
last edited this record?

3. I would like to prevent the user's from accessing the design of the
forms, tables and queries.

Plus I'd like to restrict them accessing the tables & queries which
support these forms. Plus the sub forms within these forms.

I already have set the db up so when it opens the "frmLogin" opens up.
However if they hold the shift key down they can bypass straight to
tables etc behind.

What is the best way to do this?

Can I split the database?

Thank you very much in advance.

Jess

Oct 25 '07 #1
7 2639
See help on the AllowBypassKey property.

To track who created a record when, and who updated it last and when, add 4
fields to each table. Then assign the appropriate values to them in
Form_BeforeUpda te in each form.

The code below illustrates how to do that, assuming fields named:
EnteredOn Date/Time
EnteredBy Text
UpdatedOn Date/Time
UpdatedBy Text
and optionally if you want to record who marked a record inactive an when:
InactiveOn Date/Time
InactiveBy Text

You call it in Form_BeforeUpda te with:
Call SampRecord(Me)

It records the name the user signed into Windows with, calling this
function:
http://www.mvps.org/access/api/api0008.htm

Replace the error logger with your own, or use this one:
http://allenbrowne.com/ser-23a.html

Create an MDE to prevent users creating/modifying forms/report/code.

You can still split the database.

Public Function StampRecord(frm As Form) As Boolean
On Error GoTo Err_StampRecord
'Purpose: Stamp the user and date/time into the record.
'Return: True if successful.
'Argument: frm = the bound form to be stamped.
'Assumes: Fields named EnteredOn, EnteredBy, UpdatedOn, and UpdatedBy.
' If there is an Inactive field, assumes InactiveOn and
InactiveBy.
'Usage: In Form_BeforeUpda te:
' Call StampRecord(Me, True)
Dim strForm As String
Dim strUser As String

strForm = frm.Name 'For error handler.
strUser = fOSUserName()

If frm.NewRecord Then
frm!EnteredOn = Now()
frm!EnteredBy = strUser
Else
frm!UpdatedOn = Now()
frm!UpdatedBy = strUser
End If

If HasInactive(frm ) Then
With frm!Inactive
If .Value = .OldValue Then
'do nothing
Else
If .Value Then
frm!InactiveOn = Now()
frm!InactiveBy = strUser
Else
frm!InactiveOn = Null
frm!InactiveBy = Null
End If
End If
End With
End If

Exit_StampRecor d:
Exit Function

Err_StampRecord :
Call LogError(Err.Nu mber, Err.Description , conMod & "StampRecord()" ,
"Form = " & strForm)
Resume Exit_StampRecor d
End Function
Private Function HasInactive(frm As Form) As Boolean
'Purpose: Return True if the form's Record Source includes a yes/no
field named Inactive.
Dim iType As Integer
On Error Resume Next
iType = frm.Recordset!I nactive.Type
If (iType = dbBoolean) Or (iType = dbInteger) Or (iType = dbLong) Then
HasInactive = True
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"thebarefootnat ion" <th************ ***@googlemail. comwrote in message
news:11******** **************@ e9g2000prf.goog legroups.com...
Hi,

I have created an access db that I would like to secure.

The database will exist on a shared drive and be used at a number of
different locations hence the reason to secure the database.

The users of the database also use other databases therefore I want to
secure this database without using the wizard so it does not effect
their other databases.

So far I have created a "frmLogin" used for logging into the database
which gives the user access to two forms
"frmNewReco rd" and "frmSearch"
>>From the "frmSearch" the user can access "frmEditRecord" .

What I would like to do now is:
1. Track when a user creates a new record. I have created text box
within the "frmNewReco rd" named "Created_by " how can I have this
populated automatically by the user who created it?

2. I'd also like to track when a user edits an existing record within
"frmEditRecord" . I have created a text box named "Last_Updated_b y".
How can I have this text box automatically updated by the user who
last edited this record?

3. I would like to prevent the user's from accessing the design of the
forms, tables and queries.

Plus I'd like to restrict them accessing the tables & queries which
support these forms. Plus the sub forms within these forms.

I already have set the db up so when it opens the "frmLogin" opens up.
However if they hold the shift key down they can bypass straight to
tables etc behind.

What is the best way to do this?

Can I split the database?

Thank you very much in advance.

Jess
Oct 25 '07 #2

I don't need to track the recording of active or inactive.

I already have the extra columns in my tblMain where the data will be
stored. They are named:
CREATED_BY
CREATED_ON_DATE
LAST_UPDATED_BY
LAST_UPDATED_DA TE

I don't fully understand this part of the
strForm = frm.Name 'For error handler.
strUser = fOSUserName()
Is the frm.Name = the name of the login form?

Is strUser = to the module code reference to above?

Thanks
Jess

On Oct 25, 3:30 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
See help on the AllowBypassKey property.

To track who created a record when, and who updated it last and when, add 4
fields to each table. Then assign the appropriate values to them in
Form_BeforeUpda te in each form.

The code below illustrates how to do that, assuming fields named:
EnteredOn Date/Time
EnteredBy Text
UpdatedOn Date/Time
UpdatedBy Text
and optionally if you want to record who marked a record inactive an when:
InactiveOn Date/Time
InactiveBy Text

You call it in Form_BeforeUpda te with:
Call SampRecord(Me)

It records the name the user signed into Windows with, calling this
function:
http://www.mvps.org/access/api/api0008.htm

Replace the error logger with your own, or use this one:
http://allenbrowne.com/ser-23a.html

Create an MDE to prevent users creating/modifying forms/report/code.

You can still split the database.

Public Function StampRecord(frm As Form) As Boolean
On Error GoTo Err_StampRecord
'Purpose: Stamp the user and date/time into the record.
'Return: True if successful.
'Argument: frm = the bound form to be stamped.
'Assumes: Fields named EnteredOn, EnteredBy, UpdatedOn, and UpdatedBy.
' If there is an Inactive field, assumes InactiveOn and
InactiveBy.
'Usage: In Form_BeforeUpda te:
' Call StampRecord(Me, True)
Dim strForm As String
Dim strUser As String

strForm = frm.Name 'For error handler.
strUser = fOSUserName()

If frm.NewRecord Then
frm!EnteredOn = Now()
frm!EnteredBy = strUser
Else
frm!UpdatedOn = Now()
frm!UpdatedBy = strUser
End If

If HasInactive(frm ) Then
With frm!Inactive
If .Value = .OldValue Then
'do nothing
Else
If .Value Then
frm!InactiveOn = Now()
frm!InactiveBy = strUser
Else
frm!InactiveOn = Null
frm!InactiveBy = Null
End If
End If
End With
End If

Exit_StampRecor d:
Exit Function

Err_StampRecord :
Call LogError(Err.Nu mber, Err.Description , conMod & "StampRecord()" ,
"Form = " & strForm)
Resume Exit_StampRecor d
End Function
Private Function HasInactive(frm As Form) As Boolean
'Purpose: Return True if the form's Record Source includes a yes/no
field named Inactive.
Dim iType As Integer
On Error Resume Next
iType = frm.Recordset!I nactive.Type
If (iType = dbBoolean) Or (iType = dbInteger) Or (iType = dbLong) Then
HasInactive = True
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"thebarefootnat ion" <thebarefootnat ...@googlemail. comwrote in message

news:11******** **************@ e9g2000prf.goog legroups.com...
Hi,
I have created an access db that I would like to secure.
The database will exist on a shared drive and be used at a number of
different locations hence the reason to secure the database.
The users of the database also use other databases therefore I want to
secure this database without using the wizard so it does not effect
their other databases.
So far I have created a "frmLogin" used for logging into the database
which gives the user access to two forms
"frmNewReco rd" and "frmSearch"
>From the "frmSearch" the user can access "frmEditRecord" .
What I would like to do now is:
1. Track when a user creates a new record. I have created text box
within the "frmNewReco rd" named "Created_by " how can I have this
populated automatically by the user who created it?
2. I'd also like to track when a user edits an existing record within
"frmEditRecord" . I have created a text box named "Last_Updated_b y".
How can I have this text box automatically updated by the user who
last edited this record?
3. I would like to prevent the user's from accessing the design of the
forms, tables and queries.
Plus I'd like to restrict them accessing the tables & queries which
support these forms. Plus the sub forms within these forms.
I already have set the db up so when it opens the "frmLogin" opens up.
However if they hold the shift key down they can bypass straight to
tables etc behind.
What is the best way to do this?
Can I split the database?
Thank you very much in advance.
Jess- Hide quoted text -

- Show quoted text -

Oct 25 '07 #3
"thebarefootnat ion" wrote
I don't fully understand this part of the
> strForm = frm.Name 'For error handler.
strUser = fOSUserName()

Is the frm.Name = the name of the login form?
It would be the name of the form passed to the function as the argument
"frm" in

Public Function StampRecord(frm As Form) As Boolean
Is strUser = to the module code reference to above?
fOSUserName is the name of a function, not the name of a module. Access
functions return a value... that particular one returns the name of the
Windows login of the current user, IIRC. Access sub procedures execute code
and can pass data in their arguments but don't return a value just by
specifying the name of the sub, as do functions.


Oct 25 '07 #4
As I have created a login form to get into the database I'd prefer to
bring back the User Name from this form, not from the Windows login.

My login form is called "frmLogin" and the User reference text box is
"UserID".

Can anyone help me with the code to pull the UserID from "frmLogin"
into the textbox "CREATED_BY " in the form "frmNewReco rd".

Thanks in advance


On Oct 26, 5:01 am, "Larry Linson" <boun...@localh ost.notwrote:
"thebarefootnat ion" wrote
I don't fully understand this part of the
> strForm = frm.Name 'For error handler.
> strUser = fOSUserName()
>
Is the frm.Name = the name of the login form?

It would be the name of the form passed to the function as the argument
"frm" in

Public Function StampRecord(frm As Form) As Boolean
Is strUser = to the module code reference to above?

fOSUserName is the name of a function, not the name of a module. Access
functions return a value... that particular one returns the name of the
Windows login of the current user, IIRC. Access sub procedures execute code
and can pass data in their arguments but don't return a value just by
specifying the name of the sub, as do functions.

Oct 25 '07 #5
thebarefootnati on <th************ ***@googlemail. comwrote in
news:11******** **************@ e9g2000prf.goog legroups.com:
As I have created a login form to get into the database I'd prefer to
bring back the User Name from this form, not from the Windows login.

My login form is called "frmLogin" and the User reference text box is
"UserID".

Can anyone help me with the code to pull the UserID from "frmLogin"
into the textbox "CREATED_BY " in the form "frmNewReco rd".

Thanks in advance
I'll tell you that if you'll tell me how to adjust the fuel injectors on my
Toyota VTI engine. And, oh yeah, I'll need to know how to open the car hood
too?
I asked my wimpy twin, Lyle, but he just said, "Since by asking the
question you've established that you are not an accomplished automotive
engineer, and since Toyota probably knows what it's doing, and since you
paid quite a lot for this Toyota expertise, you should just use the car in
the way Toyota intended."
So, I'm pretty well stuck.

kyle
Oct 26 '07 #6
"thebarefootnat ion" <th************ ***@googlemail. comwrote
As I have created a login form to get into the database
I'd prefer to bring back the User Name from this form,
not from the Windows login.

My login form is called "frmLogin" and the User
reference text box is "UserID".

Can anyone help me with the code to pull the UserID from
"frmLogin" into the textbox "CREATED_BY " in the form
"frmNewReco rd".
Heh, heh... That kyle... he's always giving his brother Lyle a hard time.

If you want some guidance on Roll-Your-Own (RYO) security, you first have to
agree to read and understand the following: (1) The Access Security Wizard
is about as close to worthless as any feature of Access could be, because it
often misleads people into thinking that their databases are secure. (2)
Access own security, described in detail in the "Access Security FAQ" of 39
pages that you can download from Microsoft is also fallible (but, if you
carefully read, study, and follow the steps, it will be as secure as you are
going to make an Access database). Don't count on it if your application or
data is worth more than about US$140-150 because that is what it will cost
to obtain code to crack it. (3) This is the key item: Any RYO security you
create will be significantly less secure than either of these, crackable
without expense, without third-party tools, by Access developers with only
modest talent. If you have honest users who don't want to "get at"
information in your DB that they shouldn't, it can serve, at best, to keep
them from accidentally stumbling into objects where they have no need to be.

That said, presumably you are going to check the userid entered from your
form against a table of valid userids, and also the user's password? If
not, you'd better rethink the whole idea. Then you have to store, or save,
the current user's id somewhere where you can retrieve it. (1) One
convenient place would be a public variable in a standard module, but those
may be (at least usually are) lost if you have an unhandled error... thus,
if you choose this approach, you also need to shut down the application
immediately if the variable in which you have saved your userid turns up
empty or null. (2) Another way is to create a property of the database and
save it to and use it from there. (3) Perhaps even easier and somewhat
safer is to store it in a field on a form that you have hidden one way or
another.

Whichever way you choose, just remember that most any plain ol' everyday
Access developer with a medium level of knowledge, will be able to break
your security scheme in hardly any time at all.

And, by the time you get through with all the implementation and levels of
obfuscation that you (wrongly) convince yourself will actually make it
secure, you could have downloaded the Access Security FAQ, studied,
re-studied, and learned it, and applied it... so at least it might cost a
database thief or a data thief $150 to steal your stuff.

If you really need to protect your data, put it in a server DB and apply the
server DB's security. If you want to protect your database application, be
aware that, an experienced Access developer can watch it run, and re-create
it faster than you did in the first place, because you've already done all
the "heavy lifting" of determining and laying out for them what information
needs to be dealt with, where, and by whom, to solve the business problem.

Larry Linson
Microsoft Access MVP

Oct 26 '07 #7
If you still have frmLogin open, and it has the correct name in text box
Text0, then in the BeforeUpdate event of frmNewRecord you would use:
Me.CREATED_BY = Forms!frmLogin! Text0

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"thebarefootnat ion" <th************ ***@googlemail. comwrote in message
news:11******** **************@ e9g2000prf.goog legroups.com...
As I have created a login form to get into the database I'd prefer to
bring back the User Name from this form, not from the Windows login.

My login form is called "frmLogin" and the User reference text box is
"UserID".

Can anyone help me with the code to pull the UserID from "frmLogin"
into the textbox "CREATED_BY " in the form "frmNewReco rd".

Thanks in advance


On Oct 26, 5:01 am, "Larry Linson" <boun...@localh ost.notwrote:
>"thebarefootna tion" wrote
> I don't fully understand this part of the
> strForm = frm.Name 'For error handler.
> strUser = fOSUserName()
>
Is the frm.Name = the name of the login form?

It would be the name of the form passed to the function as the argument
"frm" in

Public Function StampRecord(frm As Form) As Boolean
> Is strUser = to the module code reference to above?

fOSUserName is the name of a function, not the name of a module. Access
functions return a value... that particular one returns the name of the
Windows login of the current user, IIRC. Access sub procedures execute
code
and can pass data in their arguments but don't return a value just by
specifying the name of the sub, as do functions.
Oct 27 '07 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
4654
by: John | last post by:
Hi, I've recently upgraded from Access 97 to Access 2002 & the performance basically stinks. I have tried the following items listed below but it has only had a minor impact: 1) Upgraded Jet 4 to Service Pack 7 2) Set Subdatasheet Name to from 3) Shortened the file name of the .MDB file 4) Made sure the Track name AutoCorrect info is off
3
1701
by: icb | last post by:
Hi I have a front-end/back-end setup. The front-end utilises all unbound forms populating the back-end via the code I have written. All fine so far. Prior to splitting the database I ran the security wizard and used a shortcut from the desktop. All fine except of course I can open the back-end directly. The security FAQ appears to be out of date insofar as there is no wrkgadm.exe with 2002 and therefore you simply run the security...
2
1436
by: David Ingram | last post by:
Greetings all! I am aiming to set up simple permissions for users on a database here at work, to the effect that certain users should be restricted from opening certain forms and reports. I tried setting up groups and permissions, but found that users were still able to open forms I had thought would have been restricted. I then tried the security wizard, and found that TOO restrictive, especially if I find another job and am not...
3
2149
by: Mike Marriott | last post by:
Hello all, Newbie Here (please go easy, you cant make me fell more stupid than I allready do) I have a small, but complex data base, in Access 2000, that I am creating, I have allready starting to add data to it. Alot of data (around 30 hours work, creating and entering data). I did a stupid thing, I protected the thing. . . . Using the Security
4
14033
by: rcmail14872 | last post by:
I have MS SQL Server 2000 evaluation version on my desktop PC. I have the SQL Server client tools (only) on the laptop. The two computers are networked. I had an Access MDB database on my laptop, but I converted it with the Access upsizing wizard to an Access ADP database with the Access front-end on the laptop connecting to the SQL Server on the desktop. Now I want to move from this test environment to the "real" server. How do I go about...
3
1529
by: mar10 | last post by:
I'm working on a database for a local business that requires different security for different individuals. There is one PC at the front desk and several individuals will be working with the database throughout the day. I haven't worked with the Access Security wizard but have this question - will different individuals be able to sign on and off of the database on this PC without logging on to the PC itself? That is to say, will the...
24
1708
by: Bob Alston | last post by:
Could develop web pages that would interact with the access/jet database - like DAP but without the security flaw??? One of Access' competitors (Filemaker Pro as I recall) touts its EASY way to build web forms using its product. Sounds like they are trying to beat Access in a new venue - the web. Bob Alston bobalston9 AT yahoo DOT com
5
1762
by: summerwind | last post by:
I wonder if someone can help me please. I have developed apps for small business on a bespoke basis for a long time. I have usually gone along to the client to install the apps. My client base was such that they usually had Access pre-installed on their machines before I arrived. Over the last year I discovered a hole in a niche market and developed an off-the shelf app that cutomers can buy.
52
10013
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server 2005, and, since he already has licenses for Office Pro 2002, he wants to upgrade to that. I've been saying that we need to upgrade to Access 2003, not 2002, even if Office is kept at 2002. We are also looking to do a fair amount of...
0
10854
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10601
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9388
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7794
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5652
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4460
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4023
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.