806.TXT
=====================================================================
Protecting Your Data
=====================================================================
PRODUCT: R:BASE VERSION: 5.0 or Higher
=====================================================================
CATALOG: General Informaton AREA : General Information
=====================================================================
Security is vital to a database. You need to protect your confidential
and sensitive data and guard against accidental and inadvertent edits
of data. R:BASE's optimistic concurrency control, table and row
locking, constraints (primary and foreign keys), and data entry and
delete rules provide a wide variety of data integrity controls. In
addition, R:BASE has the following two options for controlling access
to your data. The two options are:
SQL Grant/Revoke system of access rights
Form read and modify passwords
Each option provides a different type and level of security.
Understanding and using these options enables application developers
to provide appropriate security for any type of database and
application.
Owner Identifier
The first level of security is the owner identifier. To use either of
the R:BASE data access control systems, you must first assign an owner
identifier to the database.
Assign the owner identifier by selecting Access rights from the
Utilities menu. Click on the Change Owner button. You are prompted to
enter a new owner identifier. After assigning the owner identifier,
R:BASE prompts you for the owner identifier the next time you start
R:BASE and connect to the database. You can also change the owner
identifier using the RENAME command in the R> prompt window.
Only the owner of the database can use commands or menu options that
modify database structure. The owner of a database has all rights to
all tables. As the owner of the database, you decide the access rights
you want to give to other users of the database. The SQL Grant/Revoke
access rights are assigned by selecting Access Rights from the
Utilities menu or by using the GRANT and REVOKE commands at the R>
prompt. Form read and modify passwords are assigned when creating a
form in the Form Designer.
Using GRANT/REVOKE Access Rights
The SQL Grant/Revoke access rights system gives you flexibility to
provide different levels of access to a single table to many different
users. The database owner can grant the following levels of
permissions:
SELECT Permission to read data from the specified
table. Generally, if you assign INSERT, DELETE,
or UPDATE rights to a user, you also need to
assign them SELECT rights. If SELECT permission
is not assigned, the user cannot view data from
the table.
INSERT Permission to add new rows to the specified
table. To add data with a form or the Data
Browser, the user must be assigned INSERT
permission. In addition, importing data or using
the LOAD or INSERT commands from the R> prompt
requires INSERT permission.
DELETE Permission to delete rows from the specified
table. For users to delete a row of data or all
rows from a table, they must be granted DELETE
permission.
UPDATE Permission to modify data in the specified table
on a column by column basis. UPDATE permission
can be granted without a column list specified,
giving the user permission to change the data in
any column in the table. When granted with a
column list, UPDATE permission lets the user
change data in the specified columns only.
ALTER Permission to modify the structure of the
specified table. Normally, only the owner of a
database can change the structure of a database,
such as adding new columns to a table, changing
the definition of existing columns, or dropping
tables and views. The owner can grant permission
to a user to change the column definitions in a
table by assigning the user ALTER permission. In
addition to changing the structure of a database
by modifying table definitions, the ALTER
permission gives the user permission to DROP the
specified table or view. This permission is only
available in R:BASE 4.5++ and higher.
CREATE Permission to create new, permanent tables in
the database (STATICDB OFF only). When STATICDB
is set to on, any user can create a new table or
view, that table or view is temporary, however,
and automatically removed when the database is
disconnected. Additionally, any user can create
a permanent view or a table using one of the
relational commands_PROJECT, INTERSECT, UNION,
JOIN_without having CREATE permission assigned.
The CREATE permission lets the owner give
another user the right to add tables to the
database using the CREATE TABLE command. This
permission is only available in R:BASE 4.5++ and
higher.
WITH GRANT Permission to grant the specified permission to
OPTION other users. This permission is used to allow
other users to grant access rights. You might
want to assign a department supervisor, for
example, the WITH GRANT OPTION so that they can
assign access rights to users in their
department.
A user identifier can be granted one or more of these permissions for
any table. The owner can grant the same permission to many different
users. Permissions are granted on a table by table basis. The same
permission on a table can be granted to many users in one command, but
permissions on each table must be granted separately. You cannot grant
permissions to many tables in one command. Using the ALL PRIVILEGES
option, the owner can grant SELECT, UPDATE, INSERT, and DELETE
permissions at one time. The ALTER and CREATE permissions must be
granted individually.
The SQL Grant/Revoke system is in effect as soon as you assign an
owner identifier to the database. Once an owner identifier is
assigned, only the owner can access data until the owner grants
permissions to other users. Once you assign an owner identifier, you
must assign permissions to users on specific tables for those users to
access data in the database. If permissions are not explicitly granted
to users, they cannot access any data in any table. Permissions can be
granted to any user by specifying PUBLIC as the user identifier.
The current user identifier is stored in the keyword USER. When the
current user identifier is not the owner identifier, the only tables
displayed for use are those tables for which the current user
identifier has permission. All forms, reports, and labels are listed,
however, regardless of permissions on the underlying tables.
When using forms, R:BASE looks for Grant/Revoke permissions on the
form tables if no form passwords have been defined. 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 lookup expressions require SELECT permission on the lookup table.
Reports and labels require SELECT permission on the driving table or
view, as well as SELECT permission on the lookup table(s) used in any
expressions.
When a user creates a new table with one of the relational commands or
by choosing Save Answer in the Data Browser, the user is automatically
assigned all access rights except for the WITH GRANT OPTION to the new
table. When a user with CREATE permission makes a new table, that user
is automatically assigned all rights to the table, including the WITH
GRANT OPTION. The LIST ACCESS command shows the permission as ALTER
when access rights are automatically assigned to new tables. The ALTER
permission automatically implies all other privileges.
Displaying Permissions
The owner of the database can list all user identifiers and assigned
permissions by using the LIST ACCESS command or choosing Access Rights
from the Utilities menu. 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. Some of the LIST
ACCESS options an owner can use are:
LIST ACCESS_displays all access rights granted for the
entire database
LIST ACCESS FOR username_displays all access rights
granted to all tables for the specified user
LIST ACCESS ON tablename_displays all access rights
granted to all users for the specified table
LIST ACCESS permission_displays all access rights granted
for all users and all tables with the specified
permission
Backing Up a Database
Any user can use the menu option in R:BASE 5.0 to back up the entire
database. The menu option makes a compressed backup of the disk files.
When the files are restored, the user has the same access rights as in
the original database. The menu option for backing up a database in
R:BASE 4.5++ and earlier operates the same as the R:BASE BACKUP
command.
The BACKUP command from the R> prompt makes an ASCII file of R:BASE
commands. A user can back up data for tables or views for which they
have been granted SELECT permission. Only the owner can back up the
structure of a table, or back up the entire database. When the
structure is backed up, the owner identifier and user identifiers are
written in ASCII format to the backup file and are readable by anyone
who looks at the file.
Granting Access to System Tables
The owner of a database does not need to explicitly grant access to
the system tables. R:BASE automatically retrieves data from the system
table if the user has permission to use the command that needs the
system table information. For example, to enter data with a form,
R:BASE needs to read the form definition from the SYS_FORMS or
SYS_FORMS2 system table. If the user has permission to access the
tables on which the form is based, R:BASE automatically retrieves the
form definition; the owner does not need to grant the user access to
the SYS_FORMS and SYS_FORMS2 tables.
The only time the owner needs to grant a user access to a system table
is when the owner wants the user to be able to create forms, reports,
or labels. In this situation, the owner grants the user INSERT,
SELECT, UPDATE, and DELETE permissions on the appropriate system
tables.
When using Crystal Reports or the SQL Engine, the owner must grant
users SELECT permission on the system tables SYS_TABLES, SYS_COLUMNS,
and SYS_TYPES. Crystal Reports and the SQL Engine send SQL commands to
R:BASE that require permission to read these three system tables.
Form Passwords
Because forms can access multiple tables, the Grant/Revoke access
rights system might not apply. You can have one form that accesses
tables with different Grant/Revoke permission levels assigned. To
resolve this dilemma, forms have their own password system. The
database owner specifies a read password or a modify password for a
form. The password applies to all tables associated with the form.
Form passwords override the Grant/Revoke access rights system; if you
assign a password to a form, a user must enter that password as the
current user identifier to use the form.
Only the owner of a database can assign form passwords. The passwords
are assigned by choosing Form Settings from the Layout menu. Form
passwords assign permission to read or edit data with the form; they
are not related to permission to create or modify the form. The LIST
FORMS command lists all forms in the database, even those with
passwords assigned.
Setting User Identifiers
R:BASE might prompt for a user identifier when a database is
connected. This happens when all the tables are protected and no
access rights have been granted to PUBLIC. After you have assigned an
owner identifier to the database, but before any access rights are
granted to users, R:BASE prompts for a user identifier when the
database is connected. If rights are granted to PUBLIC, no user
identifier is needed to connect to the database and R:BASE does not
prompt for one. If an access right has been granted to PUBLIC, you
must specifically set the user identifier; R:BASE does not prompt you
for one.
To enter or change the user identifier, choose Set User ID and
Password from the Utilities menu or use the SET USER command at the R>
prompt. When you enter your user identifier and optional password,
R:BASE puts the value you enter into the keyword USER. The user
identifier can be the owner identifier, the name of a user who has
been granted access rights to some of the tables in the database, a
form password, or PUBLIC.
Only one user identifier is currently active. The SHOW USER command
displays the current user identifier. To capture the current user
identifier in a variable, use the command SET VAR vpass =
(CVAL('user')). The keyword USER is not the same as the network ID or
NAME.
R:BASE does not check to see that a valid user identifier was entered
in response to the SET USER command or other prompt. The user
identifier is checked at the time a command is executed. R:BASE checks
the current value of the keyword USER to see if that user identifier
has the appropriate permission to execute the requested action. For
example, the SELECT command requires SELECT permission on the table(s)
referenced in the SELECT command. When the command is executed, R:BASE
checks to see if the current user identifier has SELECT privileges on
those tables.
User Passwords
Each user can assign a password to their own user identifier. Once a
password has been assigned to a user identifier, R:BASE automatically
prompts for the password after the user identifier is entered. To gain
access to the database, the correct user identifier and corresponding
password must be entered. The database owner cannot look at users'
passwords, but the owner can reset the password for a user identifier.
When the current user identifier is the database owner, setting a
password assigns the password to the owner identifier. Neither the
owner nor any user can look at or reset the password for the owner
identifier.
If a user forgets their user identifier, the database owner can tell
that user what the user identifier is, or reassign it. The owner can
also reset the password for a user identifier. If the owner identifier
or password is forgotten, there is no way within R:BASE to retrieve it
or change it.
For additional information, refer to the GRANT entry in the Reference
Manual or in online Help.