Security vs. Efficiency

[John Vinson]

Well, here's a freqent answerer with a question of my own.

I have a database with a complex form, with a Tab control with
subforms on three of the tab pages. These subforms are only rarely
needed, but of course their existance causes slowdowns when the form
is opened or when I move from record to record, since (even if nobody
is looking at them) they must be requeried.

Ok, I learned a trick here which works pretty well: set the Subform's
SourceObject to blank, and only set it to the form name in the tab
control's Change event. Great; speeds opening the form noticably.

BUT... when I try it as a user with restricted permissions, I get the
error that I cannot change the properties of the Form because I don't
have sufficient privileges! Only by giving all users Design permission
on the form am I able to get this to work; and neither I nor my client
want to do that.

Any good way to get around this problem? Or should we just pay the
penalty in performance?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Albert D. Kallal]

I take it this is not a mde file?

You can certainly change the sub-form control source object at runtime, and
since one (might) is using a mde..then form design time permissions should
not matter..should it? (as a note, you *can* change the source object in a
mde..and it works fine..I do this all the time).

Thus, as Spock would say...logic dictates that you must be using a mdb here!

I did just in case wanted to be sure you realize that source object can be
changed with a mde. However, I had no idea this required design time
permissions....

 

[Eric Schittlipz]

Well, here's a freqent answerer with a question of my own.

I have a database with a complex form, with a Tab control with
subforms on three of the tab pages. These subforms are only rarely
needed, but of course their existance causes slowdowns when the form
is opened or when I move from record to record, since (even if nobody
is looking at them) they must be requeried.

Ok, I learned a trick here which works pretty well: set the Subform's
SourceObject to blank, and only set it to the form name in the tab
control's Change event. Great; speeds opening the form noticably.

BUT... when I try it as a user with restricted permissions, I get the
error that I cannot change the properties of the Form because I don't
have sufficient privileges! Only by giving all users Design permission
on the form am I able to get this to work; and neither I nor my client
want to do that.

Any good way to get around this problem? Or should we just pay the
penalty in performance?

John W. Vinson[MVP]

I also encountered this issue and have just been looking through a database
where I did get this to work. From a brief look through the project,
standard users don't have any permissions to change any forms design, but I
needed to allocate read design permissions on the forms which were used as
subforms. The code is written for the OnChange event of the main tab and is
as follows:

Select Case tabMain

Case 1
If sbfSub1.SourceObject <> "frmSub1" Then
sbfSub1.SourceObject = "frmSub1"
End If

....etc

Perhaps another thing to note is that link master and child fields remain
set, the whole time, even if the controlsource of the subform control is
blank. I think that's all I needed to get it to work, but it was a while
ago and there could be something else I needed to do - but I don't think so.
Anyway, I have just checked and it certainly does work without any user
needing any modify design privileges. So if you can't get it to work, I
could double-check this for you.

 

[Dirk Goldgar]

Ok, I learned a trick here which works pretty well: set the Subform's
SourceObject to blank, and only set it to the form name in the tab
control's Change event. Great; speeds opening the form noticably.

BUT... when I try it as a user with restricted permissions, I get the
error that I cannot change the properties of the Form because I don't
have sufficient privileges! Only by giving all users Design permission
on the form am I able to get this to work; and neither I nor my client
want to do that.

Any good way to get around this problem? Or should we just pay the
penalty in performance?

I do this in one of my apps, and my answer was to give users Read Design
permission on the main form and the forms that will be used as subforms.
This application was delivered as an MDB, not an MDE, so I don't know if
using an MDE would make this unnecessary.

 

[John Vinson]

I do this in one of my apps, and my answer was to give users Read Design
permission on the main form and the forms that will be used as subforms.
This application was delivered as an MDB, not an MDE, so I don't know if
using an MDE would make this unnecessary.

Thanks all. I'll try it on the .mde (which I am indeed using in
production). Design mode on three subforms might be just safe
enough...

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Nick Coe \(UK\)]

John,

How much if any of the security props can be altered in
code? I just wondered if it's possible to change security
permissions to allow changes by code in design mode then
reset and turn permissions off when done.

--
Nick Coe (UK)
AccHelp v1.01 Application Help File Builder
http://www.alphacos.co.uk/
Download Free Demo Copy

 

[Jeff Conrad]

Hi John,

Sorry I'm late for the party, I was stuck in traffic.

I have a database with a complex form, with a Tab control with
subforms on three of the tab pages. These subforms are only rarely
needed, but of course their existence causes slowdowns when the form
is opened or when I move from record to record, since (even if nobody
is looking at them) they must be requeried.

Ok, I learned a trick here which works pretty well: set the Subform's
SourceObject to blank, and only set it to the form name in the tab
control's Change event. Great; speeds opening the form noticeably.

BUT... when I try it as a user with restricted permissions, I get the
error that I cannot change the properties of the Form because I don't
have sufficient privileges! Only by giving all users Design permission
on the form am I able to get this to work; and neither I nor my client
want to do that.

Any good way to get around this problem? Or should we just pay the
penalty in performance?

Is it too late to offer another option?

I have done something *similar* for a client application. Although it did not involve subforms, it
did involve temporarily giving someone permissions they would not otherwise have, given their
current security clearance level.

I am the "Super User" of the development MDW file, but I am not in the distributed MDW file to add
additional security. The distributed MDW file only has one person who is a member of the Admins
group. Through careful coding procedures the only thing this person can do besides opening the
database is "Diddly Squat." None of the regular users of the program have Admin rights which
presented a problem when I wanted to do these functions:
1. Create a user
2. Change Passwords
3. Delete a user
4. Assign/change user's security levels
5. Change the startup form

As you can see, only a member of the Admins group can do these functions.

My workaround was to create a temporary workspace using my Logon specs 'wrapped' around whatever
*Admin* code I needed to perform. Something like so:

Dim wrk As DAO.Workspace
Set wrk = DBEngine.CreateWorkspace("", "MyUserName, "MyPassword", dbUseJet)
'
' Yadda, yadda whatever code here
'
Set wrk = Nothing

By doing it this way I do not have to 'give the keys to the vault' to anyone. The users are
temporarily authorized to do some function in the blink of an eye and then everything goes back to
normal. This has worked quite well for a long time and not a single problem has surfaced. And since
I distribute MDE files, the code (and my user information) is protected.

I have not done any speed testing with your exact parameters, but it would seem to me that any
performance hit would be negligible. Just wrap your Tab Control's Change event code inside a
temporary workspace for a user with Design permissions on the form(s) in question. Looks to me like
you could have your cake and eat it too!

 

[John Vinson]

How much if any of the security props can be altered in
code? I just wondered if it's possible to change security
permissions to allow changes by code in design mode then
reset and turn permissions off when done.

Perhaps... though I would guess that I'd need to give the users
Security Administration permission, which would sort of defeat the
whole purpose of security!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

My workaround was to create a temporary workspace using my Logon specs 'wrapped' around whatever
*Admin* code I needed to perform. Something like so:

Dim wrk As DAO.Workspace
Set wrk = DBEngine.CreateWorkspace("", "MyUserName, "MyPassword", dbUseJet)
'
' Yadda, yadda whatever code here
'
Set wrk = Nothing

By doing it this way I do not have to 'give the keys to the vault' to anyone. The users are
temporarily authorized to do some function in the blink of an eye and then everything goes back to
normal. This has worked quite well for a long time and not a single problem has surfaced. And since
I distribute MDE files, the code (and my user information) is protected.

VERY very clever. I didn't realize that a Workspace had that
capability.

After I get about 700MByte of snarky old DOS data migrated into Access
this weekend I'll take a look at it! Thanks Jeff!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Jeff Conrad]

VERY very clever.

Ohhh thanks!

I didn't realize that a Workspace had that capability.

Yep. Quite honestly I would be surprised if it does not work in this situation.

After I get about 700MByte of snarky old DOS data migrated into Access
this weekend I'll take a look at it! Thanks Jeff!

You're more than welcome John.
Would you like your pebble back?

I'm kidding, just kidding!!!
<g, d, & r>