The owner can also grant and revoke permissions to users using the GRANT and REVOKE commands at the R> Prompt or within a command file. Permissions are granted on a table by table basis.
To grant display, edit, and enter permissions to the user Doug, the following commands would be used:
GRANT SELECT ON Employee TO DOUG
GRANT UPDATE ON Employee TO DOUG
GRANT INSERT ON Employee TO DOUG
To grant edit permission for specific columns in the Contact table to the user Bill, the following command would be used:
GRANT UPDATE ( +
ContFName +
ContLName +
ContPhone +
ContFax +
ContCell +
ContPager +
ContEMail +
) ON Contact TO BILL
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. To grant display, edit, and enter permission in the Customer table to several users, the following command would be used:
GRANT SELECT, UPDATE, INSERT ON Customer TO GEORGE, RICHARD, MARY, HELEN
There is also the GRANT command parameter, ALL PRIVILEGES, for the owner to grant display, edit, enter, and delete permissions at one time. The ALTER and CREATE permissions must be granted individually.
GRANT ALL PRIVILEGES ON InvoiceHeader TO DOUG
GRANT ALL PRIVILEGES ON InvoiceDetail TO DOUG
The following command grants the user John, who is not the database owner, permission to alter table Customer.
GRANT ALTER ON Customer TO John
The following command line grants the user John, who is not the database owner, permission to create tables.
GRANT CREATE TO John
As the owner of a database, user privileges can be revoked from users. If the database owner or other users have assigned user privileges with the WITH GRANT OPTION, a user can revoke only the user privileges that the user granted to others.
The following command revokes permission granted to Jane to display or print data, or add rows to the InvoiceHeadertable.
REVOKE SELECT, INSERT ON InvoiceHeader FROM JANE
The following command revokes the UPDATE user privilege granted to Sam for all tables and views in the database.
REVOKE UPDATE FROM SAM
The following command revokes all user privileges granted to Sam, except those granted to him as a member of PUBLIC.
REVOKE ALL PRIVILEGES FROM SAM
The following command revokes all user privileges for all tables and views for all users.
REVOKE ALL PRIVILEGES FROM SAM, JANE, JOHN, PUBLIC
When a user creates a new table with one of the relational commands or by choosing "Save Result As" 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.
