DBA cannot create table in SYS schema

[DeepDiverMom]

Here are facts about my situation.

1) I am logged into Oracle 10g as the SYSTEM user.
2) SYSTEM user is (as usual) a member of the DBA role, which I have confirmed possesses the CREATE ANY TABLE privilege.

I attempt to issue this command:

CREATE TABLE SYS.X (Y NUMBER);
*
ERROR at Line 1:
ORA-01031: insufficient privileges

Why, if I have the CREATE ANY TABLE privilege, will it not allow me to create a table in the SYS schema? Is the only way to create a table in the SYS schema to be connected as SYS? Is this something that I didn't get the memo on?

 

[dbtoo2001]

According to the oracle propaganda, you should never create objects in the sys schema, doesn't matter if you logged in as system or sys, you shouldn't do it, Only oracle should do it.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#sthref125

SYS
When you create an Oracle Database, the user SYS is automatically created and granted the DBA role.

All of the base tables and views for the database data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)

Ensure that most database users are never able to connect to Oracle Database using the SYS account.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#sthref2895

Protection for Administrator Connections
Only database administrators should have the capability to connect to a database with administrative privileges. For example:

CONNECT username/password AS SYSDBA/SYSOPER

Connecting as SYSOPER gives a user the ability to perform basic operational tasks (such as STARTUP, SHUTDOWN, and recovery operations). Connecting as SYSDBA gives the user these abilities plus unrestricted privileges to do anything to a database or the objects within a database (including, CREATE, DROP, and DELETE). SYSDBA puts a user in the SYS schema, where they can alter data dictionary tables.

 

[Beilstwh]

That being said, what possible reason can you have to want to build tables in SYS and to fragment the system tablespace which WILL cause your entire database to slow down. What you want to do is just silly.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Bill,

There is no indication that DeepDiverMom is attempting to create the object in the SYSTEM tablespace. It may very well be that she is creating the object in another tablespace, thus avoiding any danger of impacting the SYSTEM tablespace.

If DeepDiverMom is creating a table to support a strictly database-administration activity, such as a trigger that monitors an "after servererror on database" or an "AFTER LOGON ON DATABASE" event, then having that trigger owned by SYS (affecting a table that is stored in a non-SYSTEM tablespace) might even prove to be a requirement. Creating such infrastructure across multiple instances, owned by SYS, can, in fact, be highly advisable (again, without fragmenting the SYSTEM tablespace).

So, suggesting that "What you want to do is just silly" may prove to be hasty, harsh, and even unwarranted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DeepDiverMom]

Your intuition, Santa, is hitting on all cylinders. I would not place anything in the SYSTEM tablespace following the install.

Since Oracle is deprecating the SYSTEM user, and since I did not want to contrive another user for administrative tasks, and since what I wanted to do was audit/track logins, I felt that SYS was the appropriate user to own that infrastructure.

Thanks, all, for your contributions.

 

[Beilstwh]

Sorry... It was harsh. After being up 36 hours, I got a little testy.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Bill,

Being up 36 straight hours certainly tests one's mettle. You deserve a break today (and tomorrow, and for about a week). Why not come out West for vacation. It's been over 100 deg. F. for about a week now (and there's a lot of smoke in the air from our fires), but at least it's dry heat..

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Turkbear]

Hi,
Right..You bake instead of broil

Just spent the week of the 4th in Phoenix ( no lower than 110 for a high, got to 117 on the 4th - we played 18 holes)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dbtoo2001]

DeepDiver, if you want to 'audit/track logons' why not use Oracle's auditing capability?