Chester R. West II - 1998

In most applications developed in today’s world, security issues come up. Whether it is the at home record keeping system or the big time corporate all encompassing super duper system. So why would you want to consider implementing security within your system(s)? First, and most obviously, you will want to keep those unwanted people out of your system and your data. We all know about these dark scoundrels that loom out there. These people are just itching to get in and steal and/or destroy your data. Second, and less obviously, you will want to keep your most trusted workers or family members out of your system and data. That is right, even those you trust must be kept out. Not because they are going to maliciously steal and destroy your data, but because they may get where they should not be. And many times, they do not even know they have arrived at a place where they should not be. Usually, the hard workers just find those short cuts or shortcomings by trying to do their job better and faster. And in some instances, they are just curious or bored. In many of today’s corporate systems, there is information that contains private personal information about customers and/or employees (salaries, social security numbers, etc.). Or, there may be financial information about your own company or your customers. Someone, who finds out something they should not, can cause damage within a company. This information can lead to disgruntled employees. Even worse, a user that has the ability to change information they should not may not only cause internal problems for a company, but also result in legal problems. These things should be protected under the ‘need to know’ rule. The United States military recognizes and utilizes the ‘need to know rule.’ Even the highest ranked person cannot get into information and/or areas if they do not pass the ‘need to know’ rule. So to protect your company, clients and employees, we need to apply security to our analysis, design and development steps.

Well, yes and no. The DBA can only do so much to help you out. Let us review some of the things the DBA can do. The DBA can control who has a user-id/password on the system. The DBA can restrict the insert, update, delete and even select on database objects and restrict execution of stored database procedures. The DBA can even group these privileges using database roles, which can also have passwords. So this will for the most part, keep out the unwanted user. What does it do for the trusted user we discussed earlier though? Not much, without your help. If the trusted user can get to the information via your Forms application, then they can get to it via other tools too (SQL*Plus, Excel, Access, etc.). To solve this problem, we can have the DBA grant the database roles we discussed, as non-default roles with passwords. No privileges will be granted except via a role. What this does is keep the privileges from being granted until the role is activated via a ‘SET ROLE’ using the password assigned. You can embed code into the start-up of your forms and reports. This code will need to call the stored procedure DBMS_SESSION.SET_ROLE (). Also, do not forget to ensure that your routine is run if the user is allowed to reconnect from your form. So, is the job complete? Well, lets review what we have so far. Only users with user-ids can get into the database. Those users cannot access the data except via your forms and reports. And they can only interact with the tables granted by the DBA. Using all the tricks found in the DBA bag-o-tricks, data can be completely secured from that unwanted user. And it can be partially secured from the trusted user. But there is still more work to be done. Remember that the DBA can only secure access to the database instance and the database objects (tables, views, procedures, etc.) Now it is your turn to further the security process in the application.

As mentioned above, one of the basic things we can do is grant roles that only have access to data after a SET ROLE command is issued in the user’s session. Unless each form and/or report requires a separate session, I recommend creating the infamous ‘Main Screen’ that comes up first, and implement the security mentioned before into it’s startup code. This makes for a bit easier implementation. Since we now have the data covered, how do we keep users out of forms and reports they should not be in? Oracle actually provides a neat feature to be implemented within the form menu that will implement security based on user roles. Basically, the security property is turned on and database roles are assigned to each menu item a user with that role is assigned. You define the database roles in the same manor the DBA does to secure database objects. The database roles to control table access will normally not apply to how forms and reports are accessed. So, the roles created here will be business rule specific. For example, there may be a role for the Human Resources department called HR. This role will be used to allow all HR users to access the forms and reports they need on a daily basis and keep them out of what they do not need to get into. But, most organizations will also require at least two roles per business unit. The second role being for the manager or administrator role. So, now we also create a Human Resources Manager role, we will call it HR_MGR. And we will grant the HR role to the HR_MGR so that the managers have access to all HR forms and reports as well as any forms and reports for the HR manager only. If you have not seen the potential problem starting yet, let me explain. Right now we have at least two roles per business unit that will use the forms. So multiply the total number of business units accessing the database by two. For our purposes, lets say there are ten business units, thus twenty roles to be created and maintained by the DBA in the database that have virtually nothing to do with database objects. You also have twenty roles that will need to be manually assigned and maintained in your menu modules. And in today’s corporate world, we get reorganized about once a year. So I hope your role names are generic enough to last a few years. Do you see the problem now? Well it gets worse. Many organizations will not only have managers and workers, but they will also have some people that do specialized tasks within the unit. Let’s use our HR role as an example. Lets say that the HR manager has decided that the senior HR person will also be able to run the month end analysis reports, normally reserved for use by the manager. This may be necessary to cover the out-of-office obligations the manager has, etc. For database security reasons, you do not want the HR manager giving out their password. So we now also need to create an HR_MNTH_END role for the reports, and assign it as needed. This will grow out of control very quickly once a manager starts handing out different responsibilities. Thus, our original twenty roles can very quickly double, triple or more. And this means it is not only hard for you to maintain the menu modules, but the DBA you depend on really hates you for requiring him to maintain so many roles that do not secure any database objects. The issue remains though, we need to be more secure. So let’s look for a solution from a different angle.

What I am proposing is basic enough to implement in any Developer/2000 project as well as easy enough to fit into any Designer/2000 project. The basic concept is used in many of Oracle’s own applications, though it may be implemented differently. What we need is a place to store the user-ids that will access the system. A place to store the names of the modules (forms, reports, etc.) that make up the application. A place to store and assign those same application/business roles that we discussed earlier. Each role will be assigned to the modules it can access and also to the users the role is valid for. Finally, we need a client side methodology used to validate against the tables prior to running any module that is easy to implement.

First, we will explore the database design of this security scheme. As mentioned above, we will need to store module information, application user information and the security role definitions. Then we will need to be able to map role access to modules as well as role privileges to application users. When defining the module information, we will not only need the name of the module, but also an identification of what type of module it is (i.e. SCREEN, REPORT and GRAPHICS). Knowing the type of module will cover us in situations in which multiple modules have the same name. And for reporting purposes, we will want to have a description for each module, user and role definition. Now let us also think outside the box and consider a true enterprise solution. We need a way to ensure that all applications can be secured using one set of tables. If we tie all of the tables together with an application definition, we can then manage all of our security, for every application, at one point with one set of maintenance forms. Figure 1 shows a possible table layout to fill these needs. This database schema design provides us a unique advantage from a Designer/2000 standpoint. The structure I am using is designed to allow us to easily write a utility that will move over the application, module, role and user definitions defined in our Designer/2000 repository, into our table structure as well as map them. An example of some of the code that can be written to do this can be seen in code listing 1. I do recommend however that you also create maintenance screens and only populate initially out of Designer/2000. You do not want to turn your Designer/2000 repository into a security system too.

Now we will explore how to build code for your application to use the database schema we just defined. The first thing we need is a simple procedure to check and see if the user has at least one role assigned that is required to run the module. This program is really no more than a SQL statement. Once we have our CHECK_SECURITY() function, we could simply call it prior to calling any module, see code listing 2. Thus, putting a wrapper around all calls.

While this will work, it may not be a best solution. It not only requires extra coding on both sides of every call to a module, but also requires a bit of understanding and discipline to be had by future maintenance programmers. What I suggest is that we take advantage of PL/SQL’s ability to overload functions. All of the standard CALL_FORM, OPEN_FORM, RUN_PRODUCT, etc. functions exist in a built-in package called STANDARD. What we can do is create a client side PL/SQL Library (PLL) that holds a package called SECURE_CALL. Within this package, each of the standard functions will be overloaded. Within the overloaded functions, we can call our CHECK_SECURITY() function. Not only does this provide encapsulation of the security process, but the mere name of the package gives the indication of security being performed on a call as shown in code listing 3.

Another advantage we gain, is that the PLL can be attached to any FORM, MENU or REPORT for use. Thus, the use of the PLL provides the ultimate in flexible, portable, reusable and easily maintained client server security. In a Designer/2000 project, having a PLL makes for easy implementation by simply attaching it to your form and report templates. You can define the call method for a language in Designer/2000, see figure 2. This however only affects a portion of your calls (i.e. Menus). Some modules will still need to be manually adjusted, (i.e. Forms calling other Forms). The same property can be set on the module definition itself as well as in the MENU – GENERATE OPTIONS preferences. Another disadvantage to this method is that as new releases of Developer come out, you will need to modify your security PLL to match any changes or additions.

While the ideas presented so far can provide a quality basic solution for your security needs, it still may be incomplete. The solution is however flexible in that in can be used as the building blocks for more advanced ideas. For example, you may want to allow users into all forms and reports but only insert, update and delete on blocks they have access too. Using the model above, we could expand the module definition to include block and frame definitions. Then we could map roles to those blocks and frames. Then our code can do lookups in the When-New-Form-Instance trigger and modify the block properties accordingly. We could also build a table that lists all of the tables within an application. Then have our forms go against views to the tables and include a security check in the where clause of the view. Using our initial construct we can stay as simple or get as complicated as our security needs require or imaginations can come up with. The one thing to remember is that security does require an overhead price. The more complicated the security is, the more code that will have to be processed to handle it.

Security is a piece of every application system whether you realize it or not. Many times, security is taken for granted in the analysis and design of an application as a piece of database design. And while this is a piece of it, we have seen that this may not be the complete solution for you. Security should not be taken lightly! And it should also not be limited to keeping out the bad guys. Come up with a definition of all of your security needs and then define a solution that is also an enterprise solution. This means that you can define it once and then simply implement it into your systems. My suggestions hopefully will give you a starting point for your security solution, no matter how complicated it gets. And whether you use mine, Oracle’s, your own or any combination, you will need to consider it. Do not ignore it! Just do it!

Special thanks to Peter Brenner of Tactics who wrote the utility used to migrate the Designer/2000 definitions into our application security tables (code listing 1). Also, thanks to all the other Tactics consultants and First Union analyst that critically reviewed the security method described above.