When the datasource is based on a table, the query shows all columns of that table. I have tables with many columns… So this “generated statement” is totally unreadable and also not modifiable. Most of the times I need only a few columns. Also, users may have different access to values. For example, managers can see everything and normal users don't. Do clever users with knowledge about ajax, json, bootstrap etc have access to data they are not allowed to see?

My questions are:

a) Is Formspider selecting all columns of the table or only the columns shown on panels?

b) Is all selected data reachable in a json or other “file” or dataset?

c) Is it possible to display a column based on the value of another column? For example: if security_level_email=MANAGER, the emailadress is only shown to managers and other users see a warning “no access” in that field. If this is possible, is there any way the user could retrieve the value of the emailadress even if he has no access? This is really very important.

asked 26 Sep '14, 06:54

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231329
accept rate: 0%


Hi Jan-Willem,

I always use views with instead of triggers as datasources. Using views, all things you mention in your questions a relatively easy to achieve. You can control the exact query that will be used for your screen, and there is no way a user can get to unauthorized data when using db views, since the data will not be available in the view, there is no way to get around that.

Best regards,
Michiel A

link

answered 28 Sep '14, 05:38

Michiel%20A's gravatar image

Michiel A
5161544
accept rate: 13%

Thanks Michiel !

I will do some trials with views and instead of triggers.

Will this solve these three problems? I am not sure yet... Only during runtime the application will be aware of the user (Oracle Portal username). The access to data depends on that username. I have similar views in applications built with Oracle Portal and that works fine. So, there is hope :) However, in order to make that work, first the single sign on between Portal and Formspider has to be solved.

Best regards, Jan Willem

link

answered 28 Sep '14, 06:19

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231329
accept rate: 0%

Hi Jan-Willem,

I cannot help you with the single sign-on with Portal. We stopped using Oracle portal years ago (i am happy to say ;-)

Good luck, Michiel

(28 Sep '14, 14:33) Michiel A

Hi Michiel, i think there are only a few companies that figured out all options :) Even Oracle experts sometimes do not believe that our sites have been built with it. For example www.tvdijkzicht.nl, www.t-line.nl, www.tc-denieuwebrug.nl, well we have 100 examples :) However there is one important lesson to learn: never rely on Oracle products that are free from licences. As soon as Oracle has bought Webcenter Portal, Oracle Portal was dead because webcenter is 50 times more expensive. That's also one of the reasons that i have no confidence at all in APEX. A free Oracle product does not exist. When Oracle buys Formspider, it's the end of APEX :) :) :)

By the way - the single sign on is almost working...

(28 Sep '14, 14:48) Jan Willem V...

Nice! (btw, i still wake up sweating sometimes dreaming of installing Portal 3.0.9 that came in Oracle 9iAS :-))

(28 Sep '14, 15:26) Michiel A

Hi,

the single sign on is working and i can indeed use views to restrict the records based on the current user :)

I will now proceed with the views with instead of triggers. I wonder how these must be defined when one user can view and update a column and another user sees "no access" on the screen. That can be different for each record, for example the restricted user can see and update emailadresses of 90% of the people in the table and for 10% he has no access. But managers can handle all 100% emailadressen. The instead of trigger should not update all emailaddresses to the value "no access"...

Kind regards, Jan Willem

link

answered 29 Sep '14, 03:38

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231329
accept rate: 0%

Hi Jan,

You should use Formspider Security for this purpose. The instead of trigger is not place to implement role based security.

Kind Regards,
Yalim

(29 Sep '14, 04:08) Yalim Gerger ♦♦

Hi Jan,

As Michiel said, you should use Views if you want to reduce the number of columns in a Datasource Definition.

Do clever users with knowledge about ajax, json, bootstrap etc have access to data they are not allowed to see?

No.

Is Formspider selecting all columns of the table or only the columns shown on panels?

FS selects all columns specified in the datasource definition but sends to the client only the columns that are bound to a UI object.

Is all selected data reachable in a json or other “file” or dataset?

No.

Is it possible to display a column based on the value of another column? For example: if security_level_email=MANAGER, the emailadress is only shown to managers and other users see a warning “no access” in that field. If this is possible, is there any way the user could retrieve the value of the emailadress even if he has no access? This is really very important.

Yes. You can implement security in different ways:

1) You can use Formspider Security. Using Formspider Role Based Security you can define read and write level access to datasource definitions, datasource definition columns. Using the FS Security API you can even implement row level security. with FS Security, you can also manage the visible, editable and enable attributes of every UI Object. Bound UI Objects automatically inherit the read write privileges of the datasource column they are bound to.

2) You can use a combination of SQL, Formspider API's and grid attributes to achieve the same thing. For example, using a view, you can mask data by applying a security function to it such as:

select filterByUserPermissions(email_tx,[user name]) email_tx from employees

In addition to this, you can use API's such as api_component.setGridHeaderVisible to show/hide the column in the Grid which shows the email.

In both cases, there is no way the user can retrieve data he has no right to access.

Formspider role based security is easier to use, less error prone and outperforms any other option. So if I were you, I'd take a good look at it.

Kind Regards,
Yalim

link

answered 29 Sep '14, 03:50

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

edited 29 Sep '14, 04:07

Hi Yalim,

the Security Repository seems to be based on users. My products have their own security mechanism, which is not only needed for the formspider application i am going to develop, but also for all other applications built in plsql. There are hundreds of thousands of users.

When i invoke the formspider application from Portal with single sign on, i know the user and his roles according to my security mechanism.

Is it possible to only use the objects "Role", "Key" en "RoleKey"? For example: the user has my role MANAGER and that is equal to the formspider Role "MGR". Or the user has my role DEPUTY which quals to formspider role "DEP". Can i tell formspider that the user has role MGR or DEP without using the object "User"? And then use the Read and Write Keys?

Kind regards, Jan Willem

link

answered 29 Sep '14, 04:25

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231329
accept rate: 0%

Hi Jan,

Technically this is possible but will require some extra customization of Formspider Security. One other option is to that you can duplicate your users in the Formspider Security repository and keep them in sync with your current repo.

(29 Sep '14, 04:34) Yalim Gerger ♦♦

Hi Yalim, my security mechanism is a large application on its own with complex processes. Also, one user can be active for one or more customers and for customer A he is a manager and for customer B only a deputy.

Possible work around may be to create two users in the fs reposity: one manager and one deputy. The question then is: would it be possible to set the user in the formspider application based on the role that is passed through with my single sign on. Something like: when planmysport-role=MANAGER then formspider-user=MANAGER. In fact all managers will then run the formspider-application as one user MANAGER, but that is no problem because the data they see depends on the view that is using the real single sign on user.

(29 Sep '14, 04:52) Jan Willem V...

So, i fact the question is: is it possible to set the formspider user from a plsql procedure? The password is not relevant at all.

(29 Sep '14, 04:54) Jan Willem V...

Hi Yalim,

as noted in the comments above, i can not duplicate all users to the Security Repository. I now want to take a look at the idea to create two users: one manager and one deputy.

Would this be possible:

If the planmysport-role=MANAGER then call api_security.login ( 'MANAGER', password ).

If the planmysport role=DEPUTY then call api_security.login ( 'DEPUTY', password ).

By doing this, everybody is using the same (only 2) Formspider-users. Will that be a problem?

Kind regards, Jan Willem

link

answered 29 Sep '14, 10:22

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231329
accept rate: 0%

Hi Jan,

No, this will not be a problem at all. This setup will work just fine.

Kind Regards,
Yalim

(29 Sep '14, 13:33) Yalim Gerger ♦♦
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×7
×7
×2

Asked: 26 Sep '14, 06:54

Seen: 2,057 times

Last updated: 29 Sep '14, 13:33


© Copyright Gerger 2017