Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> Bind Var in Execute Immediate - DDL statement
        
(Message started by: Pete Finnigan on Dec 23^rd, 2008, 6:54am)

Title: Bind Var in Execute Immediate - DDL statement
Post by Pete Finnigan on Dec 23^rd, 2008, 6:54am

Hi Everyone,

I am trying to build ALTER USER command dynamically with bind vars; however its giving me the error-ORA-01935 missing user or role name

Does anyone has any idea how to resolve it. I want it using bind variable only; otherwise there are possibility of SQL Injection?

Thanks in advance.

declare
sql1 varchar2(1000);
account_name_i NVARCHAR2(50):='DEEPA';
new_password_i NVARCHAR2(50):='ASDASF';
old_password_i NVARCHAR2(50):='ASFASGFSDG';
begin
sql1:= 'ALTER USER :user_n IDENTIFIED BY :new_pass REPLACE :0ld_pass ';
EXECUTE IMMEDIATE sql1 using account_name_i, new_password_i, old_password_i ;
end;

Title: Re: Bind Var in Execute Immediate - DDL statement
Post by Pete Finnigan on Dec 26^th, 2008, 2:35am

You don't get to use bind variables. Do the following:

1. Surround the username, new password and old passwords with double quotes.

2. Reject any username, new password or old password that contain double quotes.

Example:

if instr(account_name_i, '"') > 0
or instr(new_password_i, '"') > 0
or instr(old_password_i, '"') > 0 then
raise_application_failure(-20001,
'No double quotes allowed');
end if;
execute immediate 'alter user "'
|| account_name_i
|| '" identified by "'
|| new_password_i
|| '" replace "'
|| old_password_i
|| '";';

Title: Re: Bind Var in Execute Immediate - DDL statement
Post by Pete Finnigan on Jan 1^st, 2009, 7:48pm

Just to add to the last poster. I would also ensure that a password verification function and password management are implemented. Also ensure that you do not allow this function to be used to set key users passwords such as SYS, SYSTEM, SYSMAN....

Also enable audit on ALTER USER and CREATE USER and DROP USER.

Finally you can also use a DDL trigger to "check" its use. I did a blog post some time back on this.

cheers

Pete

Title: Re: Bind Var in Execute Immediate - DDL statement
Post by Pete Finnigan on Jan 2^nd, 2009, 2:33pm

Pete,

I would appreciate more detail on your recommendation: "Also ensure that you do not allow this function to be used to set key users passwords such as SYS, SYSTEM, SYSMAN". We have a function with these restrictions, but I have not seen this policy articulated.

Title: Re: Bind Var in Execute Immediate - DDL statement
Post by Pete Finnigan on Feb 5^th, 2009, 10:22am

Hi,

Sorry for the delayed response. I had a broken hand during January and then away on business and only in the last week or two is my hand fine again so i have been limiting my typing somewhat.

Yes, if you create a function to expose the ALTER USER privilege then ensure that it cannot be used for the alteration of key users. I mentioned SYS, SYSTEM and SYSMAN as examples, you would extend this of course.

So you would create a schema to manage the altering of users passwords. This schema would have CREATE SESSION and ALTER USER system privileges. Ideally it would not have others. You would need to grant create procedure whilst creating the code or create the code from a DBA account in this schema. In fact in "run mode" you can revoke the CREATE SESSION privileges. So create a procedure in this schema that is definer rights. Then in this procedure do ensure that you check the caller is a user allowed to alter passwords. Possibly check that the location is correct - i.e. that maybe its called from the application - i.e. check the IP address is the IP Address of the application server.

Then in the code itself check which users password is being changed. An ideal situation would be that different groups of admin staff can only change smaller groups of "users" passwords - or rather re-set them. This is simple PL/SQL to check the user being changed is within an allowed group.

hope this helps

kind regards

Pete