Schema organization
Hi all, I'm a fan of having the processing done by a user different than the owner of the data. Am I alone ? For example, we're on a datawarehouse system where the data owner is DWH. The etl tool repository owner is TOOL_POWERMART and the reporting tool repository owner is TOOL_BOWEBI. The etl processing is done by user DWH_PM_TRTMNT and the reporting processing is done by user DWH_BO_TRTMNT. This way, nobody is connecting as the data's owner. The developpers and Informatica (Powermart) consultant would prefer working directly as DWH. What do you think ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Schema organization
Stephane, I'm sure they would prefer to operate as the schema owner but to me it's sloppy practice. You're right; create the objects as DWH and grant the necessary permissions to the application users. That way only the schema owner has 'admin' rights (create, drop, alter etc.). If they can't come up with an excellent reason (it will things easier for us is NOT an excellent reason) then you should stick to your guns. Regards, Mike |+- || paquette stephane | || stephane_paquette@| || yahoo.com | || | || 07/05/01 11:40 AM | || Please respond to | || ORACLE-L | || | |+- | || | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Mike Hately/ETECH) | | Subject: Schema organization | | Hi all, I'm a fan of having the processing done by a user different than the owner of the data. Am I alone ? For example, we're on a datawarehouse system where the data owner is DWH. The etl tool repository owner is TOOL_POWERMART and the reporting tool repository owner is TOOL_BOWEBI. The etl processing is done by user DWH_PM_TRTMNT and the reporting processing is done by user DWH_BO_TRTMNT. This way, nobody is connecting as the data's owner. The developpers and Informatica (Powermart) consultant would prefer working directly as DWH. What do you think ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Schema organization
i try to play by the same rule as you. but if i can't then i put the ddl triggers on the tables owned by the schema owner, which keeps at least the strucutures from being altered. joe paquette stephane wrote: Hi all, I'm a fan of having the processing done by a user different than the owner of the data. Am I alone ? For example, we're on a datawarehouse system where the data owner is DWH. The etl tool repository owner is TOOL_POWERMART and the reporting tool repository owner is TOOL_BOWEBI. The etl processing is done by user DWH_PM_TRTMNT and the reporting processing is done by user DWH_BO_TRTMNT. This way, nobody is connecting as the data's owner. The developpers and Informatica (Powermart) consultant would prefer working directly as DWH. What do you think ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing) -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Schema organization
A couple of other reasons for this approach: When you create a DAD in Oracle Application Server, you can choose to store the password in the config file. On version 3.02 it is in unencrypted form and is optional. On 4.x it's encrypted and mandatory (unless you cheat and remove the password entry once you create the DAD). This permits you to change the password on the schema owner without having to change your DAD, especially important if your destination database is not owned by you! We normally define a schema and a secondary account like data and data_pub. We give data_pub all of the data manipulation rights to the data schema. Very safe. If you let others create database links to your database it is nice to avoid giving them the password to your schema. Since you can see the password unencrypted on their end after the database link is created this is especially dangerous. I would love to see this become standard practice. Once something goes into production there should not be that much DDL affecting the original schema anyway. We generally create all of the packages in the original schema and grant execute to the public schema. Hope this helps. --Michael -Original Message- Sent: Thursday, July 05, 2001 10:06 AM To: Multiple recipients of list ORACLE-L Stéphane, Your not alone. I like having one schema that owns all of the objects and a second or more that manipulate the data therein. The reason is that many times the passwords for the other user accounts get hard coded into software making them almost impossible to change. This way if the person who was maintaining the application leaves you can change the password there to do maintenance without breaking everything. Also if you do get a hacker in, it's a lot harder to have to delete everything vs drop a table. Dick Goulet Reply Separator Author: =?iso-8859-1?q?paquette=20stephane?= [EMAIL PROTECTED] Date: 7/5/2001 2:40 AM Hi all, I'm a fan of having the processing done by a user different than the owner of the data. Am I alone ? For example, we're on a datawarehouse system where the data owner is DWH. The etl tool repository owner is TOOL_POWERMART and the reporting tool repository owner is TOOL_BOWEBI. The etl processing is done by user DWH_PM_TRTMNT and the reporting processing is done by user DWH_BO_TRTMNT. This way, nobody is connecting as the data's owner. The developpers and Informatica (Powermart) consultant would prefer working directly as DWH. What do you think ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenkins, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Schema organization
paquette stephane wrote: Hi all, I'm a fan of having the processing done by a user different than the owner of the data. Am I alone ? For example, we're on a datawarehouse system where the data owner is DWH. The etl tool repository owner is TOOL_POWERMART and the reporting tool repository owner is TOOL_BOWEBI. The etl processing is done by user DWH_PM_TRTMNT and the reporting processing is done by user DWH_BO_TRTMNT. This way, nobody is connecting as the data's owner. The developpers and Informatica (Powermart) consultant would prefer working directly as DWH. What do you think ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] I fully agree with you. DML and DDL are too businesses better kept separated. -- Regards, Stephane Faroult Oriole Corporation -- http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Schema organization
So what your DDL triggers are doing. And if they do not allow DDL to work how you can execute DDL - disabling these triggers? Alex Hillman -Original Message- Sent: Thursday, July 05, 2001 8:00 AM To: Multiple recipients of list ORACLE-L i try to play by the same rule as you. but if i can't then i put the ddl triggers on the tables owned by the schema owner, which keeps at least the strucutures from being altered. joe paquette stephane wrote: Hi all, I'm a fan of having the processing done by a user different than the owner of the data. Am I alone ? For example, we're on a datawarehouse system where the data owner is DWH. The etl tool repository owner is TOOL_POWERMART and the reporting tool repository owner is TOOL_BOWEBI. The etl processing is done by user DWH_PM_TRTMNT and the reporting processing is done by user DWH_BO_TRTMNT. This way, nobody is connecting as the data's owner. The developpers and Informatica (Powermart) consultant would prefer working directly as DWH. What do you think ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing) -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Schema organization
yeppers, if you want to prevent ddl on tables when you are required to give out the schema owner password, you put those triggers on all of the objects. This assumes the user logging does NOT have alter any trigger. The triggers are owned by a separate userid and look like this(besides you can alter the trigger to let specific userids do the alter without having to disable them). create or replace trigger create_control_triggerbefore create on SCHEMA_NAME.schema begin raise_application_error(-20001,'NO CREATE DDL ALLOWED');end;/ create or replace trigger drop_control_triggerbefore drop on SCHEMA_NAME.schema begin raise_application_error(-20001,'NO DROP DDL ALLOWED');end;/create or replace trigger alter_control_triggerbefore alter on SCHEMA_NAME.schema begin raise_application_error(-20001,'NO ALTER DDL ALLOWED');end;/ Joe [EMAIL PROTECTED] 07/05/01 12:03PM So what your DDL triggers are doing. And if they do not allow DDL to workhow you can execute DDL - disabling these triggers?Alex Hillman-Original Message-Sent: Thursday, July 05, 2001 8:00 AMTo: Multiple recipients of list ORACLE-Li try to play by the same rule as you.but if i can't then i put the "ddl" triggers on the tables owned by theschema owner, which keeps at least the strucutures from being altered.joepaquette stephane wrote: Hi all, I'm a fan of having the processing done by a user different than the owner of the data. Am I alone ? For example, we're on a datawarehouse system where the data owner is DWH. The etl tool repository owner is TOOL_POWERMART and the reporting tool repository owner is TOOL_BOWEBI. The etl processing is done by user DWH_PM_TRTMNT and the reporting processing is done by user DWH_BO_TRTMNT. This way, nobody is connecting as the data's owner. The developpers and Informatica (Powermart) consultant would prefer working directly as DWH. What do you think ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing)-- Joe Testa Performing Remote DBA Services, need some backup DBA support?For Sale: Oracle-dba.com domain, its not going cheap but feel free toask :)-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Joe Testa INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Hillman, Alex INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Schema organization
ya gotta love them new triggers in 8i. I used a similar scheme to keep duhvelopers from changing the passwords on common development accounts, because they would promptly forget them and call the DBA. Jared On Thursday 05 July 2001 09:21, JOE TESTA wrote: yeppers, if you want to prevent ddl on tables when you are required to give out the schema owner password, you put those triggers on all of the objects. This assumes the user logging does NOT have alter any trigger. The triggers are owned by a separate userid and look like this(besides you can alter the trigger to let specific userids do the alter without having to disable them). create or replace trigger create_control_trigger before create on SCHEMA_NAME.schema begin raise_application_error(-20001,'NO CREATE DDL ALLOWED'); end; / create or replace trigger drop_control_trigger before drop on SCHEMA_NAME.schema begin raise_application_error(-20001,'NO DROP DDL ALLOWED'); end; / create or replace trigger alter_control_trigger before alter on SCHEMA_NAME.schema begin raise_application_error(-20001,'NO ALTER DDL ALLOWED'); end; / Joe [EMAIL PROTECTED] 07/05/01 12:03PM So what your DDL triggers are doing. And if they do not allow DDL to work how you can execute DDL - disabling these triggers? Alex Hillman -Original Message- Sent: Thursday, July 05, 2001 8:00 AM To: Multiple recipients of list ORACLE-L i try to play by the same rule as you. but if i can't then i put the ddl triggers on the tables owned by the schema owner, which keeps at least the strucutures from being altered. joe paquette stephane wrote: Hi all, I'm a fan of having the processing done by a user different than the owner of the data. Am I alone ? For example, we're on a datawarehouse system where the data owner is DWH. The etl tool repository owner is TOOL_POWERMART and the reporting tool repository owner is TOOL_BOWEBI. The etl processing is done by user DWH_PM_TRTMNT and the reporting processing is done by user DWH_BO_TRTMNT. This way, nobody is connecting as the data's owner. The developpers and Informatica (Powermart) consultant would prefer working directly as DWH. What do you think ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing) Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: quoted-printable Content-Description: HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
