Re: Survey: How many schemas is "many"
- From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Aug 2014 10:50:51 +0100
Good point about the empty set, and I'm all for pedantry :)
In practice (and from a security audit point of view) there is a big
difference between a read-only account with no CREATE privileges or direct
grants and a designed application schema. At my last job we had a situation
where a private synonym would have solved a problem for an application we
couldn't easily change, but we couldn't do it as that would have
reclassified the app connection account as a schema, requiring a whole new
request submission to avoid a policy violation (and the request was rightly
turned down because applications should not connect directly to schemas).
At my current site we have a handy support account on production that can
create its own tables, but you wouldn't want to diff it with anything as
it's full of CTAS junk, and in any case it's about to be removed for
security reasons. So I'd say there is a useful difference between "account"
and "schema", and if people really mean "schemas" I'm surprised anyone has
more than about 10 excluding Oracle system ones. I'm not sure which of us
is being more pedantic, though.
William
On 7 Aug 2014, at 10:04, Niall Litchfield <niall.litchfield@xxxxxxxxx>
wrote:
That's a fair question, Patrice's original question arose in the context of
the SQL Developer diff tool for comparing schemas in 2 different databases.
In that context I considered an empty schema to count towards the number of
schemas in a db since you definitely want to know if it is empty in db A
but populated in db B. However it did seem likely to me that most people
would go with your definition - hence Q2.
As I'm away for a bit now, and we have 60 responses, the results so far (DB
account = any user, schema = user owning objects) are below. So the
anecdotal evidence from this list is that it is unusual, but hardly unheard
of, to have > 100 users owning database objects. If anyone missed Jeff's
later reply on the other thread the DBDiff feature of SQL*Developer isn't
really intended to be used at that sort of scale.
Total DB accounts
0-10 15.00%
10-100 40.00%
100-500 28.33%
500-1000 10.00%
1000-5000 1.67%
5000+ 5.00%
Total Schemas
0-10 31.67%
10-100 45.00%
100-500 18.33%
500-1000 3.33%
1000+ 1.67%
Niall
<pedantry>
I'd go with schema as being a set of objects in a single namespace and of
course would say that that must logically include the empty set :)
</pedantry>
On Thu, Aug 7, 2014 at 8:58 AM, William Robertson <
william@xxxxxxxxxxxxxxxxxxxx> wrote:
> How are we defining "schema"? To me it's a collection of database objects
> owned by a single account (or equivalent namespace), so I was a bit puzzled
> by the two-part question. A user that owns no objects (such as a read-only
> production account) is not a schema, surely.
>
> William Robertson
>
>
> On 5 Aug 2014, at 14:35, Niall Litchfield <niall.litchfield@xxxxxxxxx>
> wrote:
>
> All
>
> For those not following the dbdiff thread I've created a 2 question survey
> at https://www.surveymonkey.com/s/VGKZMY5 to get some statistics on how
> many different schemas databases in the wild actually contain. If we get
> more than, say, 50 responses I'll post back the answers here.
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
