Fatal NI connect error 12170.

From Mos Doc ID 1286376.1

Fatal NI Connect Error 12170, 'TNS-12535: TNS:operation timed out' Reported in 11g Alert Log   

In this Document
Symptoms
Changes
Cause
Solution
References
APPLIES TO:

Oracle Net Services - Version 11.1.0.6 to 12.1.0.2 [Release 11.1 to 12.1]
Information in this document applies to any platform.
TNS-12170, ORA-12170, TNS-12535, TNS-00505 alert.log


SYMPTOMS


nt secondary err code: 110   Monitoring of the 11g database Alert log(s) may show frequent timeout related messages such as:

- On Oracle Solaris:

***********************************************************************

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
Time: 22-JAN-2011 21:48:23
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092))

---------
The "nt secondary err code" will be different based on the operating system.

Linux x86 or Linux x86-64: "nt secondary err code: 110"
HP-UX Server: "nt secondary err code: 238"
AIX: "nt secondary err code: 78"




CHANGES

No changes are necessary, but may have recently upgraded the database to 11g release 1 or higher, or installed a new Oracle11g database.

Note: Prior to 11gR1 these same 'Fatal NI connect error 12170' are written to the sqlnet.log

CAUSE

These time out related messages are mostly informational in nature.  The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out.  The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.

The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */

For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */


For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)

Description:  A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default.  See (Doc ID 454927.1).

SOLUTION

Suggested Actions:

- Search the corresponding text version of the listener log located on the database server for the corresponding client connection referenced by the Client address details referenced in the alert log message.

For the message incident below you would search the listener log for the 'Client address' string: 

(ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092))

The search of the listener log should find the most recent connection before the time reference displayed in the alert log message, e.g. '22-JAN-2011 21:48:23'.

-Corresponding listener log entry:

22-JAN-2011 21:20:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMN11264.us.oracle.com)(CID=(PROGRAM=D:\app\mcassady\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=mcassady-lap)(USER=mca
ssady))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092)) * establish * AMN11264.us.oracle.com * 0

- Alert log entry:
------------

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
Time: 22-JAN-2011 21:48:23
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092))

------------

Note the time of the client corresponding client connection(s) in the listener log.  Here you may find a particular client, set of clients or particular applications that are improperly disconnecting causing the timeout errors to be raised and recorder in the database alert log.

 

See the following for more information and a potential solution where a firewall may be causing this issue:   Note:1628949.1   Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out



You may choose to revert from the new Automatic Diagnostic Repository (ADR) method to prevent the Oracle Net diagnostic details from being written to the alert log(s) by setting the following Oracle Net configuration parameters:


To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :

DIAG_ADR_ENABLED = OFF

Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora:

DIAG_ADR_ENABLED_<listenername> = OFF

   - Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is 'LISTENER', the parameter would read:

DIAG_ADR_ENABLED_LISTENER = OFF

-Reload or restart the TNS Listener for the parameter change to take effect.

ORA-01403

Hiçbir sonuç döndürmeyen SELECT INTO sql cümlesi çalıştırıldığında alınır.
Exception yakalama ile hatayı geçebiliriz.
BEGIN
SELECT dogum_yeri INTO v_dogum_yeri FROM kisiler WHERE id=501;
EXCEPTION
WHEN no_data_found THEN
— <<kayıt bulunamazsa yapılacak işlem>>
v_dogum_yeri :=’Dy Girilmemiş’;
END;

Someone on dba-Village forum asked about how to protect username and password for connecting to RMAN catalog database. Since I’m strongly against using remote os authentication (remote_os_auth=true) in real life production environment, I would probably choose Oracle Wallet for storing credentials for connecting to rman catalog.

Note: With Oracle Wallet implementation we’re relying on file system permissions that protects our wallet; it’s not perfect but I’m happy to trade this “risk” with (in my humble opinion) much riskier approach of using remote os authentication. Anyone with access to the wallet that has auto-login feature turned On, can connect as user stored in the wallet without a password! Approach described in this post should be used seldom and with care.
Here is a mini How-to (I was using Windows 10 and Oracle12c):

---

########################################### 1) Create Oracle wallet - the result of this step is directory D:\oracle\rmancat_wallet   with two files: cwallet.sso and ewallet.p12 . ###########################################   cmd> mkstore -wrl D:\oracle\rmancat_wallet -create Enter password: mysecret   PASSWORD_POLICY : Passwords must have a minimum length of eight characters and contain alphabetic characters combined with numbers or special characters. Enter password: mysecret1   Enter password again: mysecret1     #################################################### 2) Adding database user credentials to this wallet ORA11   .... TNS alias for RMAN catalog database rmancat .... database user test    .... password for rmancat ####################################################   mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora11 rmancat test   Enter wallet password:   Create credential oracle.security.client.connect_string1     ####################################### 3) Configure sqlnet.ora at client side #######################################   WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\oracle\rmancat_wallet))) SQLNET.WALLET_OVERRIDE = TRUE   ######################################## 4) Test connection ########################################   D:\ORACLE>sqlplus /@ora511   SQL*Plus: Release 11.1.0.6.0 - Production on ╚et Jul 10 13:58:00 2008   Copyright (c) 1982, 2007, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options   SQL> show user USER is "RMANCAT"   ######################################## 5) Additional security checks ########################################   I would recommend to save sqlnet.ora and tnsnames.ora separately from the common oracle home, for example we can copy both files to the wallet directory (in our example D:\ORACLE\rmancat_wallet). Make sure that only user executing rman backup has read permissions on this directory. By default Oracle changes permissions only on files cwallet.sso and ewallet.p12, leaving directory permission to be inhereted from the parent (at least that's the case on Windows, I'm not sure about Linux/Unix)!     Don't forget to setup environment variable TNS_ADMIN pointing to your wallet directory at the beggining of backup script.     ############################## 6) Various handy commands ##############################   >> ------------------------------ >> List the content of the Wallet >> ------------------------------   cmd> mkstore -wrl D:\oracle\rmancat_wallet -listCredential   Enter wallet password:   List credential (index: connect_string username) 1: ora11 rmancat   >> ------------------------------------------------- >> Modify credential stored in the wallet >> -------------------------------------------------   cmd> mkstore -wrl D:\oracle\rmancat_wallet -modifyCredential ora11 rmancat newpassword   Enter wallet password: mysecret1   Modify credential Modify 1   >> ----------------------------------- >> Deleting credential from the wallet >> -----------------------------------   cmd> mkstore -wrl D:\oracle\rmancat_wallet -deleteCredential ora11   Enter wallet password:   Delete credential Delete 1   >> ------------------------------------ >> Adding credential >> ------------------------------------   Remember, you can have only ONE user per TNS alias stored in the wallet. If you need to store two users (schemas) for one TNS, then you'll have to use two wallets!   For example, this is possible:   > mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora11 rmancat test > mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora12 scott tiger   If you try to add second account for the same TNS alias, you'll get error:   > mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora12 scott2 tiger   Create credential Secret Store error occured: oracle.security.pki.OracleSecretStoreException: Credential already exists   In this case you'll need to create new wallet for scott2.

When using command-line tools such as Oracle SQL*Plus, which can accept a plain-text password, users must use an alternative logon method that does not expose the password.

"For Oracle SQL*Plus, which cannot be configured not to accept a plain-text password, and any other essential tool with the same limitation:

1) Document the need for it, who uses it, and any relevant mitigations, and obtain AO approval.
2) Train all users of the tool in the importance of not using the plain-text password option and in how to keep the password hidden.

- - - - -
Consider wrapping the startup command with a shell or wrapper and using an Oracle external password store.

Oracle provides the capability to provide for a secure external password facility. Use the Oracle mkstore to create a secure storage area for passwords for applications, batch jobs, and scripts to use or deploy a site-authorized facility to perform this function.

Check to see what has been stored in the Oracle External Password Store.

To view all contents of a client wallet external password store, check specific credentials by viewing them. Listing the external password store contents provides information used to decide whether to add or delete credentials from the store. To list the contents of the external password store, enter the following command at the command line:

$ mkstore -wrl wallet_location -listCredential

For example:

$ mkstore -wrl c:\oracle\product\12.1.0\db_1\wallets -listCredential

The wallet_location specifies the path to the directory where the wallet, whose external password store contents is to be viewed, is located. This command lists all of the credential database service names (aliases) and the corresponding user name (schema) for that database. Passwords are not listed.

Configuring Clients to Use the External Password Store

If the client is already configured to use external authentication, such as Windows native authentication or Transport Layer Security (TLS), then Oracle Database uses that authentication method. The same credentials used for this type of authentication are typically also used to log on to the database.

For clients not using such authentication methods or wanting to override them for database authentication, set the SQLNET.WALLET_OVERRIDE parameter in sqlnet.ora to TRUE. The default value for SQLNET.WALLET_OVERRIDE is FALSE, allowing standard use of authentication credentials as before.

If wanting a client to use the secure external password store feature, then perform the following configuration task:

1. Create a wallet on the client by using the following syntax at the command line:
orapki create -wallet wallet_location -auto_login_local

For example:
orapki wallet create -wallet c:\oracle\product\12.1.0\db_1\wallets -auto_login_local
Enter password: password

The wallet_location is the path to the directory where the wallet is to be created and stored. This command creates an Oracle wallet with the autologon feature enabled at the location specified. The autologon feature enables the client to access the wallet contents without supplying a password.

The mkstore utility -create option uses password complexity verification.

2. Create database connection credentials in the wallet by using the following syntax at the command line:
mkstore -wrl wallet_location -createCredential db_connect_string username
Enter password: password

For example:
mkstore -wrl c:\oracle\product\12.1.0\db_1\wallets -createCredential oracle system
Enter password: password

In this specification:
The wallet_location is the path to the directory where the wallet was created. The db_connect_string used in the CONNECT /@db_connect_string statement must be identical to the db_connect_string specified in the -createCredential command.

The db_connect_string is the TNS alias used to specify the database in the tnsnames.ora file or any service name used to identify the database on an Oracle network. By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory on UNIX systems and in ORACLE_HOME\network\admin on Windows.

The username is the database logon credential. When prompted, enter the password for this user.

3. In the client sqlnet.ora file, enter the WALLET_LOCATION parameter and set it to the directory location of the wallet created in Step 1. For example, if the wallet was created in $ORACLE_HOME/network/admin and the Oracle home is set to /private/ora12, then need to enter the following into the client sqlnet.ora file:

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /private/ora12/network/admin)
)
)

4. In the client sqlnet.ora file, enter the SQLNET.WALLET_OVERRIDE parameter and set it to TRUE as follows:

SQLNET.WALLET_OVERRIDE = TRUE

This setting causes all CONNECT /@db_connect_string statements to use the information in the wallet at the specified location to authenticate to databases.

When external authentication is in use, an authenticated user with such a wallet can use the CONNECT /@db_connect_string syntax to access the previously specified databases without providing a user name and password. However, if a user fails that external authentication, then these connect statements also fail.

Below is a sample sqlnet.ora file with the WALLET_LOCATION and the SQLNET.WALLET_OVERRIDE parameters set as described in Steps 3 and 4.

Below is a sample SQLNET.ORA File with Wallet Parameters Set

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /private/ora12/network/admin)
)
)

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION =1.2 or 1.1

(Note: This assumes that a single sqlnet.ora file, in the default location, is in use. Please see the supplemental file "Non-default sqlnet.ora configurations.pdf" for how to find multiple and/or differently located sqlnet.ora files.)

Note: ""SSL_VERSION = 1.2 or 1.1" is the actual value, not a suggestion to use one or the other."

Pete Finnigan's Oracle Security Weblog-Grant DBA to yourself - exploit or not?

Yesterday Peter from the Master of Disaster Blog sent me an email to ask if I had seen the issue in his post before and whether it was a new exploit. I looked at the post and immediately recognised that this is not an exploit. Peter cannot grant DBA two himself as an exploit but he can do it because he first had granted system privileges that allow this. I could not post a blog post yesterday as I was teaching one of my online Oracle security classes.

As well as online Oracle security classes I will also be teaching my two day class - How to perform a security audit of an Oracle database in my home city of York, UK. There are still places left and if you would like to make the trip to York, please contact me to book your place. This class is taught by me and enables you to go away and secure your own databases

Whilst I had time first thing to do a quick demo and email Peter yesterday I didn't have time till now to write up a proper post; so here it is.

The blog post on Peters page has been updated to state that I sent him an explanation and the issue is not an exploit but the code he posted in the post linked above is pretty much the same. In the post Peter suggested that he could create a new user and then grant a bunch of roles and other grants to that user and then connect as the new user (although the actual connect was not shown) and then the new user was able to grant DBA to himself.

I did some tests based on Peters code. First i created a script tuser.sql based on the significant part of his script - granting IMP_FULL_DATABASE is the key. here is the script:

-- tuser.sql
create user tuser identified by tuser
default tablespace users
temporary tablespace temp;

GRANT CONNECT TO TUSER;

grant imp_full_database to tuser;

connect tuser/tuser@//192.168.1.95:1539/orcl.localdomain

grant dba to tuser;

I then ran this script as follows:

C:\_aa\PB\bin>sqlplus sys/oracle1@//192.168.1.95:1539/orcl.localdomain
as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 10 08:27:58 2017

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


Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL> @tuser

User created.


Grant succeeded.


Grant succeeded.

Connected.

Grant succeeded.

SQL> sho user
USER is "TUSER"

SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
CONNECT
IMP_FULL_DATABASE
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE

6 rows selected.

The role is not enabled as when you grant a role to yourself its not there by default. So log out and log back in at the TUSER and see if the DBA role is enabled:

SQL> connect tuser/tuser@//192.168.1.95:1539/orcl.localdomain
Connected.
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE
CAPTURE_ADMIN
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE

ROLE
--------------------------------------------------------------------------------
GATHER_SYSTEM_STATISTICS
OPTIMIZER_PROCESSING_RATE
EM_EXPRESS_ALL
EM_EXPRESS_BASIC
SCHEDULER_ADMIN
XDBADMIN
XDB_SET_INVOKER
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
OLAP_XS_ADMIN

ROLE
--------------------------------------------------------------------------------
OLAP_DBA

23 rows selected.

SQL>

The TUSER user now has the DBA role but as I said this is not an exploit or bug, its because the TUSER has IMP_FULL_DATABASE and in fact he needs to system privileges to grant the DBA role. GRANT ANY ROLE and GRANT ANY PRIVILEGE:

SQL> @who_has_priv



who_has_priv: Release 1.0.3.0.0 - Production on Sat Jun 10 03:57:39 2017
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PRIVILEGE TO CHECK        [SELECT ANY TABLE]: GRANT ANY ROLE
OUTPUT METHOD Screen/File                [S]:
FILE NAME FOR OUTPUT              [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]:
EXCLUDE CERTAIN USERS                    [N]:
USER TO SKIP                         [TEST%]:

Privilege => GRANT ANY ROLE has been granted to =>
====================================================================
        User => SYS (ADM = NO)
        Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
                Role => DBA (ADM = NO) which is granted to =>
                        User => TUSER (ADM = NO)
                        User => SYS (ADM = YES)
                        User => SYSTEM (ADM = NO)
                User => SYS (ADM = YES)
                User => GSMADMIN_INTERNAL (ADM = NO)
        Role => DBA (ADM = NO) which is granted to =>
                User => TUSER (ADM = NO)
                User => SYS (ADM = YES)
                User => SYSTEM (ADM = NO)
        Role => DV_REALM_OWNER (ADM = NO) which is granted to =>
                User => SYS (ADM = YES)
        Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
                User => TUSER (ADM = NO)
                User => SYS (ADM = YES)
                Role => DBA (ADM = NO) which is granted to =>
                        User => TUSER (ADM = NO)
                        User => SYS (ADM = YES)
                        User => SYSTEM (ADM = NO)
                Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is
granted to =>
                        Role => DBA (ADM = NO) which is granted to =>
                                User => TUSER (ADM = NO)
                                User => SYS (ADM = YES)
                                User => SYSTEM (ADM = NO)
                        User => SYS (ADM = YES)
                        User => GSMADMIN_INTERNAL (ADM = NO)
        Role => EM_EXPRESS_ALL (ADM = NO) which is granted to =>
                Role => DBA (ADM = NO) which is granted to =>
                        User => TUSER (ADM = NO)
                        User => SYS (ADM = YES)
                        User => SYSTEM (ADM = NO)
                User => SYS (ADM = YES)
        User => GSMADMIN_INTERNAL (ADM = NO)
        User => SPATIAL_CSW_ADMIN_USR (ADM = NO)

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>
SQL> @who_has_priv



who_has_priv: Release 1.0.3.0.0 - Production on Sat Jun 10 04:25:11 2017
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PRIVILEGE TO CHECK        [SELECT ANY TABLE]: GRANT ANY PRIVILEGE
OUTPUT METHOD Screen/File                [S]:
FILE NAME FOR OUTPUT              [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]:
EXCLUDE CERTAIN USERS                    [N]:
USER TO SKIP                         [TEST%]:

Privilege => GRANT ANY PRIVILEGE has been granted to =>
====================================================================
        User => TUSER (ADM = NO)
        Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
                User => SYS (ADM = YES)
                Role => DBA (ADM = NO) which is granted to =>
                        User => TUSER (ADM = NO)
                        User => SYS (ADM = YES)
                        User => SYSTEM (ADM = NO)
                Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is
granted to =>
                        Role => DBA (ADM = NO) which is granted to =>
                                User => TUSER (ADM = NO)
                                User => SYS (ADM = YES)
                                User => SYSTEM (ADM = NO)
                        User => SYS (ADM = YES)
                        User => GSMADMIN_INTERNAL (ADM = NO)
        Role => DV_REALM_OWNER (ADM = NO) which is granted to =>
                User => SYS (ADM = YES)
        Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
                Role => DBA (ADM = NO) which is granted to =>
                        User => TUSER (ADM = NO)
                        User => SYS (ADM = YES)
                        User => SYSTEM (ADM = NO)
                User => SYS (ADM = YES)
                User => GSMADMIN_INTERNAL (ADM = NO)
        User => GSMADMIN_INTERNAL (ADM = NO)
        User => SYS (ADM = NO)
        Role => EM_EXPRESS_ALL (ADM = NO) which is granted to =>
                Role => DBA (ADM = NO) which is granted to =>
                        User => TUSER (ADM = NO)
                        User => SYS (ADM = YES)
                        User => SYSTEM (ADM = NO)
                User => SYS (ADM = YES)
        Role => DBA (ADM = NO) which is granted to =>
                User => TUSER (ADM = NO)
                User => SYS (ADM = YES)
                User => SYSTEM (ADM = NO)

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

As you can see from the above who_can_access script results the IMP_FULL_DATABASE has these rights. Logically you would expect that GRANT ANY ROLE would allow someone to grant the DBA role to themselves but this is not the case:

Peters-MacBook-Pro:____12_2 pxf$ sqlplus sys/oracle1@//192.168.56.95:1539/orcl.localdomain as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 11 11:43:29 2017

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


Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> create user tuser identified by tuser;

User created.

SQL> grant create session to tuser;

Grant succeeded.

SQL> grant grant any role to tuser;

Grant succeeded.

SQL> connect tuser/tuser@//192.168.56.95:1539/orcl.localdomain
Connected.
SQL> grant dba to tuser;
grant dba to tuser
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> 

If we also try and grant GRANT ANY PRIVILEGE then it fails differently:

SQL> connect sys/oracle1@//192.168.56.95:1539/orcl.localdomain as sysdba
Connected.
SQL> drop user tuser cascade;

User dropped.

SQL> create user tuser identified by tuser;

User created.

SQL> grant create session, grant any privilege to tuser;

Grant succeeded.

SQL> connect tuser/tuser@//192.168.56.95:1539/orcl.localdomain
Connected.
SQL> grant dba to tuser;
grant dba to tuser
      *
ERROR at line 1:
ORA-01924: role 'DBA' not granted or does not exist


SQL> 

Finally, we need to grant both rights and the it works:

SQL> connect sys/oracle1@//192.168.56.95:1539/orcl.localdomain as sysdba
Connected.
SQL> drop user tuser cascade;

User dropped.

SQL> create user tuser identified by tuser;

User created.

SQL> grant create session, grant any role, grant any privilege to tuser;

Grant succeeded.

SQL> connect tuser/tuser@//192.168.56.95:1539/orcl.localdomain
Connected.
SQL> grant dba to tuser;

Grant succeeded.

SQL> 

So we actually need CREATE SESSION to connect, GRANT ANY ROLE and GRANT ANY PRIVILEGE to be able to grant DBA to ourselves. The reason the original code by Peter works is because he granted IMP_FULL_DATABASE to his user; so no exploit and no bug. The issue is because this role has these two system privileges. The IMP_FULL_DATABASE role is very dangerous and is as good as SYSDBA. This is because it also has ALTER USER and we can then change the SYS password and become SYSDBA. Do not grant IMP_FULL_DATABASE or rights such as GRANT ANY%.

Remember about my classes and please register if you would like to join a class especially the live Work class at the end of October.

Vault : Grant object privilege to sysdba

If you enabled valut on the system and if you want to grant select on privilege to dba, it is enough to write this command :

conn hr/hr;
grant select on emp to dba;

conn yoursys/yourpasswd as sysdba;

select count(*) from emp;
(Ora-01031 ....)

conn hr/hr;
grant select on emp to yoursys;

conn yoursys/yourpasswd as sysdba;
select count(*) from emp;
(Ora-01031 ....)

In order to work, you should give this privilege to sys user;

conn hr/hr;
grant select on emp to sys;

conn yoursys/yourpasswd as sysdba;
select count(*) from emp;
ok...

Grant on V$ Views - ORA-02030

Today i was faced with an error when I try to give permission on a v$view

SQL> grant select on v$instance to hr;
grant select on v$instance to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

What is ORA-02030?

The error ORA-02030 comes up when an attempt is made to select from a database object that is not a table or view.

SQL> select object_name
  2       , owner
  3       , object_type
  4  from all_objects
  5  where object_name like 'V$INSTANCE';

OBJECT_NAME     OWNER    OBJECT_TYPE
--------------- -------- ----------------
V$INSTANCE      PUBLIC   SYNONYM

How to resolve ORA-02030

ORA-02030 can be eliminated by granting select on the underlying table/view of the v$ synonym.
To find the underlying table/view, query all_synonyms:

SQL> select table_owner, table_name from all_synonyms
  2  where synonym_name like 'V$INSTANCE';

TABLE_OWNER                    TABLE_NAME
------------------------------ -----------
SYS                            V_$INSTANCE

That’s it – synonym v$instance points to an object with a slightly different name: v_$instance.


v_$instance is, indeed, not a table but a view:

SQL> select object_type from all_objects where object_name = 'V_$INSTANCE';

OBJECT_TYPE
-------------------
VIEW

Given these findings, all that’s needed is to grant select to the non-admin user on the v_$ view corresponding to the v$ synonym.

SQL> grant select on v_$instance to hr;

Grant succeeded. 

ORA-40365: The SYS user cannot be locked while the password file is in its current format.

CMD > orapwd file=$ORACLE_HOME/database/pwd$ORACLE_SID.ora format=12.2

CMD > orapwd describe file=pwd$ORACLE_SID.ora

Password file Description : format=12  

CMD > sqlplus / as sysdba

SQL> show user

USER is "SYS"

SQL> alter user sys account lock;

User altered.

SQL>  select username,lock_date from dba_users where username='SYS’:

USERNAME     LOCK_DATE

-------------------- ------------------

SYS     25-JAN-18

SQL> select username,lock_date from v$pwfile_users;

USERNAME     LOCK_DATE

-------------------- ------------------
SYS     25-JAN-18
SYSDG
SYSBACKUP
SYSKM 

Oracle database firewall

It all started with acquisition of Secerno, a database firewall vendor, in 2010. Secerno's product “DataWall” helped analyze how databases are accessed so that DBA’s can set up policies to control the access.

The database firewall has the ability to analyze SQL statements sent from database clients and determine whether to pass, block, log, alert, or substitute SQL statements, based on a defined policy. Users can set whitelist and blacklist policies to control the firewall. It can detect injected SQL’s and block them.  According to Oracle, the database firewall can do the following -

• Monitors and blocks SQL traffic on the network with white list, black list and exception list policies
• Protects against application bypass, SQL injection and similar threats
• Reports on database activity for SOX, PCI, HIPAA and other regulations, choosing from dozens of out-of-the-box reports
• Supports other Databases as well - MS SQL Server, IBM DB2 , and Sybase

The Database Firewall joins other database-security products offered by Oracle such as Oracle Advanced Security, Audit Vault, Database Vault, Secure backup etc.

Oracle Database firewall comes in 2 components:-

Database Firewall:

• Record and analyze SQL transaction requests and responses from one or more Oracle, Microsoft SQL Server, or Sybase databases, and Sybase SQL Anywhere.
• Categorizes SQL transactions
• Enforces data policies
• Enables real-time alerting and event propagation

Database Firewall Management Server:

• Aggregates SQL data from one or more Database Firewalls
• Serves as a reporting platform for business reports
• Centralizes the distribution of data control policies, but still enables the use of different policies for specific databases
• Stores and manages log files, including archiving and restoring the log files
• Remotely manages all Database Firewalls to which it connects
• Integrates with third-party applications, such as Crystal Reports

However there are some key issues that it does not address and hence would need use of other security options such as Audit Vault, VPD etc. For example, Privileged users can login to the OS directly and make local connections to the database. This bypasses the database firewall.

Oracle Database Privileges

In the previous blog, we looked at the three types of privileges Oracle database provide - administrative, system and object privileges. We are taking a deeper dive on SYS and SYSDBA in this post as many users do not really understand the concept behind these entities. Based on our years of experience, in order to secure your database, you need to understand these concepts inside out. It is surprising to find that many stakeholders of the database still misunderstand these terms. We try to answer most frequently asked questions with respect to these terms.

Let's get this straight. SYS and SYSDBA are two different things! Figure 1 captures the difference.

Figure 1: SYS user and SYSDBA privilege

- SYS is a real database user. It is not just a real user, it is the super user of database - equivalent to Linux root user - it sees all and can do all in the database!
- SYSDBA is a privilege - it is an administrative privilege which allows to do virtually anything on the database (Figure 2 shows the operations a SYSDBA privileged user can do.)

Figure 2: What a SYSDBA can do

- SYS schema holds the data dictionary!
- SYS is by default granted SYSDBA privilege (no one can change this.) in addition to SYSOPER privilege.

How SYSDBA privilege is different from system/object privileges?
- Even though SYSDBA privilege is granted to a user, it is not exercise, if the does not login as SYSDBA
- CONNECT command decides if you are login as an administrative user or not (Figure 3)

Figure 3: Exercising SYSDBA privilege

- If you are granted system and/or object privileges, they are always available for you to exercise when you login to the database.
- Also, SYSDBA privilege allows you to operate on unopened database whereas system/object privileges can only be exercised in an open database.

Let's assume that user scott is granted SYSDBA privilege. He can either login as a regular database user in which case he is restricted to exercise only those operations that are granted through system/object privileges and roles. Note: if you are using the multi-tenant option, you will have to provide the PDB name in the CONNECT statement (say PDB name is pdb1, then CONNECT scott/tiger@pdb1). For simplicity we assume a non-multitenant database in this post.

SQL> CONNECT scott/tiger
Connected.
SQL> SHOW USER;
USER is "SCOTT"

Or he can login as SYSDBA. This allows him to exercise SYSDBA privileges in addition to other privileges he is granted via system/object privileges and roles.

SQL> CONNECT scott/tiger AS SYSDBA
Connected.
SQL> SHOW USER;
USER is "SYS"

- You can login to the database even when it is not open (i.e. only mounted)

What is the difference between SYS and SYSDBA privileged granted user (we call it a named SYSDBA)?
- When you login as SYS user, login user and current user are SYS.
- When you login as a named SYSDBA user, login user is the named user (e.g. scott) and the current user is SYS.
- SYS can revoke the SYSDBA privilege from the name SYSDBA user but the reverse is not true.

What can a named SYSDBA do in the database?
- The previous answer partially answers this question.
- A named SYSDBA acts like a SYS user. It can virtually do anything that SYS user can do including changing the password of SYS user. (A side note: relying on password to authenicate users is thus not a good idea! it is better to use external authentication mechanisms such as Kerberos.)

Do you ever need to login as SYSDBA user to maintain a database? What are the best practices?

Figure 4: Minimize the use of SYSDBA

- It is a good practice to minimize the use of SYS user account as much as possible.
- Most of the database operations do not need SYSDBA privileged users. However, you need to login as a SYSDBA user if you need to do maintenance operations such as patching, migration.
- If the database administrator does not need access to data but still need to perform maintenance operations, you may grant SYSOPER privilege instead.
- Also it is a good practice to use custom administrative privileges depending on the task at hand - SYSDG, SYSKM, SYSBACKUP and SYSRAC.
- If the database administrator is responsible for performing operations on an open database, you may not need to grant any administrative privileges to the administrator - only system and object privileges should be sufficient to do her job.

Who can grant SYSDBA privilege?

Figure 5: Granting SYSDBA privilege

- SYS
- Any user having SYSDBA privilege

Who can access SYS schema (i.e. data dictionary)?

Figure 6: Restricting Access to Data Dictionary

- SYS user
- If O7_DICTIONARY_ACCESSIBILITY parameter is set to FALSE, "ANY PRIVILEGE" users can access SYS schema objects. It is highly recommended to set this parameter to TRUE so that ANY PRIVILEGE users cannot access SYS schema data.
- Any user who is granted SELECT ANY DICTIONARY privilege.
- Any user given explicit object privilege on SYS schema objects
- Any user having SELECT_CATALOG_ROLE may select a designated set of SYS schema objects - the set of objects are decided by Oracle at the time of installation. (SELECT_CATALOG_ROLE is basically granted SELECT on a bunch of SYS objects - mainly views. There two similar roles EXECUTE_CATALOG_ROLE and DELETE_CATALOG_ROLE for execute and delete operations respectively.)

E.g.
Let's see the value of O7_DICTIONARY_ACCESSIBILITY parameter:
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

So, by default no one except SYS can access the data dictionary.

scott does not have access to dictionary table sys.user$.
SQL> CONNECT scott/tiger
Connected.
SQL> SELECT count(*) FROM sys.user$;
SELECT count(*) FROM sys.user$
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

Let's check if scott has SELECT ANY TABLE privilege.
SQL> SELECT privilege FROM USER_SYS_PRIVS;
It shows that scott does not have SELECT ANY TABLE privilege

Let's grant SELECT ANY TABLE privilege to scott and see if he can access sys.user$.
SQL> CONNECT scott/tiger
Connected.
SQL> SELECT count(*) FROM sys.user$;
SELECT count(*) FROM sys.user$
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

As you can see, O7_DICTIONARY_ACCESSIBILITY parameter blocks even powerful SELECT ANY TABLE privileged users.

The following grant is performed for illustrative purposes. You should not grant select on SYS objects to regular users.

SQL> CONNECT / AS SYSDBA

Connected.

SQL> GRANT SELECT on user$ TO scott;

Grant succeeded.

SQL> CONNECT scott/tiger

Connected.

SQL> SELECT count(*) FROM sys.user$;

  COUNT(*)

----------

       151

As you can see, if a user is granted object privilege on SYS objects, they can access it.

- What is the difference between SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE role?
     - SELECT ANY DICTIONARY provides access to more SYS tables (for example, select on user$ table, but not SELECT_CATALOG_ROLE.
     - Also, if you are granted the role but not the privilege, you may not do any SELECT on the sys tables/views you are allowed to access in a PL/SQL procedure (you need direct grant to access them.)

How do I know if a given user is logged in as SYSDBA?
- The current user is "SYS"
SQL> SHOW USER;
USER is "SYS"

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
SYS_CONTEXT('USERENV', 'SESSION_USER')
--------------------------------------------------
SYS

- ISDBA parameter of USERENV system context is TRUE.
SQL> SELECT SYS_CONTEXT('USERENV', 'ISDBA') FROM DUAL;
SYS_CONTEXT('USERENV', 'ISDBA')
--------------------------------------------------
TRUE


What is the difference between SYSDBA and DBA?
- This is another frequently misunderstood concept!
- As mentioned, SYSDBA is the most powerful administrative privilege.
- DBA is a role! DBA role is granted all system privileges.
- If a user is granted DBA role, she can connect to the database only when it is in OPEN status.

What is a database privilege?

- In order to perform any task (e.g. select a table, execute a PL/SQL procedure) on the database, you need to have "access rights" to perform the task. These access rights are called privileges (e.g. SELECT privilege on the table, EXECUTE privilege on the PL/SQL procedure).

- In formal terms, privileges "authorizes" users to perform certain tasks.

What kind of privileges do we have in an Oracle database?

The following diagram shows the kind of privileges that an Oracle database have.

Figure: Oracle Database Privileges

What are administrative privileges?

Figure: Administrative Privileges

- Administrative privileges allow to perform regular database operations (such as selecting from a table, creating indexes) as well as database maintenance operations such as patching, migration, backing up, tuning, etc. 

- Prior to 12c, there were only two administrative privileges: SYSDBA, and SYSOPER. These are very powerful privileges and should be granted to uses with care. 

- SYSDBA privilege is equivalent to super user in Linux operating system.

- 12c introduced the following administrative privileges to provide better separation of duty and reduce the use of SYSDBA privilege.

  - SYSBACKUP (for backup and recovery operations)

  - SYSDG (for data guard operations)

  - SYSKM (for key management for TDE)

  - SYSRAC (for managing Real Application Clusters)

Figure: All Administrative Privileges are subset of SYSDBA

You can query the V$PWFILE_USERS to see the administrative users:

SQL> select username, sysdba, sysoper, sysasm, sysbackup, sysdg, syskm from V$PWFILE_USERS;

USERNAME             SYSDBA SYSOPER SYSBA SYSDG SYSKM

-------------------- ------ ------- ----- ----- -----

SYS                  TRUE   TRUE    FALSE FALSE FALSE
DBA0                 FALSE  TRUE    FALSE FALSE FALSE

DBA1                 FALSE  FALSE   FALSE FALSE FALSE

DBA1                 FALSE  FALSE   TRUE  FALSE FALSE

DBA2                 FALSE  FALSE   FALSE TRUE  FALSE

DBA3                 FALSE  FALSE   FALSE FALSE TRUE

What are system privileges and what are object privileges?

Figure: System and Object Privileges

- System privileges are applicable to the complete system and not specific to a given database object. For example, SELECT ANY TABLE system privilege allows to select from any table in the database.

- System privileges are very powerful and thus should be granted with care.
- Object privileges on the other hand grant access rights to specific database objects. For example, SELECT on scott.emp table.

- Object privileges support least privilege security principle but they are difficult to maintain.

How administrative privileges different from other privileges?

- Administrative privileges can be used even when database is not open.

- If a user is granted multiple administrative privileges (for example, SYS user is granted SYSDBA and SYSOPER administrative privileges), the user can login as only one administrative privilege user (for example as SYSDBA or as SYSOPER).

  - CONNECT SYS/<password> AS SYSDBA

  - CONNECT SYS/<password> AS SYSOPER

- When a user connects as SYSDBA (e.g. CONNECT scott/tiger AS SYSDBA), the current user is not the login user (i.e. scott), but is SYS user.

- When a user connects as SYSOPER (e.g. CONNECT scott/tiger AS SYSOPER), the current user is also not the login user (i.e. scott), but is PUBLIC user. 

How do I look up who is granted which privileges?

- SYS.V$PWFILE_USERS for administrative privileges

- SYS.DBA_SYS_PRIVS for system privileges

- SYS.DBA_TAB_PRIVS for object privileges