Wednesday, January 25, 2012

ORA-00257

I restored a database to a remote site today as part of a test.
 
In the screen shot below, I was logged on to the UNIX server as user oracle. I then tried to connect to the restored database using OS authentication. This failed so I connected as SYS instead.
 
I investigated the cause in a separate session (not shown here). For some reason, the archive area had incorrect permissions (it was owned by root, not oracle) so the redo logs could not be copied there. I changed the permissions on the archive directory so that oracle could write to it (this is not shown here either).
 
Then I was able to switch log file groups and connect to the database as ORACLE.
 
Note the error message, which says: Connect internal only. This is left over from Oracle 8 when you could use the command connect internal to login to the database as SYS:
 
UNIX > sqlplus /
 
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jan 25 10:49:21 2012
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
 
Enter user-name: / as sysdba
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL >
  
At this point I changed the ownership of the archive area in a separate UNIX session using chown. Then I returned to this SQL session and continued as shown below:
  
SQL > alter system switch logfile;
 
System altered.
 
SQL > alter system switch logfile;
 
System altered.
 
SQL > conn /
Connected.
SQL > show user
USER is "ORACLE"
SQL >

Another Example Using Intersect

In the course of helping a colleague today, I had to find a database user who had 2 roles assigned. You can do this with a couple of subqueries but it is easier with an INTERSECT. I was logged in as user ORACLE when I did this and I was surprised to see ORACLE in the list alongside USER_2. It seems that, if you create a role, it is automatically assigned to you. I did not know this. You can see this demonstrated at the end of the example. I ran it on an Oracle 9 database but the same thing happens in Oracle 10 and 11:
 
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> create role role_a
  2  /
 
Role created.
 
SQL> create role role_b
  2  /
 
Role created.
 
SQL> grant role_a to user_1
  2  identified by user_1
  3  /
 
Grant succeeded.
 
SQL> grant role_a, role_b to user_2
  2  identified by user_2
  3  /
 
Grant succeeded.
 
SQL> grant role_b to user_3
  2  identified by user_3
  3  /
 
Grant succeeded.
 
SQL> select distinct grantee from dba_role_privs
  2  where grantee in
  3  (select grantee from dba_role_privs
  4   where granted_role = 'ROLE_A')
  5  and grantee in
  6  (select grantee from dba_role_privs
  7   where granted_role = 'ROLE_B')
  8  /
 
GRANTEE
------------------------------
ORACLE
USER_2
 
SQL> select grantee from dba_role_privs
  2  where granted_role = 'ROLE_A'
  3  intersect
  4  select grantee from dba_role_privs
  5  where granted_role = 'ROLE_B'
  6  /
 
GRANTEE
------------------------------
ORACLE
USER_2
 
SQL> create role blah
  2  /
 
Role created.
 
SQL> select grantee from dba_role_privs
  2  where granted_role = 'BLAH'
  3  /
 
GRANTEE
------------------------------
ORACLE
 
SQL>

Wednesday, January 04, 2012

os_authent_prefix

In Oracle 10, the default value for os_authent_prefix was ops$:
 
SQL> l
  1  select value, isdefault
  2  from v$parameter
  3* where name = 'os_authent_prefix'
SQL> /
 
VALUE      ISDEFAULT
---------- ---------
ops$       TRUE
 
SQL>
 
So, if you created an ops$oracle user with a password:
 
SQL> grant dba to ops$oracle
  2  identified by andrew
  3  /
 
Grant succeeded.
 
SQL>
 
And you were logged onto the server hosting your database as UNIX user oracle:
 
TEST10 > whoami
oracle
TEST10 >
 
... you could logon to the database externally:
 
TEST10 > sqlplus /
 
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 3 14:41:28 2012
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL>
 
... or by using the password, which may not have been what you intended:
 
SQL> conn ops$oracle/andrew
Connected.
SQL>
 
To stop this happening, you could add the following line to your database’s parameter file:
 
os_authent_prefix = ""

remote_os_authent

The Oracle RDBMS used to have a parameter called remote_os_authent. This specified whether or not you could connect to an instance remotely using OS authentication. Setting it to true was a security risk, especially if you used OS authentication for database users which had the DBA role. For example, you might have an externally identified user in your database called ORACLE and grant the DBA role to that user. A malicious user with admin rights on a remote machine could create a user called oracle on that machine and use it to connect to your database as an administrator without providing a password. In version 11, the Oracle RDBMS deprecated this parameter but have retained it (for now) for backward compatibility. The example below illustrates this. I ran it on a UNIX server as a UNIX user called oracle. First I connected to the database as SYS and set remote_os_authent to true in the server parameter file:
 
SQL> conn / as sysdba
Connected.
SQL> alter system set
  2  remote_os_authent = true
  3  scope = spfile
  4  /
 
System altered.
 
SQL>
 
Then I bounced the database. The Oracle RDBMS displayed an error message when it saw the deprecated parameter:
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
 
Total System Global Area  158703616 bytes
Fixed Size                  2086736 bytes
Variable Size              83888304 bytes
Database Buffers           67108864 bytes
Redo Buffers                5619712 bytes
Database mounted.
Database opened.
SQL>
 
I reconnected to the database remotely and reset remote_os_authent in the server parameter file:
 
SQL> conn /@test11
Connected.
SQL> show user
USER is "ORACLE"
SQL> alter system reset remote_os_authent
  2  scope = spfile
  3  /
 
System altered.
 
SQL>
 
Then I bounced the database again. This time there was no error message:
 
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  158703616 bytes
Fixed Size                  2086736 bytes
Variable Size              88082608 bytes
Database Buffers           62914560 bytes
Redo Buffers                5619712 bytes
Database mounted.
Database opened.
SQL>
 
Changing the remote_os_authent parameter stopped the remote connection working:
 
SQL> conn /@test11
ERROR:
ORA-01017: invalid username/password; logon denied
 
Warning: You are no longer connected to ORACLE.
SQL>