RE: Schema Naming Standards
Date: Wed, 27 May 2015 12:32:39 +0000
Message-ID: <939730CDCC52DA43AB03D00E4C678B9C2EAEB55C_at_GOXSA1707.fplu.fpl.com>
+1 Mark – We have a vendor that does that for a SQL Server based application and it makes it very difficult putting things back together after a refresh.
For us, we typically have an application schema and an application run account. Example – APPLICATION and APPLICATION_USER. The APPLICATION schema hosts the objects and the APPLICATION_USER account runs the application from the server. It took a while to get the developers on board, but now even they think it is a more secure way of running the application.
Thanks!
Jeremy
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark J. Bobak
Sent: Wednesday, May 27, 2015 2:40 AM
To: MARK BRINSMEAD; jeff.d.smith_at_oracle.com
Cc: Backseat DBA; ORACLE-L
Subject: Re: Schema Naming Standards
This is an EXTERNAL email. Exercise caution. DO NOT open attachments or click links from unknown senders or unexpected email.
I agree with all of the above.
Just wanted to add one more thought. It's almost always smart to *not* tie your schema to the environment. For example, it's a bad idea to have an app called ACME, and the schema/app owner schema called ACME_DEV in ACME_TST in test, ACME_PRD in prod, etc. It will hugely complicate things like refreshes from prod to test, dev, etc. (Yes I've seen/worked on such a system. Big pain.....)
-Mark
On Tue, May 26, 2015, 11:59 PM MARK BRINSMEAD <mark.brinsmead_at_gmail.com<mailto:mark.brinsmead_at_gmail.com>> wrote: There are many "standards" out there. Not all are good ones. Case in point, any time I see something named FOO_USER (i.e., a suffix _USER added to a name) I want to assume that thing is a ROLE. One might have roles like PAYROLL_USER, PAYROLL_ADMIN, PAYROLL_MANAGER, etc. And guess what? "PAYROLL" is an application schema. I generally agree with Mark Bobak, though. If you find yourself inheriting a standard, it is usually best (for everybody) to just stick to it, even if it annoys you. Even if there are good reasons that it annoys you. Unless there is a clear desire to return your application to the design stage (sometimes that happens) its probably a mistake to try to change an existing naming standard -- usually the best result you will achieve is mild confusion and at the worst you can create a disaster. Some "standards" can be pretty questionable, though. Sometimes, for example, it can be necessary to replace a table with a view (or vice versa), so a naming standard that appends suffixes like _TABLE and _VIEW to object names can, over time, produce some pretty strange results. (And how does the standard deal with Materialized Views, which are both tables and views?) Personally, I have always been opposed to naming standards that force you to encode implementation details in an object's name, because it can restrict your ability to later change the implementation. (Another good example is the standard requiring "shell scripts" to have a ".sh" or ".csh", or ".ksh" suffix, depending on how they are coded. What happens when I need to rewrite one as a C program?) Anyway, this is definitely a topic where 1,000 DBAs will have 1,000 different opinions. And probably, few of those opinions really matter unless you are starting a new application (or IT environment) entirely from scratch. And now, having done my part to fan the flames of a religious war, I will bow out and end my part in this conversation.
On Tue, May 26, 2015 at 6:44 PM, Jeff Smith <jeff.d.smith_at_oracle.com<mailto:jeff.d.smith_at_oracle.com>> wrote: I know of some in the SQL space that think that tables should end in _TABLE and views in _VIEW.
Yuck.
But that’s a purely subjective response, not discounting our current 30 character limit for object names.
From: Mark J. Bobak [mailto:mark_at_bobak.net<mailto:mark_at_bobak.net>]
Sent: Tuesday, May 26, 2015 6:31 PM
To: backseatdba_at_gmail.com<mailto:backseatdba_at_gmail.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: Re: Schema Naming Standards
Trying to start a religious war here, eh? :-)
Seriously, whatever standard you pick, stick with it.
In my opinion, having the app owner schema name end in _USER seems counterintuitive to me, but what do I know?
In my limited experience, we had the app owner schema named after the product, batch programs logged in as users whose names were related to the subsystem they were associated with, and end users had logins that were first initial and last name.
-Mark
On Tue, May 26, 2015, 5:31 PM Jeff Chirco <backseatdba_at_gmail.com<mailto:backseatdba_at_gmail.com>> wrote:
I was wondering if others have a naming standard for application schemas. Like do you had a prefix or suffix? It was started before I got to this company that all schemas end in "_USER" and then I later added a "_APP" for application logins to separate schemas that owned the objects and what the application logs in as. So for example for a Payroll program the schema would be PAYROLL_USER instead of just PAYROLL.
We are starting up a new database for a big system and the developers had asked to drop the _USER. The _APP will not be needed since users will log in directly to this particular database.
I am open to the idea but was wondering what is common? Part of my concern is that one database will be different than others but really the _USER serves no purpose.
Thanks in advanced.
Jeff
--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 27 2015 - 14:32:39 CEST