59. Securing Your Front End (Programming In Microsoft Access 2013) 🎓

hello again everyone and welcome back to programming in Access 2013 my name is Steve Bishop today's video is going to be on securing the front end in our last video we secured the data file by simply putting a password protection on it but we can't really do that with the front end file because we want our users to just basically be prompted with the login screen if we password-protected it and just left it at that once the user put in the password they would still have a lot of they would basically still have full admin rights and developer rights to that database and we really want to start limiting the users access to the different functions of your database so I'm going to hop out of my PowerPoint presentation here and if I open up the database right now you'll see that I get the navigation pane on the left hand side and this is not good because if I just want to open up the employees table I can get people's passwords and usernames and wages and date of birth and all sorts of private information so clearly we don't want our users to just have full access to this kind of thing then if the user right clicks on something notice they get this little context menu we probably don't want that to happen we don't want them to be able to just right-click on something and get access especially if it's like the login form for example if I right click here I can close it so that's not good I don't want them to be able to do that then when it comes to the ribbons up top here we've got the create tab the external data tab the database tools tab these are all things that the users probably should not have access to we only want the developers to have access to that type of thing so we're going to shut down the access to all of this and again like I said before we really want it so that when the database opens up the users printed presented with the login form and all of these different security issues are taken care of so luckily for us this is actually all available in one simple little place it's going to be under the file tab under options under current database and here we have those options that we kind of covered a few a few episodes ago but I'm going to go over a few more of the a few more of them a little bit more deeply here under the display form here if I click on the drop-down you'll notice that all the forms that I've created are now available I'm going to go ahead and select form login and what this does is when the database opens the frm login form will start up automatically so again when the user opens up the database the first thing the user will be presented with is our login form and that's exactly the type of functionality we want okay I'm going to scroll down here and under navigation there's this little checkbox here next to display navigation pane I'm going to uncheck that and that will get rid of the navigation pane over here on the left hand side if I scroll down a little further I've got allow full menus and if if I uncheck this this will disable all of the extensible functionality that's available through these tabs like the create tab external data tab database tools tab etc so all of those tabs will disappear for the user and that's precisely what I want then lastly we have allowed default shortcuts menu and that's essentially the right mouse button click okay that's the context menu that can come up I'm going to go ahead and disable that as well so now that I've got everything disabled I'm going to go and click OK says I need to close and reopen the database for the changes to take effect so let's go ahead and do that and now when I reopen the database you'll see I get prompted with the login screen ok and my file tab and Home tab are the only two tabs that are available you'll notice the create tab is gone along with all the other tabs and my navigation pane on the left-hand side is also gone but not only that when I designed this login form if you recall we turned it into a modal form which basically means the user must interact with this form and this form alone they cannot click on anything behind it I'm getting a little beep son that tells me you're doing something wrong so I'm really limiting the users access to just this one single form and that's exactly the type of thing that I want to do so let me go ahead and log in here oops if I put the right password in all right there we go okay so I'm logged in here I really have only the access to those things available from my home tab my file tab navigation pane is gone I can't right click on anything so great we've got all the limited functionality we want but there's a bit of a problem if I is the user with all my options disabled if I click file here and I go to privacy options uh-oh I can go into the current database and undo everything you just did right so that's not good I don't want that to happen well let me go ahead and turn all this stuff back on here because I'm going to show you how to take care of this okay first thing I need to do is I need to go and create a new table okay so I want to go to create I'm going to go to table design and here in my table design I'm going to make two fields one is going to be called ribbon name notice the capitalization here it's going to be a type of short text then the next field is going to be ribbon XML again notice the capitalization and it's going to be a type of long text okay then the under ribbon name I'm going to go ahead and make this the primary key just a word to those of you who might still be using office 2010 or 2007 instead of long text you're going to need to use memo okay just in case you are still on one of the older versions okay so we've got ribbon name ribbon XML short text long text now we need to go ahead and save the table and it's going to be called you cysts ribbons and again notice the capitalization your capital u capital s capital R okay so that's great now I've got my uses ribbons table I'm going to go ahead and open this up in the and the table view here and let's expand this out so I can see everything I got nice and big here okay now the first ribbon I'm going to make is going to be let's call it blank or default yeah that's fine okay I've got a little bit of code here which I'm going to copy and paste into here which is a special ribbon that basically uses the current ribbon okay but it also disables the application options dialog okay and that's that options function that's available up here and file so you don't really need to worry about this this is XML code here uh and you know later on we may cover ribbons at a later point but for right now just copy and copy what you see up here on the screen let me make it a little bit bigger so you can see exactly the the tab situation here just copy what you see up here if you feel like you know pausing the video right now go ahead jot this down or type it out somewhere because you're going to need it okay so we went ahead and did all that let's go ahead and save that close out of that I'm going to need to close out of my database again and then reopen it for this to be to be for this new ribbon to be available I'm going to go to my options and now if I go under current database scroll down here see where it says ribbon name I have the drop down here to use default click OK and now watch what happens when I open this up file notice my options are gone okay so the options are gone all right well okay I got a bit of a new problem I need to go back in and turn off all those functions so I don't have that options option anymore how the heck do I get in there and change all those options back ok I created a little web for myself didn't I well guess what us developers know about a little tiny secret that you're going to all want to be clued in on here if you hold the shift key down when you open up a file that will bypass all of the options that you set all the ribbons that you set and any automatic execution that you've said so you'll see I basically still get my file and options okay so I've gone back to basically the default I have all them I'll have my navigation pane I'll have my ribbon up top here I'll have my right-click ability and I will bypass the opening of the login form so let me just go ahead and set all those options back up for you and I'll show you if I change this back to login form and that's good as the default disable that disable that disable that click OK ok again and now when I open up my database as a user I have to log in oops and now if I click on file I don't have the options anymore okay I can only print or exit in that great so now I've really locked this sucker down now some of you may be screaming at your monitor right now like wait a minute if I know I can hold the shift key down and doing so bypasses all of that and I get all these fancy options back up and I can access all that well I can do it my users can do it and you'd be precisely right if you can do it your users can do it now you could go and you could say well I hope that they don't figure it out but really what you should probably do is create some code here okay and I'm going to take you through this first and foremost under my uh under my employees table here I've changed my particular account I've added a new employee type called three so if you look at our employees type I'm going to call it administrator and it's got an ID type of three so any user who has an employee type of three we're going to need to keep track of this we're going to use this employee type to determine whether or not the user is going to have certain functionality when they log in okay I'm going to go ahead and go to my login form and let's go to design view here and when the user logs in and the on click event here I'm going to add a little bit of code to the on click event for my login button we scroll down here and before they log in I want it or before the form main opens up I'm going to check to see what the users security level is so let me go ahead and see I'll just did a find first on table one employees so let's go ahead and do if RS and I think what was that security level C was the what was the field name here it was all right employee type underscore ID so let me go back here employee type underscore ID is equal to three then let's go ahead and close our end if here I'm going to add some code in here that is going to prompt the user if they are a type of employee type three I've got the code already pre written out here so I'm just going to copy and paste it and this code essentially let me just tap it over make it a little bit more legible is going to set a special property called allow bypass key okay and the way this works is I'm going to I'm going to dim a prop as property I'm going to set prop equal to a the value returned by the create property method from my current DB so set prop equal current DB create property and then in parentheses it's going to be allowed bypass key in quotations comma DB bullion because it's going to be a blue yon type and then we're sending a the initial value to false okay you also you may notice I have this on error go to set property that's because if the property is already set when you go to append it to your properties you'll kick out an error and so we're just going to go ahead and use the fact that that'll happen to just skip down to the next bit here okay it's a little bit of error handling just in case that property already exists then we're going to prompt the user again this is if they are an administrator it's going to say would you like to turn on the bypass key okay and that's again the shift key if the user selects yes then that will turn bypass back on if the user selects no it's going to go ahead and keep it false okay so that's what we're going to do we're going to use this bit of code and I'll leave it up here on the screen for just a second for everybody to pause and type this out it's going to be starting from just tab this down here so starting from here at the dim prop is property down to this end if here you're going to want to put that in your code and make sure that you're checking to see what the employees security level is or what group they are to make sure that they should be able to get this prompt okay and it's very very important that you take this step because if you change this allow bypass key to false and you don't give the users any way to turn it back on you're not going to be able to bypass this you're going to basically be limiting the the database forever and you're not going to be able to get out of it and you're not going to be able to get into tables and stuff you'll never be able to go in as a developer and that's not good okay so I'm going to go ahead and save it debug it make sure everything's good looks like it is so go ahead and close this and now if I open it up and if I log in as me would you like to turn on the bypass key I'm going to say no I don't okay so now the bypass key is turned off and now I'm going to hold the shift key down when I try to open this and you'll see bada bing even holding the shift key down I still don't have any of the functionality so I have completely fully locked down my database no one can get into it no one can do any of those special functions the only thing that I can do is log in here and because I am an administrator I can do this I can do and now it should ask me to turn it on click yes let's turn the bypass key back on and now if I try to reopen the database holding the shift key down and there we go okay now I highly recommend that before you enter this code on before you add that property you should probably make a copy of your database somewhere and make sure you keep it in a safe location that your users will never get a version of because you're you know just in case you forget the password or something happens to the table and the employees list doesn't allow you to log in as this user anymore you can really screw yourself good so make sure you've got a back-up plan and you're storing this database somewhere that doesn't have that property set on it all right so there you go that's how you can successfully lock down your database file and not allow your users to get into anything other than what you want them to

Channel: Programming Made EZ

Views: 198,194

Rating: 4.9189191 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, beginner, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, Front And Back Ends, Software (Musical Album)

Id: i1n7H686bWI

Length: 16min 55sec (1015 seconds)

Published: Thu May 08 2014