Archive for

Oracle sql92_security parameter

The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements.

Example :

SQL> create user employees identified by employees;

User created.

SQL> grant connect,resource to employees;

Grant succeeded.

SQL> conn employees/employees;
Connected.
SQL> create table empl_object as select * from all_objects;

Table created.

SQL> select count(*) from empl_object;

COUNT(*)
----------
55877

SQL> conn / as sysdba
Connected.
SQL> create user usera identified by usera;

User created.

SQL> grant connect to usera;

Grant succeeded.

SQL> create user userb identified by userb;

User created.

SQL> grant connect to userb;

Grant succeeded.

SQL> conn employees/employees;
Connected.
SQL> grant update,select on empl_object to usera;

Grant succeeded.

SQL> conn usera/usera
Connected.

SQL> update employees.empl_object set owner='test' where owner='SYS';

23687 rows updated.

SQL> COMMIT;

Commit complete.

SQL> conn userb/userb
Connected.
SQL> update employees.empl_object set owner='SYS' where owner='test';

23687 rows updated.

SQL> commit;

Commit complete.

Enable sql92_security parameter.

SQL> show parameter sql92_security;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql92_security boolean FALSE

SQL> alter system set sql92_security=TRUE scope=SPFILE;

System altered.

SQL> show parameter sql92_security;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql92_security boolean FALSE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2226912 bytes
Variable Size 687867168 bytes
Database Buffers 905969664 bytes
Redo Buffers 7348224 bytes
Database mounted.
Database opened.
SQL> show parameter sql92_security;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql92_security boolean TRUE

SQL> conn usera/usera
Connected.
SQL> update employees.empl_object set owner='test' where owner='SYS';

23687 rows updated.

SQL> commit;

Commit complete.

Now you can not update the table.

SQL> conn userb/userb
Connected.
SQL> update employees.empl_object set owner='SYS' where owner='test';
update employees.empl_object set owner='test' where owner='SYS'
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

Just read more detail from  This blog

Oracle impossible passwords, why?

When you configuring the password for a user in database, it automatically calculating the password hash normally or bypassing the hash process and writing a value to the PASSWORD column of sys.user$.

Just a exmple :

SQL> create user imppassword identified by imppassword;

User created.

SQL> select name,password from sys.user$ where name='IMPPASSWORD';

NAME                           PASSWORD

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

IMPPASSWORD                    27D780816A86DC5C

When this user provides their credentials to Oracle to log in, Oracle will take the credentials, create the password hash, and then compare it with the value stored in SYS.USER$, find a match, and allow the user to connect.

SQL> grant connect,resource to imppassword;

Grant succeeded.

SQL> conn imppassword/imppassword;

Connected.

If we created the user and explicitly set the password value, things would be different.

SQL> create user password identified by values 'password';

User created.

SQL> grant connect,resource to password;

Grant succeeded.

SQL> select name,password from sys.user$ where name = 'PASSWORD';

NAME                           PASSWORD

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

PASSWORD                       password

This time the value stored is the text string PASSWORD.This is an impossible password, because regardless of the input, the Oracle password hashing algorithm could never produce output that matches. Now you can not able to log in this user. Its called impossible password.

Why it need for implement?

If you would like to lock the user account, The attacker tries to login the these user account it shows the user account is locked , they may get a good information from this error.  Locking default accounts tells an attacker what schemas and therefore what features are installed on a given database.This allows them to create a plan of attack before they ever penetrate the database, building a list of potential exploits with the benefit of knowing which potentially vulnerable features are installed.

SQL> conn scott/tiger

ERROR: ORA-28000: the account is locked

Warning: You are no longer connected to '

ORACLE. SQL>

Furthermore, the attacker now knows the password for each account is set to the default value! Setting an impossible password and unlocking the accounts prevents an attacker from logging in, but also provides no information about the existence of each account/schema or their password.

SQL> conn password/password

ERROR: ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

With impossible passwords, an attacker gets no information from an attempt to log in to the database with each default account and password. Less information is always better and it’s our responsibility to make a hacker’s job as difficult as possible.

Listener valid node checking

Valid node checking is the one of the listener secuirty feature. I thought may be very lesser known this feature. What purpose of this feature use, this feature uses If you configuring the the set of invited or exluded nodes, you can take control of which hosts are allowed to connect your database. You can add it hostnames or ip address in sqlnet.ora file.

In other cases it can be more complex, particularly when a single database is hosting several applications.There will be some cases where a database must accept connections from a large number of hosts, when the list of hosts that connect is constantly hanging, or even some organizations that change both IP addresses and hostnames for their machines on a somewhat random basis. For these systems,Valid Node Checking is not a viable feature.

If you think your database may connect few client hosts, you can add this feature in sqlnet.ora file. this feature restricted to connect the database from other client hosts.

Let’s See ,

Database Server , I just configured following entries in sqlnet.ora file.

tcp.validnode_checking = yes

 

 tcp.invited_nodes = (siebelpoc)

Reload listener

lsnrctl>reload

From client host :

[oracle@siebelpoc ~]$ export ORACLE_SID=testdb 
[oracle@siebelpoc ~]$ sqlplus system/Admin123@testdb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 26 11:02:48 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME --------- TESTDB

I can able to connect.

Again I just edited invited nodes in database server sqlnet.ora file

tcp.validnode_checking = yes

 

tcp.invited_nodes = (siebel)

Reload listener

lsnrctl>reload

From client machine,

[oracle@siebelpoc ~]$ sqlplus system/Admin123@testdb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 26 11:05:36 2012

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

ERROR: ORA-12537: TNS:connection closed

Now I can not able to connect the database from client hosts because of i changed invited node in sqlnet.ora file.

TNS-12508: TNS:listener could not resolve the COMMAND given

LSNRCTL> set  log_directory /u01/app/oracle/product/11.2.0.2/dbhome_2/network/admin/lsnrlog

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxxxxxx)(PORT=1521)))

TNS-12508: TNS:listener could not resolve the COMMAND given

the above issues happen while i’m trying to set log directory for listener. But It refused the comments, Why it’s refused the commands means I enabled the ADMIN_RESTRICTIONS parameter option in listener.ora file. So that reason, I cann’t set command used as administrator level, It restircted for executing the following set command options like SET TRC_FILE, SET TRC_DIRECTORY,SET TRC_LEVEL, SET LOG_DIRECTORY,SET LOG_STATUS,SET CURRENT_LISTENER,SET STARTUP_TIME.

When enabled, ADMIN_RESTRICTIONS instructs the Listener not to accept any administrative commands from lsnrctl. Instead, an administrator must log in to the Listener’s host OS and make configuration changes directly in listener.ora.

So In this case, We should  disable the  ADMIN_RESTRICTIONS_LISTENER = OFF and after reload the listener.

This parameter used for preventing from hackers who are trying to take the listener trace file through remote and It refused the set commands…

TNS-01251: Cannot set trace/log directory under ADR

When you’re trying to set new listener log directory, you may face a same error below

LSNRCTL> set log_directory /u01/app/oracle/product/11.2.0.2/dbhome_2/network/admin/lsnr_log 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxx)(PORT=1521)))

TNS-01251: Cannot set trace/log directory under ADR

Solution :

If the parameter DIAG_ADR_ENABLED_listenername is set to ON in the listener.ora file, the trace and log file should be located under ADRBASE_listenername location. If ADR is enabled, all listener related trace file and log file stored in under ADR base location. If you don’t want to store there, you can disable ADR.

In listener.ora file

DIAG_ADR_ENABLED_LISTENER = OFF

reload the listener.

LSNRCTL> reload

LSNRCTL> set log_directory /u01/app/oracle/product/11.2.0.2/dbhome_2/network/admin/lsnr_log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxx)(PORT=1521)))
LISTENER parameter "log_directory" set to /u01/app/oracle/product/11.2.0.2/dbhome_2/network/admin/lsnr_log The command completed successfully