Posts

Showing posts with the label Oracle Security

Simple password encryption package to demonstrate how

rem ----------------------------------------------------------------------- rem Purpose:   Simple password encryption package to demonstrate how rem                  values can be encrypted and decrypted using Oracle's rem                  DBMS Obfuscation Toolkit rem Note:        Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql rem Author:     Frank Naude, Oracle FAQ rem ----------------------------------------------------------------------- ---- create table to store encrypted data -- Unable to render TABLE DDL for object ATOORPU.USERS_INFO with DBMS_METADATA attempting internal generator. CREATE TABLE USERS_INFO (   USERNAME VARCHAR2(20 BYTE) , PASS VARCHAR2(20 BYTE) )users; ----------------------------------------------------------------------- ----------------------------------------------------------------------- CREATE OR REPLACE PACKAGE PASS...
Post a Comment
Read more

How to Secure our Oracle Databases

How Secure can we make our Oracle Databases?? This is a routine question that runs in minds of most database administrators.   HOW SECURE ARE OUR DATABASES. CAN WE MAKE IT ANYMORE SECURE . I am writing this post to share my experience and knowledge on securing databases. I personally follow below tips to secure my databases:  1. Make sure we only grant access to those users that really need to access database. 2. Remove all the unnecessary grants/privileges from users/roles. 3. Frequently audit database users Failed Logins in order to verify who is trying to login and their actions. 4. If a user is requesting elevated privileges, make sure you talk to them and understand their requirements. 5. Grant no more access than what needed. 6. At times users might need access temporarily. Make sure these temporary access are revoked after tasks are completed. 7. Define a fine boundary on who can access what?? 8. Use User profiles / Audit to ensure all activities are tracked. 9....
Post a Comment
Read more

Using special characters in ORACLE DB passwords

Using special characters in ORACLE DB passwords   Using special characters in ORACLE DB passwords. Most times we restrict our selves from using complicated special characters as we think that we can't use them in passwords, but in oracle DB world we are allowed to use most special characters as passwords. See this simple example to see how to use special char's in passwords and login. To log into DB using cmdline we need to use single quote ''. else oracle some times doesn't recognize the password. See below example : I created a user with password using special characters. SQL> alter user C##atest identified by "atest113.."; User C##ATEST altered. [oracle@Linux03 admin]$ sqlplus C##atest/atest113.. SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:39:56 2016 Copyright (c) 1982, 2014, Oracle.  All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Solution to this is to use single quote: [oracle@Linux03 admin]$ sqlplus ...
Post a Comment
Read more

configure complex password (password verify function) in oracle database

  configure password verify function in oracle database We can enable the oracle provided "password verify function" to enforce strong password restrictions for our DB users. This function with other profile parameters can create a strong security for the database.To enable the oracle password verification function you need to execute the utlpwdmg.sql file from ORACLE_HOME/rdbms/admin as sysdba. [oracle@Linux03 home]$ cd /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/ [oracle@Linux03 admin]$ ls utlpwdmg.sql [oracle@Linux03 admin]$ sqlplus / as sysdba SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql Function created. Grant succeeded. Function created. Grant succeeded. Function created. Grant succeeded. Profile altered. Note : You can query current default profile settings using as below select * from DBA_PROFILES where profile='DEFAULT'; Starting from 12c this file (utlpw...
2 comments
Read more

configure complex password (password verify function) in oracle database

  configure password verify function in oracle database We can enable the oracle provided "password verify function" to enforce strong password restrictions for our DB users. This function with other profile parameters can create a strong security for the database.To enable the oracle password verification function you need to execute the utlpwdmg.sql file from ORACLE_HOME/rdbms/admin as sysdba.   [oracle@Linux03 home]$ cd /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/ [oracle@Linux03 admin]$ ls utlpwdmg.sql [oracle@Linux03 admin]$ sqlplus / as sysdba SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql SQL> @/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utlpwdmg.sql Function created. Grant succeeded. Function created. Grant succeeded. Function created. Grant succeeded. Profile altered. Note : You can query current default profile settings using as below select * from DBA_PROFILES where profile='DEFAULT'; Starting from 12c this file (utl...
Post a Comment
Read more

TNS-00583: Valid node checking: unable to parse configuration parameters

 TNS-12560: TNS:protocol adapter error  TNS-00583: Valid node checking: unable to parse configuration parameters I  Received following errors when trying to startup my listener. I verified the listener.ora and sqlnet.ora files and everything seemed to look normal.   [oracle@Linux03 encryption_keystore]$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:05:32 Copyright (c) 1991, 2014, Oracle.  All rights reserved. Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521))) TNS-12560: TNS:protocol adapter error  TNS-00583: Valid node checking...
3 comments
Read more

Enable Database Auditing in oracle

ENABLE AUDITING IN ORACLE DATABASE SERVER SETUP FOR DB AUDITING Auditing is a default feature available in Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command. SQL> SHOW PARAMETER AUDIT NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN                                ...
Post a Comment
Read more

Enable auditing in oracle database

ENABLE AUDITING IN ORACLE DATABASE SERVER SETUP FOR DB AUDITING Auditing is a default feature available in Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command. SQL> SHOW PARAMETER AUDIT NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN                                ...
Post a Comment
Read more

select grants on tables to users or roles

select grants on tables to users or roles SELECT   OWNER,   GRANTEE,   GRANTOR,   TABLE_NAME,   PRIVILEGE FROM   DBA_TAB_PRIVS WHERE   table_name   = 'TABLENAME' AND PRIVILEGE IN ('DELETE','INSERT','SELECT','UPDATE') AND GRANTEE   IN ('ROLE1','ROLE2') ORDER BY   1,2; select grants on table to a user thru role or direct priv select Grantee,'Granted Through Role' as Grant_Type, role, table_name from role_tab_privs rtp, dba_role_privs drp where rtp.role = drp.granted_role and table_name = 'TABLENAME' union select Grantee,'Direct Grant' as Grant_type, null as role, table_name from dba_tab_privs where table_name = 'TABLENAME' ;
Post a Comment
Read more

Restricting database access to IP addeess using sqlnet.ora

**************************************************************                       FROM MY CLIENT MACHINE: ************************************************************** C:\Windows\System32>tnsping pdborcl1 TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:20 Copyright (c) 1997, 2010, Oracle.  All rights reserved. Used parameter files: C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo cl1))) OK (0 msec) ************************************************************** NOW I want my DB to be only be accessible from this machine and restrict all other. For this I will need to make changed to my DB server sqlnet.ora : ************************************************************** ON...
Post a Comment
Read more