Application Authorization - Expert Oracle and Java Security - page 222

Listing 10-16. Function to Find Database User
FUNCTION f_is_user( just_os_user VARCHAR2 )
RETURN VARCHAR2
AS
return_char VARCHAR2(1) := 'N';
v_count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM sys.all_users
WHERE username = just_os_user;
IF v_count > 0 THEN
return_char := 'Y';
END IF;
RETURN return_char;
END f_is_user;
 Note Return to file Chapter10/SecAdm.sql for reference in this discussion.
Notice from where we get the indicator that the user is an Oracle user. We read from the Oracle data
dictionary view, SYS.ALL_USERS . That view is granted select to PUBLIC . I believe this is a bit of a security
problem. If a hacker gets access to any Oracle user account, he can read the ALL_USERS view and get a list
of all the user names he might try to access. There are other views of the data dictionary that are likewise
granted for select by PUBLIC , which I believe oversteps security. Another particular view is
SYS.ALL_SOURCE , which lists the entire body of every stored procedure in every schema, and other code to
which a user is granted execute. Letting a hacker see our code (whether he's a legitimate but rogue user
or an infiltrator) is inviting further compromise.
When we create a dedicated database for application authentication in Chapter 11, we will revoke
select by PUBLIC for these particularly sensitive views. There are a few additional views that we will also
remove from PUBLIC access.
Proxy Through Application Verification and Other Proxies
Finally, with regard to our application verification user, we need to permit every user to proxy through
appver . Our logon trigger will not see the proxy, but when we execute the procedure to get the secure
application role related to the current application, we will use our proxy test as part of SSO. For example:
ALTER USER osuser GRANT CONNECT THROUGH appver;
Do not forget to create an Oracle user for every OS user that you want to give access to your
applications. And grant that each user may proxy through appver . Also for each OS user, grant that her
account may proxy through the role associated with the specific application.
For example, to give an OS user named “coffin” access to the hrview application, you'd need to
execute these commands:
CREATE USER coffin IDENTIFIED EXTERNALLY;
GRANT CREATE_SESSION_ROLE TO coffin;