| Create user profile [message #222751] |
Tue, 06 March 2007 05:31  |
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 #223446 is a reply to message #222826] |
Thu, 08 March 2007 23:58  |
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 #225161 is a reply to message #225011] |
Sun, 18 March 2007 11:22 |
 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
