sql help

Posts 9 · Viewed 6098 · Go to Last Post

· Snapshot 0

Like · Likedby · 0

Hello, I am trying to print out the source for eachof the password verify function in Oracle, but it doesnt seem to work using the script below:

select name, text from dba_source where name in (select limit from dba_profiles where resource_name = 'PASSWORD_VERIFY_FUNCTION');

Any help is appreciated,
Thanks

prankster

· Snapshot 8

Like · Liked by · 0

by source do you mean the source code of the function, the one that is in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql?
Anyway here is a sql that displays profile, resource_naem and Limit
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_name='PASSWORD_VERIFY_FUNCTION';

Did some more search..it looks like when u run utlpwdmg.sql, it does three things
-creates a function called verify_function_11g (for 11 g)
-Alters profile which enables the default profile with the new password fucntion.
-Creates legacy function verify_function.

if you are trying to print the source of these two functions, then first run that sql, that will create these two function.
Then do
select TEXT from all_source where TYPE='FUNCTION'
check if your function is there
and then do
select TEXT from all_source where name='VERIFY_FUNCTION'

I've not tried this myself, dont have admin rights.
See if this helps, or someone else might have better answer.

Last edited: 07-Apr-11 05:50 PM

CyLegend

· Snapshot 21

Like · Liked by · 0

Thank you prankster and yes by source i meant the source code of the function

Last edited: 07-Apr-11 05:46 PM

prankster

· Snapshot 37

Like · Liked by · 0

just updated my original content, see if that helps.

prankster

· Snapshot 40

Like · Liked by · 0

if you are trying to print the source of these two functions, then first run that sql, that will create these two function.
Then do
~~select TEXT from all_source where TYPE='FUNCTION'~~
select NAME from all_source where type='FUNCTION'
check if your function is there

CyLegend

· Snapshot 47

Like · Liked by · 0

I will try that, thanks

CyLegend

· Snapshot 120

Like · Liked by · 0

Hi Prankster,

Do you know of a script that will run through the table "dba_source" as well as "dba_profiles" to get to the user password ("password_verify_function" of dba_profiles"). I want to automate to list out all source code withought having to access each dba table. The original script that I tried below did not seem to work: Any help will be appreciated.

select name, text from dba_source where name in (select limit from dba_profiles where resource name = "PASSWORD_VERIFY_FUNCTION");

Thanks again!

prankster

· Snapshot 129

Like · Liked by · 0

IMO your original sql should work. Only thing is you dont have any verify_function created. Thats why it is displaying nothing.
Once you run utlpwdmg.sql and then assign particular function to the profile. The limit should have the funciton name . You can verify this by using following sql,

SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile='DEFAULT'and resource_name='PASSWORD_VERIFY_FUNCTION';

Currently you'll have something like below since no verify function is assigned to a profile.
PROFILE                        RESOURCE_NAME                    RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------------------------
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD      NULL

Once you apply password verification function to the profile using
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

The above output would look something like below
PROFILE                        RESOURCE_NAME                    RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------------------------
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD      verify_function

Then your original should work. I've not tested it out myself, neither done it before, but did some search. And this makes sense to me. There might be better answers from DBAs. Let us know if u find better soln.