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 23rd, 2008, 6:54am)

Title: Bind Var in Execute Immediate - DDL statement
Post by Pete Finnigan on Dec 23rd, 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 26th, 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 1st, 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 2nd, 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 5th, 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



Powered by YaBB 1 Gold - SP 1.4!
Forum software copyright © 2000-2004 Yet another Bulletin Board