|
||
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 |