Granting Privileges to a Whole Table

Go Up to Granting Privileges

Use GRANT to give a user or object privileges to a table, view, or role. At a minimum, GRANT requires the following parameters:

An access privilege
The table to which access is granted
The name of a user to whom the privilege is granted

The access privileges can be one or more of SELECT, INSERT, UPDATE, DELETE, REFERENCE. The privilege granted can also be a role to which one or more privileges have been assigned.

The user name is typically a user is the InterBase security database, (admin.ib by default), but on UNIX systems can also be a user who is in /etc/password on both the server and client machines. In addition, you can grant privileges to a stored procedure, trigger, or role.

The syntax for granting privileges to a table is:

GRANT <privileges>
 ON [TABLE] { <tablename> | <viewname>}
 TO { <object> | <userlist> [WITH GRANT OPTION]
 | GROUP <UNIX_group>}
 | EXECUTE ON PROCEDURE procname TO { <object>
 | <userlist>}
 | <role_granted> TO {PUBLIC | <role_grantee_list> }[WITH GRANT OPTION];

<privileges> = ALL [PRIVILEGES] | <privilege_list>
<privilege_list> = {
  SELECT
 | DELETE
 | INSERT
 | UPDATE [(col[, col …])]
| REFERENCES [(col[, col …])] }[, <privilege_list> …]
<object> = {
 PROCEDURE procname
 | TRIGGER trigname
 | VIEW viewname
 | PUBLIC }[, <object> …]
<userlist> = {
 &nbsp;&nbsp;[USER] username
 | rolename
 | UNIX_user }[, <userlist> …]
<role_granted> = rolename[, rolename …]
<role_grantee_list> = [USER] username[, [USER] username …]

Notice that this syntax includes the provisions for restricting UPDATE or REFERENCES to certain columns, discussed on the next section, Granting Access to Columns in a Table.

The following statement grants SELECT privilege for the DEPARTMENTS table to a user, EMIL:

GRANT SELECT ON DEPARTMENTS TO EMIL;

The next example grants REFERENCES privileges on DEPARTMENTS to EMIL, permitting EMIL to create a foreign key that references the primary key of the DEPARTMENTS table, even though he does not own that table:

GRANT REFERENCES ON DEPARTMENTS(DEPT_NO) TO EMIL;

Tip:
Views offer a way to further restrict access to tables, by restricting either the columns or the rows that are visible to the user. See Working with Views for more information.

Advance To:

Granting Access to Columns in a Table