At 02:48 PM 15/03/2004 +0000, you wrote:

>What is the easiest way of finding out what permissions a particular
>role or user has been assigned?

This SP might help:

SET TERM ^ ;
CREATE PROCEDURE SP_PRIVILEGES
RETURNS (
Q_ROLE_NAME VARCHAR(31),
ROLE_OWNER VARCHAR(31),
USER_NAME VARCHAR(31),
Q_USER_TYPE VARCHAR(7),
W_GRANT_OPTION CHAR(1),
PRIVILEGE CHAR(6),
GRANTOR VARCHAR(31),
QUALIFIED_OBJECT VARCHAR(63),
Q_OBJECT_TYPE VARCHAR(7) )
AS
DECLARE VARIABLE RELATION_NAME VARCHAR(31);
DECLARE VARIABLE FIELD_NAME VARCHAR(31);
DECLARE VARIABLE OWNER_NAME VARCHAR(31);
DECLARE VARIABLE ROLE_NAME VARCHAR(31);
DECLARE VARIABLE OBJECT_TYPE SMALLINT;
DECLARE VARIABLE USER_TYPE SMALLINT;
DECLARE VARIABLE GRANT_OPTION SMALLINT;
DECLARE VARIABLE IS_ROLE SMALLINT;
DECLARE VARIABLE IS_VIEW SMALLINT;
BEGIN
FOR SELECT
RTRIM(CAST(RDB$USER AS VARCHAR(31))),
RDB$USER_TYPE,
RTRIM(CAST(RDB$GRANTOR AS VARCHAR(31))),
RTRIM(CAST(RDB$RELATION_NAME AS VARCHAR(31))),
RTRIM(CAST(RDB$FIELD_NAME AS VARCHAR(31))),
RDB$OBJECT_TYPE,
RTRIM(CAST(RDB$PRIVILEGE AS VARCHAR(31))),
RDB$GRANT_OPTION
FROM RDB$USER_PRIVILEGES
INTO :USER_NAME, :USER_TYPE, :GRANTOR, :RELATION_NAME,
:FIELD_NAME, :OBJECT_TYPE, :PRIVILEGE, :GRANT_OPTION
DO BEGIN
SELECT
RTRIM(CAST(RDB$OWNER_NAME AS VARCHAR(31))),
RTRIM(CAST(RDB$ROLE_NAME AS VARCHAR(31)))
FROM RDB$ROLES
WHERE RDB$ROLE_NAME = :USER_NAME
INTO :ROLE_OWNER, :ROLE_NAME;
IF (ROLE_NAME IS NOT NULL) THEN
Q_ROLE_NAME = ROLE_NAME;
ELSE
BEGIN
Q_ROLE_NAME = '-';
ROLE_OWNER = '-';
END
IF (GRANT_OPTION = 1) THEN
W_GRANT_OPTION = 'Y';
ELSE
W_GRANT_OPTION = '';
IS_ROLE = NULL;
SELECT 1 FROM RDB$ROLES
WHERE RDB$ROLE_NAME = :RELATION_NAME
INTO :IS_ROLE;
IF (IS_ROLE = 1) THEN
QUALIFIED_OBJECT = '(Role) '||RELATION_NAME;
ELSE
BEGIN
IF (
(FIELD_NAME IS NULL)
OR (RTRIM(FIELD_NAME) = '')) THEN
FIELD_NAME = '';
ELSE
FIELD_NAME = '.'||FIELD_NAME;
QUALIFIED_OBJECT = RELATION_NAME||FIELD_NAME;
END
IF (OBJECT_TYPE = 0) THEN
BEGIN
IS_VIEW = 0;
SELECT 1 FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME = :RELATION_NAME
AND RDB$VIEW_SOURCE IS NOT NULL
INTO :IS_VIEW;
IF (IS_VIEW = 1) THEN
OBJECT_TYPE = 1;
END
EXECUTE PROCEDURE SP_GET_TYPE(:OBJECT_TYPE)
RETURNING_VALUES :Q_OBJECT_TYPE;
EXECUTE PROCEDURE SP_GET_TYPE (:USER_TYPE)
RETURNING_VALUES :Q_USER_TYPE;
SUSPEND;
END
END^