9.6 Application Control of Access
Access control really begins when the application is started and continues through the entire session. We have divided the control approach into the following three steps.
- Restricting user startup of the application
We want to prevent unauthorized users from even seeing the application display.
- Row access control by the application
The application can supplement the views. For performance reasons, we may have the application directly access the base table and we may restrict access to the rows in a manner similar to the view.
- Enabling access through the application
We have the application initiate a process that is completed entirely within the database. The approach will avoid hardcoding the password in the application and will not require the user to know the password.
9.6.1 Startup Control
Before you can control what the user can do, you must know something about the user. This is typically the user's login name. Through the login name , you should be able to obtain the user's organization information, the type of job the user performs , and so on. You must also know something about the application. This information should be complementary to the information you know about the user. In this application, we are able to get the user's information because the login names are part of the record in the EMPLOYEE table.
Because all access is role-based, the application can check the roles the user has and compare those to the roles assigned to the application in the APP_ROLES table. We describe this table more thoroughly later in the section "Using Password-Protected Roles."
The APP_ROLES table contains the application name and the roles that application will require. On startup, the application checks the roles in that table against the roles granted to the user. If there is no match, the application displays a generic login error message and exits without showing the first screen. Avoid messages that convey the nature of the failure such as "You do not have the xyz role," because the user may be clever enough to find someone who does have that role and steal the person's login password.
| || |
We recommend a very generic message such as "Insufficient privilege, access denied ."
While logged in as the owner of the APP_ROLES security table, you can create a view to simplify the required logic.
CREATE OR REPLACE VIEW app_roles_auth AS SELECT ar.role_name, ar.application FROM app_roles ar, user_role_privs urp WHERE ar.role_name = urp.granted_role ;
This view returns only the roles the user has that match those in the APP_ROLES table. Note that there is no qualification such as "user_name = user" in this view. That qualification occurs in USER_ROLE_PRIVS, a data dictionary view already restricted to the user (as described in Chapter 4). Also, the view does not limit the roles to a specific application since the roles are not known until the view is accessed. Let's assume that the user is running the USER_REVIEW application. At startup, the application runs this query:
SELECT role_name FROM app_roles_auth WHERE UPPER(application) = 'USER_REVIEW';
Case is important because all entries in the data dictionary are uppercase. You might not know if the application field is from a data dictionary table, so the UPPER function guarantees the match will be done in uppercase. An alternative would be to specify SELECT COUNT(*), which returns a count of the roles. Using the count is acceptable if it is only important to know if there are matches, rather than which ones match.
Within the Oracle Forms tools (either Developer or Designer 2000), this query could be placed in one of several triggers such as:
You should use the most appropriate one for your own situation. In most cases, this is the WHEN_NEW_FORM_INSTANCE trigger.
9.6.2 Application Row Access Control
To control access to specific rows, you have to know (as you did for startup control) something about the user and what the access requirements are. If these access conditions are complex, perhaps you should create a table with an application name column, a block column, and a query_limits column. The application name and block are put into the first two columns, and the block WHERE conditions put into the query_limits column. If multiple conditions are required, there should be additional columns for matching with user- and application- related criteria so only one row will be retrieved.
Once the query limits are retrieved, the application has to modify the SELECT statement. Within the Oracle Forms tool, you can modify the SELECT the form builds to include your customized WHERE conditions. You would do this with the SET-BLOCK-PROPERTY built-in program. Also, within this tool, the UPDATE, INSERT, and DELETE statements do not have to be modified. Indeed, they cannot be modified since the form builds them on the fly, and there are no built-in functions to perform this task. UPDATE and DELETE use ROWID to ensure the proper record is being manipulated, and the form handles that. If a third-party tool is being used, the developer will have to handle these three statements in a way appropriate for the product.
9.6.3 Using Password-Protected Roles
One problem now remains: how to turn on the roles that require a password without hardcoding it into the application, passing it over a network, or requiring that the user know it. The latter will defeat the whole purpose of the password protection idea since we do not want the user to access this data directly. We want the user to access the data only via the application.
The method described here uses a special user account. The security user account will have very specific privileges. You could just use a general DBA account, but for security reasons, we recommend you create a separate security user account. If you use your normal DBA account and the password is compromised, serious damage is likely to occur. The impact is somewhat diminished if the security user account password is discovered .
The security user will have more to do than just facilitate the enabling of password-protected roles. The security user will maintain user accounts as well. For that reason, the scripts that follow have more privileges than may seem appropriate at this point in our discussion.
We create the APP_ROLES table to hold application and role information. The table may be owned by any user, including the security user. The script provided here to create the security user assumes the table is not owned by the security user. First we create the table; next , we create the security user; and finally, that user creates the TSEC package.
22.214.171.124 Create the APP_ROLES table
The first script creates the APP_ROLES table as follows :
doc mkaprole.sql Create the app_roles table. This table will be used with the TSEC.SROLE procedure to set password protected roles during the execution of an application. # DROP TABLE app_roles; CREATE TABLE app_roles (role_name VARCHAR2(30) NOT NULL , -- name of role program VARCHAR2(48) NOT NULL , -- tool program associated with role. value found in v$session passwd VARCHAR2(30) NOT NULL , -- password for role application VARCHAR2(8) NOT NULL , -- application name of the -- application authorized to use role. role_desc VARCHAR2(80) -- description ) TABLESPACE &1 STORAGE (INITIAL 50K NEXT 24K MAXEXTENTS 100 PCTINCREASE 0 ) ; COMMENT ON TABLE app_roles IS 'security table for role passwords. read only by function TSEC.SROLE to extract the passwords.'; COMMENT ON COLUMN app_roles.role_name IS 'name of role as it was created.'; COMMENT ON COLUMN app_roles.program IS 'tool program associated with the role. value comes from v$session'; COMMENT ON COLUMN app_roles.passwd IS 'password for role. not encrypted.'; COMMENT ON COLUMN app_roles.application IS 'name of application which should be running when role is enabled.'; COMMENT ON COLUMN app_roles.role_desc IS 'comment or description of entry.';
The purpose of the columns is explained in the column documentation comments. These are stored in the data dictionary. A nice report can be generated to document the system if comments are created.
126.96.36.199 Create the security user
After the table is created, user sys creates the security user. The code provided here switches logins several times. It also employs substitution variables in a manner that is correct, but not common. There is a syntax oddity that we should explain.
When you use substitution variables in a SQL statement, you normally do not need a variable terminator unless the variable is to be used in conjunction with an additional value. You don't run into this very often, as most variables are used like this:
WHERE this_column_in_the_table = &1
However, if the substitution is part of a string for example, the owner name in:
CREATE SYNONYM apple FOR &1.orange;
there is a problem.
As you know, the syntax to create a synonym is:
CREATE SYNONYM <synonym name> FOR <owner>.<table>;
In the syntax, the dot serves as a separator between the owner name and table name. When using the substitution variable, however, the dot becomes the variable terminator, so if mary were passed into the first CREATE statement, it would be resolved as:
CREATE SYNONYM apple FOR maryorange;
and that will not work. The dot separator between the owner, mary , and the table orange is missing. The one that was there became the variable terminator. Since it served that purpose, it could not also be used as the owner-table separator. Therefore, a second dot is needed. Thus the statement becomes:
CREATE SYNONYM apple FOR &1..orange;
When the variable is resolved, it is terminated by the first dot, and the second remains as the separator, so the statement is resolved as:
CREATE SYNONYM apple FOR mary.orange;
which is what is needed.
The following script creates the security user.
doc mksysadm.sql This script must be executed by user sys. arguments: 1 = username for security user (password is the same) 2 = default tablespace for security user 3 = temporary tablespace for security user 4 = owner of app_roles table 5 = password for owner Create the privileges necessary for a system administrator role. Create a system administration user. This user must be able to create and drop users and roles and also update the app_roles table. Access to certain system tables is also required. Privileges required are checked in the user role maintenance forms and it will not start if the privileges specified here are not granted. # SET TERMOUT ON ECHO ON SPOOL mksysadm.lis -- attempt to drop the role. error here is ok. DROP ROLE admin_usr_role; -- create the sysadmin role CREATE ROLE admin_usr_role; -- privilege to connect and establish a login session GRANT create session TO admin_usr_role; -- privileges for personal objects GRANT create synonym TO admin_usr_role; GRANT create procedure TO admin_usr_role; -- privileges to administer users GRANT create user TO admin_usr_role; GRANT drop user TO admin_usr_role; GRANT alter user TO admin_usr_role; -- privileges to administer roles GRANT create role TO admin_usr_role; GRANT drop any role TO admin_usr_role; GRANT alter any role TO admin_usr_role; GRANT grant any role TO admin_usr_role; -- privilege to grant privileges to roles or users GRANT grant any privilege TO admin_usr_role; -- create the security user, grant the role, -- set default and temporary tablespaces. create user &1 identified by &1; GRANT admin_usr_role TO &1; ALTER USER &1 DEFAULT TABLESPACE &2 TEMPORARY TABLESPACE &3 DEFAULT ROLE admin_usr_role; -- grant privileges on sys 's views to the security user and -- not the role. this user will be executing pl/sql and that -- engine does not recognize privileges granted through roles. GRANT select ON &tab TO &1 WITH GRANT OPTION DEFINE tab=sys.dba_roles / DEFINE tab=sys.dba_role_privs / DEFINE tab=sys.dba_users / DEFINE tab=sys.dba_sys_privs DEFINE tab=sys.dba_tablespaces / DEFINE tab=sys.system_privilege_map DEFINE tab=v_$session / -- create synonyms for the security user CREATE SYNONYM &1..dba_roles FOR sys.dba_roles; CREATE SYNONYM &1..dba_role_privs FOR sys.dba_role_privs; CREATE SYNONYM &1..dba_users FOR sys.dba_users; CREATE SYNONYM &1..dba_sys_privs FOR sys.dba_sys_privs; CREATE SYNONYM &1..dba_tablespaces FOR sys.dba_tablespaces; CREATE SYNONYM &1..system_privilege_map FOR sys.system_privilege_map; CREATE SYNONYM &1..v$session FOR sys.v_$session; -- connect as the app_roles table owner and grant privs to security CONNECT &4/&5 GRANT select, insert, update, delete ON app_roles TO &1 WITH GRANT OPTION; -- connect as the security user and create a private synonym -- for the role security table and the sys tables. cannot -- guarantee that public synonyms exist. CONNECT &1/&1 CREATE SYNONYM app_roles FOR &4..app_roles; SPOOL OFF SET ECHO OFF PROMPT PROMPT spool file for mksysadm is: mksysadm.lis PROMPT
We did a lot in this script. The program is documented internally, but a short overview of the steps is in order:
A role is created and assigned the system privileges required by the security user.
The security user is created.
Privileges on sys 's objects are granted directly to the security user. This is because PL/SQL will be used and object privileges cannot be inherited through a role.
Synonyms for sys 's objects are created for the security user.
The APP_ROLES owner connects and grants privileges to the security user with GRANT OPTION. This is needed again because PL/SQL is being used and the package owner has to be able to temporarily allow the executing user access rights on the APP_ROLES table.
The script then connects as the security user. The security user creates a private synonym for the APP_ROLES table.
188.8.131.52 Create the PL/SQL program that sets roles
Next, the TSEC package and package body are created by the security user as follows:
doc File: mkpwsec.sql This script creates the tsec package and package body. The body contains one function: SROLE. The function is used as an argument to the standard database procedure DBMS_SESSION.SET_ROLE. This procedure requires a string of roles to be set. For example, suppose a user had been granted the finadmin (financial administrator) and finpersadmin (financial personnel administrator) roles, and that these roles were not set as defaults. Then the command: DBMS_SESSION.SET_ROLE(finadmin,finpersadmin); would activate the two roles. If there were a password (apw_4u) for the finadmin role, then the command would be: DBMS_SESSION.SET_ROLE(finadmin identified by apw_4u,finpersadmin); To avoid coding all the roles and passwords into applications, the TSEC.SROLE function will assemble the string of roles and passwords for use by DBMS_SESSION.SET_ROLE. Function TSEC.SROLE is then used as the argument to set_role thus: EXEC DBMS_SESSION.SET_ROLE(TSEC.SROLE('APPLICATION_NAME')); Since the function is embedded in the call to set_role, it will be executed first and will be executed within the database. The returned string becomes the argument list for set_role which also executes within the database. This means that the only argument visible outside of the database when the call is made is the application name required by function TSEC.SROLE. The returned string of roles and passwords never appears outside of the database, nor is it ever seen by the application. The TSEC.SROLE function returns a string to enable all roles granted to the user appropriate for the application when the appropriate tool is running an appropriate application, as well as the user's default roles (including those granted to public). Passwords for roles are maintained in the APP_ROLES table, which has the following structure: Name Null? Type --------------- -------- ---- ROLE_NAME NOT NULL VARCHAR2(30) -- Name of role PROGRAM NOT NULL VARCHAR2(48) /* TOOL Program associated with role. Value found in v$session */ PASSWD NOT NULL VARCHAR2(30) -- Password for role APPLICATION NOT NULL VARCHAR2(8) -- Application name /* Application authorized to use role. Argument 1 in function tsec.srole. */ ROLE_DESC VARCHAR2(80) -- Description Logic: The function checks V$SESSION to insure that the user is running the correct tool for the role (like F45RUN.EXE). If the user is not running a registered tool and program, this function returns only the default roles. Calling format: EXEC DBMS_SESSION.SET_ROLE(TSEC.SROLE('APPLICATION_NAME')); Returned value (assumes a standard role: genselect): genselect, abc identified by xyz, def identified by uvw, ... Where: APPLICATION_NAME corresponds to the APPLICATION field in the table. It is not case sensitive. abc and def are an example role names, and xyz and uvw are example role passwords. When creating users, grant all roles to the user, then alter the user to make the role with "create session" the default role. For example a standard select only role would be GENSELECT. # CREATE OR REPLACE PACKAGE tsec IS FUNCTION srole (arg1 IN VARCHAR2) RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY tsec AS FUNCTION srole (arg1 IN VARCHAR2) RETURN VARCHAR2 IS role_cmd VARCHAR2(256); prog_name VARCHAR2(48); i INTEGER; /* get the password protected roles then get the user's default role. */ CURSOR c1 IS SELECT role_name DECODE (a.passwd, null, null, ' identified by 'a.passwd) result FROM app_roles a, dba_role_privs r, v$session v, dba_roles dr WHERE UPPER(a.application) = UPPER(arg1) AND a.role_name = r.granted_role AND a.role_name = dr.role AND r.grantee = user AND dr.password_required = 'YES' AND INSTR(v.program, a.program) > 0 AND (INSTR(UPPER(nvl(v.module,'no module')),'sql') = 0 OR UPPER(a.program) = 'ALL') AND v.audsid = USERENV('sessionid') UNION SELECT drp.granted_role FROM dba_role_privs drp, dba_roles dr WHERE drp.granted_role = dr.role AND dr.password_required = 'no' AND drp.default_role = 'YES' AND grantee IN (user, 'PUBLIC') ; role_rec c1%ROWTYPE; BEGIN /* Following query will return only the users default role if the program in v$session does not match the program in app_roles table */ i := 0; OPEN c1; LOOP FETCH c1 INTO role_rec; EXIT WHEN c1%NOTFOUND; i := i+1; IF (i = 1) THEN role_cmd := role_rec.result; ELSE role_cmd := role_cmd ', ' role_rec.result; END IF; END LOOP; CLOSE c1; RETURN role_cmd; END; END tsec; /
184.108.40.206 Implementation logic
We want to enable the role without giving the user the password, hardcoding the password into the application, or having the password be transmitted over a network. The TSEC package will allow the application to retrieve the default roles and all roles requiring passwords. This solves the problem of giving the password to the user. Since the program also checks to make sure that the correct application is being run, the user is prevented from running the TSEC.SROLE function from the SQL*Plus command line and discovering the roles and passwords that are valid within the system. Now we need a way to execute this function without moving the roles and passwords outside the database.
Oracle supplies many PL/SQL procedures that are created when the database is first initialized . These procedures are available for general use. One procedure, found in the DBMSUTIL.SQL script, is the package DBMS_SESSION. Within the package body is the procedure called SET_ROLE. This is one of the few PL/SQL procedures that executes on behalf of the user not the procedure owner and it can be used by the database user to activate a role.
You are probably aware that in a SQL*Plus session, you (or any user) can issue the SET ROLE command, and you may wonder why a stored program is available to do the same thing. It is there to simplify the coding when a role needs to be set from an application or from PL/SQL. SET ROLE cannot be directly used in PL/SQL.
The calling syntax allows for multiple roles to be passed to the procedure. Some of these roles may have passwords, as in:
DBMS_SESSION.SET_ROLE(role1, role2, ..., role10 IDENTIFIED BY apw_4u, ... );
Procedure SET_ROLE uses the passed information and builds the following SQL statement:
SET ROLE role1, role2, role10 IDENTIFIED BY apw_4u;
Now that we have a program to retrieve the default roles and the roles with passwords, and a program that will issue the SET ROLE command, we can combine the two. But first, a little algebra !
Don't panic! Remember that expressions are resolved from the inside out, so if you have a formula with parentheses, you have to do the stuff inside the parentheses first. This makes sense because the outer operation has to use the results of the inner ones. For example, given the equation (4 * (6 + 2)), you first resolve the inner equation (6 + 2) = 8 and then resolve the outer equation, which becomes (4 * 8) or 32.
The same principle applies to programs. Many programs require arguments to be passed, but you can actually pass a function as well if the function returns the type of information that the program needs. This is referred to as a nested procedure call. That is how the DBMS_SESSION.SET_ROLE and TSEC.SROLE programs are used.
We call the SET_ROLE procedure and pass it the TSEC.SROLE function in the following manner:
This is a nested procedure call, and any nested call is always resolved from the lowest level first, or from the inside out. So, TSEC.SROLE will use the application name to query the APP_ROLES table and find users' default roles as well as the roles needed by the application. If any of the roles require passwords, the passwords will also be returned. TSEC.SROLE returns the user's default roles and the roles-identified-by-password strings separated by commas. After that, the DBMS_SESSION.SET_ROLE procedure uses the returned string to issue the SET ROLE command as previously shown.
| || |
All this activity takes place inside the database . The only thing transmitted over the communication media is the initial command. Neither the roles nor the passwords are returned to the application.
To refine the procedure, the SQL statement in TSEC.SROLE uses four tables:
The application name must be in the APP_ROLES table, along with all required roles and their passwords. The user must have been granted those roles. Finally, the application must be running under the proper tool, as registered in the APP_ROLES table.
Values for the program column of APP_ROLES and also V$SESSION will vary depending on the system configuration; we cannot give every possible option here. If you are running the application in host mode (that is, while physically logged onto the same machine running the database), the real application name will be found in the program column. For example, if you are running SQL*Plus on a Windows95 host, the value is PLUS33W.EXE . If you are running Forms in same environment, the value is F50RUN32.EXE . These are the actual programs.
A client/server configuration, however, is not seen by the database in the same way. If you run SQL*Plus or Forms on the client workstation, the value in the program is "OraPgm." The program name will probably be the same if you are running a web-enabled form. If the Oracle Application Server is being used as a dynamic HTML processor, a different program name may be seen.
Having set all of this in place (the APP_ROLES table, the security user, the TSEC.SROLE function, and users with roles they cannot enable), what happens when you run an application? The following analysis assumes the user is running the VERIFY_CHG application:
The user starts the application and provides the username and password.
The application validates the user.
The application sends DBMS_SQL.SET_ROLE(TSEC.SROLE(`VERIFY_CHG')) to the database.
The database executes TSEC.SROLE to determine roles for application VERIFY_CHG and the user.
TSEC.SROLE returns a string of roles and role-password combinations to DBMS_SQL.SET_ROLE.
DBMS_SQL.SET_ROLE executes the SET ROLE command to enable the required roles.
The user navigates through the application and does work.
It is important to understand that the role is set only for the current session. If the user, after starting the application, opens another window, goes to another workstation (or even a dumb terminal), and then logs into the database, that is a different session nothing done in the first session (where the application is being run) will be in place for the user.
Throughout this and other chapters, we've discussed roles, privileges, and users. Not too much has been said about the number of roles, privileges, and users you may have to establish and manage. The task can be daunting if you do not have some sort of application to simplify your administration tasks . You might also find that you need additional tools to help you document what you have in place. In this context, "documentation" means a script that can be used to recreate the account and access structures you have created in the database. In Chapter 14, we'll discuss an application that can assist you with the management of user accounts and role privileges.