Home » RDBMS Server » Security » Create user profile
Create user profile [message #222751] Tue, 06 March 2007 05:31 Go to next message
mistry0071
Messages: 6
Registered: April 2006
Location: UK
Junior Member
Hello

I am trying to alter a user's profile in PL/SQL. The user has been created using the following syntax:

Code:
CREATE USER USER_1 IDENTIFIED BY password_1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PASSWORD EXPIRE';


I have created a function called f_corp_pwd_vfy which restricts the user from using their username as their password (see below) which creates.


Code:
CREATE OR REPLACE FUNCTION f_corp_pwd_vfy
(in_username IN VARCHAR,
in_new_password IN VARCHAR,
in_old_password IN VARCHAR)
RETURN BOOLEAN
AS
pwd_okay BOOLEAN;
BEGIN
IF in_new_password = in_username THEN
raise_application_error(-20001, 'Password may not be username');
END IF;
RETURN TRUE;
END f_corp_pwd_vfy;
/


I then tried to create a profile with the function (see below) in it but for some reason it won't work. It says it can't find the function although was created.


Code:
SQL> CREATE PROFILE corp_sec_profile LIMIT
2 failed_login_attempts 3
3 password_lock_time 1
4 password_life_time 30
5 password_grace_time 10
6 password_reuse_max 5
7 password_reuse_time 365
8 password_verify_function f_corp_pwd_vfy;
CREATE PROFILE corp_sec_profile LIMIT
*
ERROR at line 1:
ORA-07443: function F_CORP_PWD_VFY not found


I have logged into oracle 9i as system as this is the only login that works. Please advise what I am doing wrong. Thanks
Re: Create user profile [message #222826 is a reply to message #222751] Tue, 06 March 2007 09:35 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
What schema did you create the F_CORP_PWD_VFY function in ? It should be created in the SYS schema.
Re: Create user profile [message #223446 is a reply to message #222826] Thu, 08 March 2007 23:58 Go to previous messageGo to next message
Muhammad Ahmad
Messages: 30
Registered: November 2002
Member
Hi,

in addition to the friend's answer:

Function must be created in the SYS schema and
must have the following specification:

function_name(
userid_parameter IN VARCHAR2(30),
password_parameter IN VARCHAR2(30),
old_password_parameter IN VARCHAR2(30))
RETURN BOOLEAN


The Procedure to Define a Function to Verify a Password:

When a new password verification function is added, the database administrator must consider the following restrictions:

• The procedure must use the specification indicated in the slide.
• The procedure returns the value TRUE for success and FALSE for failure.
• If the password function raises an exception, an error is returned and the ALTER USER or CREATE USER command is terminated.
• The password function is owned by SYS.
• If the password function becomes invalid, an error message is returned and the ALTER USER or CREATE USER command is terminated.

looking for your feedback

Regards,

Muhammad Ahmad
Re: Create user profile [message #225011 is a reply to message #223446] Fri, 16 March 2007 09:55 Go to previous messageGo to next message
mistry0071
Messages: 6
Registered: April 2006
Location: UK
Junior Member
Thanks, do you know what the default password is to login as sys? I have 9i installed on my laptop.
Re: Create user profile [message #225154 is a reply to message #225011] Sun, 18 March 2007 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

change_on_install

You'll find a list of default passwords at:
http://www.cirt.net/cgi-bin/passwd.pl?method=showven&ven=Oracle

Regards
Michel
Re: Create user profile [message #225161 is a reply to message #225011] Sun, 18 March 2007 11:22 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are on 9iR2 and created the database with DBCA, then I believe you are prevented from choosing change_on_install as the password for SYS. Therefore, it could be different than change_on_install. If you forgot the password and are a member of the Windows ORA_DBA group, then you can connect as:

C:\> sqlplus "/ as sysdba"

and change SYS password to whatever you want.
Previous Topic: Auditing the Database
Next Topic: pasword protected sysdba...Urgent!!!!!
Goto Forum:
  


Current Time: Fri Dec 09 19:21:26 CST 2016

Total time taken to generate the page: 0.05702 seconds