Segregating tools company 000

vanwinkle · #1 4th February 2002, 16:37

Baan IVc4, Oracle 7.3.4.4, Solaris 2.6

Currently, we have our tools company 000, residing in the same database as the data companies. I would like to separate the tools company data into its own database. What is going to be the easiest way to achieve this? My guess is the following:

1. create a new db instance for the tools company to reside in
2. export company 000 via oracle's export tool
3. import company 000 into the new database
3. create a new database definition in baan
4. point baan via session ttaad4111m000, assign tables to database to the new database definition.

Specifically, what entries need to be made in the tables/module portion of ttaad4111m000? That session looks like you have to assign each table to a database on a table by table basis. Is there a way to assign all tables in company 000 to a database?

I appreciate the help!

Han Brinkman · #2 4th February 2002, 17:59

You have to create the users as well in the db, especially the user baan. Furthermore you have to add a role r_baan, add dba rights and assign the role to the user baan.
Also the normal rights like connect etc. should be given to the user baan.

Don't forget that if you are going to do this that you will have to create your users from now on in two instances!

You can assign all tables from one company to a specific database definition. That will work.

Regards,
Han

patvdv · #3 4th February 2002, 22:21

Han,

I don't think you need to assign DBA to 'r_baan'. Seems awfully dangerous to me. I think the grants can be migrated alright when exporting company as long as you use the correct export parameters for Oracle's EXP utility. And yes, user management will be a bit of a nightmare though.

vanwinkle · #4 4th February 2002, 22:37

What I was trying to accomplish is faster refreshing of development data. The data that I have in the development server is serveral months old. At my disposal, I have an EMC Symmetrix to take a copy of production and import it into development. I would just overlay the existing database, but all the code that is being developed has table pointers located in company 000. So I wanted to split out company 000, so I could just overlay the data every week/month, etc.

How do other sites refresh the data for their development servers?

Thanks!

Han Brinkman · #5 5th February 2002, 10:48

Pat,

I agree that assigning DBA to r_baan is dangerous, but that the way baan installs it standard!

I haven't tried it myself but what are the consequences if you remove it. If you recreate tables you should do it then at least if you'r logged on as the owner of the tables.

Rgrds,
Han

patvdv · #6 5th February 2002, 12:07

Han,

I think maybe we are talking different things here? I am talking about the Oracle role 'DBA' as it normally standard owned by users sys and system. If I check on a typical Baan server:

Code:

SQL> select * from dba_role_privs where granted_role = 'DBA';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
OPS$ORACLE                     DBA                            NO  YES
OPS$ROOT                       DBA                            NO  YES
SYS                            DBA                            YES YES
SYSTEM                         DBA                            YES YES
TEMPDBA                        DBA                            NO  YES

5 rows selected.

No 'DBA' is granted to 'R_BAAN'. What makes up the 'R_BAAN' role is a select, insert, update and delete on each baan table individually granted to R_BAAN by user 'baan'. These can be queried in dba_tab_privs:

Code:

GRANTEE    OWNER      TABLE_NAME                     GRANTOR    PRIVILEGE       GRA
---------- ---------- ------------------------------ ---------- --------------- ---
R_BAAN     BAAN       TTTAAD000000                   BAAN       DELETE          NO
R_BAAN     BAAN       TTTAAD000000                   BAAN       INSERT          NO
R_BAAN     BAAN       TTTAAD000000                   BAAN       SELECT          NO
R_BAAN     BAAN       TTTAAD000000                   BAAN       UPDATE          NO
R_BAAN     BAAN       TTTAAD050000                   BAAN       DELETE          NO
R_BAAN     BAAN       TTTAAD050000                   BAAN       INSERT          NO
R_BAAN     BAAN       TTTAAD050000                   BAAN       SELECT          NO
R_BAAN     BAAN       TTTAAD050000                   BAAN       UPDATE          NO
R_BAAN     BAAN       TTTAAD100000                   BAAN       DELETE          NO
R_BAAN     BAAN       TTTAAD100000                   BAAN       INSERT          NO
R_BAAN     BAAN       TTTAAD100000                   BAAN       SELECT          NO

etc.

Table drop and creations are always done by user 'baan' inside the database as user 'baan' is the only one who has 'resource' assigned to it:

Code:

SQL> select * from dba_role_privs where grantee = 'BAAN';

GRANTEE    GRANTED_ROLE                   ADM DEF
---------- ------------------------------ --- ---
BAAN       CONNECT                        NO  YES
BAAN       RESOURCE                       NO  YES
BAAN       R_BAAN                         YES YES

For this reason we need to have all users connected to the 'baan' group in the $BSE/lib/ora/ora_groups file! Correct me if I am wrong

Han Brinkman · #7 5th February 2002, 13:17

Pat,

Is seems that my configuration is slightly different, dba is assigned to r_baan on my system. However I have to tell you that this configuration is pretty old (more than 2 years) could be that it's no longer needed or added by someone.

I have to pick a time to perform some test to see what happens if I remove it.

Thanks for the hint.

Han

patvdv · #8 5th February 2002, 15:22

I think the most commonly used method is probably the 1-to-1 company company within Baan itself. If your data volume is not too big and number of companies not too high, it is a easy and simple way.