Security Scripts - Firebird
Interview Questions
All Tutorials
Job SearchIf you have stayed with this chapter so far, no doubt you have reached the conclusion that implementing SQL privileges is one helluva lot of typing. Well, you’d be right. In reality, we don’t use interactive methods to do this job. We write scripts—or, rather, we write stored procedures that write the scripts for us.
Generally, if we have a good privileges scheme worked out, we can generate a couple of scripts that load up our roles and general permissions. Usually, a manually written script is wanted for EXECUTE permissions, since it is fairly difficult to work up a formula for those.
With the advent of the EXECUTE STATEMENT statement in Firebird 1.5, which enables us to get past Firebird’s inability to execute DDL statements in PSQL, we can bulk-load permissions directly to the database, through a stored procedure. An example of such a procedure is in Listing later in this section.
For those who love a tool with a graphical interface, there are plenty of them around. Most provide a utility that automates the creation of security scripts; some carry through and install the permissions directly for you.
Creating a Script
The author prefers to generate a security script. It can be tested and annotated, and it provides most of the documentation needed for QA and as a basis for custom deployments. An example of such a script is the following listing.
For the script, we might use an external file into which to output the script. However, the Permscript procedure listed here is designed to run in isql and to pass its output to a text file from there.
Procedure to Generate Permissions Script
/* (c) Helen Borrie 2004, free for use and modificationunder the Initial Developer's Public License */
SET TERM ^;
CREATE PROCEDURE PERMSCRIPT (
CMD VARCHAR(6), /* enter 'G' or 'R' */
PRIV CHAR(10), /* a privilege, or 'ALL' or 'ANY' */
USR VARCHAR(31), /* a username */
ROLENAME VARCHAR(31), /* a role, existing or not */
GRANTOPT SMALLINT, /* 1 for 'WITH GRANT [ADMIN] OPTION' */
CREATE_ROLE SMALLINT) /* 1 to create new role ROLENAME */
RETURNS (PERM VARCHAR(80)) /* a permission statement, theoretically */
AS
DECLARE VARIABLE RELNAME VARCHAR(31); /* for a table or view name */
DECLARE VARIABLE STRING VARCHAR(80) = ''; /* used in proc */
DECLARE VARIABLE STUB VARCHAR(60) = ''; /* used in proc */
DECLARE VARIABLE VUSR VARCHAR(31); /* username for 'TO' or 'FROM' */
DECLARE VARIABLE COMMENTS CHAR(20) = '/* */';
BEGIN
/* Necessary for some UI editors */
IF (ROLENAME = '') THEN ROLENAME = NULL;
IF (USR = '') THEN USR = NULL;
IF (PRIV = '') THEN PRIV = NULL;
/* Not enough data to do anything with */
IF ((PRIV IS NULL AND ROLENAME IS NULL) OR USR IS NULL) THEN EXIT;
/* If there's a rolename, we'll do stuff with it */
IF (ROLENAME IS NOT NULL) THEN
BEGIN
/* If a role name is supplied, create the role if requested */
IF (CREATE_ROLE = 1) THEN
BEGIN
PERM = 'CREATE ROLE '||ROLENAME||';';
SUSPEND;
PERM = 'COMMIT;';
SUSPEND;
PERM = COMMENTS;
SUSPEND;
END
VUSR = ROLENAME;
END
/* If there's a rolename, we'll apply the permissions to the role and grant the role to the supplied user */
ELSE/* We are not interested in the role: permissions are just for user */
VUSR = USR;
/* Decide whether it's a GRANT or a REVOKE script */
IF (CMD STARTING WITH 'G') THEN
STUB = 'GRANT ';
ELSE
STUB = 'REVOKE ';
IF (ROLENAME IS NOT NULL) THEN
BEGIN
IF (STUB = 'GRANT') THEN
BEGIN
/* Grant the role to the user */
STRING = STUB||ROLENAME||' TO '||USR;
IF (GRANTOPT = 1) THEN
STRING = STRING||' WITH ADMIN OPTION ;';
END
ELSE
STRING = STUB||ROLENAME||' FROM '||USR||';';
PERM = STRING;
SUSPEND;
PERM = COMMENTS;
SUSPEND;
END
/* If ANY was passed in as privilege, create all perms separately */
IF (PRIV = 'ANY') THEN
STUB = STUB||'SELECT,DELETE,INSERT,UPDATE,REFERENCES ON ';
ELSE
STUB = STUB||PRIV||' ON ';
/* Cycle through the table and view names and create a statement for each */
FOR SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME NOT STARTING WITH 'RDB$'
INTO :RELNAME DO
BEGIN
STRING = STUB||:RELNAME||' ';
IF (CMD STARTING WITH 'G') THEN
STRING = STRING||'TO ';
ELSE
STRING = STRING||'FROM ';
STRING = STRING||VUSR;
IF (CMD STARTING WITH 'G'
AND GRANTOPT = 1 AND ROLENAME IS NULL) THEN
STRING = STRING||' WITH GRANT OPTION ;';
ELSE
STRING = STRING||' ;';
PERM = STRING;
SUSPEND;
END
PERM = COMMENTS;
SUSPEND;
END ^
SET TERM ;^
Creating and Running the Script
Go to the Firebird /bin directory and start isql under the SYSDBA login, connecting to the database. We will use the procedure to make a script that adds a role 'MANDRAKE', grants the role to user USER1, and then sets up the permissions for the role. Then it will do the same thing again with an existing role 'PURPLE' for user USER2:
SQL> OUTPUT L:DATAEXAMPLESPERMSCRIPT.SQL;SQL> SELECT * FROM PERMSCRIPT ('G', 'ALL', 'USER1', 'MANDRAKE', 1, 1);
SQL> COMMIT;
SQL> SELECT * FROM PERMSCRIPT ('G', 'ALL', 'USER2', 'PURPLE', 1, 0);
SQL> COMMIT;
SQL> OUTPUT;
SQL> INPUT L:DATAEXAMPLESPERMSCRIPT.SQL;
SQL> COMMIT;
SQL> SHOW GRANT;
That’s all there is to it. You will get an error message when the INPUT utility encounters the non-SQL “window dressing” printed by OUTPUT, but it will not interfere with writing the permissions.
Installing Perms Directly from a Procedure
The procedure grant_perms, shown in Listing, is basically the same procedure. Instead of producing a set of output lines for isql to run as a script, it actually installs the permissions directly, via EXECUTE STATEMENT. This technique isn’t available for Firebird 1.0.x.
Permissions Procedure
/* (c) Helen Borrie 2004, free for use and modificationunder the Initial Developer's Public License */
SET TERM ^;
CREATE PROCEDURE GRANT_PERMS
(CMD VARCHAR(6),
PRIV CHAR(10),
USR VARCHAR(31),
ROLENAME VARCHAR(31),
GRANTOPT SMALLINT)
AS
DECLARE VARIABLE RELNAME VARCHAR(31);
DECLARE VARIABLE EXESTRING VARCHAR(1024) = '';
DECLARE VARIABLE EXESTUB VARCHAR(1024) = '';
BEGIN
IF (ROLENAME = '') THEN ROLENAME = NULL;
IF (USR = '') THEN USR = NULL;
IF (PRIV = '') THEN PRIV = NULL;
IF ((PRIV IS NULL AND ROLENAME IS NULL) OR USR IS NULL) THEN EXIT;
IF (CMD STARTING WITH 'G') THEN
EXESTUB = 'GRANT ';
ELSE
EXESTUB = 'REVOKE ';
IF (ROLENAME IS NOT NULL) THEN
BEGIN
IF (EXESTUB = 'GRANT') THEN
BEGIN
EXESTUB = EXESTUB||ROLENAME||' TO '||USR;
IF (GRANTOPT = 1) THEN
EXESTUB = EXESTUB||' WITH ADMIN OPTION';
END
ELSE
EXESTUB = EXESTUB||ROLENAME||' FROM '||USR;
EXECUTE STATEMENT EXESTUB;
END
ELSE
BEGIN
IF (PRIV = 'ANY') THEN
EXESTUB = EXESTUB||'SELECT,DELETE,INSERT,UPDATE,REFERENCES ON ';
ELSE
EXESTUB = EXESTUB||PRIV||' ON ';
FOR SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME NOT STARTING WITH 'RDB$'
INTO :RELNAME DO
BEGIN
EXESTRING = EXESTUB||:RELNAME||' ';
IF (CMD STARTING WITH 'G') THEN
EXESTRING = EXESTRING||'TO ';
ELSE
EXESTRING = EXESTRING||'FROM ';
EXESTRING = EXESTRING||USR;
IF (GRANTOPT = 1) THEN
EXESTRING = EXESTRING||' WITH GRANT OPTION';
EXECUTE STATEMENT EXESTRING;
END
END
END ^
SET TERM ;^
If you want to experiment with these scripts yourself and modify them to suit your needs, you can find the sources in the Downloads section in the file permscripts.sql.
