The SQL Grant/Revoke access rights system assigns, removes, and lists access rights to the database. Access rights determine which actions a user can and cannot perform on the database. The owner of the database can assign and remove access rights to anyone; however, users can't assign or remove access rights unless the owner has given them permission.
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. 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. Permissions can be granted to all users by specifying PUBLIC as the user identifier.
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. |
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. |
REFERENCES |
Permission to create a table with a foreign key that references a table with a primary key. |
WITH GRANT OPTION |
Permission to grant the specified permission to 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. |
When using forms, R:BASE looks for Grant/Revoke permissions on the form tables. 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.
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. To grant or revoke access rights to users, the database owner can use the User Privileges interface or commands at the R> Prompt.
