Administration of Security - Expert Oracle and Java Security - page 358

AS
m_stmt VARCHAR2(100);
BEGIN
m_stmt := 'BEGIN sys.usr_role_adm_pkg. p_drop_user@apver_link ( :1 ); END;';
EXECUTE IMMEDIATE m_stmt USING username;
END p_drop_apver_user;
PROCEDURE p_set_apver_proxy_through ( username VARCHAR2, proxyname VARCHAR2 )
AS
m_stmt VARCHAR2(100);
BEGIN
m_stmt :=
'BEGIN sys.usr_role_adm_pkg. p_set_proxy_through@apver_link ( :1, :2 ); END;';
EXECUTE IMMEDIATE m_stmt USING username, proxyname;
END p_set_apver_proxy_through;
PROCEDURE p_drop_apver_proxy_through ( username VARCHAR2, proxyname VARCHAR2 )
AS
m_stmt VARCHAR2(100);
BEGIN
m_stmt :=
'BEGIN sys.usr_role_adm_pkg. p_drop_proxy_through@apver_link ( :1, :2 ); END;';
EXECUTE IMMEDIATE m_stmt USING username, proxyname;
END p_drop_apver_proxy_through;
END apver_usr_adm_pkg;
/
-- Grant to role
GRANT EXECUTE ON ojsaadm.apver_usr_adm_pkg TO ojs_adm_admin;
The procedures in the ojsaadm.apver_usr_adm_pkg correspond to procedures in the
sys.usr_role_adm_pkg , with one exception. The procedure in this package, p_create_apver_user , calls
both the p_create_user_once and p_create_user_many procedures in sys.usr_role_adm_pkg . No
exception is thrown by the sys.usr_role_adm_pkg procedures, so we will succeed in calling
p_create_user_many even if the user already exists and p_create_user_once fails. Finally, we grant
EXECUTE on this package to the ojs_adm_admin role.
Select an Existing User
We return now from examining the Oracle structures and grants needed to accomplish our plan to the
Admin Users functional screen. We will look first at the actions that occur when the application user
selects an existing user from the drop-down userComboBox . The userComboBox_actionPerformed()
method gets called. We first get the user ID from within the parentheses in the selected item by calling
the pullIDFromParens() method like this:
userID = Utility.pullIDFromParens((String)userComboBox.getSelectedItem());
Next, we create a delayed thread whose run() method contains the query shown in Listing 12-31.
Notice that we select all the rows of the instance_proxy_users view - this is a static query, not vulnerable
to SQL injection. We walk through the ResultSet testing each row for a value of client that matches our
selected user. Then we check if the value of proxy matches any of our administrative users, and we select