Granting group privileges and SQL roles.

    At the time IB was born in 1985, standardization regarding SQL was totally absent. Probably SEQUEL was the only known generic query language at such time. But it was not only the lack of DML commands widely accepted the fact that allowed database engine implementors to come up with their own custom, proprietary solutions, but the lack of DDL standardization. So, each vendor offered unique management solutions.

The primary language for IB was GDML until v3. For IB4, a great effort to standardize the server high level language was done as a recognition that SQL was the accepted solution, so SQL92 was a taken as a model. However, SQL features like group management, DATE-only and TIME-only data types and quoted identifiers weren't part of IB4. Also, in v4, the DSQL interface (or DSQL utility) became a part of the server and not of the client and QLI disappeared in the Windows version, as the same time GPRE was given much less importance, to reflect the focus on dynamic applications rather on embedded SQL. As QLI disappeared, there was no easy way to setup group of users in IB4.X for Windows, since the system table rdb$security_classes is managed through group privilege commands done in GDML, the primary language of QLI. So, IB4 was left virtually without group management, although in UNIX you can map users from the operating system.

IB5 changed that limitation and implemented the part of SQL's DDL that allows to create roles and grant or revoke permissions not only on users and procedures (as IB4 did) but to give and deny permissions to SQL roles and to give or retire roles assigned to users or procedures. As a result of the new functionality, not only CREATE ROLE and DROP ROLE are recognized, but that the system table rdb$user_privileges tracks users and roles as well, although rdb$security_classes is still managed automatically behind scenes. While a grant to a user allows WITH GRANT OPTION, the standard demands that a grant to a role allows WITH ADMIN OPTION and IB5 implements it. In theory, you give a user the right to give another user the same rights or a user with such role to give another user the same role. In practice, it's better to avoid those options, since they convert any user in a small sysdba that can grant the same privilege to others until the security of the db becomes very weak and administration, including tracking powerful users, becomes a nightmare.

At connection time, IB doesn't assume a default role for a user and it's in sync with the SQL standard. While the standard allows roles to be granted to roles, you can't do that in IB: roles only can be granted to users or procedures. (When I've written about procedures in this pages, I implicitly included triggers, that are procedures activated by the engine itself in response to an operation. I won't use the word "event" to refer to this operation, since it could be confused with IB events.)

This page was last updated on 2001-01-16 01:40:28