Tuesday, April 14, 2009

SYS.LINK$ Shows all Database Link Passwords unencrypted

From Metalink Note 202987.1:

Problem Description:
Database link passwords are stored as plain text.
A database link is a mechanism used toprovide a method of transparently accessing one server from another.
When creating a database link, a user name and password of the account on the remote server can be specified. Creating the database link without credentials works only if the user exists on both databases and has the same password.
Once this is done, all queries using the link have the privilege of the indicated account on the remote server. By omitting an account and password when creating a database link, the account and password of the user connecting through the link is used. Indicating the username and password of an account to use for all connections through a link can lead to passwords being exposed.
Database link passwords until recently (version 10gR1) were stored unencrypted in the database. Users with SELECT privilege on the SYS.LINK$ table could view the passwords in plain text. Setting up links to authenticate as the current user prevents unencrypted passwords from being exposed, prevents linked servers from being compromised, and provides increased accountability. Oracle accounts were found with permission to view the table SYS.LINK$. Access to view the table SYS.LINK$ should be restricted because database link passwords are stored unencrypted in this table.

Possible Symptoms:
If you have SELECT ANY TABLE privilege on a database, you can see the password of the user that can belong to a remote or local database(s) in the SYS.LINK$ table and using this password, you can connect these remote or local databases at will.
We rely on sys to protect link$. If customers don't trust a DBA, there are many things the DBA can do that make any encryption attempt useless.

Important change in the SELECT ANY DICTIONARY system privilege:
In Oracle release 10gR1, the access to SYS.LINK$ was removed from the SELECT ANY DICTIONARY system privilege (hence the ORA-1031 error), while this still doesn't solve the general problem: tools such as Oracle Enterprise Manager that depend on SELECT ANY DICTIONARY to be available can be deployed without access to SYS.LINK$.

There are no workarounds to protect against this potential vulnerability but it is possible to use this:

Drop the database link and create a link without specifying an account and passwords.
To drop a database link, execute the command:
SQL> drop database link [link name];

To re-create a link without hard coding the password, execute the command:
SQL> create database link using [connection string];

To revoke permissions from the account or role, execute the following command:
SQL> revoke select on SYS.LINK$ from [account or role] ;

Currently there is not a patched Installer available to deal with this problem.
One of the workarounds listed above must be used.
It is no more the case under version 10g Release 2 (10.2.0.x), the LINK$ table now contains a new column PASSWORDX that contains the encrypted database link password.
Details of the encryption scheme will not be disclosed for obvious reasons.

Friday, April 3, 2009

How to connect as a user for a short time, without knowing the password.

Let's say you want, temporarily, to connect to your database as a user, whose password you don't know, and, perhaps, you don't want to find it out.
Furthermore, when you've finished, the user should be able to connect using the same password.
Each user's password is encrypted and stored in the database in the SYS.USER$ table, under PASSWORD column and in 11g databases this is the only place you can find it, since it was removed from DBA_USERS view [Oracle note 735651.1].
You may query the DBA_USERS to get it:

Now, change user's password, so you may connect to the database using that account:
User altered.


You've finished and it's time to restore the password, so:
User altered.

ORA-01017: invalid username/password; logon denied

Thursday, April 2, 2009

Viewing Concurrent Requests' output on external viewer stopped working

In e-BS, you have the option to view Reports' output using an external viewer, depending on the output's format.
I got a call saying suddenly users could not view a few Excel Reports. A IE was spawn, but instead of displaying the output, it closed after a few moments.
Nothing was changed in Profile Values and Viewer Options, server side. The funny thing was that it was working for some of my colleagues. The only difference was that they had OS Windows XP SP2. Indeed, users facing the problem had SP3 installed. So, obviously, SP level was the cause.
Since I did not find anything relevant in Metalink, I realized it was one of those cases, where you have to face Microsoft's and Oracle's existential quests, which make our lives so much easier and simpler.
So, I wondered why someone should install a newer SP, according to Microsoft? To make your OS "safer", of course, among other things. After realizing this tremendous fact, I decided to check IE's Security Settings. Since we are facing a download problem, let's view the Downloads Section. "Automatic prompting for file downloads" is disabled?
That looked suspicious. In SP2 its default value is enabled.
So, I enable the option, and problem solved...