Full Security question !? (1 Viewer)

[coda7777]

hello mates,

first thread here and i hope i haven't missed something even though i searched through forums .

i'm not expert for access but learning quickly and trying many things.
anyway i'm building access DB with user interface forms for my new work.
so far i'm doing okey trying many things but...

the File will be shared with 104 persons working in my department and i want to fully secure it ! means....
nobody see the VBA codes , nobody can edit designs , nobody can open the tables or queries except those i put on (forms, reports, linked buttons)

some people have basic knowledge of access , so hiding navigation , ribbons etc won't be totally safe

i want make it like an Application only user interface

1- What Steps should i do ? for full security !!!
2- what can i do if i upgrade the DB with more forms and i want to replace the 1 at work without losing any data , just like another version with more options
3- last question i've been trying to make button in a form to select specific record and delete it from DB if it matches the written in TextBox (tried macro and VB) kinda stuck.
eg. texbox = Name , the button search in EmployeeDB for that name and delete the whole record with all its data (i could get it by simple combo box but for user faults i want it written value to prevent mistakes)

 

[Uncle Gizmo]

>>>2- what can i do if i upgrade the DB with more forms and i want to replace the 1 at work without losing any data <<<<
Use the typical front and back-end arrangement. You will need to set it up like this anyway if you are sharing with 104 people.

Re:- >>>Select specific record and delete it from DB<<<<
It's usually a good idea not to delete data, a better option is to have a checkbox field in the same row of the item you would want to delete and Flag the checkbox to indicate the record is deleted. Use your queries to make sure it's no longer visible to the users. Provide a routine for the admin to permanently delete the records.

 

[coda7777]

2- yea i get the front end back end
but i don't want to lose data in "BE" when i replace it with new one

3- i'd prefer if i could the entire row but only from a user interface not from the table as i intend to lock the tables completely

 

[Uncle Gizmo]

A back-end should only ever have tables in it.

It is always a problem if you alter a live back end, really you should avoid getting into this position in the first place!

When I had to do this, I created some code which would compare the Old tables and a new tables, highlighting the differences and then I would manually adjust the tables to make them the same this way I didn't have any data loss.

The next improvement I plan for my system is that it will update the tables automatically.



Sent from my SM-G925F using Tapatalk

 

[coda7777]

been sometime but i wanna refresh the thread
after i finished here's what i done

i disabled the Shift bypass and made command button with password to enable or disable the module
i set the DB to open on home page and disabled all navigation and everything
i set the home page on load with this "docmd.ShowToolbar "Ribbon", acToolbarNo"
i set the home page to on Unload with this docmd.CloseDatabase
i set all DB overlapping windows and closed all records and min-max-close buttons

literally nobody can see anything or access codes or mess with anything
but i have a gap that need a solution !!

The DB can be imported into new blank DB and everything will be moved
i tried making MDE file it bugged the whole thing i have no idea why
i don't care about splitting , it's not important to protect the tables as much as protecting the designs, codes, automation
i don't want some1 to crack my work and mess with it later

solution ?

 

[CJ_London]

The DB can be imported into new blank DB and everything will be moved

they can the tables if they are in the front end, they can't everything else if it is a mde/accde. You should put the tables in a password protected back end and hide the password.

i tried making MDE file it bugged the whole thing i have no idea why
i don't care about splitting

making a mde or accde is the only way. From the above, it sounds like your code is poorly written, generating bugs and preventing a mde/accde being created. You need to fix all those bugs first.

Re splitting - you need to, end of. If you don't you are guaranteed to corrupt your db at some point, losing both all your code and all your data.

 

[The_Doc_Man]

With 104 potential users, if 1/4 of them are in the same FE at the same time, and if they are all using the same forms, you will have at least one "corrupted database" message by the end of the day. DO NOT set this up for an unsplit database. You'll look like me - gray hair and not much left of that - in a short time.

The trick with updating a back end is for you to look into DDL (Data Definition Language) that goes hand-in-hand with SQL. This will let you alter a BE table. To protect yourself, you do this at a predetermined time when the BE will be known to one and all that it will be down. Once you are sure that everyone is out of the BE, rename it or move it to another folder. (You can't do this UNTIL everyone is out.) Then make a backup copy by opening it directly so you can get to the FILE tab on the ribbon, where you do things like Compact & Repair, Backup, etc.

I rarely use macros but this is a time when one might be useful to run a sequence of DDL statements for you. The idea would be to test the DDL on a copy of your BE to verify that it makes the required changes. Then when changing the BE, work on a fresh copy that you made when you took the BE down. Then test it against the new FE that would correspond to your BE changes. You wouldn't unleash this against your "true" copy until it passes muster on the test copies, of course.

So, the FE has no BE to talk to because you renamed it or moved it. You make the copy, run the DDL sequence to make your changes, and if nothing bombs out, rename or move the updated copy back in place. But before you do, move the updated FE in place and send out a notice to all of your users. OR you can search this forum for threads describing how to trigger an automatic download of the FE every time. (Hint: You can build a little batch job, hang an icon on it, and tell the users to copy THAT to their individual machines.)

There is a lot of work to make this a strong system but it will NEVER be bullet-proof as long as it is implemented entirely in Access.

As long as you have an opening form that is your "junkyard dog" you can get people to leave by attaching timers to that form and by building a schedule table for down-time events. Then if you are in a down-time event - determined by

Now() BETWEEN EventStart AND EventEnd

(where you get the event times from the schedule table), you can know to take special action. Do not let people log in if an event is under way. Make the application reject the attempt to open (e.g. by doing a Cancel on the Opening Form's _OnOpen event) Also, put a timer event on that form to test the time every several minutes. Give folks a warning 10 minutes before the time of your shutdown and then have all of your forms check the time. When it is time to go, make every form do a Me.Undo and then a Me.Close, except for the Opening From which does an Application.Quit.

It isn't pretty and it isn't simple. But that is the way to deal with a BE that requires updating. Note that if you use the Batch Icon method for the FE, updating it is totally transparent. I don't have the reference, but just search for Automatic Update of Front End (or of FE).

 

[coda7777]

the DB is complied fine , compacted fine , running smooth until i make MDE
it give macro bug error
and FE or BE is not the big deal i can split it
but to protect FE from importing considering it will be shared file and cannot be with password

 

[The_Doc_Man]

If the FE is shared among more than a few users at a time, I guarantee you will come to grief. When you say the FE will be a "shared" file, there may be a language issue, so I'll make it clearer. If you have many people logging in to the SAME FE FILE AT THE SAME TIME then it is almost inevitably going to corrupt everything in sight. The frequency of this occurrence will be proportional to the factorial of the number of simultaneous users of the same FE file.

 

[MarkK]

cross posted: http://www.utteraccess.com/forum/index.php?showtopic=2043078&hl=

 

[coda7777]

If the FE is shared among more than a few users at a time, I guarantee you will come to grief. When you say the FE will be a "shared" file, there may be a language issue, so I'll make it clearer. If you have many people logging in to the SAME FE FILE AT THE SAME TIME then it is almost inevitably going to corrupt everything in sight. The frequency of this occurrence will be proportional to the factorial of the number of simultaneous users of the same FE file.

thanks for the advice
i got no problem making copies and throwing back the BE to the mapped drive shared folder among the employees

and i managed to keep the file safe as front end except for the importing trick :/

 

[The_Doc_Man]

Unfortunately, since Access is a small-business utility, it isn't as robust in terms of security as one might hope. However, even the U.S. Dept. of Defense has some Access FE files fronting SQL back-ends for personnel information covered by Privacy Act and HIPAA, so they don't worry about the risk of importing from the FE too much.

To be honest, the most stringent methods you can imagine won't be enough for long, because if someone is determined to get your code, they will. All you can do is make it a bigger annoyance for them. But that is true for a LOT of things. Consider web pages, for example. Your average browser gives you the ability to examine the code on the page, whether it is HTML, Java, or Absolute Gibberish. You DO want to make the FE hard to CHANGE but making it hard to read might not be worth too much skull sweat.