Granting Privileges - Firebird

Access privileges can be granted on an entire table or view. It is also possible to restrict UPDATE and REFERENCES privileges to specific columns.

A GRANT statement is used to give a user, role, or stored procedure a specific privilege for an object. The general syntax pattern for granting privileges on objects is

GRANT <privileges>
ON [TABLE] <table> | <view> | <object> | <omit ON clause>
TO <generic-user>
[{WITH GRANT OPTION} | {WITH ADMIN OPTION}];
<privileges> = <privilege> | <privilege-list> | <role-name> | ALL
<privilege> = INSERT | DELETE | UPDATE [(column [, column [,..]] ) ]
| REFERENCES [(column [, column [,..]] ) ] | EXECUTE
<privilege-list = [, privilege [, <privilege-list [,...]]]

Notice that the <privilege> syntax includes the provisions for restricting UPDATE or REFERENCES to certain columns, as discussed in the next section.

<object = <stored-proc> | <role-with-privileges> <generic-user> = <user> | PUBLIC | <user-list> | <UNIX-user> | GROUP <UNIX-group> | <user-object>
<user-list> = <user>, {<user> | <user-list}
<user-object> = <role> | <trigger> | <stored-proc>

The <user> is generally a user that is defined in the USERS table of the Firebird security database. On all-POSIX client/server networks, it can also be a user account that is in /etc/password on both the server and client machines, or a UNIX group that both have in /etc/group. For databases used with the Windows Embedded Server, a “mock user” user name (known to the application) is allowed.

The following statement grants some privileges for the DEPARTMENTS table to a user, CHALKY:

GRANT SELECT, UPDATE, INSERT, DELETE ON DEPARTMENTS TO CHALKY;

UPDATE Rights on Columns

The UPDATE privilege, unmodified, lets the user update any column in the table. However, if you specify a comma-separated list of columns, the user will be restricted to updating only the specified columns.

In the following statement, all users will have update permissions for the CUSTOMER table, but they will only be able to update CONTACT_FIRST, CONTACT_LAST, and PHONE_NO:

GRANT UPDATE (CONTACT_FIRST, CONTACT_LAST, PHONE_NO) ON CUSTOMER TO PUBLIC;
  • When the option to grant UPDATE on a list of columns is used, multiple permissions are stored in the system table RDB$USER_PRIVILEGES, one for each column. Rights can be granted or revoked on each column individually.
  • When the column-level permission is not used, only one permission is created. There is no way to remove rights on some columns and keep others. It would be necessary to revoke the permissions that contain the rights you want to remove and add a new one with the amended rights.

Thanks to the rules for SQL privileges—“The funniest thing since the Marx Brothers,” according to a colleague—it is possible to grant both column-level permissions and the unqualified table-level UPDATE and REFERENCES privileges to the same user. It can cause complications if a user’s column-level UPDATE or REFERENCES right is revoked, since the same user’s table-level permission is unaffected.

Views offer an elegant way to restrict access to tables, by restricting the columns and/ or the rows that are visible to the user in highly customized ways.

REFERENCES Rights on Columns

The REFERENCES privilege is a necessary accompaniment to granting permissions on a table that has a foreign key. It is needed if a user creating a foreign key in a table does not own the table referenced by the key.

REFERENCES grants permissions on columns. All of the columns referenced by the grantee table’s foreign key must be involved. If the GRANT REFERENCES statement refers to the table, without specifying columns, then the permissions are granted on every column. The columns that are not involved in the link between the foreign key and the referenced table’s primary key are not affected.

If you prefer, you can specify just the key columns and, perhaps, save a little overhead if the referenced table has a lot of columns. If you do so, you must specify all of the linking key columns. The simplified syntax pattern is

GRANT REFERENCES
ON <primary-table> [ ( key-column [, <key-column> [, ...]] ) ]
TO <needful-user>
[WITH GRANT OPTION] ;

The next example grants REFERENCES privileges on DEPARTMENTS to CHALKY, permitting CHALKY to write a foreign key that references the primary key of the DEPARTMENTS table, even though he doesn’t own that table:

GRANT REFERENCES ON DEPARTMENTS(DEPT_NO) TO CHALKY;

Privileges on Objects

When a trigger, stored procedure, or view needs to access a table or view, it is sufficient for either the owner of the accessing object, the accessing object, or the user who is executing it to have the necessary permissions.

On the other hand, privileges on tables can be granted to a procedure instead of to individual users, as a security measure. The user needs only the EXECUTE privilege on a procedure that accesses a table.

A stored procedure, view, or trigger sometimes needs privileges to access a table or view that has a different owner. To grant privileges to a trigger or stored procedure, include the keywords TRIGGER or PROCEDURE, as appropriate, before the name of the module.

Here, the procedure COUNT_CHICKENS is granted INSERT permission for the PROJ_DEPT_BUDGET table:

GRANT INSERT ON PROJ_DEPT_BUDGET TO PROCEDURE COUNT_CHICKENS;

Granting the EXECUTE Privilege

To use a stored procedure, users, triggers, or other stored procedures need the EXECUTE privilege on it. If a view selects output fields from a selectable stored procedure, the view must have the EXECUTE privilege, not the SELECT privilege.

The simplified syntax pattern is

GRANT EXECUTE
ON PROCEDURE <procedure-name>
TO <grantee>;
<grantee> = [ PROCEDURE <procedure-name> [, <procedure-name> [, ..]]]
[ TRIGGER <trigger-name> [, <trigger-name [, ...]]]
[ VIEW <view-name> [, <view-name> [, ...]]]
| <role-name | <user-or-list> | PUBLIC
[WITH GRANT OPTION];

A stored procedure or trigger needs the EXECUTE privilege on a stored procedure whose owner is not the same as its own. Note that a trigger is owned by the owner of the table that owns the trigger.

If your GRANT EXECUTE statement is granting privileges to PUBLIC, no other types of grantees can be listed as TO arguments.

Here, the GRANT EXECUTE statement grants the privilege on the procedure CALCULATE_BEANS to two ordinary users, FLATFOOT and KILROY, and to two stored procedures whose owners are not the owner of CALCULATE_BEANS:

GRANT EXECUTE ON PROCEDURE CALCULATE_BEANS
TO FLATFOOT,
KILROY,
PROCEDURE DO_STUFF, ABANDON_OLD;

Privileges on Views

Privileges on views are somewhat complicated. The owner of the view needs to grant the SELECT privilege to users, just as a table owner would. The complications start if the view is updateable—either naturally or through view triggers —or the view involves other views or selectable stored procedures. Data changes on an updateable view are actually made to the base tables. Unless the owners of the base objects have already granted the user the applicable rights (INSERT, UPDATE, DELETE, EXECUTE) on the base tables and objects, and any selectable stored procedures or views, the user will need to acquire them from the view’s owner.

REFERENCES privileges are not applicable to views, except under one (usually avoidable) situation. If a view uses a table that has foreign keys to other tables, the view needs REFERENCES privileges to those other tables if the tables themselves are not used in the view.

Multiple Privileges and Multiple Grantees

It is possible to grant several privileges in one statement and to grant one or more privileges to multiple grantee users or objects.

Multiple Privileges

To give a grantee several privileges on a table, name the granted privileges in a comma-separated list. The following statement assigns INSERT and UPDATE permissions on the DEPARTMENT table to user CHALKY:

GRANT INSERT, UPDATE ON DEPARTMENT TO CHALKY;

A list of privileges can be any combination, in any order, of SELECT, INSERT, UPDATE, DELETE, and REFERENCES. EXECUTE has to be assigned in a separate statement, on its own.

The REFERENCES privilege cannot be assigned to a view.

The ALL Privilege

The ALL privilege combines SELECT, INSERT, UPDATE, DELETE, and REFERENCES all in one package. For example, the following statement grants CHALKY the whole package of permissions for the DEPARTMENT table:

GRANT ALL ON DEPARTMENT TO CHALKY;

You can also assign the ALL package to triggers and procedures. In this statement, the procedure COUNT_CHICKENS gets full rights to the PROJ_DEPT_BUDGET table:

GRANT ALL ON PROJ_DEPT_BUDGET TO PROCEDURE COUNT_CHICKENS;

Privileges for Multiple Users

Several syntaxes enable you to grant privileges to multiple users in a single statement. You can assign privileges to

  • A list of named users or procedures
  • A UNIX group
  • All users (PUBLIC)
  • A role (then assign that role to a user list, to PUBLIC, or to a UNIX group)

To a List of Named Users

To assign the same access privileges to a number of users in a single statement, provide a comma-separated list of users in place of the single user name.

The following statement gives INSERT and UPDATE permissions on the DEPARTMENT table to MICKEY, DONALD, and HPOTTER:

GRANT INSERT, UPDATE ON DEPARTMENTS TO MICKEY, DONALD, HPOTTER;

To a List of Procedures

To assign privileges to several procedures in a single statement, provide a comma- separated list of procedures. Here, two procedures get privileges in one statement:

GRANT INSERT, UPDATE
ON PROJ_DEPT_BUDGET
TO PROCEDURE CALCULATE_ODDS, COUNT_BEANS;

To a UNIX Group

Operating system account names on Linux/UNIX are accessible to Firebird security through a feature of Firebird privileges that is not standard SQL. A client running as a UNIX user adopts that user identity in the database, even if the account is not defined in the Firebird security database.

The machine accessing the server must be listed as a trusted host on the server (files /etc/host.equiv or /etc/gds_host.equiv, or in .rhost in the user’s home directory on the server). On connecting, the user gets logged in under its group identity, as long as it does not supply its Firebird user name and password as a connection parameter— Firebird credentials overrule UNIX credentials.

Linux/UNIX groups share this behavior: SYSDBA or a Superuser can assign SQL privileges to UNIX groups. Any operating system–level account that is a member of the group inherits the privileges granted to the group, for example:

GRANT ALL ON CUSTOMER TO GROUP sales;

To All Users (PUBLIC)

To assign the same access privileges on a table to all users, grant the privileges to PUBLIC. PUBLIC encompasses only users—not triggers, procedures, views, or roles.

GRANT SELECT, INSERT, UPDATE ON DEPARTMENT TO PUBLIC;

Privileges granted to users by way of PUBLIC can be revoked only by revoking them from PUBLIC. You can’t, for example, revoke a privilege from CHALKY that CHALKY acquired as a member of PUBLIC.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics