Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Shruti.

Asked: February 12, 2001 - 9:14 pm UTC

Last updated: May 10, 2005 - 2:30 pm UTC

Version: 8.0.4

Viewed 1000+ times

You Asked

Hi Tom,


To my previous question and your reply was as given below thank you very much for the same . I had some more quesry on that. Our problem was that some of our smart users they write anonymous PL/SQL block and just run them on SQL prompt though I have not given them (INSERT,DELETE,UPDATE,EXECUTE) access with the help of PRODUCT_USER_PROFILE.

How can we stop that?


2.I am using PRODUCT_USER_PROFILE for restricting user from
using INSERT,UPDATE command. For some users I want to stop
access to SqlPlus and Sql Forms itself. Is there any way of
doing it.

This would really be a great help to me

Thanks

and I said...

1) you cannot.

2) for sqlplus, you would disable all possible commands (sqlplus
and SQL commands). there is no way to disable access to sqlplus
itself.

Forms does not use the product_user_profile table.. You would
have to incorporate this functionality into your forms yourself.





and Tom said...

1) fire your smart users. They ar

Actually, I prefer to never give anyone insert/update delete. I would put my application login in plsql packages -- grant execute on the packages. The "smart" end users cannot even see the tables, let alone touch them. The application is the only way to access the data.

you can use PUP to disable DECLARE, BEGIN, and EXECUTE -- that'll stop PLSQL from being executed.

2) Say you could cut off sqlplus (you cannot, its just a program). What stops them from downloading TOAD, or using Excel+ODBC, or ...... any of thousands of other things that connect to Oracle.....


You might be interested in my answer to:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1445204371337 <code>
as well.



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How do you do that

hash, May 10, 2005 - 2:01 pm UTC

Hi Tom
I've seen this type of answer from you at quite a few threads but i really could'nt find a starting point for this. Like you said

Actually, I prefer to never give anyone insert/update delete. I would put my
application login in plsql packages -- grant execute on the packages. The
"smart" end users cannot even see the tables, let alone touch them. The
application is the only way to access the data.

can you give an example on how you put your application login in plsql packages -- grant execute on the packages
just a tiny example will give me a way to work this out
thanks

Tom Kyte
May 10, 2005 - 2:30 pm UTC

create package demo_pkg
as
procedure hire_emp( ...... );
procedure fire_emp( ...... );
procedure transfer_emp( ..... );
procedure give_a_raise( .... );
......
and so on
end;

put your database logic, well, in the database.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library