Re: V$ tables

In article <3D2DABC9.F38D4991_at_exesolutions.com>, Daniel Morgan <dmorgan_at_exesolutions.com> writes
>Pete Finnigan wrote:
>
>> Hi All
>>
>> I know this reply is a month late but I wanted to add to this thread as
>> its relevant for me. Someone in the thread suggested its OK to grant
>> access to all v$ and dba_ views?.
>>
>> I am currently writing a step-by-step guide for securing Oracle for the
>> SANS institute and I am interested in the groups view "on security
>> grounds" of which "dba_", "v$" or "all_" views should have access
>> granted to them. Obviously developers and others sometimes need access
>> to some of these views for tuning, system info etc - but exactly which
>> ones? and why are they safe?
>>
>> In my view, for security we should always use the "least privilege"
>> principle and remove all default privileges and grant back what is
>> actually needed. Most databases I have seen are not very secure and
>> usually development databases are worse. Most unauthorised access is
>> employees disgruntled or otherwise looking at what they should not.
>>
>> For instance I would not allow access either directly or through roles
>> to DBA_USERS. Access to that reveals the password hashes ( although
>> there are a number of other ways to get at them), the "values" command
>> can only be used as a DBA so what use are they to anyone?, well its
>> possible to use the hash. A brute force attack of the password can be
>> done off line for instance when the hash is known. Even access to
>> all_users might seem innocuous but it gives the reader of the view a
>> complete list of users and then a simple SQL statement can generate a
>> set of connect attempts for all users. There are many more examples I
>> can think of.
>>
>> Anyway I am interested in everyone's view of which views are "really"
>> not a risk. to be granted to developers.
>>
>> cheers
>>
>> pete Finnigan
>>
>> pete_at_peterfinnigan.demon.co.uk
>> pete_at_petefinnigan.com
>>
>> http://www.pentest-limited.com/oracle-security.htm - "Exploiting and
>> protecting Oracle"
>>
>> http://online.securityfocus.com/infocus/1522 - "A simple Oracle Security
>> Scanner"
>>
>> http://www.pentest-limited.com/default-user.htm - "Oracle Default User
>> and Password List"
>>
>> http://www.pentest-limited.com/utl_file.htm - "Extracting Clear Text
>> Passwords from the SGA"
>>
>> In article <3D0A137B.F00D3EC9_at_exesolutions.com>, Daniel Morgan
>> <dmorgan_at_exesolutions.com> writes
>> >Charlie Edwards wrote:
>> >
>> >> Hi,
>> >>
>> >> I would be grateful for a DBA's opinion on this ...
>> >> Which V$ tables should be available to developers as a matter of
>> >> course?
>> >>
>> >> v$process, v$session, v$lock, v$sqlarea, v$sqltext, v$locked_object,
>> >> v$session_longops ...
>> >>
>> >> Any others?
>> >>
>> >> For that matter, is there any reason why developers should not have
>> >> acess to ALL V$ tables in a development environment?
>> >>
>> >> Thanks,
>> >>
>> >> CE
>> >
>> >Please allow me to ask the question in a different way. What possible
>> >justification is there for not giving developers access to all v_$
>> >views? And for that matter all dba_ views too?
>> >
>> >Can they change anything?
>> >Can they do any damage?
>> >Is there a negative side to having developers that know and care about
>> >how their work will impact production?
>> >
>> >Give them the whole darned thing and be glad they asked.
>> >
>> >The only reason I have ever felt for DBAs denying access to the data
>> >dictionary views (other than ego) is that they are so insecure with
>> >their own skills that they are afraid someone might see that they
>> >haven't been doing their job well.
>> >
>> >Daniel Morgan
>> >
>>
>> --
>> This email and any files transmitted with it are confidential and
>> intended solely for the use of the individual or entity to whom they
>> are addressed. If you have received this email in error please notify
>> the system manager at admin_at_pentest-limited.com
>> --
>> Pete Finnigan
>> IT Security Consultant
>> PenTest Limited
>>
>> Office 01565 830 990
>> Fax 01565 830 889
>> Mobile 07974 087 885
>>
>> pete_at_peterfinnigan.demon.co.uk
>>
>> www.pentest-limited.com
>
>I agree with your basic principle. That is why I believe, for example, that
>the canned roles created by Oracle as CONNECT, RESOURCE, and DBA should be
>dropped at the same time the passwords are changed for SYS and SYSTEM.
>

Great!, Oracle say in the docs that the historic roles are only included for compatibility with older versions and will be dropped in future versions. The privileges included in them are too severe.

>But at the same time I think it is reasonable to separate development
>concerns from those in production. In production there is no need for anyone
>to see V_$RESERVED_WORDS. But I wonder how anyone could possibly find value
>in refusing developer's access to this essential information. What could
>possibly be put at risk?
>

I am saying "least privilege principle" both for development and for production, drop / revoke all and put back "actually" what's needed. That doesn't mean that the same list / needs exist in production and development. If a developer needs v$reserved_words then OK, its on the list, but even though I say least principle and revoke all, there should be a core list of views that are OK for security reasons and that's what I want to aim for.

I think there is a misconception that because its development it doesn't need to be secured or it can be less secure than production. If an attacker or malicious employee has better access in development he/she may be able to learn enough of the applications structure to break into production.

>Among the views I always make available for development are v_$database,
>v_$instance, v_$reserved_words, v_$session, and v_$mystat. But then I'm one
>that believes a developer should be able to kill their own sessions rather
>than bringing down the database.
>

Thanks for this list Daniel, I will add it to mine.

I have to disagree with you about allowing developers or anyone for that matter to kill their own sessions. Allowing someone to kill their own sessions needs ALTER SYSTEM. I have covered this in the SANS guide and suggested that ALTER SYSTEM is not granted to any users except admins.

Here is an example:-

In session 1...

SQL> create user tester identified by tester;

User created.

SQL> grant create session to tester;

Grant succeeded.

SQL> grant alter system to tester;

Grant succeeded.

SQL> connect tester/tester
Connected.
SQL> alter system enable restricted session;

System altered.

SQL> In a session 2 try the following..:-)

Microsoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp.

C:\>sqlplus dbsnmp/dbsnmp

SQL*Plus: Release 8.1.5.0.0 - Production on Thu Jul 11 21:42:19 2002

(c) Copyright 1999 Oracle Corporation. All rights reserved.

ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Enter user-name:

enabling KILL unfortunately allows other ALTER SYSTEM commands that could be used to launch Denial of service attacks.

Thanks for the comment and you list of views though Daniel,

kind regards

Pete

>Daniel Morgan
>

-- 
pete_at_peterfinnigan.demon.co.uk
pete_at_petefinnigan.com

http://www.pentest-limited.com/oracle-security.htm - "Exploiting and
protecting Oracle"

http://online.securityfocus.com/infocus/1522 - "A simple Oracle Security
Scanner"

http://www.pentest-limited.com/default-user.htm - "Oracle Default User
and Password List"

http://www.pentest-limited.com/utl_file.htm - "Extracting Clear Text
Passwords from the SGA"