I installed Formspider 1.8 The process create a database user and then many system priviledges was granted. In a production environment, the user need all this system priviledges ? Are there any minimal set of priviledges for to grant ? and ... The tomcat user to connect with the data base must be the same formspider? Or may be another with less priviledges? With so many privileges, how I handle security in the DB?

asked 30 Jun '15, 13:14

eduardo's gravatar image

eduardo
112
accept rate: 0%

Thanks Yalim I'll wait for the blog

(01 Jul '15, 10:06) eduardo

Hi Yalim, any news on the subject

(26 Aug '15, 09:54) eduardo

Hi Eduardo,

Unfortunately no. But with a good reason. :-). We are working hard to release the new version of Formspider. It will be ready any day now and after that this is one of the top things I will work on.

I sincerly apologize for the delay.

Kind Regards,
Yalim

(26 Aug '15, 10:19) Yalim Gerger ♦♦

Hi Eduardo,

No, these privileges are needed for a smooth development experience. In the production environment you don't need to give these broad privileges.

As you develop with Formspider, what privileges you need in production becomes very obvious.

I'll put together a blog post about this topic in the coming weeks. (I'll start on it after we complete our webinar on July 7th).

Kind Regards,
Yalim

link

answered 01 Jul '15, 03:27

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

Hi Yalim, Now, I have got Formspider 1.10.1 version. Are there any news on the subject or document over it ? I think that one first approach can be, to drop all priviledges "type any", "alter database" and "alter system". Isn't it ?

link

answered 25 Aug, 10:28

eduardo's gravatar image

eduardo
112
accept rate: 0%

Hi Eduardo,

I think we have a better, cleaned up script which is more suitable for production environments. I'll talk to the team and make sure we make that script public (assuming it exists.)

But your approach is correct. Formspider needs very different permission for development and production purposes. For development it obviously needs a lot more privileges. However, most of the create any procedure, table etc... privileges are not needed in production.

In production, Formspider schema needs to be able to run the queries used in the applications it is supposed to run. It also needs to be able to issue DML to tables/views that you expect it to issue DML to and execute the procedures that the applications it runs are calling. So it needs nothing out of ordinary than what a usual JEE application server needs.

I'll search, find and publish the script we use for production.

Kind Regards,
Yalim

link

answered 25 Aug, 15:58

Yalim's gravatar image

Yalim ♦♦
2.8k5
accept rate: 21%

edited 25 Aug, 16:03

Thanks Yalim. Tell me when you publish the script for download and apply it.

link

answered 28 Aug, 10:53

eduardo's gravatar image

eduardo
112
accept rate: 0%

I hope that Yalim is late with the answer for the same reason: he and his team are working hard to realease the next version of [mobile] Formspider which could be ready any day now. :) Looking forward to testing [and buying] it. :)

link

answered 30 Aug, 03:21

brg's gravatar image

brg
2115
accept rate: 0%

Mobile turned out to be a monumental project. We are already in production at a customer but getting it ready to be used by developers will take time. I apologize that this takes so much time but mobile tech is moving so fast and the minimum viable product is so big. It is taking a lot of time.

(30 Aug, 11:53) Yalim ♦♦

Thank you, Yalim, for keeping us updated! I'm keeping my fingers crossed. :))

(30 Aug, 15:33) brg

Hi Eduardo,

I talked to the team. On our download page, there is an option to download the scripts of Formspider installation. It has a pruned privilege script file. You may still adjust the privileges after you install Formspider to production but that is a good script to start with.

Hope this helps.

To repeat: Formspider schema in production needs less privileges to run applications. You may need to give it a few privileges temporarily while you are installing a Formspider application to the FS schema.

While running the applications in it, all FS Schema needs is to be able to run the queries in the applictions, issue DML to tables/views applications are supposed to issue DML to and execute packages the applications in it should execute to run properly.

Kind Regards,
Yalim

link

answered 30 Aug, 12:01

Yalim's gravatar image

Yalim ♦♦
2.8k5
accept rate: 21%

Hi Yalim,

I not found the scripts of Formspider installation that you say, but I found a zip file of Formspider Manual Installation 1.11.0 and using 01_SYS.sql I made a lot of revokes. The current system privileges of user Formspider are the following: -- 25 System Privileges for FORMSPIDER GRANT CREATE SESSION TO FORMSPIDER; GRANT CREATE VIEW TO FORMSPIDER; GRANT CREATE TRIGGER TO FORMSPIDER; GRANT CREATE TYPE TO FORMSPIDER; GRANT CREATE JOB TO FORMSPIDER; GRANT CREATE INDEXTYPE TO FORMSPIDER; GRANT CREATE MATERIALIZED VIEW TO FORMSPIDER; GRANT CREATE PROCEDURE TO FORMSPIDER; GRANT CREATE TABLE TO FORMSPIDER; GRANT CREATE SEQUENCE TO FORMSPIDER; GRANT CREATE PUBLIC SYNONYM TO FORMSPIDER; GRANT CREATE SYNONYM TO FORMSPIDER; -- Are these following necessary????? GRANT ALTER SESSION TO FORMSPIDER; GRANT SELECT ANY TABLE TO FORMSPIDER; GRANT EXECUTE ANY INDEXTYPE TO FORMSPIDER; GRANT SELECT ANY SEQUENCE TO FORMSPIDER; GRANT SELECT ANY DICTIONARY TO FORMSPIDER; GRANT CREATE ANY TRIGGER TO FORMSPIDER; GRANT CREATE ANY CONTEXT TO FORMSPIDER; BEGIN SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT, GRANTEE => 'FORMSPIDER', GRANT_OPTION => FALSE); END; / GRANT UNDER ANY TYPE TO FORMSPIDER; GRANT UNDER ANY VIEW TO FORMSPIDER; GRANT EXECUTE ANY TYPE TO FORMSPIDER; GRANT EXECUTE ANY PROCEDURE TO FORMSPIDER; GRANT CREATE ANY PROCEDURE TO FORMSPIDER;

I sure that somethings privileges may be revoke, there are still system privileges of type "any". You better know how Formspider works, can you suggest which ones? Regards, Eduardo.

link

answered 11 Oct, 09:56

eduardo's gravatar image

eduardo
112
accept rate: 0%

Hi Eduardo,

Sorry for the late reply. Great to hear that you are making progress! I've looked over the grants you posted. Formspider might be using some of the grants you wrote.

I will do some hand waving here and I apologize for that upfront. What it comes down to is this: Formspider database components are used for two purposes, development and production. These need different grants. Development needs more grants to provide a more fluid development experience. Production needs a lot less grants during execution of the applications. It only needs more grants during the import of new applications to production.

There is also the installation phase of the Formspider schema. For example, Formspider creates a CONTEXT. But it doesn't constantly have to have the grant to create a CONTEXT. It only needs this grant during installation.

For development, from the list you provided above, the following are needed for a fluid development experience:

CREATE SESSION TO FORMSPIDER; GRANT CREATE PROCEDURE TO FORMSPIDER; GRANT CREATE TABLE TO FORMSPIDER; GRANT ALTER SESSION TO FORMSPIDER; GRANT SELECT ANY TABLE TO FORMSPIDER; GRANT SELECT ANY SEQUENCE TO FORMSPIDER; GRANT SELECT ANY DICTIONARY TO FORMSPIDER; GRANT EXECUTE ANY TYPE TO FORMSPIDER; GRANT EXECUTE ANY PROCEDURE TO FORMSPIDER;

Please note that this may not be a complete list.

For production databases, it is a different story though. While you are importing an application to Formspider in production, you need to give Formspider the privileges above but you can revoke some of them after you successfully imported the application to Formspider. To execute an application Formspider needs the following grants:

  • CREATE SESSION TO FORMSPIDER;

  • GRANT EXECUTE ANY PROCEDURE TO FORMSPIDER; (You can actually limit this, too. Formspider only executes Formspider Actions. So it only needs execution rights for procedures/packages that there is an FS Action for. For example, if you have code in the HR schema, say, a procedure MY_PROC1 which is called by a Formspider Action, Formspider only needs grants to be able to execue MY_PROC1. So you can be very strict about access rights if you'd like.

  • Formspider also needs to be able to run the SQL queries that the developers wrote in the FS datasource definitions. Formspider does this via the BDF_SCRIPTEXECUTER package it creates in the datasource schema of the application. This will require additional grants.

It sounds a little complicated, but I assure you it is not. It is a topic which is difficult to give general advice on because everyone has different set of requirements and setups. But for a particular installation the process is rather intuitive.

Again, sorry for a little bit of hand waving. We should provide a more precise document about this.

Hope this helps.

Kind Regards,
Yalim

link

answered 17 Oct, 14:32

Yalim's gravatar image

Yalim ♦♦
2.8k5
accept rate: 21%

Hi Yalim, Thanks for your answer. I have continued with the "revoke" doing the following: BEGIN SYS.DBMS_RULE_ADM.REVOKE_SYSTEM_PRIVILEGE (    PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT,    REVOKEE => 'FORMSPIDER'); END; / REVOKE ALTER SESSION FROM FORMSPIDER; REVOKE SELECT ANY DICTIONARY FROM FORMSPIDER; REVOKE CREATE ANY TRIGGER FROM FORMSPIDER; REVOKE CREATE ANY PROCEDURE FROM FORMSPIDER; REVOKE SELECT ANY SEQUENCE FROM FORMSPIDER; REVOKE EXECUTE ANY PROCEDURE FROM FORMSPIDER; - when you remove execute any procedure, we must make grant bdf_scriptexecuter Example: GRANT EXECUTE ON HR.BDF_SCRIPTEXECUTER TO FORMSPIDER; After these revoke's I had to recompile the invalid FS objects

Then, with these grants, we successfully test an export / import of an application. And the execution of the application was also executed correctly. It only remains to try removing the following permissions: GRANT SELECT ANY TABLE TO FORMSPIDER; GRANT EXECUTE ANY INDEXTYPE TO FORMSPIDER; GRANT CREATE ANY CONTEXT TO FORMSPIDER; GRANT UNDER ANY TYPE TO FORMSPIDER; GRANT UNDER ANY VIEW TO FORMSPIDER; GRANT EXECUTE ANY TYPE TO FORMSPIDER; Regards eduardo

link

answered 18 Oct, 13:34

eduardo's gravatar image

eduardo
112
accept rate: 0%

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:

×3

Asked: 30 Jun '15, 13:14

Seen: 1,025 times

Last updated: 18 Oct, 13:34


© Copyright Gerger 2017