DOCUMENT #674
=======================================================================
PASSWORDS - A PRIMER
=======================================================================
PRODUCT: R:BASE VERSION : 3.1 or Higher
=======================================================================
AREA : DATABASE SECURITY CATEGORY: APPLICATION
=======================================================================
Security is vital to a database. You need to proctect your
confidential and sensitive data. You need to guard against
accidental and inadvertent edits of data. R:BASE's optimistic
concurrency control, table and row locking, and data entry and delete
rules provide data integrity controls. In addition, R:BASE has three
passwords systems to allow you to control access to your data. The
three password systems are:
SQL Grant/Revoke
read/modify table
form
Each provides a different type and level of security. Understanding
and using these password systems enables application developers to
provide appropriate security for any type of database and
application.
OWNER Password
==============
The first level of security is the owner password. To use any of the
R:BASE password systems, you must first assign an OWNER password to
the database.
Assign the owner password by selecting Change owner... on the Access
rights pulldown from Info Create/modify. You are prompted to enter a
new owner password. After assigning the owner, R:BASE prompts you
for the password the next time you start R:BASE and connect to the
database.
Only the owner of the database can use commands or menu options that
modify database structure. Only the owner can assign access rights to
other users. When you select Create/modify from the menus, R:BASE
will prompt for the owner password when necessary. The owner of a
database has all rights to all tables.
As the owner of the database, you then decide the access rights you
want to give to other users of the data. The SQL Grant/revoke
passwords are assigned through the menu system (Access rights) or
from the R> prompt. Table passwords are only assigned from the R>
prompt, and form passwords as assigned through Forms Create/modify.
Setting Passwords
=================
R:BASE does not automatically prompt for a password. To enter or
change the password, use the SET USER command or choose User
password... from the Tools menu. When you enter your password in
response to the prompt, R:BASE simply puts the value you enter into
the keyword USER. The USER is NOT the same as the network ID or NAME.
R:BASE may prompt for a password when a database is connected. This
happens only if all tables are password protected and no rights have
been granted to PUBLIC.
Only one password can be currently active. Passwords and users are
interchangeable to R:BASE. The current user is the currently active
password. SHOW USER displays the current user (password). To
capture the current user in a variable use SET VAR vpass =
(CVAL('user')).
R:BASE does NOT check the validity of the password when it is entered
in response to the SET USER or other prompt. It is checked at the
time a command is executed. R:BASE checks the current value of the
keyword USER to see if that user has the appropriate permission to
execute the requested action. For example, the SELECT command needs
READ permission, so when the SELECT command is executed, R:BASE
checks to see if the current user (password) has read/select
permission on the tables.
Table Passwords (READ/MODIFY)
=============================
Each table can be assigned a read (select) password or a modify
password. When an owner password is assigned, the table passwords on
all of the existing tables are automatically set to the owner
password. When you create a new table, table passwords are not
automatically assigned; the owner must add them using the RENAME RPW
and RENAME MPW command at the R> prompt.
The database owner can change the table (read/modify) passwords by
using the RENAME command. There is no way to see what the existing
passwords are. When you LIST a table, the display indicates YES
if table passwords have been defined, but the actual password does not
display.
As the name imples, this password system simply restricts read access
and modify (edit) access to the data on a table-by-table basis. To
use commands that modify data, the current user must match the modify
password for that table. To use commands that just look at data, the
current user needs to match the read password. Modify permission
implies read permission. The LIST and F3 hotkey only show
tables the current user has at least read access to, however, all
Forms, Reports and Labels are listed regardless of permissions on the
underlying tables.
Table passwords can be used to control modify access to tables
through the Info menu but not through forms. Forms ignore table
passwords. You can use any form regardless of the underlying table
passwords. Lookups in forms, though, do require read permission on
the lookup table.
Reports and labels require read permission on the driving table or
view of the report, as well as read permission on the lookup
table(s).
GRANT/REVOKE
============
Table passwords are simple but may not provide enough flexibility for
your database and users. Having modify permission allows you to add
data, edit data and delete data, but maybe not everyone should be
allowed to delete data. The SQL Grant/revoke password system gives
you this flexibility. The database owner has four levels of
permission assignments:
SELECT = read data from the table
INSERT = add new rows to the table
DELETE = delete rows from the table
UPDATE = modify data in the table on a column by column basis
Users can have one or more of these permissions. You can have many
users with the same permissions assigned as opposed to table
passwords where a table has a password and every user must know that
password. Using the Grant/revoke password system allows each user to
have their own password. All permissions do imply SELECT permission.
The owner of the database can get a list of all passwords and
assigned permissions by using the LIST ACCESS command. The owner can
list access rights for a particular user, for a permission type, for
a table or for all permissions. A user can list their permissions
only.
The SQL Grant/revoke system is in effect as soon as you GRANT a
permission. Once you GRANT to a user, you must assign permissions to
tables and users to access data. If permissions are not
explicitly GRANTed to users, they are not able to access any data
in any table. Permissions can be GRANTed to PUBLIC, i.e. anyone.
Table (read/modify) passwords are ignored once the SQL grant/revoke
is in use.
As with table passwords, the only tables displayed with LIST or F3 are
those tables the current user has permission on. All Forms, Reports
and Lables are listed regardless of permissions on the underlying
tables.
Forms looks for underlying grant/revoke passwords. In general, to
add data using a form, INSERT permission is required. To edit data,
UPDATE permission is required. To delete rows DELETE permission is
needed. Form lookups require at least SELECT permission on the lookup
table.
Reports require at least SELECT permission on the driving table or
view of the report, as well as SELECT permission on the lookup
table(s).
When a new table is created, it is automatically assigned the access
rights of the user who created it.
Form Passwords
==============
Because forms can access multiple tables, the table (read/modify) or
grant/revoke passwords systems may not apply. You can have one form
access tables with different table passwords or grant/revoke
permission levels assigned. To resolve this, forms have their own
password system. You can specify a read password or a modify password
for a form. The password applies to all tables associated with the
form. Form passwords override all other password systems, i.e. if
you assign a password to a form, the current user must enter that
password to use the form.
Only the owner of a database can assign form passwords. The passwords
are assigned from the Form Settings screen. Form passwords are not
related to permission to modify the form.
LIST FORMS lists all forms, even those with passwords assigned.
Conclusion
==========
Using passwords appropriately will protect your data from
unauthorized access and accidental edits. For more information see
the Passwords entry in the Command Dictionary section of your
Reference Manual and the articles "Beyond Passwords" and "Database
Protection -- More Than Just Passwords" in this issue of the Exchange.