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
"frmNewRecord" 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 "frmNewRecord" 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_by".
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

Re: Access Security without using the Security Wizard

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_BeforeUpdate 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_BeforeUpdate 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_BeforeUpdate:
' 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_StampRecord:
Exit Function

Err_StampRecord:
Call LogError(Err.Number, Err.Description, conMod & "StampRecord()",
"Form = " & strForm)
Resume Exit_StampRecord
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!Inactive.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.

"thebarefootnation" wrote in message
news:1193288712.067842.271810@e9g2000prf.googlegroups.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
> "frmNewRecord" 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 "frmNewRecord" 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_by".
> 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
>

Re: Access Security without using the Security Wizard

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_DATE

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" wrote:
> 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_BeforeUpdate 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_BeforeUpdate 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_BeforeUpdate:
> ' 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_StampRecord:
> Exit Function
>
> Err_StampRecord:
> Call LogError(Err.Number, Err.Description, conMod & "StampRecord()",
> "Form = " & strForm)
> Resume Exit_StampRecord
> 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!Inactive.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.
>
> "thebarefootnation" wrote in message
>
> news:1193288712.067842.271810@e9g2000prf.googlegroups.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
> > "frmNewRecord" 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 "frmNewRecord" 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_by".
> > 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 -

Re: Access Security without using the Security Wizard

"thebarefootnation" 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.

Re: Access Security without using the Security Wizard

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 "frmNewRecord".

Thanks in advance




On Oct 26, 5:01 am, "Larry Linson" wrote:
> "thebarefootnation" 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.

Re: Access Security without using the Security Wizard

thebarefootnation wrote in
news:1193355001.644803.226130@e9g2000prf.googlegroups.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 "frmNewRecord".
>
> 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

Re: Access Security without using the Security Wizard

"thebarefootnation" wrote

> 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
> "frmNewRecord".

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

Re: Access Security without using the Security Wizard

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.

"thebarefootnation" wrote in message
news:1193355001.644803.226130@e9g2000prf.googlegroups.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 "frmNewRecord".
>
> Thanks in advance
>
>
>
>
> On Oct 26, 5:01 am, "Larry Linson" wrote:
>> "thebarefootnation" 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.

Re: Access Security without using the Security Wizard

On Oct 27, 7:22 pm, "Allen Browne" wrote:
> 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.
>
> "thebarefootnation" wrote in message
>
> news:1193355001.644803.226130@e9g2000prf.googlegroups.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 "frmNewRecord".
>
> > Thanks in advance
>
> > On Oct 26, 5:01 am, "Larry Linson" wrote:
> >> "thebarefootnation" 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.- Hide quoted text -
>
> - Show quoted text -

Thanks that's worked only problem is.

In my frmlogin I reference the combo box (UserID) to a table
(tblUserList) which has a primary key.

In order to bring in the name in the combo box I use the following
reference

SELECT tblUserList.ID, tblUserList.UserID FROM tblUserList ORDER BY
tblUserList.ID;

Then in the column count I select column 2, hence to only bring the
name through.

Any idea on how to do the same in the code bringing that name into the
CREATED_BY text box?

Re: Access Security without using the Security Wizard

On Nov 1, 2:12 am, thebarefootnation
wrote:
> On Oct 27, 7:22 pm, "Allen Browne" wrote:
>
>
>
> > 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.
>
> > "thebarefootnation" wrote in message
>
> >news:1193355001.644803.226130@e9g2000prf.googlegroups.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 "frmNewRecord".
>
> > > Thanks in advance
>
> > > On Oct 26, 5:01 am, "Larry Linson" wrote:
> > >> "thebarefootnation" 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.- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks that's worked only problem is.
>
> In my frmlogin I reference the combo box (UserID) to a table
> (tblUserList) which has a primary key.
>
> In order to bring in the name in the combo box I use the following
> reference
>
> SELECT tblUserList.ID, tblUserList.UserID FROM tblUserList ORDER BY
> tblUserList.ID;
>
> Then in the column count I select column 2, hence to only bring the
> name through.
>
> Any idea on how to do the same in the code bringing that name into the
> CREATED_BY text box?

If I understand you, and I probably don't, assuming frmLogin has a
module, it would be simple to create a
Public Function fCreated_By$()
On Error Resume Next
fCreatedBy=Form_frmLogin.UserID.Column(1)
End Function
When the user was done logging in, frmLogin could be hidden, and you
could amend your code to close the database on any vital activity that
tried to proceed without its existence.
So when another form was opened, the form load event code could enter
the UserName into the CREATED_BY textbox, or its default value could
be set to fCreated_By(). Probably you'd want to store the CREATED_BY
ID in the CREATED_BY field and establish a relationship with
referential integrity.
Of course, this wouldn't secure your DB diddley-squat. The rankest
amateur could create a new db, link to your tables and do whatever to
them. And the next to rankest amateur could bypass any of your opening
Forms or AllowByPass Keys properties. And one step up from there, most
reasonably perceptive users could use a hex-editor to read or modify
any string data in the db.
And the person who claimed he/she had created "security" for the db
would be responsible.
If I had to create my own security for an Access/Jet db I might have a
shot, if I took a month or so. That's because I didn't have to ask
questions about how to get the CREATED_BY textbox populated. But with
that knowledge comes the realization that regardless of how blindingly
brilliant I am I am not likely to beat the determined hacker, and I
might even overlook some simple stratagem that would let my Aunt Alice
in; I am not likely to exceed the blinding brilliance of Microsoft's
Access security creators.
If I really wanted to accomplish what I think you want to accomplish I
would convert my tables to SQL-Server Express which has some functions
that may be helpful to that purpose, and which can control the
permissions of each user to very small doses, and which keeps a log
file of every time you say shift in your chair while accessing it
data.
BTW, Access security, the made in Redmond kind, is so safe that it
would take a determined Googler at least five minutes to find whatever
was needed to bypass it, maybe six with a dial-up connection.