Home » Developer & Programmer » Forms » Application setup on logon (Oracle Forms 11g)
Application setup on logon [message #657577] Tue, 15 November 2016 01:55 Go to next message
shamsad.khan@gmail.com
Messages: 5
Registered: October 2016
Junior Member
Hi All,
I have developed an application (Forms 11g and Oracle database 11g, Windows 64 bit). I have a main schema ("main_schema" where in all my application's database objects have been created.

I have created many users, through which individual user can access the application.
I have created logon database trigger. In it I have issued command "alter session set current_schema=main_schema"
I have created many roles (admin, manager, supervisor, operator, ...). Each role have different privileges on (table,procedure,function,sequence...). Based on users, roles are assigned.
I have created my login screen. The user has to enter the username and password and through logon(username,password||'@'||connect_string,false), the user gets logged in.

I need suggestion from you all.

1) Every individual user right to access forms, reports, menus , .. are stored in a table (user_info) that is in the main_schema.
After the user logon, I need to access the main_schema and retrieve data from the table user_info. Based on the user access information, I can enable or disable/hide menus at runtime.

The above approach is not good as far as security is concern.

I would appreciate if you all can give me the best approach or method step by step so the user doesn't get access to what is not granted to him/her wrt security.

Thanks
Re: Application setup on logon [message #657582 is a reply to message #657577] Tue, 15 November 2016 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67288
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The above approach is not good as far as security is concern.
Why?

Re: Application setup on logon [message #657584 is a reply to message #657582] Tue, 15 November 2016 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Agree with Michel - what are you specifically worried about users doing?
Re: Application setup on logon [message #657593 is a reply to message #657577] Tue, 15 November 2016 07:55 Go to previous messageGo to next message
shamsad.khan@gmail.com
Messages: 5
Registered: October 2016
Junior Member
Hi

Let say User "A" logged on. Now the application needs to know the forms, reports that this user "A" has been given permission to access. This information is in the "main_schema". So to get this information, I need to hard code in the form the "main_schema" and its password and get logged on. Then get the information of User "A", logout and then login back to user "A".

This is not cool as anyone can come to know the main_schema password and do the damaging.
My request is what changes do I need to make in my application so that he it runs without any security issues.
Every user logs in through his user id and password and the application gets his access level permission from main_schema and accordingly the menu gets changed at runtime.

i hope I am clear with my query
Re: Application setup on logon [message #657595 is a reply to message #657593] Tue, 15 November 2016 08:01 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
shamsad.khan@gmail.com wrote on Tue, 15 November 2016 08:55
Hi
I need to hard code in the form the "main_schema" and its password and get logged on.
Why?
Re: Application setup on logon [message #657596 is a reply to message #657595] Tue, 15 November 2016 08:04 Go to previous messageGo to next message
shamsad.khan@gmail.com
Messages: 5
Registered: October 2016
Junior Member
Please tell then how to go ahead. I need solution
Guide me the correct approach
Re: Application setup on logon [message #657598 is a reply to message #657593] Tue, 15 November 2016 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I have no idea why you think logging on to the main schema is necessary.
Forms itself is going to read this data and it can do so as the application user, just grant select on the table to the users and not insert/update/delete
If you're worried about people logging in and seeing what other users have access to (though I'm not sure why that would be a problem) then create a view along the lines of:
SELECT *
FROM config_Table
WHERE username = user

And grant that to the users rather than the config table.
Re: Application setup on logon [message #657599 is a reply to message #657598] Tue, 15 November 2016 08:32 Go to previous messageGo to next message
shamsad.khan@gmail.com
Messages: 5
Registered: October 2016
Junior Member
Thanks. You got my problem.
What I understood from your reply is
I should create a view based on config_table then Grant select on view to the users;
In the form I write
SELECT * FROM config_Table WHERE username = :user

In this way, I retrieve the user "A" information from config_table

But what difference it makes if I grant select on config_table instead of view
Re: Application setup on logon [message #657600 is a reply to message #657599] Tue, 15 November 2016 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're not worried about users logging in via sqlplus and querying the config table (and you shouldn't need to worry) then you don't need a view at all and you can just put that query in the form.
Re: Application setup on logon [message #657610 is a reply to message #657600] Tue, 15 November 2016 13:41 Go to previous messageGo to next message
shamsad.khan@gmail.com
Messages: 5
Registered: October 2016
Junior Member
Hi, thanks for quick reply.

is there anyway that the user can access the main_schema only through application.
The user can't access main_schema through sqlplus, toad or any third party tool.
Re: Application setup on logon [message #657620 is a reply to message #657610] Wed, 16 November 2016 03:05 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Define access the main_schema.
You've already said you're giving the users grants to access the objects in it.
Previous Topic: Forms taking longer time to execute query / Fetch Data to another block
Next Topic: List value highlighted not show
Goto Forum:
  


Current Time: Thu Aug 06 14:38:42 CDT 2020