Use the GRANT command to assign privileges to users of a table or view.
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.
TEMPORARY
Grants permission to users to create temporary tables. Users who have been granted permission to use this command have all privileges on the temporary 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.
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). For a value with spaces, the userid must be enclosed in quotes.
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). For a value with spaces, the userid must be enclosed in quotes.
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.
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 that uniquely identifies a user to the system. A user identifier can be of 128 characters (or less). 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 SET USER.
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... |
Table |
|
Table |
|
Table |
|
Component tables |
|
Table |
|
Table |
|
Table |
|
Table 1 and table 2 |
|
Table 1 and table 2 |
|
Table |
|
Driving table/view, and any look-up tables |
|
Table 1 |
|
Table |
|
Table |
|
Table |
|
Table |
|
Table 1 and table 2 |
|
Table |
|
Table 1 and table 2 |
|
Table |
* SET VARIABLE and SET STATICVAR commands require 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... |
Column |
|
Table. All columns in the table are displayed if you have SELECT permission on the table. |
|
Column |
|
Column list |
R:BASE Commands that Require Other User Privileges
Command |
User Privilege |
Access on... |
DELETE |
Table or single-table view. |
|
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. |
|
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 |
Table or single-table view, without calculations. |
|
Any user privilege |
Any user privilege granted allows users to list all tables for which they have user privileges. |
|
Any user privilege |
Any user privilege granted allows users to display columns for which they have user privileges.
|
|
Any user privilege |
Any user privilege granted allows users to display constraints for tables for which they have user privileges. |
|
Any user privilege |
Any user privilege granted allows users to display indexes for which they have user privileges. |
|
SELECT |
The SELECT user privilege allows users to view a generalized list of system tables and views. |
|
SELECT |
The SELECT user privilege allows users to view a detailed list of system tables and views |
|
Any user privilege |
Any user privilege granted allows users to display tables for which they have user privileges. |
|
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 |
|||
CREATE |
REVOKE 1 |
||
GRANT 1 |
|||
Database owner's user identifier |
REVOKE 1 |
||
GRANT 1 |
|||
DELETE |
LIST 3 |
||
Form password 2 |
ENTER 1 |
||
INSERT |
ENTER 2 |
LIST 3 |
|
REFERENCES |
ENTER 2 |
||
LIST 3 |
|||
SELECT |
|||
LIST 3 |
|||
UPDATE |
|||
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 Jane to 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 the user Abe, who is not the database owner, permission to alter the Customer table.
GRANT ALTER ON Customer TO Abe
The following command line grants the user Abe, who is not the database owner, permission to create tables.
GRANT CREATE TO Abe
The following command line grants the user Noah permission to create temporary tables.
GRANT TEMPORARY CREATE TO Noah
