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

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' '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"'
*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?

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?

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)

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?

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

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 Fri Jan 22 1999 - 02:41:17 CST