Differences

This shows you the differences between two versions of the page.

--- security [2018/12/08 12:49] – created 0.0.0.0
+++ security [2025/12/21 15:25] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Security ======
+  *  [[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 ====
+[[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>
-  * [[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]]
-=====Show users with elevated privileges=====
-<code>0@@</code>