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 
     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 
     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
     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
     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
     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 
     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
     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 
     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 
     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 
     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 
     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.