0

Hi guys,

I'm developing an application to perform SQL query's from ASP.NET in ORACLE and I want to get a list of all DDL, DCL, TCL reserved words to prevent changes to database.

Maybe, ¿There is a database table in oracle with this list? like v_$reserved_words.

I will really appreciate your help guys, and the solution has to be right that way, because i connect to database with an generic user with all privileges and my company doesn't allow me to change that.

Faruck
  • 11
  • 3
  • 1
    Do it the other way round: maintain a list of allowed statements - that list will be _much_ shorter. Btw: there is indeed a `V$RESERVED_WORDS` view but it won't help you as it doesn't tell you what kind of statement this reserved word belongs to. –  Dec 03 '14 at 12:56
  • 6
    I would suggest, instead of checking for certain statements you should control what the user can do by granting just the permissions required to use the app. If they don't have the ability to execute and ALTER TABLE statement then the statement will fail. That is more typical and would be much easier. – Mark Wagoner Dec 03 '14 at 13:01
  • Hi, thanks your help, but how can I get this allowed statements? And really, there is no way to know the restricted words? :S – Faruck Dec 03 '14 at 13:03
  • 1
    This is not the way you secure a system. You should allow the caller to send any SQL they like; the permission system in Oracle was *specifically designed* to stop them doing anything you don't want them to do. Don't try to rewrite the wheel. – Jeffrey Kemp Dec 03 '14 at 13:57
  • Are DML (`INSERT`, `UPDATE`, `MERGE`, etc) and anonymous PL/SQL blocks (`begin null; end;`) allowed? – Jon Heller Dec 03 '14 at 18:55

1 Answers1

4

Just create a new user and give him the connect role and just select permission on the tables, views he is allowed to see.

create user test identified by notagoodpassword;

grant connect to test;

grant select on schema.table to test;

Edit: if you want the user to call a procedute/function you need

grant execute on schema.procedure to test;
a.j. tawleed
  • 894
  • 1
  • 8
  • 22