Transcript document

ACCESS SECURITY
THE TOOLS PROVIDED BY MICROSOFT TO
PROTECT YOUR WORK
Jaime Richmond
Database Developer, SEMCOR Inc.
MUCH OF THE INFORMATION CONTAINED IN THIS PRESENTATION COMES FROM THE
MICROSOFT SECURITY WHITEPAPER
PRESENTATION OUTLINE
• QUICK OVERVIEW
• THE SIMPLE AND COMPLEX
– SIMPLE, PASSWORD PROTECTION
– COMPLEX, WORKGROUPS AND USERS
– SECURITY LAYOUT, WHAT IS WHAT
•
•
•
•
WRKGPADMIN
COMMANDLINE OPTIONS
SECURITY WIZARD
SECURITY WHITE PAPER / SECURITY FAQ
QUICK OVERVIEW
• SECURITY IS ALWAYS ON
• SHARE LEVEL SECURITY
– A SINGLE PASSWORD FOR THE ENTIRE DATABASE
• USER LEVEL SECURITY
– EACH OBJECT HAS SECURITY
– ASSIGN PERMISSIONS TO USERS AND GROUPS
– PASSWORD VERIFIES USER/GROUP
SECURITY IS ALWAYS ON
• SECURE YOUR DATABASE
– IF YOU DO NOT, THEN ANYONE CAN ( AND YOU WILL BE
LOCKED OUT OF YOUR DATABASE )
– DEFAULT LOGON IS ‘ADMIN’ USER
• security is hidden from the user and they are not
required to log on
• ADMIN is by default a member of the USERS and
ADMINS group
• Members of the ADMINS group in the Workgroup
Database when the database is created can
change permissions at any time
Password/Share-Level Security
• Everyone needs to know the same
password
• Once in no restrictions
• Users can still “secure” your database and
lock you out if you don’t “secure” it
• Example - show how to turn on a password
Pictorial Overview
Workgroup
Workgroup
Workgroup
Workgroup
Database
Database
Database
Database
• Stores the user and group
information, which users belong to
which group (DOES NOT store the
permissions each group/user has to
the various objects).
All in
One
Database
Front
End
or
Forms,
Reports,
Queries
• The database used is specified by
the windows registry, or the /wrkgrp
Back
Tables
command-line switch. (running the
End
wrkgadm in c:\windows\system is
how can change the default
•Stores the permissions associated with each
workgroup used)
object in the database AND establishes the
•ALLWAYS being used, can not
default rights for newly created objects.
open a database without using one. ??What happens if delete object and then re add
The default is SYSTEM.MDW
it??
WORKGROUP DATABASE
• The workgroup database stores which users and
groups exist, which users belong to which group,
logon passwords (encrypted, of course), and the
internal security identifier (SID) for each user and
group. Workgroup databases are created by the
Workgroup Administrator. The User and Group
Accounts command on the Security submenu of
the Tools menu changes data in the workgroup
database. A single workgroup database can be
used by many application databases.
APPLICATION DATABASE
• After a user's identity and group membership
have been established by checking the logon
strings against data in the workgroup database,
all permission checking is done against system
tables within the user (APPLICATION) database.
The User and Group Permissions command on the
Security submenu of the Tools menu affects data
in the open database, not the workgroup
database. If you move objects from one database
to another, their security settings travel with
them.
LOG ON
•
•
•
•
When a user logs on, Microsoft Access looks in the MSysAccounts table of the
workgroup database for a user of the same name (case INsensitive). If a user
with the same name is found, it then validates the password (case sensitive).
The password is only used to validate users when they log on. It has no other
effect on security.
By default, Microsoft Access first attempts to log on as the user Admin, with a
blank password. If this log on fails, the user is presented with the Log on
dialog box.
If a user name and password were specified on the command line (using the
/User and /Pwd flags), Microsoft Access first tries to log on using that user
name and password. If this log on fails, the user is presented with the Log on
dialog box.
Once logged on, the user's SID is retrieved. Then the SIDs of all groups to
which that user belongs are retrieved. These SIDs are used for all subsequent
security operations within Microsoft Access
NOTE: Microsoft Access system tables (those prefixed with MSys*) are officially "undocumented" and are subject to change in
future versions. Any applications that read directly from system tables are likely to fail in future versions of Microsoft Access.
CHANGING PERMISSIONS
• Permissions on an object can be changed by anyone who
has permissions to do so. By default, the following users
have this permission: members of the Admins group of the
workgroup database in use when the database was
created, the owner of the object, and anyone who has
Administer permissions on the object, either explicitly or
implicitly.
• For any given objec5t, there are two user/group accounts
that have the irrevocable right to assign permissions on
that object: 1) the owner of the object (usually the user
who creates it) and 2) the Admins group of the workgroup
database in use when the database was created
USERS GROUP
•
The Users group is the default group. All Microsoft Access users always
belong to the Users group. Thus, any permissions that are assigned explicitly
to the Users group are available to all users in all installations of Microsoft
Access. If you ever need to unsecure an object, simply assign permissions on
that object to the Users group.
–
•
SID: The Users group's SID is identical across all installations of Microsoft
Access. Therefore, if you assign permissions to the Users group on objects in
a database that you keep in London and then mail that database to users in
Sydney, those users will be able to access those objects, even though they
are members of a different workgroup.
–
•
Universal membership in the Users group is implemented by code in the Microsoft
Access UI, not the Microsoft Jet database engine. Therefore, programmers adding
new user accounts through DAO code need to explicitly add new users to the Users
group themselves -- it won't happen automatically.
Visual Basic applications that don't refer to a workgroup database run under the
permissions of the Admin user, not the Users group.
Default Permissions: By default, the Users group gets full permissions on all
newly created objects.
ADMIN USER
•
The Admin user is the default user account. Anyone using Microsoft Access without
logging on is using the Admin account. Any objects that are owned by the Admin user or to
which the Admin user has explicit permissions are unsecure.
–
•
•
Unless they are logged on to a different account using the /User and /Pwd flags on the command
line.
SID: The Admin user's SID is identical across all installations of Microsoft Access and
Visual Basic®. Even if no one in your workgroup is using the Admin account ( because you
have password-protected it), all objects owned by the Admin user or to which the Admin
user has explicit permissions are still open to anyone using their own copy of Microsoft
Access or Visual Basic. This is one of the most common misunderstandings in Microsoft
Access security administration. Fortunately, once the problem is understood, it's easily
prevented: Make sure the Admin user has no explicit permissions and owns no objects.
The Security Wizard described below makes this easy.
Default Permissions: By default, the Admin account doesn't receive any permissions on
new objects. However, when any user creates an object, the user account is automatically
granted full permissions on this object by default, and the user becomes the owner of that
object. Because all users in unsecured environments log on as Admin, the Admin account
usually owns a lot of objects and has a lot of permissions. When it's time to secure your
database, using the Security Wizard is the easiest way to remove those permissions.
ADMINS GROUP
•
•
•
•
Users who are members of the Admins group (note the "s") are the true administrators of
a workgroup. They manage user and group membership, and they have the power to
clear users' passwords. There must be at least one member of the Admins group at all
times.
SID: is created by encrypting three strings: user name, company name, and workgroup
ID. The Microsoft Access setup program uses only the first two -- the user's name and the
company name -- as encryption seeds for the Admins group's SID. Because these two
strings are available from the Help menu, it is critical that users implementing security
use the Workgroup Administrator to create a new Admins group with a secure SID.
The Workgroup Administrator prompts for all three seed strings that are fed into an
encryption program to generate the Admins group SID. If the workgroup database ever
becomes destroyed or corrupted beyond repair, you can generate a new Admins group
with an identical SID by running the Workgroup Administrator again and entering the
same strings.
Default Permissions: The Admins group of the workgroup database in use when the
database was created by default gets full permissions on all new objects in a database.
More importantly, the Admins group's Administer permissions, the right to change
permissions, can't be revoked through the Microsoft Access UI. Even if you clear the
Administer check box in the User and Group Permissions dialog box for the Admins
group on an object, the permission remains.
SECURING THE DATABASE
• CREATE NEW WORKGROUP DATABASE
• ADD NEW GROUP/USER THAT HAS
ADMINISTRATIVE PERMISION
• REMOVE ADMIN FROM ADMINS GROUP
( remove admin password for users to use
db w/out log on )
• ADJUST PERMISIONS OF USER
1.Use the Workgroup Administrator program (Wrkgadm.exe) to create a new
workgroup information file. Write down the Name, Organization, and
WorkGroup ID strings that you will be prompted for when creating your new
workgroup information file and store them in a safe place. Without a valid
workgroup information file, you could conceivably be locked out of your database
forever.
2.The Workgroup Administrator automatically logs you on using the new workgroup
information file. Open any database.
3.You'll be logged on as a user named Admin. Select Tools|Security|User and
Group Accounts to add a password for the Admin user. The Admin user is the
default account, and setting its password is what "activates" security.
4.Create a new user, which is the account you will use to secure the database. Add
this new user to the Admins group.
5.Remove the Admin user from the Admins group so that Admin is a member only of
the Users group. You cannot delete any of the built-in users or groups.
6.Quit Microsoft Access and log back on as the new user account you created in
step 4. You will not have a password for this account yet, so now is a good time
to set one.
7.Open the database you want to secure and run the Security Wizard. Select the
objects you want to secure (it makes sense to secure them all). The wizard will
then create a new database which will be owned by your new user and import all
of the objects and relationships into it. It will also remove all permissions from
the Admin user and the Users group and encrypt the new database. The original
database will not be altered.
8.Open the new database, create your own custom groups and assign the level
of permissions needed to these groups. Because everyone is a member of
the Users group, only grant permissions to it that you want everyone to
have. Do not place people in the Admins group because its members have
irrevocable power to administer database objects.
9.Create your own users and assign them to the groups that reflect the level of
permissions you want them to have. Do not assign permissions directly to
users because that is extremely hard to administer. Users inherit
permissions from the groups they are members of, and keeping track of the
permissions assigned to a group is much easier than keeping track of the
separate permissions of individuals.
10.Additionally, you may need to remove the Open/Run permission from the
database container for the Users group manually through the security
menus or through code. This will prevent someone from opening the
database by using another workgroup information file or the default
System.mda/mdw. The User Level Security Wizard that ships with
Microsoft Access 97 now removes the Open/Run database permissions for
the Users group.
Have a secure Workgroup
Database
Workgroup
Database
•Create Developer/Company workgroup
database
•Remove ADMIN from ADMINS
•Verify USERS permissions
•Use Security Wizard - when logged onto
Access using Developer/Company workgroup to secure existing or newly created databases
COMMAND LINE OPTIONS
• /excl
– exclusive access
• /ro
– read only
• /user username
– starts access using the specified username
• /pwd password
– uses the supplied password when logging on
• /profile
– starts using specified user profile versus the standard windows
redistry
• /compact targetdatabase
– compacts database before the /; overwrites existing database
unless targetdatabase is specified; closes Access
COMMAND LINE OPTIONS
• /repair
– repairs the database and closes Access
• /convert
– converts database to 95(97?)database with new name
• /x macro
– starts Access and runs specified macro
• /cmd
– specifies the return a value for the Command Function
• /nostartup
– starts Access without the startup dialog box
• /wrkgrp
– starts access using the specifiedworkgroup information file
SECURITY WIZARD
•
•
•
•
EASILY SECURES YOUR DATABASE
QUICK
PROVIDES SOME FLEXABILITY
PRIOR PLANNING IS THE KEY
RESOURCES
• SECURE70.EXE
– The MicroSoft Security WhitePaper
• SECFAQ.EXE
– The MicroSoft frequently asked security
questions document
• HELP FILE