Database Reference
In-Depth Information
CREATE OR REPLACE PACKAGE BODY CREATE_DIRECTORY as PROCEDURE createdirectory(directory_name IN
VARCHAR2, directory_path IN VARCHAR2) IS
l_exec_string VARCHAR2(1024):= 'CREATE OR REPLACE DIRECTORY ';
l_directory_name_stripped VARCHAR2(1024);
l_directory_name_dstripped VARCHAR2(1024);
l_directory_name_validated VARCHAR2(1024);
l_directory_stripped VARCHAR2(1024);
l_directory_validated VARCHAR2(1024);
BEGIN
l_directory_name_stripped := REPLACE(directory_name,'''','');
l_directory_name_dstripped := REPLACE(l_directory_name_stripped,'"','');
l_directory_name_validated := DBMS_ASSERT.simple_sql_name(l_directory_name_dstripped);
l_directory_stripped := REPLACE(directory_path,'''','');
l_directory_validated := REPLACE(l_directory_stripped,'.','');
IF instr(l_directory_validated,'/u01/thisismypath') = 1
THEN
l_exec_string := l_exec_string||l_directory_name_validated ||' AS '||''''||l_
directory_validated||'''' ;
EXECUTE IMMEDIATE (l_exec_string);
END IF;
END createdirectory;
END create_directory;
/
EXEC CREATE_DIRECTORY.createdirectory('PAULSDIR2','/u01/thisismypath');
In the preceding example DBMS_ASSERT is used to validate the input to the procedure, and additionally the
directory path given can only be a sub-directory of
/u01/thisismypath
, thus limiting the location that can be written
to by Oracle to a safe location. This approach means that the CREATE ANY DIRECTORY privilege can be used, but
without the ability to write over the password file thus adding SYSDBAs.
Wrapping Alter User
Another example of a very powerful system privilege is ALTER USER. In this following example we validate the input
to an ALTER USER procedure to check that PROXY clause has not been added.
create or replace PACKAGE BODY UM_USER as
PROCEDURE change_password (pi_new_password IN VARCHAR2) IS
l_exec_string VARCHAR2(1024) := 'ALTER USER ';
l_stage VARCHAR2(1024);
l_email_subject VARCHAR2(256);
l_email_message VARCHAR2(4096);
l_user_type UM_USERS.USER_TYPE%TYPE;
l_password_quoted varchar(1024);
l_password_not_quoted varchar(1024);
l_password_validated varchar(1024);