Re: V$ tables

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Thu, 11 Jul 2002 22:56:37 GMT
Message-ID: <3D2E0CF2.453F041C@exesolutions.com>

Pete Finnigan wrote:

> 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..:-)

> C:\>sqlplus dbsnmp/dbsnmp

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

> 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"

I too agree with restricting access and my rant that follows should be read with that in mind. But I believe in restricting it as is appropriate to the tasks at hand. Treating developers like end-users just moves the senior developers to quit and go somewhere where they will be appreciated for their skills and can get a decent job done.

Actually killing a session does not require ALTER SYSTEM. It can be done with kill -9 in UNIX and can be done with ORAKILL. But even if it could only be done with ALTER SYSTEM there is nothing that stops someone from creating a stored procedure that does the dirty work where the developer only has execute on the procedure and can only kill their own sessions. This is like five minutes of coding at most.

<RANT>

But I would like any DBA out there that has ever had a bad experience with ALTER SYSTEM, in a development enviornment, to step forward. I've yet to meet one. I think this is paranoia with the same level of validity as many other Oracle myth debunked here recently.

And let me add that if the concern is that some developer will do something else with ALTER SYSTEM other than kill their own sessions ... then perhaps that developer should be escorted to the door and handed a letter of recommendation to present at the unemployment office. If developers are not free to utilize all of the tools Oracle provides to do their job the end result will be exactly the kind of garbage I see in such abundance.

I suggest a poll be conducted here of DBAs managing development databases.

Q1: Have you given access to DBMS_PROFILER to your developers?
Q2: If not, why not?
Q3: If not how do you expect to receive decent code from developers?
Q4: If not, and without going to a book, or web site, and looking it up do you

know what DBMS_PROFILER is?

The prosecution rests.

</RANT>

Daniel Morgan Received on Thu Jul 11 2002 - 17:56:37 CDT