14.2 Running the Application
The security maintenance form we discuss in this chapter was developed using the Oracle Developer 2000 product. It was originally developed in version 4.5 of the Forms module of Developer 2000, and has been migrated to version 5.0. This form works only on users, roles, and system privileges, not on table grants. The use of grants was not a requirement for this particular application.
This application uses data dictionary tables and the APP_ROLES table. This table and its function were described in Chapter 9. This table is one that you create, and it is not part of the data dictionary. This is the only table used by the form whose contents you can modify directly. Changes in all of the other sections of the form are handled by PL/SQL procedures.
14.2.1 Initial Display
Figure 14.1 shows the startup display. There are three sections to the form: user maintenance, role maintenance, and role assignment. The latter also has a part in the role maintenance function.
Figure 14.1. Maintenance form initial display
The top four fields are the user maintenance section. Here a user may be created or dropped and the password changed if (when) the user forgets it. The default and temporary tablespace assignments are also made here. You may think from looking at the initial display format that there's no way to drop a user, but that depends on the status of the username entered in the first field. We'll explain more about this in the next section.
14.2.2 Selecting or Creating a User
If a username is entered that does not exist, a message to that effect appears to the right of the "User to create or modify" field. Let's take an existing user for this example. You can enter the name in the field, or the field may be queried. If you query the field, a pop-up window appears in the upper left corner of the form, as shown in Figure 14.2.
Figure 14.2. Username query pop-up
Here we select the security username. Pressing the "OK" button will move this name into the first field. At this time the form display changes to indicate that this is an existing user, and it now looks like Figure 14.3. Had we just entered an existing username, the same check would have occurred and the message would still have been displayed.
Figure 14.3. After selecting or entering an existing username
As you can see, now the form advises you that the user exists, and a new button appears that will allow you to drop the user. Also note that the button previously labeled "Create user" now says "Change PASSWORD." In addition, after you navigate into the "Available roles" block, that block is automatically populated with all the roles in the data dictionary. Finally, the two checkboxes to the right of the roles fields indicate the status of the role with respect to the username. It may be assigned to the user, may be the user's default role, or may be both. As you move through the "Available roles" records, the checkboxes change to indicate the relationship of the role to the user.
14.2.3 Assigning Roles
If a role is not assigned to the user, the "Assigned to user?" checkbox will be blank but functional while the "Default role?" checkbox will be dimmed, indicating it is disabled (similar to the "Drop this role" button below it). This is illustrated in Figure 14.4, where we have moved the cursor to the CONNECT role.
Figure 14.4. Status of the CONNECT role relative to the security user
The "Default role?" checkbox is disabled because if a role is not assigned, it cannot possibly be made a default role. If you check the "Assigned to user?" checkbox, the current role becomes assigned to the user at that moment and the "Default role?" checkbox becomes enabled. Checking the "Default role?" checkbox will cause the role to be designated as a default role while preserving all existing default roles since a user can have more than one. The opposite action occurs if a box is checked and you uncheck it. The role is then either removed from being a default, or removed from the user altogether.
There is a limit to the maximum number of roles a user can have enabled at one time. This limit is set by the MAX_ENABLED_ROLES parameter in the INIT.ORA file, and it defaults to 20 if you do not set it. You can assign more than this maximum value, but if you make more than the maximum as the default, then the user will not be able to log in. Few users ever need more than two or three default roles; most do not need more than five or six total.
14.2.4 Creating a New Role
In Figure 14.5, we decided to create the CDDEPTHD role. The window is accessed by pressing the "Manage roles" button. After entering the role name, we have the option of entering a password.
Figure 14.5. Creating a new role
When we enter a password for the role and move out of the field, the "ADD TO ROLE TABLE" checkbox becomes enabled. If we check this box, the Role Security Table screen will be displayed as shown in Figure 14.6. You can see in Figure 14.6 that the role name and password are already pre-populated with the values we just entered. Notice that these fields are disabled (dimmed) and the values cannot be changed at this time. If the password has to be changed, that can be done later by using the "ROLE SECURITY TABLE" button to navigate directly to the Application Role Security screen. This part of the form uses the APP_ROLES table. A PL/SQL procedure is executed when the password is changed to apply the change to the data dictionary.
Figure 14.6. Adding role to the Role Security Table
All we have to do is add is the "Program" and "Application" information. (Remember that we mentioned these in Chapter 9.) When completed, we press the "Create role" button to the left on the main screen to create the role and add it to the APP_ROLES table.
14.2.5 Adding System Privileges to Roles
After returning to the initial form, we navigate into the "Available roles" block and move to the "ADMIN_USR_ROLE" role. By pressing the "System privs" button, the form shown in Figure 14.7 is displayed. This shows all of the available system privileges. These may be assigned or de-assigned to the currently selected role by checking or unchecking the checkbox to the right of the privilege.
Figure 14.7. Adding system privileges to a role
14.2.6 Reserving the Security of the Security Maintenance Form
There is a funny thing about the Security Maintenance form: the user has privileges to grant anything to anybody, but cannot grant anything to himself. How did this happen? This user has no privileges on any of the tables in the system other than the APP_ROLES table, but this user can create any user and grant any privilege to that user or any existing user. For this reason, if you implement an application such as the one we describe in this chapter, there should be only one person who uses it. If you want more than one, you must create another security user with a different password.
We have a recommendation for how to ensure the security of the passwords used for this application while making sure there is a contingency plan. The user given the job of using this application should immediately change his or her account password and write the new password on a paper, put that into an envelope, seal it, and sign across the seal. Then that person should give the envelope to the security manager for safekeeping. This way, there is only one person who knows the password. If some grants are made that should not have been made, and you have an audit trail implemented behind this application (not hard to do), then you will know who did it.
Security books usually tell you never to write down your password, but that rule really applies to keeping the password in your wallet or purse, or on the back of a favorite photograph. The approach we've suggested allows the password to be available if an emergency arises and the person who knows the password is not available. Of course, if the envelope is opened, then the security user must change the password as soon as possible and prepare another envelope.
14.2.7 How Does the Code Work?
You might be wondering how the application does its work, since most of the fields on the form are from data dictionary tables, and we cannot modify those directly with conventional INSERT, UPDATE, or DELETE SQL statements. The changes are made by dynamically building SQL statements using the CREATE and ALTER USER commands. But this causes another problem within PL/SQL. Most of you may be familiar with the SQL substitution variables as are frequently used in "canned" program. Let's assume you have a program, SHODEPTS.SQL , with the following code.
SELECT deptno FROM &1 GROUP BY deptno ;
Since there are at least two tables in the schema with the deptno column, you can run this program and pass the table to be used as argument 1. This would be either of the following:
@shodepts emp @shodepts dept
Or you can let the program prompt you for the value of &1.
Unfortunately, you cannot do this type of substitution directly in PL/SQL. While you can build the statement string dynamically, the ability to execute a variable does not exist. But you can do this by using the DBMS_SQL built-in package provided in the standard database installation. This package allows dynamic SQL to be built and executed within PL/SQL. We will explain how that package is used within the application.
When the application was initially developed it was discovered that the various pieces of code to manage the user all had much in common when the DBMS_SQL package was being used. Because of this, it was decided to put all of these code pieces into a package. This package was called MG_USR, a shorthand name for "manage user."
220.127.116.11 About the mg_usr package
There are three actions to be performed on a user:
Create the user
Drop the user
Change the user
Although the CREATE USER command can be quite complex, it was decided to use the simplest part of the CREATE USER command in one function and use the ALTER USER command in another function. Dropping a user requires some decision logic since a user can simply be dropped or the user and all of his or her objects can be dropped. Finally, changing a user could involve the password or the default and temporary tablespaces. Because all user access to objects was to be controlled by roles, object granting was not included. Also, role assignment is handled by another part of the application.
The mg_usr package is created in two parts : the package declaration and the package body declaration. A package declaration identifies the procedures and functions that will be available for calling. The mg_usr package has three functions, as shown below, and as you can see, the package declaration is quite small:
PACKAGE mg_usr IS FUNCTION cr_usr (un IN VARCHAR2, pw IN VARCHAR2) RETURN NUMBER; FUNCTION dr_usr (un IN VARCHAR2, casc IN BOOLEAN DEFAULT FALSE) RETURN NUMBER; FUNCTION cg_usr (un IN VARCHAR2, action IN VARCHAR2, value IN VARCHAR2) RETURN NUMBER; END mg_usr;
The three actions we identified are all implemented as function:
cr_usr creates the user
dr_usr drops the user
cg_usr changes the user
These functions will return a status code so the program that calls them can determine if they were successful or not. By placing the test for success in the calling code, error handling can be made specific to the event under which the call was made. This, as you will see, isn't really necessary here, but it is considered to be best practice and was followed for consistency in the application.
The cr_usr function requires a username and password to work. These are passed as the un and pw arguments. The function returns a if it was successful, and a 1 if it failed.
Function dr_usr requires the username to be dropped, and a TRUE or FALSE for the casc argument that is a BOOLEAN datatype. This argument defaults to FALSE if it is not provided. The value of casc will be used to determine if the CASCADE option should be used when the user is dropped.
Finally, the cg_usr function is used to change the user. The three arguments are: un, representing the username of the user to be changed; action, representing what is to be done to the user; and value, representing the particular attribute that is to be applied to the user.
18.104.22.168 About the mg_usr package body
The package body supplies the code to be executed. The body of mg_usr has some variables declared before the functions are defined. These cannot be used by an external program, so they are said to be private to the package body. The code for the package body is shown here:
PACKAGE BODY mg_usr IS cid INTEGER; stmt VARCHAR2(200); FUNCTION cr_usr (un IN VARCHAR2, pw IN VARCHAR2) RETURN NUMBER IS BEGIN cid := DMBS_SQL.OPEN_CURSOR; stmt := 'create user 'un' identified by 'pw; DBMS_SQL.PARSE(cid, stmt, 2); DBMS_SQL.CLOSE_CURSOR(cid); RETURN 0; EXCEPTION WHEN OTHERS THEN RETURN 1; END cr_usr; FUNCTION dr_usr (un IN VARCHAR2, casc IN BOOLEAN DEFAULT FALSE) RETURN NUMBER IS BEGIN cid := DMBS_SQL.OPEN_CURSOR; stmt := 'drop user 'un; IF casc THEN stmt := stmt' cascade'; END IF; DBMS_SQL.PARSE(cid, stmt, 2); DBMS_SQL.CLOSE_CURSOR(cid); RETURN 0; EXCEPTION WHEN OTHERS THEN RETURN 1; END dr_usr; FUNCTION cg_usr (un IN VARCHAR2, action IN VARCHAR2, value IN VARCHAR2) RETURN NUMBER IS BEGIN cid := DMBS_SQL.OPEN_CURSOR; stmt := 'alter user 'un; IF UPPER(action) = 'PWD' THEN stmt := stmt' identified by 'value; ELSIF UPPER(action) = 'DEFTS' THEN stmt := stmt' default tablespace 'value; ELSIF UPPER(action) = 'TMPTS' THEN stmt := stmt' temporary tablespace 'value; ELSIF UPPER(action) = 'DROP' THEN stmt := 'drop user 'un' cascade'; ELSE MESSAGE('Unknown action: 'action); RETURN 1; END IF; DBMS_SQL.PARSE(cid, stmt, 2); DBMS_SQL.CLOSE_CURSOR(cid); RETURN 0; EXCEPTION WHEN OTHERS THEN RETURN 1; END cg_usr; END;
You can see that each function repeats the same name, argument list, and return value as declared in the package specification. The critical parts of code in each of the functions are the following four lines that appear in the cr_usr function:
cid := DMBS_SQL.OPEN_CURSOR; stmt := 'create user 'un' identified by 'pw; DBMS_SQL.PARSE(cid, stmt, 2); DBMS_SQL.CLOSE_CURSOR(cid);
These appear in various forms in each of the other functions. The first line uses the DBMS_SQL.OPEN_CURSOR function to open a cursor or memory area that will be used to execute the SQL statement. The variable cid is used as a reference to this cursor. The SQL statement to be executed is then assembled and saved in the stmt variable. This is not necessary, but is considered best practice as it makes subsequent statements easier to read.
Having obtained the cursor and stored the SQL in a variable, the database package DBMS_SQL.PARSE procedure is called. This procedure is given the cursor, the statement, and the constant "2" that identifies which version of the database is in use in this case, version 7 or 8.
| || |
The constant value "2" was used in the original version of this program that used the Oracle7 database. It did not have to be changed for use with an Oracle8 database.
The call to PARSE causes the statement contained in stmt to be executed. Because we are executing what amounts to Data Definition Language (DDL), no further action is necessary since the action completes. Had we used a SELECT, however, additional code would have been required to return the column values.
Although the cursor will be automatically closed when the function completes, it is considered best practice to explicitly close it, and that is done with the DBMS_SQL.CLOSE_CURSOR procedure.
In the dr_usr and cg_usr functions, there is some IF...THEN...ELSE logic to determine just how the SQL to be stored in the stmt variable is to be built. The execution, however, is the same in all functions.
If any part of the function should fail, the code will branch to the exception handler. Because this application was intended for use by an extremely small user group (at most two users), it was decided to only use a return code of "1" if the function failed. However, it would be possible to capture the actual code of the error and return that instead.
Now that the code to do the work has been defined, what type of code is needed in the form to call this package and its functions? This code is written behind a button.
22.214.171.124 Create user button code
The application code is associated with the BTN_CREATE_USER using the WHEN-BUTTON-PRESSED trigger. This is an Oracle Forms built-in trigger that executes the code associated with it. First we will show the code, then discuss it.
DECLARE stat NUMBER := 1; pwd VARCHAR2(35); BEGIN IF :global.usr_exists = 'F' -- User does not exist. THEN IF :ctl_usr_block.ndi_password IS NULL -- No password provided THEN pwd := :ctl_usr_block.ndi_username; -- Use username :ctl_usr_block.ndi_password := pwd; ELSE pwd := :ctl_usr_block.ndi_password; -- Password provided, use it. END IF; stat := mg_usr.cr_usr(:ctl_usr_block.ndi_username, pwd); -- Create user IF stat!= 0 -- Failure. STOP PROCESSING! THEN MESSAGE('Error creating user. Notify DBA.'); RAISE FORM_TRIGGER_FAILURE; END IF; -- Alter the new user and assign the DEFAULT tablespace. stat := mg_usr.cg_usr(:ctl_usr_block.ndi_username, 'DEFTS', :ctl_usr_block.ndi_def_ts); IF stat!= 0 THEN MESSAGE('Error assigning default tablespace. Notify DBA.'); stat := mg_usr.cg_usr(:ctl_usr_block.ndi_username, 'DROP', 'dummy'); RAISE FORM_TRIGGER_FAILURE; END IF; -- Alter the new user and assign the TEMPORARY tablespace. stat := mg_usr.cg_usr(:ctl_usr_block.ndi_username, 'TMPTS', :ctl_usr_block.ndi_temp_ts); IF stat!= 0 THEN MESSAGE('Error assigning temporary tablespace. Notify DBA.'); stat := mg_usr.cg_usr(:ctl_usr_block.ndi_username, 'DROP', 'dummy'); RAISE FORM_TRIGGER_FAILURE; END IF; pop_user_status(TRUE); -- Do some screen maintenance. -- Advise operator that user must be assigned a role to be able to login. MESSAGE('User created. You must assign a role.'); ELSIF :global.usr_exists = 'T' THEN -- User exists, change the password. stat := mg_usr.cg_usr(:ctl_usr_block.ndi_username, 'PWD', :ctl_usr_block.ndi_password); END IF; END;
When the button is pressed, this code block is executed. The logic determines what is to be done. For example, the memory variable :global.usr_exists will contain either an "F" or a "T". If it is a "T", the user exists and all that is being done is to change the user's password. That happens at the bottom of the code in the ELSIF statement. Here you can see the package.function call in the line beginning with stat. Remember that functions return values so you have to have somewhere for the returned value to be captured. Variable stat serves that purpose. The function call identifies both the package and the function separated by a period as shown below (emphasis added):
stat := mg_usr.cg_usr
Here you can see the package name: mg_usr, separated by a period from the cg_usr function that is being called. The arguments are provided within the parentheses. Fields from the form are represented using the :block.field_name syntax. The :ctl_usr_block.ndi_username and :ctl_usr_block.ndi_password are both fields on the form. The :ctl_usr_block is the block name (a section of the form), and the ndi_... is the field name. We used the convention of prefixing all fields that don't belong to a table with "ndi" to mean non-database -item. This convention is considered best practice and helps programmers understand the nature of the items on the form.
If the variable :global.usr_exists contains an "F", there is more work to be done. We have to create the user and assign both the default and temporary tablespaces. If the password field is empty, the form takes the username and uses that as the password. The assumption is that the database will force the user to change it on the first login. After that is done, several calls are made to create the user and assign tablespaces:
- Create the user
Calls cr_usr, passing the username and password
- Change the user to add the default tablespace
Calls cg_usr, passing the username, the tablespace name, and the DEFTS constant to indicate that this is for the DEFAULT tablespace
- Change the user to add the temporary tablespace
Calls cg_usr, passing the username, the tablespace name, and the TMPTS constant to indicate that this is for the TEMPORARY tablespace
You may notice that the drop user function in the package is used if the change user actions fail. It is not necessary to drop the user, but the design decision was made that the entire process should succeed or none of the work should remain .