Re: Question for DBA's-SHould Application perform DBA tasks.

Forgive me if my replies will sound a bit harsh. Looks like this is a disaster app.
Answers embedded below.

Kind regards,

Sybrand Bakker, Oracle DBA

tim.mcconechy_at_runtime.dk wrote:

> This is a fundamental type of quetsion for DBA's.
> Here is the senario.
> I am changing a foxpro app to run on Oracle.
>
> We have a portion of the app that run's a system check where either you do a
> New install or check a database's tables for fields.
>
> The NewInstall oracle portion creates a tablespace and rollback segment.
> Then it creates a user with DBA roll do access the database.
> Then it creates stored procedures and tables and triggers.
>
> The stored procedures need DBMS_SQL and DBMS_OUTPUT.
>
> So I do the following commands. 'CREATE TABLESPACE QUESTPDM DATAFILE
> "QUESTPDM" SIZE 400K AUTOEXTEND ON NEXT 5K MAXSIZE 4000M'
This would probably give you 10k extents on ALL tables. You shouldn't do that. Secondly the tablespace will be extended with 5k extents!!!

> 'CREATE TABLESPACE
> QUESTRBS DATAFILE "QUESTRBS" SIZE 50K AUTOEXTEND ON NEXT 5K MAXSIZE 4000M'
>
> 'CREATE TABLESPACE QUESTTEMP DATAFILE "QUESTTEMP" SIZE 25K AUTOEXTEND ON NEXT
> 1K TEMPORARY MAXSIZE 4000M'
>

!!!!!!!!

>
> *Rollback segment
> 'CREATE ROLLBACK SEGMENT "QUESTRBS" TABLESPACE "QUESTRBS"'
and what about the storage clause. Leaving it out will give you 10k extents, which is a disaster for rollback segments. This will definitely result in numerous problems

>
>
> *bring it online
> 'ALTER ROLLBACK SEGMENT "QUESTRBS" ONLINE'
>
> *Create Users.
> 'CREATE USER "SYSADM" IDENTIFIED BY "modacad" DEFAULT TABLESPACE "QUESTPDM"
> TEMPORARY TABLESPACE "QUESTTEMP" PROFILE DEFAULT ACCOUNT UNLOCK'
>
> *Grants.
> 'GRANT "CONNECT" TO "SYSADM" WITH ADMIN OPTION'
> 'GRANT "DBA" TO "SYSADM" WITH ADMIN OPTION'
> 'GRANT "RESOURCE" TO "SYSADM" WITH ADMIN OPTION'
> 'GRANT UNLIMITED TABLESPACE TO "SYSADM"'
>
> 'ALTER USER "SYSADM" DEFAULT ROLE ALL'
>
> *!* lcSQL='drop public synonym dbms_output'
>
> 'create public synonym dbms_output for dbms_output'
>
> 'grant execute on dbms_output to sysadm'
>
> 'create public synonym dbms_sql for dbms_sql'
>
> 'grant execute on dbms_sql to sysadm'
>
> The questions are:
> 1) Do you forsee an inherent problems with the application performing any of
> these tasks?

Yes, how about error trapping if any of these statements fails, especially the create tablespace commands (a one time operation of course). Inexperienced dba's, of which there are enough (see below) won't be able to resolve these problems. Secondly, almost all of your statements are assuming defaults. This is going to result in numerous problems, which the people in 4 won't be able to resolve.

>
>
> 2) I know a bit and I am concerned about the Synonyms for DBMS_SQL and
> DBMS_OUTPUT. My approach is to not drop the synonym (in case other users have
> grants to this synonym ect.
> Instead I attempt to create it and ignore the error if it exists.
> Then I grant execute on it to my new user?

This is unnecessary. In a standard Oracle install all public synonyms and necessary rights already exist, if catalog.sql and catproc.sql have been run, as they should always. Your program shouldn't try to 'repair' a crippled oracle install.

>
>
> 3)Would you as a DBA have a problem with the program doing these things, and
> would you rather run a script ect. (Thinking about a Create SQL option)

I would definitely have problems with some end-user doing things, especially with regard to 4, as I would consider my database to be out of control. No one should get DBA and RESOURCE privilige, as it will allow you to screw up (sorry!) the complete database. There are many priviliges around (CREATE ANY TABLE, ALTER USER QUOTA UNLIMITED ON <tablespace>) that should resolve it for you. Even if you do it this way, I would still have questions about 'random' users (see 4) becoming a power user.

>
> 4)The application also creates new users on the fly as you add them to the
> program on two levels. (Th oracle DBA level and the table level to control
> access to various modules. Would you have any problem as a DBA with a program
> doing these tasks?
>

I wouldn't take ANY responsibility for a program doing such things. If you want an insecure, out of control database, for me personally this would mean searching for another job.

>
> Remember the plan is to make the DBA's job easier and also help the less
> technical firms get up and running?

Oracle is NOT a toy. This is a common misconception of people who have been running MS database products. You should try to avoid the mistakes of MS products. Sorry for saying so, you should get more Oracle expertise in to make your app more stable.

>
>
> thanks a Million in advance for your input!
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Received on Sat Jan 23 1999 - 00:48:32 CST