Three Tier Oracle Security in London ~ Paul M. Wright

(nix, oracle, java, www, cloud ) intersect (safety, security, reliability, integrity)

Three Tier Oracle Security in London ~ Paul M. Wright RSS Feed
 

CREATE_DIRECTORY first improvement

That was quick..good to know that folks are reading the blog.

Christian wrote an email to me specifying the following.

Consider this example, which gives access to the root directory:

SQL> exec create_directory.createdirectory('rootdir as''/''--','/u01/thisismypath');

It results in the creation of the root directory “/” but without granting the privileges to the user so not a home run but the code can be improved so thank you for this input Christian.

Please adopt this new version where I pass first argument through DBMS_ASSERT after stripping quotes.

--CREATES A DIRECTORY IN A SPECIFIC OS LOCATION AND GRANTS PRIVS
CREATE OR REPLACE PACKAGE CREATE_DIRECTORY AS
PROCEDURE createdirectory(directory_name IN VARCHAR2, directory_path
IN VARCHAR2);
END create_directory;
/
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_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_validated := REPLACE(directory_path,'.','');
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);
l_exec_string := 'GRANT READ, WRITE ON DIRECTORY
'||l_directory_name_validated ||' TO '||user;
EXECUTE IMMEDIATE (l_exec_string);
END IF;
END createdirectory;
END create_directory;
/

I have just tested the above and it will not allow SQL through. The current version at this URL has been updated to v1.1
http://www.oracleforensics.com/wordpress/index.php/create_directory/
If anyone has any other improvements email me at paul.wright@oracleforensics.com
I plan to add logging and error handling to it in the near future.
Cheers and thanks Christian,
Paul

Leave a Reply

You must be logged in to post a comment.