Re: Lock SYSTEM account?

Steve wrote:

> Hi Daniel
>
> could you elaborate further on the following
>
> 2. Drop Oracle's built-in demo roles, CONNECT, RESOURCE, and DBA. They are pure
> nonsense and do nothing but decrease security.
>
> I'm interested in your line of thinking here.
>
> tks
>
> steve
>
> Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D5985AC.A3FD128D_at_exesolutions.com>...
> > Paul Brewer wrote:
> >
> > > 8.1.7.0.0 EE on a variety of platforms.
> > >
> > > We are implementing a database with a higher-than-normal level of security.
> > > We are locking certain 'built-in' accounts (DBSNMP, OUTLN and so forth). I
> > > was wondering if there would be any unwanted side-effects in locking the
> > > SYSTEM account?
> > >
> > > Also, I'm tempted to drop what Thomas Kyte describes as the 'burned in' DBA
> > > role, and use a similar 'home made' ORA_DBA role instead (the main reason
> > > for this is so that no-one is 'exempted' from after login triggers).
> > >
> > > Yes, I could test this, but we're rather pressed for time at the moment, so
> > > if anyone is able to throw a pointer or two, it would be greatly
> > > appreciated.
> > >
> > > Thanks,
> > > Paul
> >
> > I'm not sure I see much value in locking the SYSTEM account but I absolutely
> > would do the following in a high-security environment.
> >
> > 1. Physically secure the server and make sure that no-one other than root and
> > oracle have access to any directory from ORACLE_BASE on down. Especially access
> > to database, rdbms\admin, and sqlplus\admin.
> >
> > 2. Drop Oracle's built-in demo roles, CONNECT, RESOURCE, and DBA. They are pure
> > nonsense and do nothing but decrease security.
> >
> > 3. Institute (8i) protocol.ora, or in 9i, the same features in sqlnet.ora. Make
> > sure that invited_nodes and excluded_nodes are used.
> >
> > 4. Change the passwords and/or lock out the users such as those you list.
> >
> > 5. Create classes of users and profiles for each one paying special attention
> > to password complexity, password reuse, password expiration, and lockout.
> >
> > 6. Write a LOGON trigger that validates based on schema, osuser, ip address,
> > terminal, and software tool.
> >
> > Daniel Morgan

Oracle comes by default with three default roles that are intended, according to Oracle, as demo material sort of like SCOTT/TIGER. Everyone seems to have figured out that two seconds after you install the database you change the passwords for SYS and SYSTEM and drop the SCOTT schema. But CONNECT, RESOURCE, and DBA should be dropped as well. Here's why.

Most people assume, incorrectly, that the CONNECT role is how you obtain the privilege to connect to the database. That is not the case. The actual privilege to connect to the database is CREATE SESSION.

GRANT CREATE SESSION TO user_name;

What CONNECT contains is:
ALTER SESSION - is this really needed by anyone with read-only access? CREATE CLUSTER - come on now ... what end-user is creating clusters? CREATE DATABASE LINK - this one to me is pure poison. A huge security lapse. CREATE SEQUENCE - why should an end user be creating sequences? CREATE SESSION - finally the only privilege that really means anything CREATE SYNONYM - I can live with this one CREATE TABLE - why?
CREATE VIEW - why again?

A look at RESOURCE and DBA pretty much is the same thing. They are just collections of system privileges that have no realtionship to what those connecting need. I drop them and create roles like this:

READONLY - role

   CREATE SESSION (only, nothing else is required)

APPDEVELOPER - role

   CREATE SESSION
   CREATE TABLE
   CREATE SYNONYM
   CREATE SEQUENCE
   CREATE VIEW
   CREATE PROCEDURE
   CREATE TRIGGER
   CREATE TYPE APPOWNER - role

   DBA-type privileges required by the schema that owns an application. And there is no reason why an application should have CREATE ANY TABLE, for example, that is a privilege in this context without purpose.

In other words ... roles should specifically relate to job responsibilities containing what the users need and nothing more. I then create a specific profile corresponding with each role. So, for example, READONLY gets one simultaneous logon and a short idle time whereas DEVELOPER gets three simultaneous logons and a longer idle time.

Daniel Morgan Received on Thu Aug 15 2002 - 10:13:58 CDT