GRANT

GRANT (Short Name: GRA)

Use the GRANT command to assign access rights to users of a table or view.

Options

,
Indicates that this part of the command is repeatable.

ALL PRIVILEGES
Grants all access rights on the specified table, or on a view that can be updated.

ALTER
Grants permission to alter specific tables.

CREATE TO
Grants permission to users to create tables using the CREATE TABLE command. Users who have been granted permission to use this command have all rights on the tables they create, including the WITH GRANT OPTION. However, users do not have rights on any other tables in the database unless they are specifically granted permission by the owner.

DELETE
Grants permission to remove rows from the specified table or from a view that can be updated.

INSERT
Grants permission to add rows to the specified table or to a view that can be updated.

ON tblview
Specifies a table or view.

PUBLIC
Grants specified access rights to all users.

REFERENCES
Grants permission to create a table with a foreign key that references a table with a primary key.

SELECT
Grants permission to display or print data for the specified table or view.

UPDATE (collist)
Grants permission to change the values of columns in the specified table or a view that cannot be updated. If you do not include the optional (collist), the user can update all columns in the table. If you list columns, the user can update only the specified columns.

userlist
Grants specified access rights to listed users. You must separate user identifiers with a comma (or the current delimiter).

userlist, PUBLIC
Grants specified access rights to listed users and PUBLIC. Users in userlist can retain their access rights if access rights granted to PUBLIC are revoked. If, for example, ralph, sam, jane, and PUBLIC have been granted certain user access rights, revoking those rights from PUBLIC would not affect the three listed users. You must separate the user identifier with a comma (or the current delimiter).

WITH GRANT OPTION
Allows the specified users to pass the granted access rights to other users. When you use the LIST ACCESS command, an asterisk is displayed in front of the access right to show a user can grant the assigned access right to others; for example, *SELECT means a user has permission to display or print data for specified tables or views, and can grant SELECT rights to other users.

About the GRANT Command

As the database owner, you must first set your own user identifier. After setting your user identifier, you can assign access rights to other users for the tables or views in your database. You must specifically grant access rights to other users. You can assign access rights for a table to individual users, to PUBLIC, or to both. Each user can have a different set of access rights for the same table, and you can grant a user the right to grant access rights to others. You can set your user identifier with the RENAME OWNER command and assign access rights to other users by using the GRANT command.

In R:BASE for Windows you can also set your user identifier by choosing Utilities: Access Rights... and clicking the Change Owner button in the "Access Rights" dialog box. To assign access rights to other users, choose the Access Rights...option from the Utilities menu.

In R:BASE for DOS, you can also set a user identifier and assign access rights in RBDefine; enter the RBDEFINE command at the R> prompt.

Granting Access Rights

You grant access rights on tables or views, however, UPDATE rights must be granted at the column level and CREATE rights must be granted at the database level. If you assign more than one access right in a single GRANT command, separate the access rights with a comma (or the current delimiter).

You can grant the following access rights: ALL PRIVILEGES, ALTER, CREATE, DELETE, INSERT, REFERENCES, SELECT, and UPDATE; however, you can grant only the SELECT access right on views that cannot be updated.

Using User Identifiers and Passwords

A user identifier can be any unique string of 18 characters (or less) that uniquely identifies a user to the system. To maximize security, create user identifiers that are difficult to guess-such as a random string of letters and numbers. Users can assign passwords to their user identifiers for an added level of security. For information about users assigning passwords see the "SET USER" entry.

In a database where users have been assigned rights, printing reports requires one of these conditions:

A user has been granted SELECT privileges on the driving table or view and any look-up tables.

A user has been granted SELECT privileges or ALL PRIVILEGES on all tables used for the report.

PUBLIC has been granted SELECT privileges on the driving table or view.

PUBLIC has been granted SELECT privileges or ALL PRIVILEGES on all tables used for the report.

The only exception to this system of assigning rights is password-protected forms. Passwords assigned to forms, override access rights assigned with the GRANT command. If a form has not been assigned a password, the access rights you granted to the tables associated with the form are in effect.

Once R:BASE determines that a user can have access to a password-protected form, R:BASE does not verify access rights on the underlying tables. Therefore, access to a password-protected form overrides table-level access rights, making it possible for a user who does not have access rights on a table to modify the information in that table.

Creating New Tables

To create new tables in a database, a user must be assigned the CREATE access right. R:BASE assigns all access rights to the user for all tables created, including the GRANT access right.

A user must be assigned the SELECT access right to create a new table from existing tables using the INTERSECT, JOIN, PROJECT, SUBTRACT, or UNION commands. R:BASE assigns users who use these commands all access rights on the new table. These access rights do not include the GRANT access right.

Creating Views

CREATE VIEW also requires the SELECT access right on the existing tables. R:BASE assigns users who create views the same access rights they have on the source table. For views that cannot be updated, R:BASE only assigns users the SELECT access right.

Command Authorization Requirements
The following three tables list R:BASE commands and the access rights they require.

R:BASE Commands that Require the SELECT Access Right

Command SELECT Access Right on...

BACKUP DATA

Table

BROWSE   Table
COMPUTE   Table
CREATE VIEW   Component tables
CROSSTAB   Table
DECLARE CURSOR   Table
FETCH   Table
INTERSECT   Table 1 and table 2
JOIN   Table 1 and table 2
OPEN CURSOR   Table
PRINT   Driving table or view, and any look-up tables
PROJECT   Table 1
QUERY   Table
SELECT   Table
SET VARIABLE 1   Table
SUBTRACT   Table 1 and table 2
TALLY   Table
UNION   Table 1 and table 2
UNLOAD DATA   Table

1 SET VARIABLE requires the SELECT access right only when the value of the variable is derived from a column.

R:BASE Commands that Require the UPDATE Access Right

Command   UPDATE Access Right on...
CREATE INDEX   Column
EDIT ALL      Table. All columns in the table are displayed if you have SELECT permission on the table.
QUERY      Column
UPDATE      Column list

R:BASE Commands that Require Other Access Rights

Command      Access Right      Access on...
DELETE         DELETE         Table or single-table view.

EDIT USING      UPDATE, SELECT,
         DELETE, ALL PRIVILEGES   If the form is protected by a password at either the read or
                  write level, the password on the form is the overriding
                  access right. If the form is not protected by a password, the                   owner of the database must grant the specific access rights
                  on the tables used in the form.

ENTER         INSERT, ALL PRIVILEGES   If the form is protected by a password at either the read or
                  write level, the password on the form is the overriding
                  access right. If the form is not protected by a password, the                   owner of the database must grant the specific access rights
                  on the tables used in the form.

INSERT         INSERT         Table or single-table view.

LIST ALL         Any access right      Any access right granted allows users to list all tables for
                  which they have access rights.

LIST COLUMNS      Any access right      Any access right granted allows users to display columns
                  for which they have access rights.

LIST CONSTRAINTS   Any access right      Any access right granted allows users to display constraints
                  for tables for which they have access rights.

LIST INDEXES      Any access right      Any access right granted allowsusers to display indexes for                   which they have access rights.

LIST SYS_%      SELECT         The SELECT access right allows users to view a generalized
                  list of system tables and views.

LIST TABLE SYS_%   SELECT         The SELECT access right allows users to view a detailed list
                  of system tables and views

LIST TABLES      Any access right      Any access right granted allows users to display tables for                   which they have access rights.

LIST VIEWS      Any access right      Any access rights allows users to display views for which
                  they have access rights.

LOAD         INSERT         Table or single-table view.

The following table lists the access rights and the commands that use them. Some commands appear under more than one access right.

Access Rights for R:BASE Commands

Access Right         R:BASE Commands that Require The Access Right

ALTER

ALTER TABLE AUTONUM DROP COLUMN

CREATE

ALTER TABLE DROP REVOKE 1

AUTONUM GRANT 1 RULES

BACKUP ALL PACK UNLOAD ALL

BACKUP STRUCTURE RELOAD UNLOAD STRUCTURE

COMMENT ON RENAME

CREATE TABLE RESTORE

Database owner's user identifier

ALTER TABLE DROP REVOKE 1

AUTONUM GRANT 1 RESTORE

BACKUP ALL PACK RULES

BACKUP STRUCTURE RELOAD UNLOAD ALL

COMMENT ON RENAME UNLOAD STRUCTURE

CREATE TABLE

DELETE

DELETE EDIT LIST 3

Form password 2

EDIT USING 2 ENTER 1

INSERT

EDIT ENTER 2 LIST 3

EDIT USING INSERT LOAD

REFERENCES

ENTER 2 INSERT UPDATE

EDIT LIST 3

EDIT USING LOAD

SELECT

BACKUP DATA FETCH SELECT

BROWSE INTERSECT SET VARIABLE 4

COMPUTE JOIN SUBTRACT

CREATE VIEW LIST3 TALLY

CROSSTAB PRINT UNION

DECLARE CURSOR PROJECT UNLOAD DATA

UPDATE

CREATE INDEX EDIT USING UPDATE

EDIT LIST 3

1. GRANT and REVOKE do not require the database owner's user identifier for an access right that includes GRANT permission.

2. Form passwords override access rights assigned with the GRANT command. If a form does not have a password, the INSERT, DELETE , SELECT, or UPDATE access rights are required for the underlying tables.

3. Any access right granted allows users to list all tables for which they have access rights.

4. SET VARIABLE requires the SELECT access right only when the value of the variable is derived from a column.

Revoking Access Rights

The database owner can remove access rights with the REVOKE command. The syntax for the REVOKE command is the same as the syntax for the GRANT command. If you issue the REVOKE ALL PRIVILEGES command without specifying a table, R:BASE revokes all access rights including ALTER and CREATE.

Examples

The following command grants access rights to display the view named slsview to a specific user-jane, and to all users-PUBLIC.

GRANT SELECT ON slsview TO jane, PUBLIC

The following command grants access rights to add or remove information to or from the transmaster table to any user entering the user identifier sam or ralph.

GRANT INSERT, DELETE ON transmaster TO sam, ralph

The following command grants access rights to display and enter information in the transmaster table. Also, the command allows any user entering the user identifier janeto pass the SELECT and INSERT access rights on to other users.

GRANT SELECT, INSERT ON transmaster TO jane WITH GRANT OPTION

The following command grants a user who is not the database owner permission to alter a specified table.

GRANT ALTER ON customer TO John

The following command line grants a user who is not the database owner permission to create tables.

GRANT CREATE TO John