If 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 modification
/* If there's a rolename, we'll apply the permissions to the role and grant the role to the supplied user */ELSE
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;
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 modification
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.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.