Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, David.

Asked: May 24, 2004 - 4:43 pm UTC

Last updated: November 26, 2014 - 9:05 pm UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Tom,

I have been through the forum and am still unsure of the best way to do what I need to do. There seems to be caveats to most methods and some methods mentioned apparently will work on some versions and won't work on others.

In a nutshell, I need to monitor all logins as sys and system whether the login is successful or not. I need to gather the users name, machine name, database name, and whether they are trying to log in as sys or system. The information gathered from all my databases need to be written to a common database via a link.

I'm leaning towards a login trigger and write the information via links to a common database. From all I have been able to glean from the forum, this method would have the least amount of overhead and storage requirements. We have databases ranging from 8.1.7.4 to 10. Whatever solution I come up with has to work on all aforementioned Oracle databases. Your input and help would greatly appreciated.

Thanks,
David


and Tom said...

well, first -- no one, but no one should be logging in as SYS or as SYSTEM!

lock system, don't use it. alter user system account lock;

using O7_DICTIONARY_ACCESSIBILITY=FALSE (default in 9i and up), people cannot log in as SYS, they must provide *their* credentials and log in as SYSDBA.

In general, people should be logging in as *themselves*.


Using a dblink would be a rather bad idea I predict. The availability of your databases would be near zero. Also, the failure of a login trigger will not prevent DBA accounts from logging in (so, if dblink not up, trigger fires, fails and no auditing takes place)


I think you just want to

a) set audit_trail=true
b) audit connect
c) have central database periodically pull new records from sys.aud$

builtin auditing has the least amount of overhead -- from both a coding and maintenance perspective.

but - i would stress -- do not use sys/system, lock them down. you don't need to use them except in rare cases during patching/upgrades -- if at all (SYSDBA should be used in general)

Rating

  (43 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good Advice....

Michael, May 25, 2004 - 10:38 am UTC

about not using sys or system. In addition, externally identified sysdba accounts are the way to go for important operations. Tom has talked about them from time to time. Search on "hiding the password".

We are in 9iR2 and Oracle by default creates little ".aud" files in $ORACLE_HOME/rdbms/audit whenever a sysdba connects. The file might look like this:

Audit file /u01/app/oracle/product/9.2.0/rdbms/audit/ora_14601.aud
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name: SunOS
Node name: dwh-d-prd1
Release: 5.8
Version: Generic_108528-19
Machine: sun4u
Instance name: DWHPRD1
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
14601

Thu May 13 17:38:11 2004
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/10
STATUS: 0

Audit file /u01/app/oracle/product/9.2.0/rdbms/audit/ora_14601.aud
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name: SunOS
Node name: dwh-d-prd1
Release: 5.8
Version: Generic_108528-19
Machine: sun4u
Instance name: DWHPRD1
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
14601

Thu May 13 17:38:11 2004
ACTION : 'STARTUP'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: Not Available
STATUS: 0

Beyond that, in addition to enabling the audit trail as suggested by Tom, you can also give the commands
"audit alter system" and "audit alter database". Any such operations will be captured in dba_audit_trail. Finally, there is another init.ora parameter, at least on our release, "audit_sys_operations" which is false by default. We are going to enable that soon.

From the Oracle Docs, the result of audit_sys_operations=true :

"
All audit records for SYS are written to the operating system file that contains the audit trail, and not to SYS.AUD$. All SYS issued SQL statements are audited indiscriminately and regardless of the setting of the AUDIT_TRAIL initialization parameter.

Consider the following SYS session:

CONNECT / AS SYSDBA;
ALTER SYSTEM FLUSH SHARED_POOL;
UPDATE salary SET base=1000 WHERE name='myname';


When SYS auditing is enabled, both the ALTER SYSTEM and UPDATE statements are displayed in the operating system audit file as follows:

Thu Jan 24 12:58:00 2002
ACTION: 'CONNECT'
DATABASE USER: '/'
OSPRIV: SYSDBA
CLIENT USER: scott
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2002
ACTION: 'alter system flush shared_pool'
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: scott
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2002
ACTION: 'update salary set base=1000 where name='myname''
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: scott
CLIENT TERMINAL: pts/2
STATUS: 0


Because of the superuser privileges available to users who connect as SYSDBA, Oracle recommends that DBAs rarely use this connection and only when necessary. Normal day to day maintenance activity can usually be done by DBAs assigned the DBA role.
"

Tom Kyte
May 25, 2004 - 11:19 am UTC

and further, if you lock down the OS account, people will have to supply a username/password to get in -- so you wont see just "/" in those aud files.

Still able to lo log in as SYS

Livio, May 26, 2004 - 6:28 am UTC

Tom,
I have set O7_DICTIONARY_ACCESSIBILITY to FALSE, in an ORACLE 8.1.7 database server, running on a W2K professional environment. Despite it, I am still able to log in as SYS, for instance through a SQLPlus Worksheet session.
This happens both on the machine the db server is installed and on clients connected to it.
What am I missing?

Kind regards

Tom Kyte
May 26, 2004 - 8:49 am UTC

what is missing right now is an example proving that it is set I guess....


F:\oracle8i\database>sqlplus "sys/oracle as sysdba"

SQL*Plus: Release 8.1.7.0.0 - Production on Wed May 26 08:31:29 2004
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connected to an idle instance.

SQL> startup pfile=initora8i.ora
ORACLE instance started.

Total System Global Area   87394332 bytes
Fixed Size                    75804 bytes
Variable Size              57446400 bytes
Database Buffers           29794304 bytes
Redo Buffers                  77824 bytes
Database mounted.
Database opened.
SQL> show parameter o7
<b>
NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean FALSE</b>

SQL> connect sys/oracle<b>
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper</b>


Warning: You are no longer connected to ORACLE.
SQL> exit

F:\oracle8i\database>sqlplus sys/oracle

SQL*Plus: Release 8.1.7.0.0 - Production on Wed May 26 08:32:12 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.
<b>
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
</b>

Enter user-name: sys/oracle <b>as sysdba</b>

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

SQL> 

A reader, May 26, 2004 - 10:59 am UTC

Tom,

I thought you dont have/use windows. :-)

Thanks.

Tom Kyte
May 26, 2004 - 1:12 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1257800375707#18839732509385 <code>

see the first bullet point in my response there....

A reader, May 26, 2004 - 1:26 pm UTC


Log in as sysdba

Livio, May 31, 2004 - 4:59 am UTC

I have noticed that internal can log as "normal" (Oracle 8i); clause
"as sysdba" does not have to be provided. Nonetheless, internal can startup / shutdown the DB.
Is there a way to prevent internal to log in?
Would it be possible / suggested to lock sys as well as system? This way, I could only log in as my_user/pwd as sysdba improving the overall security of the DB.

Thank you for your support

Tom Kyte
May 31, 2004 - 1:13 pm UTC

upgrade to 9i -- when internal does not exist.


In 8i, to prevent internal login, you would/could lock down the OS group/account.

Yes, sys and system can/should be locked for more "protection". You might need sys/system during the installation of some products, but day to day -- not at all.

A reader, January 23, 2005 - 10:12 am UTC

Tom,

On some sites I have seen DBA's using
alter user sys identified by values 'NO WAY';

I prefer locking sys and system accout rather than using
the above option. Are there any issues using the above mechanism?

Thanks.

Tom Kyte
January 23, 2005 - 10:19 am UTC

ops$tkyte@ORA10G> show parameter o7
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

ops$tkyte@ORA10G> connect sys/oracle;
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper
 
 
Warning: You are no longer connected to ORACLE.


<b>that would be my choice, along with alter user sys account lock.</b> 

A reader, January 23, 2005 - 10:59 am UTC

Thanks Tom, what about the other option than i mentioned?

Tom Kyte
January 23, 2005 - 1:06 pm UTC

issues? no.

is it "clean"? no
does it "work"? yes



Is it possible to have all audit records (inclusive those for sys user) in the same place?

A reader, August 18, 2005 - 9:12 am UTC

Tom,

I want to control ALL ddl activity on some Linux and Windows instances. I use the select statement below to create a spool file and daily e-mail this file to me.
The problem is that if somebody use connect / as sysdba to connect and then execute something like alter table add...
these actions are not visible in the dba_audit_object view.
I found these actions written in *.aud OS files under ?/rdbms/audit/ folder in Linux and I was not able to find these actions under Windows. (I just saw some actions written in Windows application log for sys connect actions, not sys DDL).
I have audit_sys_operations = TRUE on both system.
Is there any way to have all all audit records for DDL changes written to the same place? I don't want to check twice: for changes made by sys and by others. Under Windows I did not know how to check sys ddl at all...
I am interested to control both type of changes. Sys and system are disabled, but some people can access Oracle using connect / as sysdba if they in ORA_DBA group (and for e.g. sysadmins can add somebody to the group).

The select statement:

select username, os_username,timestamp, obj_name, action_name, returncode from dba_audit_object
where timestamp between sysdate-to_number(&period) and sysdate order by timestamp, username, os_username





Tom Kyte
August 18, 2005 - 4:04 pm UTC

(look in the event log viewer on windows, that is where they go)

you can have ALL audits go to the file system (so yes, they can all goto the same place -- the file system)





Windows event log? Not very comfortable for daily search...

A reader, August 19, 2005 - 3:25 am UTC

Tom, thanks for your quick responce.

The problem with Windows event log is that there is too much not Oracle relevant information in it and I cann't serch in it using grep or so. I have more than one Windows system and I don't want to log on daily in each system and review the log files. My goal is to get e-mail with audit results (invalid session and DDL activity) inclusive those for sys (sys is very important because other people have access to sysadmin and can connect / as sysdba).
Is there any other ways to control such operation without to
log on and review event log on EACH of my windows hosts?

Thanks in advance.

Tom Kyte
August 20, 2005 - 3:37 pm UTC

You'd want to ask a windows person "so, how can I make effective use of my windows Event log on this user friendly operating system?"

There are probably tools to utilize the information from the command line, if not - well, that would be a limitation of the OS itself.

For DBAs on Whinedoze

Chris Poole, August 22, 2005 - 10:50 am UTC

This little utility:

</code> http://www.sysinternals.com/Utilities/PsLogList.html <code>

(disclaimer: I don't work for them, I just find their stuff useful)
will do the trick of looking at the Event Viewer from the command line.
In fact with Toms little piece of Java code that calls an OS process, a little bit of PL/SQL, the CAST operator and a CREATE VIEW you can in fact make the Event Viewer visible from inside the DB as a table...

...but there isn't enough room to post my code so I will leave that as an exercise for the reader :)

Tom Kyte
August 23, 2005 - 4:04 am UTC

thanks! I knew there would be a way to interact with the event log somehow.

sal, October 10, 2005 - 6:33 am UTC

While connecting to Database using
SYS(CONN SYS@DB_NAME/AS SYSDBA) user in WONDOWS NT without giving the password also It is connecting to database.How to restict that(SYS user witout password)

Tom Kyte
October 10, 2005 - 9:00 am UTC

show me a cut and paste.



Auditing DBAs

Marcio Portes, August 30, 2006 - 11:34 pm UTC

Auditing DBA - Version 9.2.0.7

We have this scenario: 6 dbas working daily basis, we need to audit every step taken by them *AND* they have to be away off audit files.

I was thinking about this, but it won't work for me.
give them sysdba, audit_sys_operations=true, let them connect using sysdba - that will generate auditfiles even though they still have access over it because oracle ownes ?/rdbms/audit (oracle needs rwx permission to write the file) - Hence they can touch those audit files and, well, do what ever they want to.

Do you see any other way to audit DBA without having them touch their own audits?

Regards,
Marcio Portes



Tom Kyte
August 31, 2006 - 9:08 am UTC

do not give them access to the physical machine - make them work in pairs when they need to (system administrator + dba).

Day to day, they do not need access to the physical machine, if you want to lock it down. In most cases - 5 of 6 of them shouldn't have access. files they think they do need access to (trace directories) can be exported and mounted elsewhere.

If they can log onto the machine - do not give them access to the oracle account (again, they do not need it, and if they do for something like a configuration change, that should be done in combination with the system adminstrator in an environment where this level of accountability is needed). They do not need to be in the dba group, they will be able to read various directories but not WRITE THEM.

also, rething the sysdba - do they *need* sysdba, probably 5 out of 6 only need sysoper.

Conn with EXTERNALLY AUTHENTICATION but with PASSWORD

Hector Gabriel Ulloa Ligarius, December 04, 2006 - 4:27 pm UTC

Hi Team Tom..

 See the next example : 

 Exist one user with externally authentication 

$ su - example
Password:*******

$sqlplus /nolog

SQL> conn _/ as sysdba
Enter password: <--- I press any letter..
Connected.      <--- Why?

Is a bug??

Database : 9.2.0.6
O.S.     : SunOS adonis 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Fire-280R

Regards
Hector Gabriel Ulloa Ligarius
San Juan ,Puerto Rico 

Tom Kyte
December 05, 2006 - 9:40 pm UTC

because that user example is in the "dba" group - the username/password are simply "not relevant"

sys%ORA10GR2> connect / as sysdba
Connected.
sys%ORA10GR2> connect not_a_user/really_not_a_password as sysdba
Connected.
sys%ORA10GR2>


if that user can connect "/ as sysdba", they can connect "anything/anything as sysdba"

sachin, December 06, 2006 - 1:49 am UTC

hello tom ,

i m still confuse that what is difference between sys and system user ?

Tom Kyte
December 07, 2006 - 8:02 am UTC

SYS is an account you will never use, you will never log in as SYS (as sysdba, maybe, but not as sys)

SYS is an account you will not use to do things - things work special for sys. Sys is "our" account, you will NOT use it.

SYSTEM is a "normal dba" account that should be locked and never used. It is not as special as SYS. It owns some tables but beyond that - nothing special.

But lock it and do not use it.

Sachin - check the documentation

Greg, December 06, 2006 - 1:40 pm UTC

John Z, June 12, 2008 - 2:51 pm UTC

hi Tom:
glad to read your review for this thread, just one thing want to ask further.

Quote you remark: "as sysdba, maybe...",

my understanding is, once anyone login as sysdba, it effectively enable that user to be a "sys" user, so that is the benefit of lock the "sys" user? (when i do an "audit connect", i only see the following, which dont even provide any benefit of audit traceability than the proxy user,

"
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
"
so is there any really any difference between a direct logon as "sys" and connect "as sysdba"?

thanks
Tom Kyte
June 12, 2008 - 2:57 pm UTC

when you connect / as sysdba - the schema you are assigned to is SYS, but you are not really logged in as sys, you are in as sysdba

just like when you log in with sysoper, you are assigned to public... you are not logged in as public (there is no user public), it is just the schema you are in, you have sysoper privileges.



ops$tkyte%ORA10GR2> alter user public identified by foobar;
alter user public identified by foobar
           *
ERROR at line 1:
ORA-01935: missing user or role name


ops$tkyte%ORA10GR2> connect / as sysoper
Connected.
public%ORA10GR2> show user
USER is "PUBLIC"
public%ORA10GR2>




there is a huge difference between logging in as sys and logging as "AS SYSDBA", huge (log in as sys and try to shutdown, it won't work until you "as sysdba" yourself (or as sysoper)

A reader, June 13, 2008 - 11:16 am UTC

thanks Tom for clarification...i am just a bit confused, as when i use the test user, who has been grant sysdba privilege, seems i can not really tell any difference between the sys user and test user.


see following output:

output of 'uname -a' ==>
bash-3.00$ uname -a
SunOS  5.10 Generic_118833-36 sun4u sparc SUNW,Sun-Fire-V240

outout of 'id' ===>
bash-3.00$ id
uid=8805(testos) gid=10(staff)

output of sqlplus ==>
bash-3.00$ sqlplus test as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jun 13 10:50:37 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> show user
USER is "SYS"

SQL> alter user sys identified by abc;
User altered.

SQL> select username ,osuser from v$session;

USERNAME                       OSUSER
---------------------------- ------------------------------
SYS                            testos
                               oracle
                               oracle
                               oracle
                               oracle
                               oracle
                               oracle
                               oracle
                               oracle
                               oracle
USERNAME                       OSUSER
----------------------------- ------------------------------
                               oracle
                               oracle
                               oracle
                               oracle
                               oracle
                               oracle



  1* select grantee,PRIVILEGE from  dba_sys_privs where grantee='TEST'
SQL> /

GRANTEE                        PRIVILEGE
------------------ ----------------------------------------
TEST                           CREATE SESSION



SQL> select grantee,GRANTED_ROLE from dba_role_privs where grantee='TEST';

GRANTEE                        GRANTED_ROLE
----------------------------- ------------------------------
TEST                           CONNECT


SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
TEST                           TRUE  FALSE

Tom Kyte
June 13, 2008 - 11:43 am UTC

type shutdown, then you'll see.

SYS is simply the username you are using when you are connected as SYSDBA.

just like PUBLIC is when you connect via SYSOPER.

sysdba means you can do more than sys can.


this:
sqlplus test as sysdba

is the same as:

sqlplus santa/claus as sysdba

you are using OS authentication, it cares not a whit what username you use, or password - they are not relevant.

sqlplus / as sysdba

is what is really is.


using TEST would only be useful over the network - so you can authentication using test's credentials instead of the OS.


Alexander, May 05, 2011 - 8:37 am UTC

Tom,

Our auditors were asking if a sys admin had root on a server could they gain access to the database. I believe they can by using OS authentication "/ as sysdba". How should people secure this? That's obviously not good, and most other database engines are not like that.
Tom Kyte
May 06, 2011 - 9:57 am UTC

Yes they can.

You would have to secure root.

How do that varies by operating system, you and limit what root can actually do, you can have different types of root as well - perhaps. It depends on the capabilities of your OS.

You would have to limit root's ability to "su"



What other database engines are NOT like that????? All database engines would be like that pretty much. If you can become a user, if you can add people to groups - all bets are off - for every database that I'm aware of. Tell me of one that something like this wouldn't be an issue in some form?????


Alexander, May 10, 2011 - 10:26 am UTC

I read about restricting root from being able to su to oracle, problem with that is your instance will have to be manually started after a reboot/crash. That's no good. Hopefully the fact that all SYSDBA logins are automatically audited will keep the auditors happy.

SQL Server and Sybase this does not apply because you need a database account to log in. MSSQL has windoze authentication but we remove the "built ins" functionality that allow local admins to get in. To your point I suppose it's possible they could reinstall them, but that would likely require a reboot because it's Windoze, and rebooting a prod box is a good way to get yourself noticed.
Tom Kyte
May 10, 2011 - 11:56 am UTC

It wouldn't matter, root can just take a copy of the database and put it anywhere they want - do you get that? All databases - everything in the world actually - suffers from this.

Unless you lock down root, root can do *anything*

And no, you would not have to manually start a database - SYSOPER (which is the public account) can connect, startup and shutdown a database - but not much else. You just need a SYSOPER user to start the database. People use SYSDBA WAY WAY too much for too many things. For starting a database it just isn't necessary.

Rebooting a windows box is a good way to get noticed? Hah, hardly, just make the change and wait for the next SCHEDULED reboot which is probably in 5 or 10 minutes ;)

Seriously though, waiting for a reboot is *not* a deterrent.


If you have an unsecured root account, they are able to do whatever they darn well please. I'd just take a backup and be done with it if I wanted to steal your data.

Alexander, May 10, 2011 - 12:37 pm UTC

"Rebooting a windows box is a good way to get noticed? Hah, hardly, just make the change and wait for the next SCHEDULED reboot which is probably in 5 or 10 minutes ;)"

Touche.

However, copying active datafiles, that wont't fly, how are you going to access them?

For making a backup, are you assuming root can su in this case? When you say "backup" do you mean making an OS copy of the files? Again I don't see what good that does you.

But in any case, I think we agree that you pretty much can't keep sys admins from getting at our stuff. Perhaps database vault would be a good way.
Tom Kyte
May 10, 2011 - 12:42 pm UTC

However, copying active datafiles, that wont't fly, how are you going to access them?


there are many utilities for that and besides, remember - this guy is ROOT.

Probably, they'd just get them right from the backup area/tape/where ever.

or maybe, just fire off the backup script.

Remember, there must be an automated way to start/stop the database - you've got scripts all over the place - to backup and whatever (and if you are using passwords and not OS authentication - how are you getting passwords to those things in an automated fashion??? How is it you can start up your sqlserver without manual intervention???)


But in any case, I think we agree that you pretty much can't keep sys admins from getting at our stuff. Perhaps database vault would be a good way.


That would stop DBA-1 from seeing what DBA-2 can see in a database.

It will not stop an unfettered OS account with SYSDBA.


You'll have to accept that for any database - unfettered root access ultimately means "that person owns everything"


I cannot agree with you in your conclusion, no - we don't agree.

You can limit root.
You can still have your automated start up

You *can* lock down root.

Alexander, May 10, 2011 - 12:58 pm UTC

I don't understand how you can secure root from becoming oracle and still have an automated startup script. SYSOPER, SYSDBA, whatever doesn't that assume you have already switched to the software owner that allows OS authentication to the database?
Tom Kyte
May 11, 2011 - 9:18 am UTC

You just need to be able to become any user that has SYSOPER.

You don't have to be "Oracle" - any account can start the database, Heck, you can start the database from a REMOTE MACHINE if you want.

Alexander, May 12, 2011 - 9:02 am UTC

Ok yeah true. But like you said, root can just copy the datafiles and read them with the strings command or something right? Which brings us back to, "you can't secure root". Preventing su to oracle is moot if they can just steal the files and read them.
Tom Kyte
May 12, 2011 - 9:29 am UTC

You can secure root, you can make it so that they cannot do that either.

One simple approach would be for the system security office to make root moot - no one gets root, but they can sudo for certain specific commands only, as needed.

and of course, if you are using advanced security - we can secure the backups using encryption - so that even if they stole the datafiles, they wouldn't be able to use them...

Windows ORA_DBA Group problem.

Towhidun Nabi, June 10, 2011 - 7:40 am UTC

Windows 2003 server, Oracle version 9iR2, Release 9.2.0.1.0.

We are working on a Software, but some new guy came along, and showed like this:

c:\>sqlplus /nolog

SQL> connect / as sysdba;
Connected.
SQL>
...
For this, he can access the whole database, and/or change everything, claiming the software has NO security, whatsoever. We tried to tell him, it is NOT an issue of the software, it is Oracle system feature, which gets automatically installed.

Our client asked us to fix the problem.

We have dropped the ORA_DBA system group, and also set 

  SQLNET.AUTHENTICATION_SERVICES= (NONE)

in the sqlnet.ora.

and the command prompt:

c:>slqplus /nolog

SQL>connect / as sysdba;
ERROR:
ORA-01031: insufficient privileges

so, it was fixed, as we thought.

BUT when we showed that to our client, that new guy said he can still log on to the database.

HE just added ORA_DBA to the system group, and edited 

  SQLNET.AUTHENTICATION_SERVICES= (NTS)

then
c:\>sqlplus /nolog

SQL> connect / as sysdba;
Connected.
SQL>
...

We tried to tell it was an Administrative password that LOGGED onto the system, OS Administrator, and if it has ORA_DBA role, it can access the database without any password. 

OUR Client asked us to fix it as that new guy is the Computer In-Charge, he will have the OS Administrative password, but we can NOT let him play with the DATABASE.

OUR client was totally convinced, we have a faulty system, our database has NO security, whatsoever.

How to stop this?? Is there any way to prevent this??

Any help is highly appreciated.

Thanks in advance.

Naeeym.

Tom Kyte
June 10, 2011 - 8:14 am UTC

the OS administrator can do anything by default, think about it. he could take the entire machine (a backup) home with him and do anything.

Unless you get something to secure your OS, to limit what he can do, he has carte blanche access to *everything*.

This is not a fault of Oracle, it is a fault of OS setup, you have a "root" user that has 100% of all privileges. You really need to talk to Microsoft and ask them "how can we partition the abilities of the OS admin so they cannot 'break' into other things"


think about this - SOMEONE has to be able to administer users - right? Someone has to be able to add users to groups - right? Someone has to - otherwise NO ONE could work - you couldn't even install Oracle!

Chuck, June 10, 2011 - 9:17 am UTC

And someone probably maintains the hardware. Imagine what the persons who can update firmware have access to do.
Where does it end?


OS Administrator

Towhidun Nabi, June 10, 2011 - 11:19 am UTC

Thanks for the quick reply.

IF the OS Administrator can copy BINARY files, no problem. If he can take backup and take it to his home, MULTIPLE BACKUP, very good.

BUT, if he can play with LIVE database, where HIS account is also maintained, if he can change any data without a FOOTPRINT, it is a problem, its like giving a GUN to a punk and ask him to handle with care.

ORA_DBA was NOT a Microsoft Invented/PUSHED technology, it was supplied/developed/installed by Oracle Software, and Oracle can NOT stop OS authentication??

Ok, like alow OS authentication, or DATABASE authentication only??

And there is no way to put a password on a DMP file, any one can take a DMP file, install it on another machine to see whats in there. Off course, DBA password needed on the other machine, well the Other Machine...

We just have to ZIPPed the DMPs with a password, and delete the DMPs.

Thanks again.

Naeeym.

Tom Kyte
June 10, 2011 - 2:54 pm UTC

You can stop OS authentication by locking down the ability of your ADMIN to do anything they want. You can in fact lock down what an admin can do. Right now your admin is 'god', they can do anything they want - to any account - any time. You can actually audit these guys too.



And there is no way to put a password on a DMP file

yes there is. http://docs.oracle.com/docs/cd/E11882_01/server.112/e16536/whatsnew.htm#SUTIL2872



Chuck Jolley, June 10, 2011 - 1:03 pm UTC

Not to spoil your innocence, but you might want to google "zip file password cracker" and count the hits.

A reader, June 27, 2011 - 6:56 am UTC

"You can stop OS authentication by locking down the ability of your ADMIN to do anything they want. You can in fact lock down what an admin can do. Right now your admin is 'god', they can do anything they want - to any account - any time. You can actually audit these guys too."

How-can you please give us some pointer to the right direction? we also use Oracle 9i, No advance security, No solid way to stop an admin to do anything.

And also I think an admin can delete from sys.aud$ where ...






Tom Kyte
June 27, 2011 - 11:24 am UTC

You would talk to your OS vendor - this isn't a database thing at this point, right now your OS administrator can do anything they want.

You would use OS audit trails for the DBA auditing

Renaming OS Oracle Install Account

Jim Cox, January 10, 2012 - 12:37 pm UTC

Hi Tom

I know you are not a windows person, but hope you can assist with my question. I have a windows server os account (the default windows admin account) on all my oracle servers that I have been using since day 1, it was renamed to say "SYSJIM" before i started. SYSJIM account has been used to install oracle, databases, clients and patches. Only acocunt that has been used for oracle. Now the powers that be want to rename that account to say "SYSCOX" and have me use a different account for all my oracle tasks. I am putting up a fight but not getting much support here. So my question is what is going to happen if that OS account is renamed ? Does Oracle care since the Windows SID is stil intact? Will I have to de-install and re-install everything on these servers under the new os account ?

Appreciate Any Support on This One

Jim
Tom Kyte
January 10, 2012 - 10:29 pm UTC

I'm sorry, I have no idea - I really do not administer windows environments at all.

Maybe someone else will comment.

Hope this helps

DJB, January 11, 2012 - 7:39 am UTC

I'd say that you'd need to remove the Oracle permissions from the renamed account - in particular membership of the ORA_DBA group - and give those permissions to the new account which will be used to administer Oracle on the server. The other thing to consider would be if you've created a login in the database based on the original account so taht you can use o\s authentication to administer the database. If so you'll need to drop the original log in and create another one based on your new o\s account to enable this to continue.

Renaming OS Oracle Install Account - Thanks

Jim Cox, January 11, 2012 - 10:50 am UTC

Thanks Tom

wanted your input anyway

Also thanks DJB

Jim

mfz, January 11, 2012 - 11:36 am UTC

@Jim


The directories containing the data files / binaries / external table has to be provisioned to the new user.

You will need to change the services ( listener , databse , CJQ , dbconsole) to sign on with the new username.

MFZ-Follow-up

Jim Cox, January 12, 2012 - 3:18 pm UTC

MFZ

i understand about the services; they all use Local System

what do you mean by:
The directories containing the data files / binaries / external table has to be provisioned to the
new user.

Oracle is telling me that re-naming the OS account will not be an issue. Are you saying there will be an issue, if i rename the OS account that was used to install oracle ?

Thanks
Jim


Don't think that's right

A reader, January 13, 2012 - 4:24 am UTC

You shouldn't need to do anything with the directories etc. All you really need is to make sure the 'new' user is in the ora_dba group, has admin privileges and create an appropriate user in the database if you're logging it in as o\s authenticated.

Don't think that's right -Thanks

Jim Cox, January 13, 2012 - 2:18 pm UTC

Thanks for your input

Jim

Database Vault and OS Authentication

stephan, February 07, 2012 - 8:23 am UTC

Hi Tom,

We're trying to put together a security strategy for a multi-database environment. We are looking to create named DBA users and limit rights at the OS level so users cannot add themselves to the ORA_DBA group.

If I read correctly, we will still need SYSDBA access to start up and shut down the databases, so we will have a small group of DBAs with access via the ORA_DBA group.

We are also considering using a database to store credentials - we don't want to have them simply scribbled on paper or whiteboards :-) So we've set up an admin DB, encrypted tablespace for the credentials, the tables that store that data is owned by a schema with zero privileges, and we have a small number of us with OS auth accounts that have read and write privileges on those tables.

Of course, connecting as SYSDBA, we can see that data. We figured this would be a good chance to try DB Vault, and see how that might fit into our security strategy.

I have two questions from this:
1) DBVault turns off OS Authentication in the name of security. How do we reconcile this with your recommendation to use OS Authentication?
2) DBVault also disables connections "as sysdba". If sysdba is disabled, how are we expected to shut down and start up the instance? It doesn't seem to me like the expectation really can be that people will leave sysdba disabled...
Tom Kyte
February 08, 2012 - 1:24 am UTC

... If I read correctly, we will still need SYSDBA access to start up and shut down
the databases ...

no, you don't. You need the SYSOPER privilege, you can start up and shutdown - and have public privileges (not sys).


So we've set up an
admin DB, encrypted tablespace for the credentials, the tables that store that
data is owned by a schema with zero privileges, and we have a small number of
us with OS auth accounts that have read and write privileges on those tables.


??? that sounds like a really bad idea - why are you not using a single sign on facility????

Or simply
http://docs.oracle.com/cd/E11882_01/network.112/e16543/authentication.htm#DBSEG99816

Ouch

stephan, February 10, 2012 - 3:19 pm UTC

Hi Tom,

Always appreciate your feedback - but I have to admit it was not what I expected in this case. Either I didn't express our goal very well, or we're overlooking something big.

This was not intended to be simply a credential database - credentials were only one part of what we were looking to store. Therefore we thought it better to have one admin DB to hold all our information rather than have some information in a database, some in a spreadsheet, some in an external password store, &c.

Is your concern with the concept of an admin DB, or with the idea that we'd be creating a database to store credentials only?

Thanks,
stephan
Tom Kyte
February 10, 2012 - 5:31 pm UTC

I'm concerned that you would be storing credentials. It is a non trivial task and one that opens you up to massive security issues. If you want to store credentials, I recommend the tool I just pointed you to.


that is what is sounded like what you think you wanted to do:

We are also considering using a database to store credentials - we don't want to have them simply
scribbled on paper or whiteboards :-) So we've set up an admin DB....

Restricting Drop any Table

Snehasish Das, December 22, 2013 - 5:30 pm UTC

Hi Tom,

Good day.

Recently I faced an issue in our development box. There are some schema/users which need to have permission to drop tables from other schema (some not all) which belong to the same application. I can't give drop any tables privilege to this user/schema as it will/can drop tables from schemas which are not related to the application.

Is there any way to restrict the drop any table privilege so that it can drop tables of the schema which it has grants to.

I know its a bad design, but as the application is old , will have to live with it.

Regards,
Snehasish Das

Please help.

Snehasish Das, January 22, 2014 - 11:38 am UTC

Hi Tom,

Happy new year.

Can you please help me out here.

Regards,
Snehasish Das

Priviledges on Only one schema

Snehasish Das, April 07, 2014 - 7:49 am UTC

Hi Tom,

I will be grateful if you can you please help me with the above request.

Regards,
Snehasish Das
Tom Kyte
April 16, 2014 - 5:36 pm UTC

there is not a direct way to do this, no.

you can write a stored procedure that uses dynamic sql to drop the tables, put logic in there to restrict the tables to be dropped to whatever you want. grant execute on this procedure to the users that need to drop some of the tables. make sure you use dbms_assert to test their inputs to avoid sql injection!!!


or you could write a DDL event trigger on this schema. You could inspect who is trying to drop what and raise an error if they should not drop it.

http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/create_trigger.htm#CIHGCJHC

Thanks

Snehasish Das, April 17, 2014 - 10:47 am UTC

Hi Tom,

thanks a lot.

Alexander, April 18, 2014 - 4:33 pm UTC

Can database vault do this? Probably overkill for this situation, but I'm curious if it can do that.
Tom Kyte
April 18, 2014 - 9:20 pm UTC

database vault would allow them to limit the use of a system privilege, such as drop any table, to a specific set of schema's and/or objects. So yes, database vault could potentially be used in this case

http://docs.oracle.com/cd/B28359_01/server.111/b31222/cfrealms.htm#DVADM70130


A reader, November 19, 2014 - 4:45 pm UTC

If you lock sys account how will you do upgrades,patching etc when you need to run the upgrade/patch sqls Tom?
Tom Kyte
November 26, 2014 - 9:05 pm UTC

you do not need access to these things day to day. You need sysdba to upgrade/patch. Get sysdba allocated to you *for that period of time*.

Use the concept of least privileges. day to day NO ONE should be logging in as SYSTEM. No one. No one should log in as SYS either (that account by default is locked). And almost no one should be logging in as sysdba.


Do you use root to develop with?
Don't use sys and system day to day. Using it when you need to (and system isn't something you need to use)

Physical standby / active database standby

A reader, April 09, 2015 - 8:21 pm UTC

Hi Tom, A quick question related to the topic of locking SYS and SYSTEM accounts.

We wanted to follow this for better security as well, however 1. Issues in keeping sync between Primary and physical standby (11.2.0.x)
2.RMAN duplicate from active database (11.2.0.x) which requires to have SYS account.

Is there any work around that we use for the above two issues?

Thanks.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.