Control which apps can connect to DB. 1

[jdemmi]

OK so here's the deal. I want to control which exeutables can connect to my DB. Obviously I'd also like to set that at a user level (DBAs and Admins (like me) need a little more flexibilty than the average end user).

So I started to search for existing methods and found this...

http://www.banjora.com/2008/02/16/blocking-exe-from-oracle/

Is this one sound? Overkill? Is there a better way? Is there an easier way?

I'd also like to INCLUDE executables, taking "white-list" approach for obvious reasons (much harder to know what apps there are out there that MIGHT connect -- which we KNOW which ones we ALLOW to connect)

If I've left anything out please let me know.

Thanks.

-- Jason
"It's Just Ones and Zeros

 

[Thargy]

Any kind of trigger, especially one used for security, is an unmitigated disaster.

Triggers are nothing to do with security. Controlling user access and privileges is to do with security.

Controlling which executable can access the db is irrelevant, you need to control users. For example, if you block sqlplus from using Oracle, then you can script no changes against your db, you have to use some other executable.

If you block sql developer (written by Oracle corporation) your developers will likely have something to say on the subject, as they will be unable to do their job.

One controls users, not applications. If a user is authorised to make changes to your db, you (as a DBA) don't give a tinker's curse about how they make those changes, so long as they are done in an auditable, reliable, reproducible and reversible way. If they use Java, ProC, super-wizzy mega-widgets version 7.2354 or whatever, provided your privileges and permissions are right, users can only make authorised changes. The application is irrelevant, controlling changes is everything.

It's simple, give every user an account with a user name and password. Grant the minimum possible privileges to each user, such that they can do their job, and no more. Thus, whichever application they choose to use, they can do no harm (unless grossly incompetent and/or malicious) and can interfere with no other users.

Oracle's security is just fine, so use it!

Regards

T

 

[jdemmi]

So then...how do you control users who don't know a lick about writing SQL queries (outside of what they've fumbled their way through in tools like Discover) from using "unsupported tools" for writing queries that impact DB performance?

There's my dilemma -- we've implemented Oracle's Discoverer product on one of our 3rd party apps. It's fine and the users are OK with it. But they're controlled because, among other reasons, we manage the business areas for them and do not let them edit/add any table or view joins. They can simply select fields (which we've revealed) to include in queries and add constraints or formatting where necessary. This is not to say that they cannot query themselves into invalid data but it is a decent layer of protection against such. Plus it includes query governors which allow admins to prevent any extrodinarily large queries from be executed/completed.

So anyway...with this implementation comes the need for DB level accounts. Now I am no DBA and I don't even pretend to play one on TV. So my DBA setup the users with the necessary account and (assume) minimum privileges and tied them all to a "reporting" role. This role is then in turn granted access to only those objects in the schema which we have deemed necessary.

Here lies the rub...these users can connect to DB via any tool out there (provided they have the Oracle client and the necessary sqlnet.ora file). The problem here is that these other tools do not have predefined business areas (configured joins, supressed fields, etc) and the users are now free to access/query those same objects we previously provided access to for Discoverer purposes -- all without any constraints.

Yes because of the previously configure Oracle security, they cannot update, edit, delete, truncate, drop, rebuild indexes, create functions, triggers, etc. They can only select. And in most hands that's not dangerous (see your grossly incompetent). But it can access DB performance. I've seen it happen.

Maybe this is not a problem in your eyes or in your environment. It is in mine. And you haven't answered my question...beg pardon....you did say triggers are disasters waiting to happen. Other than that you provided no specific suggestions for alternatives -- we're already providing users with the minimum possible privlieges....so that's not an alternative.

And lastly...one should control applications AND users. There is no reason not to. The first level of security is closing the door, the second is locking it and the third is removing it altogther, leaving only the one you want them to use.

-- Jason
"It's Just Ones and Zeros

 

[Turkbear]

Hi,
Only allow those users access to Views that your folks have developed ( revealing only those fields you want them to select - It is what Discoverer really does in the background).

Even then, a truly incompetent user could always add a where clause ( like 1=2 ) that could impact performance...The real answer is user control and education.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jdemmi]

I understand that 2 parts of the answer are user control and education. We've taken user control about as far as the system allows. Education is ongoing and takes time -- for a lot of this you either get it or you don't. The fact that many users "don't" will not cause the business to disable their access to these systems.

I'd still like to limit the applications (executables) which can connect to my database. I don't think that's an unreasonable request. I am not, though, wild about a login trigger. While functional it seems like it would cause more overhead then it would prevent from being lost.

That said I am considering authoring my own app that runs on ny Windows app server and monitor the sessions stored in V$SESSION and killing any sessions (as cleanly as possible) that don't belong.

Unless anyone has a better idea....

-- Jason
"It's Just Ones and Zeros

 

[Turkbear]

Hi,
Not sure why creating views and denying access to the underlying tables is more trouble then trying to determine what sessions do not 'belong' - in many,many shops this is a standard practice implemented by the DBAs for security.
Oracle's architecture can make determining what sessions are 'killable', without viewing what the session is doing, difficult and killing the wrong session can cause real problems.








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Thargy]

doing things in truly haphazard fashion, I have had dealings with a system that has a logon trigger, and it's caused no end of trouble, confusion and unnecessary work. You are quite right to be wary of them, and avoidance is recommended (in my case as the result of bitter experience).

I'm relieved to hear that you have granted users restricted privileges and are achieving most of what you want that way.

However, your response seems a little contradictory. You state

the users are now free to access/query those same objects we previously provided access to for Discoverer purposes -- all without any constraints.

and yet you claim

we're already providing users with the minimum possible privlieges

These statements can't be simultaneously true. If I have understood correctly, you grant a role with restricted privileges to users of your discoverer tool, and it limits the carnage they can cause, by dint of 'under the cover'

configured joins and suppressed fields

. Therefore this application is providing another level of protection to prevent "the query from hell" running and restricting its impact via throttling. Your best solution is to create yet another role, which provides the minimum privileges and permissions, by views or VPD (Virtual Private Database), which would seem an excellent way of achieving what you want. Grant users that role only, and once you've checked that it causes no problems, migrate the discoverer users to that role too. Then remove the too-liberal role.


The notion that you can control applications and/or executables is risible. How do you programatically detect the application? By inspecting the value of PROGRAM in V$SESSION perhaps? Whats to stop a user creating an executable of their own, but causing it to spoof its name to one that you allow in? Take that last as rhetorical. You don't control applications because you can't. If connection details are known, aliens from the planet Zork can connect to your system, with an application written in Zorkish. Ditto a wannabee hacker in China/Russia etc.

If however, said connection details lead to a restricted account, both the aliens and chinese can do no harm.

If applications could be controlled, there would be no such thing as a hacker.

Dealing with your issue about runaway queries consuming too much resource, Oracle does provide help in this area, by means of profiles.

http://www.dba-oracle.com/t_resource_profiles.htm

is a good intro to the subject, but with boring predictability, let me say "read the oracle documentation".

I suggest that you create the fully restricted roles, profiles and/or VPD ASAP. What would happen if management decided to use a different tool, which provided none of the aforementioned protections? The db must be capable of defending itself at all times, that way you can sleep easy.



Regards

T

 

[jdemmi]

Part of me wants to not respond to your post...you're not answering my question. But part of me can't resist.

1. "the users are now free to access/query those same objects we previously provided access to for Discoverer purposes -- all without any constraints." In Discoverer we can build the joins, hide fields, translate field names, convert booleans to text for the end user so the application views are much cleaner and relevant to the user. All of that goes out the window if these users user their discoverer (db level) accounts to login to the DB via any other app (Excel, Access, SQL+, etc)

2."we're already providing users with the minimum possible privlieges" -- we are...they have select only access to only the objects we specify. Where in Oracle's security is the mechanism that controls what door the user comes in through? Am I missing something?

My question still stands. How can you restrict which executables can connect to an Oracle DB? I am not asking is this SHOULD be done. I am asking how it should BEST be done. The trigger idea I found is garbage...I am not a trigger fan to say the least and a login one is a really bad thing. So I am still curious to know if there are other proven methods....

Here's how I am doing it now (so far). I've written a small program which periodically queries the v$session table and grabs the SID, SERIAL#, USERNAME, OSUSER, TERMINAL, PROGRAM, and MODULE columns. (I wonder why Oracle records this info anyway....hmmmm). The monitor then checks the user/app/module combo against a predfined whitelist and if the user/app/module combo is not in the list it sends me an email and logs it to a file (soon it will kill the session).


Does this make my DB hack proof? No.

Does it even make it safer? No, but argueable.

Does it force my users to stay within a corporate defined and supported tool set? Yes.

100% of the time? No. (The only guarantees are death and taxes.)

Is it flexible? Yes, the whitelist can be edited.

Is it a waste of time? Not in my environment.

And by the way...PLEASE Don't get me wrong. I am not suggesting Oracle's security is deficient in anyway Nor am I suggesting that your posts were in accurate. But you were not answering my question. You were/are providing the (EXCELLENT) answers to Oracle security best practices.

-- Jason
"It's Just Ones and Zeros

 

[Thargy]

Jason,

I believe that I can understand your frustration to a certain extent. Part of my "not answering the question" was that I was trying to point out that the approach which appears to have been forced upon you by your superiors is fundamentally wrong, and no amount of kludgy triggers and/or code will make it otherwise.

W.R.T. your point 1, my suggestion was that these joins and renamed columns etc. should be built into the db by means of views. They can easily do all that you describe, and would provide the same clean easy interface that you want, to all users, regardless of their method of accessing the db. What I'm suggesting is that you duplicate all the formatting and tidying up that's in the discoverer tool native in the db, and then provide access to only this via a new role. All users would then access the db via an account that gave them that role.

I would suggest a short period of parallel running, with both the discoverer and new accounts available. Then, once it's clear that all is well with the new approach, revoke the discoverer account, and provide it the new one instead. Then remove all the tidying up from discoverer.

That way, regardless of log on application, the users get clearly presented information, and the need to monitor log on applications ceases to exist. Tom Kyte has some excellent advice on this subject, and it might be beneficial to peruse his web site.

As he has so appositely stated, history shows that users want to access db's in more and more ways with an ever-increasing number of applications. When discoverer is defunct, one thing will persist, and that is the data. Data is vital, discoverer isn't. One day, it will be obsolete but folks will still want access to their data.

What remit do you have? Can you report to managers that providing correct security and control is only possible by incorporating all the tidying up into the db? Will they (as I suspect) fail to see the business need for this and just tell you to go away?

Regards

T

 

[jdemmi]

I see your points and yes, I am fighting some of those issues. Which is why I am using my monitor approach as a stop gap.

But one LAST question. I know how to create views and understand completely how to create new ones which mimic those we've granted Discoverer access to. But how can I manage the joins in a non-Discoverer environment? We're talking about numerous (tables/views). Enough that the really shouldn't be "pre-joined" in a view.

-- Jason
"It's Just Ones and Zeros

 

[Turkbear]

Hi,
Views are just stored queries, so they have no impact on the database until used...The tables are not actually pre-joined in any way - the view 'does' the joining on demand.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jdemmi]

Maybe I didn't explain myself clearly...

We have multiple views defined in Discoverer. In many cases we've joined some of the views together. These joins are lost outside of Discoverer.

Example:

The following views exist in the DB

HEADER VIEW
DETAIL VIEW
SUB DETAIL VIEW

In Discoverer we've joined..

HEADER TO DETAIL
DETIL TO SUB DETAIL

These joins are lost outside of the Discoverer world.

It would not be a good idea (IMO) to build all of these into "new" views for use INSIDE or OUTSIDE of Discoverer.


-- Jason
"It's Just Ones and Zeros

 

[Turkbear]

Hi,
Views are views regardless of what app uses them..

Still not sure of the problem that it would cause and it would control completely what users could access.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jdemmi]

I can't explain it -- obviously -- but in my case you cannot control the joins, effectively, for the users without a tool like Discoverer...

The app has numerous "modules". Each module contains NUMEROUS views, many of which are structured like my post above (header, detail, sub1 and in some cases sub2).

To control the joins outside of Discoverer you're suggesting that I build NEW views which join these default app views together.

That just FLAT OUT doesn't make sense and I cannot imagine that it's a best practice. Just looking at the most basic of examples..

Assume the following exist, 3rd part application provided HEADER VIEW and DETAIL VIEW

In Discoverer they're joined for the user.
If the user only selects from the HEADER view, only thee header view is accessed. If the user selects from the detail table the join was predfined and CORRECT.

Outside of Discoverer this doesn't exist. So if I follow your suggestion -- as I understand it. The user would not have access to the above views and would instead have access to a new view HEADER_DETAIL_VIEW (join of the 3rd party app's header and detail views). This is overkill....if the user only wants header info -- we're still querying the detail. Not to mention the fact that since the users need access to 30 or 40 views the combination of of them into a single view would be overwhelming.

Maybe your just lucky and all of your users understand how an RDBMS works. Mine don't -- but this doesn't change the fact that they need access to the data. I am simply trying to give it to them in the most stable and safest (for them and for me) method possible. For the company I work for, Discoverer is that tool. But..looping all the way back to the beginning -- the user requirements for that tool allow access via other doors. I think we're done here.

Your environment is bullet proof because you've implemented the best and tightest Oracle security available and all your users are RDBMS/Oracle educated and could even fill in as a DBA if needed. I'm not so fortunate.

Enjoy Utpoia....it doesn't last long.

-- Jason
"It's Just Ones and Zeros

 

[Turkbear]

Hi,
After supporting several thousand Oracle users of all skill levels, including practically none, since 1987, I hardly expect to experience utopia..

What I do know is using views ( well designed ones) is a standard way to control access to data - no regular user ( non-developer) needs access to tables directly..Many places will not allow any ad-hoc access to the database by anyone but report designers, DBAs and a few developers to avoid just the issues you raise.

Enough discourse, its your shop so it is your choice
( unless your DBAs decide to redesign the data)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Thargy]

that was part of my notion too. I inferred roughly what you stated above, and that discoverer was doing some automatic stuff for the users.

I was suggesting that you make many views which do what discoverer does for the user, but not necessarily every permutation and combination thereof. I would never suggest (and I don't think Turkbear was, but can't be sure) that you join all these views into one whopper. It would be an unmaintainable nightmare with disastrous performance.

Your post mentioned the problem of joins being lost outside discoverer. That's precisely why I suggested creating duplicate views that provide all of this stuff natively. That way, with or without discoverer, the correct data (and only the correct data) is available. If users use Excel, Access, sql or anything else, they get good data.

To be specific, I would create individual views of header, detail, sub1, sub2 ... sub n. A HEADER_DETAIL view would be the union of header and detail and so on. For a second business function I would do the same thing.

I can feel the pain in your phrases about the users and their not knowing about databases. However, providing data in only ways which are guaranteed to be good means that users are not required to know anything about the db. That's the beaute of such a solution.

Jason, you seem to be really harried by this issue, and the apparent lack of useful (as opposed to theoretically sound) responses.

Well, I suggest the following. Why not approach your superiors, and state that you are having difficulty providing adequate security whilst maintaining data integrity in reports etc. Ask for authorisation to do the best possible thing in these circumstances - carry out a test. Take say 3 or 4 business functions which make use of discoverer's abilities to do joins for the user. Recreate these with views in a separate schema, (as alluded to above). Then grant users access to the data, and rejig discoverer to give no "help" with those few functions. Access the data using excel and or anything else, and verify that the views do what they claim to, and that it is always provided correctly.

It would also give you an opportunity to guage the amount of effort involved in creating the views, cleaning out discoverer's 'help' etc. Bear in mind that creating a separate schema would be a one-off task, so don't factor that into your general conclusions.

I would be glad to assist with some of the scut work, if you post create table statements, desired output and sample data. Howzat?


Regards

T

 

[Turkbear]

Hi thargtheslayer,
You have encapsulated what I was trying to suggest as a good course of action ( and you are right, I would never recommend a view like that).









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jdemmi]

thargtheslayer ---


1. I am not and was not "really harried by this issue, and the apparent lack of useful (as opposed to theoretically sound) responses". How would you like it if you went to a restaraunt and ordered a PBJ only to be told that you don't need a PBJ -- you can make that at home.


2. To nnot create a view for every permutation increases the possibility that a user will (either by need or desire) attempt to join two or more views.

3. You're both missing the question here. We have a predetermined approved set of tools here. We simply cannot have users accessing the data outside of those tools. If they deem a new tool a requirement it and the reasons it is deemed essential will be reviewed and approved before placed into use. The data in question is in a VERY controlled environemnt (some of which is perceived -- for instance few realized this "issue" before I shed light on it)

4. I did present this to my superiors -- and in doing so mentioned that I'd already created a monitor which will alert us when any non-approved apps connect to the db. They're curious to say the least and will review our path forward after it's been monitoring our prod db for a few weeks. Oh and BTW....don't think for a minute that I wouldn't include some of the suggestions in this thread.

5. To replicate, as close as possible, Disco's help outisde of Disco would be a HUGE undertaking -- at least for the intial build. Maintenance wouldn't be too bad though. (Keep in mind -- I am already using views ONLY -- no one has access to the tables -- except the DBAs and myself)

I really do thank you both for your tips....but they never answered the question asked. And it is a valid question. What's the harm in knowing what your users are up to? None. In fact -- it might teach you something....what they prefer, what they like, what they use -- maybe an expensive tool could be elimainated. Who know's?

BTW -- the question as initially asked was overkill -- there's, at least in my case, no need to kill their sessions if they access the DB with an app outside of the "white list". Notification of such is sufficient, so I wrote a quick app to monitor the sessions, compare them to the whitelist, write offenders to a log and email the log to me (my DBAs are interested as well -- curious -- granted they can see this info at any time but who wants to stare at DB sessions all day?

-- Jason
"It's Just Ones and Zeros

 

[Turkbear]

Hi,
maybe then add this tool to your approved list ( for admins and DBAs only..) - As far as I have been able to tell, it is the best performance and general Oracle monitor around:

http://www.quest.com/spotlight-on-oracle/

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi again,
Just a note - if you place a display ( if your shop is well-financed, a big flat screen ) showing this tool's monitor screen, the color-coding will alert DBAs to issues so they do not have to continually stare at it, just notice it in passing.





To Paraphrase:"The Help you get is proportional to the Help you give.."