Re: Need DBA advices on creating a new user, application level or DBA level?

From: Jimmy <anonymous_at_anonymous.com>
Date: 2000/08/04
Message-ID: <398BA2C0.476D9CEC_at_anonymous.com>#1/1


    Sorry don't remember to mention that we are using Oracle 8.0.6 on Solaris platform.

Jimmy wrote:

> Hello all,
>
> Thanks for the suggestions.
>
> My company's policy now is, after agree with my company's DBA, he creates a
> procedure to create a new user.
> However, this procedure is owned by PROJECTA_OWNER. DBA granted "CREATE USER",
> "CREATE SESSION with ADMIN OPTION" to the PROJECTA_OWNER such that PROJECTA_OWNER
> can create a user and then grant create session to him. THe reason why this
> procedure is owned by PROJECTA_OWNER is we want to ensure all the objects created by
> PROJECTA_OWNER can be export once and then import to other database (since the
> database not only for one project, it has more than one project).
> Client application (only PROJECTA_OWNER logon) can use this procedure to create
> a new user.
>
> I have a question: is it possible to keep track who create a new user in Oracle
> (and the newly created user name and when)? i.e. any audit table in Oracle which is
> used to store the information about who and when to create a new user? Or need to
> write a PL/SQL procedures to do this?
>
> Thanks
>
> Ewan Parker wrote:
>
> > On 03-Aug-00 05:37:01, Jimmy said about Need DBA advices on creating a new user,
> > application level or DBA level?:
> > > Hello all,
 

> > > Recently, my company wants to develop an application. One of the
> > > functions of the application is it can create a new user. This function
> > > can only be done by a project owner, such as PROJECTA_OWNER.
> > > PROJECTA_OWNER is not a DBA, he is just a project owner with some system
> > > privileges (such as create user).
 

> > > However, my company's DBA strongly disagree this function. He said
> > > that creating a new user should be done by DBA, not on application
> > > level. This is because using a client application to create a user may
> > > bypass his vision, as a result, he don't know why such a user exist
> > > after the application is running (since anyone who knows the
> > > PROJECTA_OWNER password can create a new user). He think that it is more
> > > difficult to manage the user accounts in the future.
 

> > > Now, I have some questions:
 

> > > 1) What do u think the above scenario? Should user creation done by
> > > DBA, or done on application level? ANy other disadvantages if done on
> > > application level?
> >
> > Ideally, the user should be created by the DBA, but this easily causes an
> > unnecessary bottleneck. A stored procedure (written or checked by the DBA) can
> > be created in a new, different schema that creates the user and logs exactly
> > what/when/who, etc. This new schema would only have "create user", and maybe
> > "alter user" privileges permanently. The DBA would be the only person able to
> > change the procedure and the log tables. Only he can grant execute on the
> > procedure to others, so the DBA has complete control. He may introduce auditing
> > to see if the newly added users are actually in use.
> >
> > > 2) I know that if done on application level, PROJECTA_OWNER need to
> > > alter some user parameters (e.g. default and temporary tablespace etc).
> > > However, such parameters may need to be hardcoded. This is not a good
> > > practice since we need to recompile the program if the tablespace name
> > > is changed to another name. Is there other ways to handle such
> > > situation? (I think using a PL/SQL procedure to create a new user, and
> > > the application is calling this procedure. This procedure is written by
> > > DBA. In this way, DBA can change this procedure without affect the
> > > application. Am I right)
> >
> > Just create a table to hold this information. You can then use the same
> > version of the program in each environment. You are on the right track here.
> > As long as the DBA soley has control over the procedure and logs, he can be sure
> > that everything is in order, or at least a trail can lead back to the source of
> > the new user. It is a step back from full control, but it will save him time in
> > the long run.
> >
> > HTH,
> > Ewan.
> >
> > --
> > PGP footprint: 3A 82 19 D7 7A 61 D3 DC 4D F3 87 B2 27 99 BE 77
Received on Fri Aug 04 2000 - 00:00:00 CEST

Original text of this message