dbawiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
  *  [[User Profiles]]
  *  [[Random password generator]]
  *  [[http://www.oracle.com/technetwork/database/enterprise-edition/calling-shell-commands-from-plsql-1-1-129519.pdf|Allow running of OS / shell commands from within SQL and PL/SQL]]
  *  [[https://oracle-base.com/articles/10g/secure-external-password-store-10gr2|Store Oracle user (schema) passwords in a wallet instead of hard-coding them in shell scripts! - oracle-base.com]]

==== Improve security by accessing databases from Unix with wallets ====
  8 [[https://mikesmithers.wordpress.com/2022/04/04/turtles-all-the-way-removing-clear-text-passwords-from-bash-scripts-invoking-sqlplus/|Removing Clear Text passwords from bash scripts invoking SQL*Plus - Mike Smithers]]

Briefly...

Wallet Setup
<code>
mkdir tns_admin
mkdir wallet
chmod 700 tns_admin
chmod 700 wallet
</code>
Create a wallet
<code>
mkstore -create -wrl /home/atuin/wallet
</code>
Create a local sqlnet.ora file
<code>
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
 
SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/atuin/wallet)))

ifile=/u01/app/oracle/product/19.29/dbhome_1/network/admin/sqlnet.ora
</code>
Same again for the tnsnames.ora file
<code>
hr_on_eta =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = didactylos.virtualbox)(PORT = 1522))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = ETA)
      )
    )

ifile=/u01/app/oracle/product/19.29/dbhome_1/network/admin/tnsnames.ora
</code>
Protect them
<code>
chmod 600 *ora
</code>
Add a credential to the wallet with th esame name as the connect identifier in the tnsnames file
<code>
mkstore -wrl /home/atuin/wallet -createCredential hr_on_eta hr <password_for_hr>
</code>
Test it
<code>
export TNS_ADMIN=/home/atuin/tns_admin
sqlplus /@hr_on_eta
select sys_context('userenv', 'con_name') db_name,
    sys_context('userenv', 'current_user') db_user
from dual;
</code>

==== Show users with elevated privileges ====
<code>
col grantee   for a40
col privilege for a85 wrap
select grantee
,      privilege
,      admin_option
from   sys.dba_sys_privs
where  (
       privilege like '% ANY %'
    or privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
    or admin_option = 'YES'
       )
and    grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE', 'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE', 'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS', 'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS', 'TIMESERIES_DBA','WMSYS','SCHEDULER_ADMIN','SYSBACKUP' )
order  by grantee
/
</code>