GRANT (Short Name: GRA)
Top  Previous  Next

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

grant

Options

,
Indicates that this part of the command is repeatable.

ALL PRIVILEGES
Grants all user privileges 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 privileges on the tables they create, including the WITH GRANT OPTION. However, users do not have privileges 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 user privileges 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 user privileges to listed users. You must separate user identifiers with a comma (or the current delimiter).

userlist, PUBLIC
Grants specified user privileges to listed users and PUBLIC. Users in userlist can retain their user privileges if user privileges granted to PUBLIC are revoked. If, for example, ralph, sam, jane, and PUBLIC have been granted certain user privileges, revoking those privileges 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 user privileges to other users. When you use the LIST ACCESS command, an asterisk is displayed in front of the user privilege to show a user can grant the assigned user privilege 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 privileges to other users for the tables or views in your database. You must specifically grant privileges to other users. You can assign privileges for a table to individual users, to PUBLIC, or to both. Each user can have a different set of user privileges for the same table, and you can grant a user the right to grant user privileges to others. You can set your user identifier with the RENAME OWNER command and assign user privileges to other users by using the GRANT command.

In R:BASE for Windows you can also set your user identifier by choosing Utilities: Set User ID and Password. To assign user privileges to other users, choose the User Privileges 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 User Privileges

You grant user privileges or 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 user privilege in a single GRANT command, separate the user privileges with a comma (or the current delimiter).

You can grant the following user privileges: ALL PRIVILEGES, ALTER, CREATE, DELETE, INSERT, REFERENCES, SELECT, and UPDATE; however, you can grant only the SELECT user privilege 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 user privileges assigned with the GRANT command. If a form has not been assigned a password, the user privileges 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 user privileges on the underlying tables. Therefore, access to a password-protected form overrides table-level user privileges, making it possible for a user who does not have user privileges 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 user privilege. R:BASE assigns all user privileges to the user for all tables created, including the GRANT user privilege.

A user must be assigned the SELECT user privilege 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 user privileges on the new table. These user privileges do not include the GRANT user privilege.

Creating Views

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

Command Authorization Requirements
The following three tables list R:BASE commands and the user privileges 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/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 user privilege only when the value of the variable is derived from a column.

R:BASE Commands that Require the UPDATE User Privilege

Command
UPDATE User Privilege 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 User Privileges

Command
User Privileget
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 user privilege. If the form is not protected by a password, the owner of the database must grant the specific user privileges 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 user privilege. If the form is not protected by a password, the owner of the database must grant the specific user privileges on the tables used in the form.
INSERT
INSERT
Table or single-table view.
LIST ALL
Any user privilege
Any user privilege granted allows users to list all tables for which they have user privileges.
LIST COLUMNS
Any user privilege
Any user privilege granted allows users to display columns for which they have user privileges.

LIST CONSTRAINTS
Any user privilege
Any user privilege granted allows users to display constraints for tables for which they have user privileges.
LIST INDEXES
Any user privilege
Any user privilege granted allowsusers to display indexes for which they have user privileges.
LIST SYS_%
SELECT
The SELECT user privilege allows users to view a generalized list of system tables and views.
LIST TABLE SYS_%
SELECT
The SELECT user privilege allows users to view a detailed list of system tables and views
LIST TABLES
Any user privilege
Any user privilege granted allows users to display tables for which they have user privileges.
LIST VIEWS
Any user privilege
Any user privileges allows users to display views for which they have user privileges.
   

The following table lists the user privileges and the commands that use them. Some commands appear under more than one user privilege.

User Privileges 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 user privilege that includes GRANT permission.  
 
2.Form passwords override user privileges assigned with the GRANT command. If a form does not have a password, the INSERT, DELETE , SELECT, or UPDATE user privileges are required for the underlying tables.  
 
3.Any user privilege granted allows users to list all tables for which they have user privileges.  
 
4.SET VARIABLE requires the SELECT user privilege only when the value of the variable is derived from a column.  
   
Revoking User Privileges

The database owner can remove user privileges 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 user privileges including ALTER and CREATE.

Examples

The following command grants user privileges 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 user privileges 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 user privileges 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 user privileges 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