Re: Restricting user access to a database?

On Mon, 21 Jul 2003 20:14:16 +0100, Paul Brewer <paul_at_paul.brewers.org.uk> wrote:

> "Tim Kearsley" <tim.kearsley_at_milton-keynes.gov.uk> wrote in message
> news:725736ef.0307150247.72d6f12e_at_posting.google.com...
>> Hi all,
>>
>> We have a situation here where a number of users access an Oracle
>> 8.1.7.2 database running on AIX 4.3.3 on RS6000 hardware. The clients
>> use PCs running an Oracle Forms application on Windows 2000.
>>
>> Suddenly, out of the blue, management has got worried about users
>> making "unauthorised" access to the database by running SQLPlus and
>> issuing their own queries (or updates, inserts etc.). I've therefore
>> been asked as to how users' access can be restricted to just using the
>> application.
>>
>> I have responded initially by making the point that if the client PCs
>> have suitable ODBC drivers installed (and I believe they do) then
>> access could be by a whole range of applications - Microsoft's Word,
>> Excel and Access are obvious candidates.
>>
>> So, the question:
>>
>> Do you see any way of restricting the users to only accessing the
>> database through the "authorised" application? I don't believe
>> de-installing ODBC and SQLPLus on every client is an option and I
>> don't see how anything can be done at the server end. After all, a
>> SQLNet connection is all the database sees and I presume it doesn't
>> "know" whether that connection originates from a Forms application or
>> SQLPlus or anything else?
>>
>> Any thoughts very welcome.
>>
>> Regards,
>>
>> Tim Kearsley
>> Database Manager
>> Milton Keynes Council
>

One way to achieve this 'only-through-the-app' security is to ensure that nobody is given direct grant of privileges, but that all privileges are granted through roles. When you create those roles, password-protect them (Ie, 'create role xxx identified by yyy'). Then re-code your app. so that when a user clicks a button, or selects a menu option, it effectively issues the command 'set role xxx identified by yyy'. There's a package called dbms_session which has the procedure set_role to do the same thing in pl/sql.

Then, no matter how your users connect, unless they know the password 'yyy', they can log on all they want, but can't actually do anything. And that would apply to sql*plus, ODBC, JDBC, Toad, you name it. Obviously, you have to make damned sure that the 'yyy' password is never compromised.

Obviously, too, this approach has certain drawbacks (to put it mildly). First it requires your application to be re-coded. Second, if 'yyy' is ever discovered to be the password, it needs to be re-re-coded. And perhaps more significantly, privileges granted through a role can't be exercised via pl/sql modules.

An alternative would therefore be to look at row-level security (also known as Virtual Private Database, also known as Fine-Grained Access Control). You put a policy on each table. The policy adds a WHERE clause to each SQL statement issued, dependent on some condition tested for at the time it's issued. In your case, the policy might be "if they are using application X, then '1=1', else '1=2'". If a user logs in via sql*plus, ODBC, JDBC, Excel or whatever, then their query gets re-written as 'select salary_details from executive_pay where 1=2'... and thus no rows are returned. If they query via the proper application, the query is '...where 1=1', and thus all required rows are returned.

If you look in v$session, I believe there's a column for program name, so the database *is* able to query the nature of the app. that has connected, and that would be the basis for determining which WHERE clause to append. (Though in fact, you are rather more likely to want to look into the SYS_CONTEXT function, which can be used to determine all sorts of things about the connection, such as its IP address. I don't remember for certain, but I suspect SYS_CONTEXT would be able to tell you the program name, too, and without the hassle and expense of having to query a v$ view every time someone issues a query).

Advantages of RLS (which is, btw, implemented using the DBMS_RLS package) are that it is all implemented on the server-side, so no re-coding of the app is required. It is also (I hate to use this word!!) unbreakable, in the sense that there's no way around this: there's no password to discover, and the policy is an integral part of the table, so it can never be ducked around. Disadvantages are that it is modifying the queries your application issues, and hence quite possibly modifies the execution plans... what was once a beautifully-tuned app *may* turn into a pile of stodge. Also, it's a bit of a leap in the dark, in the sense that it doesn't actually prevent people accessing via non-approved methods... you (ie, Management) just have to trust that the RLS policy will be applied in all cases.

Worth investigating, nevertheless.

~QM Received on Mon Jul 21 2003 - 15:04:04 CDT