How to Secure your

ERPlite Database

Welcome to Access security lessons. MS Access can be a 'little' tricky when dealing with protecting your data. Follow the below steps to an easy, clean implementation of security feature.

Step 1 - Creating the Security File

Run the MS Access WorkgroupAdministrator application

--The shortcut file "MS Access Workgroup Administrator" can usually be found in "C:\Program Files\Microsoft Office" or "C:\Program Files\Microsoft Office\Office", which point to the C:\Windows\System\Wrkgadm.exe application.

When the program starts, it will display the current security file, and ask to Create, Join or Exit. First write down the current "Workgroup", then choose "Create…." start a new security file for ERPlite.

A new window will appear with suggested information for the new security file. Fill in the information as appropriate for your company. Write down and keep somewhere safe, the Name, Organization, and WorkgroupID. These are used to create the ‘key’ to the security file, and can be used to recreate the security file, if it gets corrupted, deleted, etc. Then click OK.

A new window will ask where the file should go. Click "Browse…".

In the new window, locate directory where you ERPlite backend file(DbM_be.mdb) is located on the server. Then under "File name:", fill in "DbM_sys.mdw", or another preferred name.(Note: the filename cannot be the same as another database file, ie DbM_be.mdw) Then click OK.

When the confirmation window will appears, confirm and click OK.

When the success windows appears, click OK.

After returning to the Workgroup Administrator window, click on "Join…". Browse to find the security file that was just created, select it, click OK, and then click "Exit". Make sure you are using a UNC location for the file, so when the frontend is copied on other computers in the network they will all be able to locate it.

Step 2 - Securing the Backend

Get a copy of the backend file from the server computer. Open the file in Access.

A) User and Group Accounts

On menu at the top, select "Tools" > "Security" > "User and Group Accounts…".

Click on "Change Logon Password" tab. Leave old password blank, and enter a new password into "New Password" and "Verify". Make sure to write down the password somewhere safe.

Click on "Groups" tab, and add user groups as desired. For this example only Receptionist is added. It is good practice to never directly give users permission. Just add users to groups with appropriate permissions, or create a new group for the user.

Click on the "Users" tab. Click on the "New…" button to add a new user. Add the appropriate Group(s) using the "Add >>" button. Other users can be added as desired, now or later. Click OK.

B) User and Group Permissions

On the main menu at the top, click on "Tools" > "Security" > "User and Group Permissions…".

As it is good practice to only change permissions for groups, User permissions will be left as none, and they will inherit all their permissions from groups. Therefore, under the “User/Group Name” box, select the List: “Groups” option.

By default, the Users group has full permissions to everything, which is very insecure. Therefore, remove all permissions from Users.

-In the User/Group Name box, select Users.

-In the “Object Type” select “Database”

-Select every object in under “Object Name” (hold Shift key to go faster)

-Unselect all checkboxes under “Permissions”

-Repeat above for all Object Types (Query,Table, Form, Report, Macro)

After removing Users permissions to secure the database, now add permissions to the Groups that were created for actual users. For this example, Receptionist will be given the following:

Database:

-Current Database

-- Open/Run – True (so ‘Casey Jones’ can open/run ERPlite)

-- Open Exclusive – False (‘Casey’ never needs to deny other users access)

-- Administer – False (‘Casey’ should not be able to change anyone’s permissions)

Table:

-Inventory Transactions, Sales Orders, Customers, CustomerExtraShipTo

-- Read Design - True

-- Modify Design - False

-- Administer - False

-- Read Data - True

-- Update Data - True

-- Insert Data - True

-- Delete Data – True

-SN can update/Read, but no Delete/Inset

Read Only

-UnitsOfMeasure, Status_WO, Where_Used, My Company Information, Employees, Currency

All Other Tables: False for all checkboxes

Query,Form,Report,Macro: There are normally none in the backend, so doesn’t matter

Step 3 - Securing the Frontend

Setting security on the frontend file is very similar to backend process.

First obtain a fresh corruption cleaned copy of the frontend. Installing ERPlite creates one as DbM_fe.mdb. See the technical support knowledgebase on the web for details of corruption cleaning custimized frontends.

Join the ERPlite security file on the server that was created earlier, via “MS Access Workgroup Administrator".

Adjust and add security settings in the database the same way as was done in Step 2 part B) of this tutorial. In this case the groups and users should have been set up during Step 2 part A), and therefore not need to be added again.

Objects to set permissions for:

Tables: The data tables were secured in the backend. No additional security needed.

Queries: Set as desired for each group/user

Forms: Set as desired for each group/user

Reports: Set as desired for each group/user

Macros: Primarily used for opening reports, emailing. Set all to Full access.

Modules: Cannot have permissions/security

Once permissions have been set, then close the database. Now set the system default security file back to the original System.mdw again, via “MS Access Workgroup Administrator".

Step 4 – Distributing to the Users

After finished setting all the permissions, the ERPlite shortcut must be modified so users will connect to the correct security file when trying to login to ERPlite.

-Go to the current desktop

-Right-click on the ERPlite shortcut

-Click “Properties” on the popup menu

-Go to the “ShortCut” tab

-Copy the value(“C:\DbM\DbM_fe.mdb”) in the “Target” field, and paste it into a text editor(ie NotePad), so it will be easier to edit and verify.

-Add to the end of the value so it will be:

C:\DbM\DbM_fe.mdb /WRKGRP “\\Server\DbM\Backend\ERPlite.mdw”

where “\\Server\DbM\Backend\ERPlite.mdw” is the full unc(network path) to your ERPlite security file on the server.

-Copy the new value and paste it into the “Target” field replacing the old value
-Test the shortcut to verify it works correctly.

Finally, to have all users have the frontend permissions in effect, and be able to access the backend data, their current unsecured file and shortcut will have to be replaced with secure versions.

-Replace all installed ERPlite frontend files(DbM_fe.mdb) with the secured ERPlite frontend file.

-Replace all ERPlite shortcuts with the new shortcut that includes the security file parameter.

Notes:

-A backup administrator should be created and added to the Admins group.

-When corruption cleaning, temporarily join the ERPlite security file on the server, and login as Admin when importing.

-Visual Basic Modules can not have permissions assigned to them.

-To select a list of consecutive rows use the <Shift> key, To select multiple rows at once, hold the <Ctrl> key while selecting.

-If Runtime Access is used, users can only run/edit data directly using forms, not tables, queries, etc. Then only forms should need permssions set specifically. All other permissions can be left as full access.

-If a user tries to access a secured database with a different security file, they will not be able to login.