In daily routine support, we very often heads from users that they don’t have access to specific tables or whatever database objects.
After investigation and proper setting afterward, as DBA, we need to test before we get back to users.
To do that, we can do followings.
1. Use “Connect Through” role
SQL> alter user enduser grant connect through dba; User altered. SQL> connect dba[enduser]@prod Enter password: Connected. SQL> show user USER is "ENDUSER"
2. create proxy user in OEM
Recently, I need to transfer my emails from Mozilla Thunderbird to Microsoft Outlook. After research, I found the following helpful solution.
Moving Messages from Thunderbird to Outlook Express and Outlook
The general steps for this migration can be summarized as below.
The above steps requires two senarios:
To export emails from Thunderbird, the best tool I tried is the Thunderbird add-on “ImportExportTools 2.8.0.1”, which is free totally.
With this add-on installed, the emails/messages in Thunderbird could be easily exported in *.eml format.
Once all emails/messages exported, just simply follow step 4 and 5 in article Moving Messages from Thunderbird to Outlook Express and Outlook .
Again, I successfully got this done without any cost. If you ever searched for this topic, you’ll find most of tools in market are commercial ones and you need to pay for them.
It doesn’t matter you like or not, sometimes, DBA/admin need to deal with automated unix shell scripts which require database access. The straightforward way for that is to create text file with oracle username along with hard coded password. It’s working but absolutely not a secure approach.
To eliminate this security vulnerability, we can use oracle wallet to keep database credential safely for such shell scripts running.
To implement it, this article published at askdba.org is certain guide for setting up oracle wallet for that specific purpose. In general, the steps could be summarized as below.
1. Create a Oracle Wallet
$mkstore -wrl $ORACLE_HOME/admin/wallet -create Enter wallet password:
2. Create a tnsnames entry
ORCL_TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBSERVER)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
3. Add database credentials to Oracle Wallet
$mkstore -wrl $ORACLE_HOME/admin/wallet -createCredential ORCL_TEST username password
4. Add wallet entry in client sqlnet.ora file
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = $ORACLE_HOME/admin/wallet) ) ) SQLNET.WALLET_OVERRIDE = TRUE
5. Ensure that auto-login is enabled for wallet
That’s all we need to do and more details could be found in this article.
With completion of above steps, you are able to use tnsname created in step 2 to connect database without hard coded database password. That’s great. But, wait a minute, I experienced further problems while I implement it in our testing database server.
The problem we firstly noticed is the error below we got while a oracle job called a unix shell script, in which sqlplus ‘/ as sysdba’ is used to connect oracle database.
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 26 11:52:01 2013
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12534: TNS:operation not supported
Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER|SYSASM}]
where <logon> ::= <username>[/<password>][@<connect_identifier>] [edition=value] | /
Enter user-name: Enter password:
ERROR:
ORA-01005: null password given; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
Briefly searched this oracle error “ORA-12534: TNS:operation not supported”, I found two related articles on Oracle Support website. But, there are relating to ENCRYPTION WALLET and Dbconsole Repository, not my case.
Further research brought out another article “The Impact of the Sqlnet Settings on Database Security (sqlnet.ora Security Parameters and Wallet Location) [ID 1240824.1]“. The #9 scenario “Avoid common sqlnet.ora errors” mentioned the follows:
+ whatever is aligned to the left is considered as a new parameter entry.
+ whatever has at least a space to the left is considered as previous line continuation.
As a result, 2 main problems would appear
1. the parameters are ignored if leaving at least a space at the left.
2. the multiple lines parameters are misinterpreted if second line or higher are left aligned.
Problems with such settings appear mostly with the WALLET_LOCATION parameter. In the example below:WALLET_LOCATION = (SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = <some directory>)))Because the second and third line are left aligned, the wallet location is ignored.
When in doubt, set everything on one line:WALLET_LOCATION = (SOURCE = (METHOD = FILE)(METHOD_DATA =(DIRECTORY = <some directory>)))
The recommended method is to set the sqlnet parameters only using the netmgr, whenever possible.
And then, I tried to change the wallet location entry, created in step 4, in sqlnet.ora to one line. However, the problem has not been resolved.
I then recalled that the unix account we use is not sitting in dba group. And, I also would like to give that user access to wallet file in step 1.
$ useradd -G staff,dba banjobs $ chmod ur+rw $ORACLE_HOME/admin/wallet/*.*
Additionally, I removed every single space (blank) for wallet entry in file sqlnet.ora. Eventually, my works were paid back. Both tns login and “sqlplus /” are all working properly.
At this moment, I can’t tell if adding user to secondary group and change permission of wallet files are necessary for that. I’ll then test it when I implement it on another server.