Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate
Execute Immediate [message #576927] Sun, 10 February 2013 22:18 Go to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
I have used execute immediate in may stored procedure but my boss told me that I should avoid it because it is risky, he told me that what if some programmer will just put drop table statement in my execute immediate. He advice me to use dbms sql parse instead. Any advice guys on how I can justify that I should use execute immediate.
Re: Execute Immediate [message #576929 is a reply to message #576927] Sun, 10 February 2013 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
Listen to your Boss!
Re: Execute Immediate [message #576934 is a reply to message #576927] Mon, 11 February 2013 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
EXECUTE IMMEDIATE or DBMS_SQL is the same thing (regarding the risk).
Avoid to use them as much as possible.

Now post your procedure and we will tell you if it is risky or not.

Regards
Michel
Re: Execute Immediate [message #576936 is a reply to message #576934] Mon, 11 February 2013 01:37 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
I'm doing a computation of formulas based from data in tables with nine steps, and the computation should be dynamic what I did was put the formulas in a table with bind variables and used execute immediate to execute them.

a good sample will be this one..lets say my table has these queries. I just think that the formula table should not be available for update or for insert by anyone to guard it from dropping tables.I also thought that why would I use dbms sql parse if it can also make use to drop tables.




step 1
SELECT ((nrate*((100-nvl(rate,0))/100))/100)*:pamount 
               FROM xxxx 
              WHERE xx_xxx = :cid
       


step 2
SELECT :pyr*(1-(rate/100))/ :step1 
              FROM xxxxx WHERE xxxxx =id
      


step 3
SELECT POWER((1+:perperiod),:v_step2) 
                FROM xxx where xseqno = :pseqno


....




[Updated on: Mon, 11 February 2013 01:46]

Report message to a moderator

Re: Execute Immediate [message #576937 is a reply to message #576936] Mon, 11 February 2013 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why these query texts are in a table?

Regards
Michel

[Updated on: Mon, 11 February 2013 01:58]

Report message to a moderator

Re: Execute Immediate [message #576938 is a reply to message #576937] Mon, 11 February 2013 02:05 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
so that they easily make their own formulas without altering the codes in the procedure.So now is it risky?
Re: Execute Immediate [message #576939 is a reply to message #576938] Mon, 11 February 2013 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what prevent them from writing something like 'drop tablespace users'?

How do you know what to bind with ":var" in their queries?

Regards
Michel
Re: Execute Immediate [message #576940 is a reply to message #576939] Mon, 11 February 2013 02:39 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
because the table will have a maintenance screen where the user will be able to build a query not by typing them but by choosing columns from the list box and put mathematical operations. but the concern of my boss are other programmers. I also think allowing only certain users to do dml operations in this table will also guard it from undesired queries.

It will be based on the sequence of the bind variables there are fixed numbers of bind var in every step..
Re: Execute Immediate [message #576943 is a reply to message #576940] Mon, 11 February 2013 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
because the table will have a maintenance screen where the user will be able to build a query not by typing them but by choosing columns from the list box and put mathematical operations.


If it uses your tool, what prevent them from doing from SQL*Plus or any other tool?

Quote:
he concern of my boss are other programmers


Same answer.

Quote:
I also think allowing only certain users to do dml operations in this table will also guard it from undesired queries.


Or the opposite to insert/delete/update statements in order to hack the database or your application data.

Regards
Michel
Re: Execute Immediate [message #577016 is a reply to message #576943] Mon, 11 February 2013 17:54 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
"If it uses your tool, what prevent them from doing from SQL*Plus or any other tool??"

They have to login in our application in order to do dml operations. Because we have our own role table the initial grant for all supposed users are only to access the database then the system will check our role table to make grants depending on the user level so there is no way that they can delete or do update if they will use other tool since the initial grant is just to login.. when it comes to using the maintenance screen they will not type anything because all options are listed in list of values.



So what is your advice then? Do I need to use dbms_sql? I think it will not change anything since you can also do DDL operations in dbms_sql.
Re: Execute Immediate [message #577024 is a reply to message #577016] Tue, 12 February 2013 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if they will use other tool since the initial grant is just to login..


And what prevent them from activating the privileges you do in the application?

Quote:
So what is your advice then? Do I need to use dbms_sql?

Quote:
EXECUTE IMMEDIATE or DBMS_SQL is the same thing (regarding the risk).


Regards
Michel
Re: Execute Immediate [message #577028 is a reply to message #576927] Tue, 12 February 2013 01:07 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
How can they activate grants if they are not allowed to do so? The only way they can make grants is through the application (that is if that user is allowed to do so based from our role table) because if they will use other tool in accessing the database the users' other privilleges will not be activitated the user will just be limited to logging in.

Re: Execute Immediate [message #577029 is a reply to message #577028] Tue, 12 February 2013 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You say you dynamically grant and revoke the privileges (using GRANT and REVOKE statements)?
Is this a client/server or a n-tiers application?
And what happen if the application crashes or if the user closes its window or kills the process? Privileges are there forever?

Regards
Michel

[Updated on: Tue, 12 February 2013 01:16]

Report message to a moderator

Re: Execute Immediate [message #577036 is a reply to message #577029] Tue, 12 February 2013 01:56 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
Of course the granting and revoking of privilleges is not in the application but in the database trigger. Our system kills sessions which are inactive for an hour although log off trigger will not be able to capture it, we a have a procedure that runs at the end of the day to revoke grants for those users who are not supposed to do work beyond office hours unless specified. Let's say that for some reasons the privileges stay, but these privileges are still based from our role table. That user will still not be able to dropped tables or to grant privillege if he is not allowed to.
Re: Execute Immediate [message #577042 is a reply to message #577036] Tue, 12 February 2013 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Our system kills sessions which are inactive for an hour although log off trigger will not be able to capture it, we a have a procedure that runs at the end of the day to revoke grants for those users who are not supposed to do work beyond office hours unless specified.


So each user have the day long to hack your database.

Quote:
Let's say that for some reasons the privileges stay, but these privileges are still based from our role table. That user will still not be able to dropped tables or to grant privillege if he is not allowed to.


Don't be so sure of that; do you know all privilege escalation methods?

Regards
Michel
Re: Execute Immediate [message #577046 is a reply to message #577042] Tue, 12 February 2013 02:37 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
hack? oh yes the user can update or insert but his username is recorded and he will be accountable for that and our users are not allowed to make unauthorized software installation thier only means of accressing the system is through the application . and we don't have a user in our production db that has drop table privillege. I don't know all of system privlleges but what I do know is even if you have a certain privilige but if you were not granted with admin/grant option you will not be able to grant it to other users. if your account is only limited to accessing 4 tables with dml operations only, how will you able to drop tables if you don't have the privellege to do it?.. please enlighten me.
Re: Execute Immediate [message #577047 is a reply to message #577046] Tue, 12 February 2013 02:42 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
I have no idea about privilege escalation method please give me some background
Re: Execute Immediate [message #577051 is a reply to message #577046] Tue, 12 February 2013 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
hier only means of accressing the system is through the application .


They don't need anything more than what is already installed to be able to access the database and many things does not need to be installed just launch the executable or script.

I will surely not post hacking method here but with only their privileges ("your account is only limited to accessing 4 tables with dml operations only") you will able to delete or modify any data.

Regards
Michel
Re: Execute Immediate [message #577066 is a reply to message #577051] Tue, 12 February 2013 03:04 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
we don't allow executables to run in our operating systems if they are not in the list provided buy the it department. we have an application for that prevents that. yes you can delete data but your account is recorded. is just an equvalent of misusing the application. so where do you run the script to make a connection?
Re: Execute Immediate [message #577072 is a reply to message #577066] Tue, 12 February 2013 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we don't allow executables to run in our operating systems if they are not in the list provided buy the it department.


How don't you allow this? I'm interesting in that.
In addition, which kind of Oracle client have you install? Instant client? OCI?
Note you can use ODBC, JDBC, VBA (as soon as there is Word or Excel on a PC you can access databases)...

Regards
Michel
Re: Execute Immediate [message #577153 is a reply to message #576927] Tue, 12 February 2013 17:55 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
We have a software that prevents the users from activating executable files if the name of executable is not listed in our defined list actually you can trick that application by making your own executable and naming it as one of the allowed executable programs. for example we allow notepad.exe to run you can rename your executable as notepad.exe but it must also have the same bytes because the software checks the bytes of the executable aside from the name.

as to connecting to database using word I just hope they don't know that hehehe, but still their accounts and actions will be recorded so they will be accountable in anything that they will do. I don't know about the difference between instant client and oci we just plainly call it oracle client. hehehe what's the difference between the two?
Re: Execute Immediate [message #577203 is a reply to message #577153] Wed, 13 February 2013 06:36 Go to previous messageGo to next message
javed.khan
Messages: 308
Registered: November 2006
Location: Banglore
Senior Member

If a programmer is putting DROP instead of something what you wrote... Nobody and no option can save you... If that "Not-Very-Good" fella want to harm he will delete your either of the statements and put his DROP.. Tell your Boss to hire trustworthy ones.

[Updated on: Wed, 13 February 2013 06:40]

Report message to a moderator

Re: Execute Immediate [message #577213 is a reply to message #577203] Wed, 13 February 2013 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Tell your Boss to hire trustworthy ones.


How do you know if someone is trustworthy? Wink

Regards
Michel
Re: Execute Immediate [message #577230 is a reply to message #577213] Wed, 13 February 2013 10:43 Go to previous messageGo to next message
javed.khan
Messages: 308
Registered: November 2006
Location: Banglore
Senior Member

Laughing Yes Its a complicated world...
Re: Execute Immediate [message #577251 is a reply to message #576927] Wed, 13 February 2013 13:39 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Hi xpat83.

I did an application in .NET like yours. I do not understand why you have to do the task in the DB when is perfectly doable from the application itself. Let's say, if I am not misunderstanding your idea,the user can dinamically build a formula but there's stored values (FROM XXXX, WHERE XXXXX) in the oracle database, so why don't you create a read only user, retrieve the values in a Dataset and then do the math or whatever you are doing within the application ?

It would me much better and worked perfectly for us.

Regards,
Steve.
Re: Execute Immediate [message #577267 is a reply to message #576927] Wed, 13 February 2013 17:47 Go to previous messageGo to next message
xpact83
Messages: 218
Registered: October 2008
Location: philippines
Senior Member
@jave.khan

Indeed as far as with regards to programmers they really have many chances to sabotage a system that they are working on

@michel

Maybe there should be an extensive background checking of employees but still it won't guarantee that they will not do something bad.

@Steve

The building of formulas is done in the applicaton but the execution is done by stored procedure using execute imemdiate. The problem there is the user who are accessing the formulas should also have dml grants on certain tables because these are also part of his role. Maybe what I should do is when a user called the said stored procedure his grant for dml operations will be revoked and will only be granted after the execution of the stored procedure.

Re: Execute Immediate [message #577270 is a reply to message #577267] Wed, 13 February 2013 17:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the execution is done by stored procedure using execute imemdiate.


And what is the owner and specification of the procedure (first lines before declaration part)?

Regards
Michel
Re: Execute Immediate [message #577354 is a reply to message #577270] Thu, 14 February 2013 07:01 Go to previous message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
@xpact83.

I'm not agree with that. What we do is allow the user to build the query they want, but with read only privileges, oracle return a dataset and all the formulas is done within the application becasuse it's much much safer. I'm giving you this example because I didn't see any DML transactions in the statements posted above.
by the way, why this obsession with doing the math formulas in the DB when it's perfectly doable from the application itself ? I can not understand that!

Steve.

[Updated on: Thu, 14 February 2013 07:02]

Report message to a moderator

Previous Topic: how to find degree for parallel DML.
Next Topic: Find Out first thursday for each month
Goto Forum:
  


Current Time: Thu Oct 02 01:11:51 CDT 2014

Total time taken to generate the page: 0.10419 seconds