This content has been marked as final. Show 15 replies
Hi,1 person found this helpful
Your authentication function parameter names must be 'p_username' and 'p_password'
This function must return a boolean to the login procedure that calls it. It has 2 input parameters 'p_username' and 'p_password' that can be used to access the values an end user entered on the login page.
My Blog: http://dbswh.webhop.net/dbswh/f?p=BLOG:HOME:0
but how to give them? I tried with...
return pkg_tr_usr.tr_check_usr( 'p_username' ,'p_password'); ORA-06550: line 2, column 8: PLS-00224: object 'PKG_TR_USR.TR_CHECK_USR(p_username, p_password)' must be of type function or array to be used this way ORA-06550: line 2, column 1: PL/SQL: Statement ignored
but did not work, any inputs please
return pkg_tr_usr.tr_check_usr( p_username ,p_password); ORA-06550: line 2, column 33: PLS-00201: identifier 'P_USERNAME' must be declared ORA-06550: line 2, column 1: PL/SQL: Statement ignored
You didn't understand him correctly
Authentication function has 2 parameters 'p_username' and 'p_password', so you will need to change your authentication function to have those parameters like this:
create or replace package pkg_tr_usr as
function tr_check_usr(p_username IN VARCHAR2, p_password IN VARCHAR2) return boolean;
CREATE OR REPLACE package body pkg_tr_usr as
function tr_check_usr(p_username IN VARCHAR2, p_password IN VARCHAR2) return boolean IS
v_ret integer :=0;
select 1 into v_ret from vl_emp_master where empid=to_number(p_username) and pwd=p_password and rownum=1;
when no_data_found then
This rownum=1 in select statement is strange, why would you check for username and password if u always select first row?
I hope this helps you.
Yes :), it seems working but I got this error...
You are not authorized to access this application or function.
Hmm i haven't seen this error before but lets go through the process of creating custom authentication
For now you are good, you have authentication function inside database.
Now go to your application, then click on Shared components and you will see section "Security". Click on Authentication Schemas.
Inside you have 3 types (Application Express(default), Database, Database account).
Do not modify any of them, you will need to create your own authentication
So go Create, then type some name (for example MyCustomAuthentication), and press next until you get Authentication Function screen
Here you will need to select radio button that says "Use my custom authentication function" and in text area below you will type this :
After that step you can press next button untill process ends.
At the end you will see MyCustomAuthentication beside default 3 types. Now click on Change Current (in bar (Application Schemas, Subscription, Change Current, History)) and select MyCustomAuthentication.
Thanks for your reposes,
but 'Shared components' is exists for only 'Data base applications', my application is a 'web-sheet application'. Can you guide my what to use when 'Data-base applications' 'Web-sheet applications' both look similar to me.
got it but not sure whether it is a proper way to do it or not...
along with the steps I mentioned..
Edit Properties-->Authorization-->Edit Access control List-->Add user names from table which you have created earlier.
Yes for websheet application is a litte different.
In authentication section u need to set Custom authentication, and in field Authentication Function (default value -BUILTIN-) type return pkg_tr_usr.tr_check_usr;
After that, you need to set Authorization.
First go to Edit Access Control List, then click on Crete Entry and create user with Username
Username must be one from your database table.
I believe that's all. If it works plz mark this topic as answered.
This is working fine but is there any way to avoid this process of manually creating 'user'?
my problem is I have to create for four thousand (4000) employees !
Ok i will assume that apex is installed in the same schema as yours "vl_emp_master" table.
First you need to manually create admin user. All users in websheet application are stored in APEX$_ACL table. So we need to insert all 4000 in there automatically.
APEX$_ACL(ID, WS_APP_ID, USERNAME, PRIV, CREATED_ON, CREATED_BY, UPDATED_ON, UPDATED_BY)
We want to insert:
WS_APP_ID that is your application id (for testing i will assume that's 100)
USERNAME - empid from your "vl_emp_master" table
PRIV - A - admin, R - reader i believe and there is one more letter i cant remember
CREATED_ON, UPDATED_ON you can put here today's date
CREATED_BY, UPDATED_BY thats your workspace name (for testing i will assume that's 'Hesh')
Ok, so open your sqldeveloper, sqlworkshop or sqlplus doesn't matter and type:
SELECT * FROM APEX$_ACL, now you should see just your admin user.
Ok now we will insert all 4000 of them.
Edited by: 910011 on May 3, 2012 2:10 PM
INSERT INTO APEX$_ACL (WS_APP_ID, USERNAME, PRIV, CREATED_ON, CREATED_BY, UPDATED_ON, UPDATED_BY) SELECT 100, empid, 'R', '03.05.2012', 'Hesh', '03.05.2012', 'Hesh' FROM vl_emp_master
one thing where did you find this?
Well there is no topics available on websheet authentication, but i knew that apex store everything in database. So i stared with this:
select owner, table_name, column_name from all_tab_columns where column_name like '%USERNAME%' AND OWNER <> 'SYS'
When i saw ACL that was it :)
That would be usefull for future issues !
i created a ProofOfConcept document regarding table driven authentication. If you are interested i can send you via email.