Home » SQL & PL/SQL » SQL & PL/SQL » Do users ever create tables in these schemas? (18)
Do users ever create tables in these schemas? [message #689668] Sun, 17 March 2024 08:31 Go to next message
Darth Waiter
Messages: 74
Registered: October 2020
Member
When I query tables from a brand-new installation of Oracle that no user has ever touched, there are tables in the following schemas:

'SYS'
,'SYSMAN'
,'SYSTEM'
,'MDSYS'
,'WMSYS'
,'TSMSYS'
,'DBSNMP'
,'OUTLN'
,'DVSYS'
,'ORDDATA'
,'GSMADMIN_INTERNAL'
,'DBSFWUSER'
,'XDB'
,'ORDSYS'
,'OLAPSYS'
Are all of them system schemas that users do not create tables in, or is it common to create tables in some of them?
I am only interested in tables, no other types of objects. Tables with or without indexes, if it matters.
Since it is technically possible for a superuser to create tables even in SYS, I am not sure. In MS SQL it is not possible to create a table in SYS, even for sa. Since it is possible in Oracle, I am not sure I can even rely on the list above to filter out system tables.
Re: Do users ever create tables in these schemas? [message #689669 is a reply to message #689668] Sun, 17 March 2024 12:57 Go to previous messageGo to next message
John Watson
Messages: 8927
Registered: January 2010
Location: Global Village
Senior Member
If you look at dba_users.oracle_maintained you will see all the schemas in which you should not create objects.
Re: Do users ever create tables in these schemas? [message #689670 is a reply to message #689668] Sun, 17 March 2024 12:59 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In Oracle, nobody can create anything, anywhere, unless allowed to. How to allow it?

The simplest option is to provide username/password combination. If you (presuming you're a DBA) don't share that info with anyone, no problem.

Another option is to grant privileges (e.g. CREATE ANY TABLE) which will let that particular user create tables in any other schema. As DBA grants all privileges to all users in your database, it is - again - your responsibility NOT to grant such privileges.

Remember, when you (as a DBA) create user in your Oracle database, you do that with CREATE USER statement and provide its password. Initially, that user can't do anything, absolutely nothing. It can try to connect, but won't succeed until you grant it CREATE SESSION - then it'll be able to connect, and - again - do nothing until you grant it something more, such as CREATE TABLE - then it'll be able to create table in its own schema, insert rows into it, query its own tables etc. But that's restricted to its own schema, it can't do anything with any other schema.

Therefore, keep passwords and privileges safe, and your database will remain safe.

[Updated on: Mon, 18 March 2024 01:10]

Report message to a moderator

Re: Do users ever create tables in these schemas? [message #689671 is a reply to message #689669] Sun, 17 March 2024 13:04 Go to previous messageGo to next message
Darth Waiter
Messages: 74
Registered: October 2020
Member
John Watson wrote on Sun, 17 March 2024 12:57
If you look at dba_users.oracle_maintained you will see all the schemas in which you should not create objects.
Is 'should not' somehow enforced or is that a gentleman's agreement?
Re: Do users ever create tables in these schemas? [message #689672 is a reply to message #689671] Sun, 17 March 2024 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Darth Waiter wrote on Sun, 17 March 2024 19:04
John Watson wrote on Sun, 17 March 2024 12:57
If you look at dba_users.oracle_maintained you will see all the schemas in which you should not create objects.
Is 'should not' somehow enforced or is that a gentleman's agreement?

Littlefoot answered this question which is by the way underlied in my answers in your previous topics.
There is nothing to enforce, just things to not allow, by default, everything is forbidden.

[Updated on: Sun, 17 March 2024 14:49]

Report message to a moderator

Re: Do users ever create tables in these schemas? [message #689673 is a reply to message #689672] Sun, 17 March 2024 17:06 Go to previous messageGo to next message
Darth Waiter
Messages: 74
Registered: October 2020
Member
It means that user tables can be expected in any of system schemas because it is technically possible to create them everywhere. An arbitrary DBA or developer may create them even under SYS and grant permissions, so if one is looking for user tables then exclusion by system schemas is not viable. Is there any other way to exclude system tables from queries?
Re: Do users ever create tables in these schemas? [message #689674 is a reply to message #689673] Mon, 18 March 2024 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once again a developer CANNOT create table.
A developer has NOT the access to SYS or any DBA privileges (unless you give him/her).

As for your previous topics, you CANNOT solve organizational problems with technical solutions.

Re: Do users ever create tables in these schemas? [message #689675 is a reply to message #689673] Mon, 18 March 2024 02:05 Go to previous messageGo to next message
John Watson
Messages: 8927
Registered: January 2010
Location: Global Village
Senior Member
Quote:
An arbitrary DBA or developer may create them even under SYS
You think so?
C:\Users\john>sqlplus system/oracle

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 18 07:03:32 2024
Version 19.17.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 17 2024 13:41:40 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

orclz> conn system/oracle
Connected.
orclz> create table sys.cannot(c1 date);
create table sys.cannot(c1 date)
*
ERROR at line 1:
ORA-01031: insufficient privileges


orclz>

Re: Do users ever create tables in these schemas? [message #689676 is a reply to message #689675] Mon, 18 March 2024 05:15 Go to previous messageGo to next message
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
Senior Member
And if an arbitrary dev/DBA can log in as sys then they absolutely can create tables in sys.
In fact all the devs in my company have sys access on the dev DBs and for us it isn't a problem, but that's because we spin up our own dev server in the cloud and we are responsible for our own.
And we all know better than to create stuff in sys.
And if someone didn't it'd just mess up their server and would be blocked from entering source code on review.
Then test instances are more locked down and production are far more locked down.
Re: Do users ever create tables in these schemas? [message #689677 is a reply to message #689676] Mon, 18 March 2024 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
And if someone didn't it'd just mess up their server and would be blocked from entering source code on review.

OK but why not prevent from doing such things at the source instead of detecting them during review? It just a waste of resources and time and money for the company.
Why any developer should have access to SYS?
Even if security in dev environment is more relaxed, it does mean it could be anything.
If a developer kills a dev db who will recreate it? What about the lost time for all developers?


OP's issue, in short, is that some high privileges has been granted to user now he has to check they didn't use them.
In his previous topic, it was a script is given to DBA to be deployed but he wants this DBA to understand the script before executing it. But these scripts should have been validated before and the DBAs who deploy them haven't to analyze but just to execute.
Roughly, all OP's questions are about how to verify things are correct despite the fact that we do any old how, instead of thinking to make things so that they cannot be otherwise than correct (but as he said this is just a philosophical point of view).

Re: Do users ever create tables in these schemas? [message #689678 is a reply to message #689677] Mon, 18 March 2024 07:27 Go to previous messageGo to next message
Darth Waiter
Messages: 74
Registered: October 2020
Member
Oh.
You still think of me as of a dev or DBA who makes decisions to create or not create objects, read/understand/run or not to read/understand/run scripts, etc, even though I never indicated that.
You should not try to follow or investigate me because I come here with isolated, technical questions that have no connections between them unless I indicate that one flows from the other.
Those who may have created such objects are neither myself or my coworkers: they are hypothetical past or present DBAs or devs who make their own decisions, however right or wrong, good or bad, and I only have to account for any possibility. That is as much as I am allowed to say.
Re: Do users ever create tables in these schemas? [message #689679 is a reply to message #689677] Mon, 18 March 2024 08:36 Go to previous messageGo to next message
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Mon, 18 March 2024 11:50


OK but why not prevent from doing such things at the source instead of detecting them during review? It just a waste of resources and time and money for the company.
Why any developer should have access to SYS?
because sometimes we need to do changes that require sys access. We don't have a team of dev DBAs. and the devs who do that that stuff are all people with deep oracle knowledge.
Could we make another dba user that would have the access needed? sure
would we occasionally need actual sys access anyway? probably

Michel Cadot wrote on Mon, 18 March 2024 11:50

Even if security in dev environment is more relaxed, it does mean it could be anything.
If a developer kills a dev db who will recreate it? What about the lost time for all developers?
dev instances in the cloud, created from source using a Jenkins pipeline (creates a linux image, installs oracle, installs application on top). To recreate you just enter your instance details on a web page and press go.
Only the developer who killed their instance is affected and if they made a habit of it someone would be having a word.
Point is that our processes are such that that level of access on dev doesn't cause us problems, and if they did routinely cause us problems we would change the process.
And the level of access on instances that we aren't prepared to rebuild at the drop of a hat is greatly curtailed.

Michel Cadot wrote on Mon, 18 March 2024 11:50

OP's issue, in short, is that some high privileges has been granted to user now he has to check they didn't use them.
In his previous topic, it was a script is given to DBA to be deployed but he wants this DBA to understand the script before executing it. But these scripts should have been validated before and the DBAs who deploy them haven't to analyze but just to execute.
Roughly, all OP's questions are about how to verify things are correct despite the fact that we do any old how, instead of thinking to make things so that they cannot be otherwise than correct (but as he said this is just a philosophical point of view).

And you definitely don't want to be in that situation.
Guess my point is that there are multiple ways to handle access but the bottom line is you don't want to be in a situation where you aren't sure you can trust the people with the access you've granted them. At that point you need to either restrict access or put in place some other process to handle it.
Re: Do users ever create tables in these schemas? [message #689680 is a reply to message #689679] Mon, 18 March 2024 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Result of my years in charge of the database security in banking (maybe you remember Jérôme Kerviel). Smile

[Updated on: Mon, 18 March 2024 08:56]

Report message to a moderator

Re: Do users ever create tables in these schemas? [message #689687 is a reply to message #689678] Wed, 20 March 2024 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Darth Waiter wrote on Mon, 18 March 2024 13:27
Oh.
You still think of me as of a dev or DBA who makes decisions to create or not create objects, read/understand/run or not to read/understand/run scripts, etc, even though I never indicated that.
You should not try to follow or investigate me because I come here with isolated, technical questions that have no connections between them unless I indicate that one flows from the other.
Those who may have created such objects are neither myself or my coworkers: they are hypothetical past or present DBAs or devs who make their own decisions, however right or wrong, good or bad, and I only have to account for any possibility. That is as much as I am allowed to say.

No, I don't.
I just noticed that your questions contain "in MS SQL we can do that" or "in MS SQL we cannot do that" and try to mimic the same behavior in Oracle, so I look things from a higher point of view trying to show that searching technical solutions for organization problems is not the good way and, for some issues, may be workaround to .

I already asked you what is your purpose which would allow to answer in a more specific and accurate way.

Now, technically, if you want to know who create some objects in some schemes you can activate either traditional audit or create an audit policy.
If you want to forbid some creations from some users despite the fact they have been granted the privileges to do so, you can create a DDL trigger (which also allows you to log these actions).
Of course, if the user has DBA privileges he/she may deactivate these ones and/or delete audit information.

Re: Do users ever create tables in these schemas? [message #689688 is a reply to message #689687] Wed, 20 March 2024 08:02 Go to previous messageGo to next message
Darth Waiter
Messages: 74
Registered: October 2020
Member
I do not try to mimic any behaviors, because like I explained, I have to account for possible past or future actions of arbitrary database owners. I do not have control over their possible actions and decisions. I must react to whatever actions they may have taken or may take in the future, whoever they are, wherever they are, and I do not care who did what, how, or why. This is not about me working on a particular instance that is under some degree of control of mine or my coworkers'. I do not care about reasoning why tables may have been created under SYS or whatever Oracle's schemas, or whether that is good or bad, sane or insane.
You are still trying to track my past and present questions and investigate me, which will lead nowhere.
The only purpose of this particular question is to find a way to filter out Oracle's OOB tables from queries. I established that it is not possible to do so by looking at schemas in which they might exist. Thus the question: is there any other way? Like I said, in MS SQL it is straightforward: I do not look at SYS or INFORMATION_SCHEMA, and that will suffice. Not so straightforward under Oracle.
Re: Do users ever create tables in these schemas? [message #689691 is a reply to message #689688] Wed, 20 March 2024 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The only purpose of this particular question is to find a way to filter out Oracle's OOB tables from queries...  in MS SQL it is straightforward
How do you do this?
How do you forbid me to write and execute a query on tables you don't want I query?

Re: Do users ever create tables in these schemas? [message #689692 is a reply to message #689691] Wed, 20 March 2024 10:49 Go to previous messageGo to next message
Darth Waiter
Messages: 74
Registered: October 2020
Member
I do not and do not intend or want to.
The engine does.
You cannot create a table in SYS or INFORMATION_SCHEMA schemas, under MS SQL, even if you are sa.
So, if a table is not under SYS schema, than it is one of those that my queries need to include.
Under Oracle, SYS AS SYSDBA can fire off "create table sys.test (c char(1))" and get "Table created". That's the difference between the 2 engines: I cannot by looking at schemas alone tell whether a table comes OOB with the Oracle engine or is created after the installation. Seeing how there are thousands of tables in a vanilla installation of Oracle, it sounds impossible for me to tell which table is vanilla and which is not.
Re: Do users ever create tables in these schemas? [message #689694 is a reply to message #689692] Wed, 20 March 2024 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I cannot by looking at schemas alone tell whether a table comes OOB with the Oracle engine or is created after the installation. Seeing how there are thousands of tables in a vanilla installation of Oracle, it sounds impossible for me to tell which table is vanilla and which is not.

No, you can't with a simple look.

Are there no patches that modify, add or remove SYS or INFORMATION_SCHEMA tables in MS SQL?
Is MS SQL completely static and can be upgraded only with a complete install?

Re: Do users ever create tables in these schemas? [message #689695 is a reply to message #689694] Wed, 20 March 2024 12:22 Go to previous message
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
Senior Member
The only way you could possibly tell for certain is to create your own list of what should be in each oracle supplied schema for each release/patch version.
Building such a list, given that the contents could be changed by a patch, is an epic task.

Oracle's approach to sys and their other schemas is that people shouldn't be messing with them and if people do that's the peoples problem not oracles.
Oracle's approach isn't going to change either.
If you're working for a company that insists that people messing with oracle schemas is *your* problem then if I was you I'd start looking for another job. The *vast* majority of companies that work with oracle actually lock these things down.
Previous Topic: Prevent user from executing a script until they will have read and understood it
Next Topic: how to sum the amounts
Goto Forum:
  


Current Time: Sat Apr 13 08:28:08 CDT 2024