May 9
wallet not open after restart the database
Causes:
Certificates uploaded in TDE wallet
Solution :
delete the certificates and open the wallet and then again import the certificates
Wallet cannot be open after restart the Database – ORA-28365: wallet is not open (Doc ID 2903167.1)
Oct 7
The Oracle Database Security Assessment Tool (DBSAT) analyzes database configurations, users, their entitlements, security policies and identifies where sensitive data resides to uncover security risks and improve the security posture of Oracle Databases within your organization.
You can use DBSAT report findings to:
Download
Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)
Installation and test
[orauat@ebsuat dbsat]$ unzip dbsat.zip
Check python installed or not
[orauat@ebsuat dbsat]$ python -V
Python 2.7.6
you should install latest version 2.6 or later
Collect :
[orauat@ebsuat dbsat]$ ./dbsat collect system@EBSUAT EBSUAT_OUTPUT
Database Security Assessment Tool version 2.0.2 (May 2018)
This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company’s policies.
Connecting to the target Oracle database…
SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 7 14:39:12 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Setup complete.
SQL queries complete.
OPatch cannot find a valid oraInst.loc file to locate Central Inventory.
Warning: Exit status 26624 from OS rule: opatch_inventory
OS commands complete.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
DBSAT Collector completed successfully.
Calling /u01/EBSUAT/db/tech_st/11.2.0/bin/zip to encrypt EBSUAT_OUTPUT.json…
Enter password:
Verify password:
adding: EBSUAT_OUTPUT.json (deflated 90%)
zip completed successfully.
Report
[orauat@ebsuat dbsat]$ ./dbsat report EBSUAT_OUTPUT
Database Security Assessment Tool version 2.0.2 (May 2018)
This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company’s policies.
Archive: EBSUAT_OUTPUT.zip
[EBSUAT_OUTPUT.zip] EBSUAT_OUTPUT.json password:
inflating: EBSUAT_OUTPUT.json
Traceback (most recent call last):
File “/u01/EBSUAT/dbsat/./sat_reporter.py”, line 6372, in <module>
fn()
File “/u01/EBSUAT/dbsat/./sat_reporter.py”, line 444, in user_section
user_login(profile_dict, acct_profiles)
File “/u01/EBSUAT/dbsat/./sat_reporter.py”, line 1036, in user_login
profile_unset(profiles, users, ‘PASSWORD_LOCK_TIME’, (‘day’, ‘days’))
File “/u01/EBSUAT/dbsat/./sat_reporter.py”, line 1157, in profile_unset
value = sing_plural(int(value), units[0], units[1])
ValueError: invalid literal for int() with base 10: ‘.0416’
Traceback (most recent call last):
File “/u01/EBSUAT/dbsat/./sat_reporter.py”, line 6378, in <module>
sat.end_report()
File “/u01/EBSUAT/dbsat/sat_analysis.py”, line 625, in end_report
xls_book.close()
File “/u01/EBSUAT/dbsat/xlsxwriter/workbook.py”, line 310, in close
self._store_workbook()
File “/u01/EBSUAT/dbsat/xlsxwriter/workbook.py”, line 624, in _store_workbook
allowZip64=self.allow_zip64)
File “/usr/local/lib/python2.7/zipfile.py”, line 732, in __init__
“Compression requires the (missing) zlib module”
RuntimeError: Compression requires the (missing) zlib module
Error: Unexpected error occurred while running DBSAT Reporter.
(The above error occurred when i try to take the report of which i collected json file, But i am not sure why the above error occurred, But the report output created. I need to check this error with oracle support).
Refer :
https://docs.oracle.com/cd/E93129_01/
Normal export exp does not support if table column encrypted using TDE method. However datapump is support.
Lets see example
Step 1:
Step 2:
Step 3:
Step 4:
Mar 25
This is another security method, you can able to connect the particular user data from client side without supplying password using wallet.
“Password credentials for connecting to databases can now be stored in a client-side Oracle wallet, a secure software container used to store authentication and signing credentials.
This wallet usage can simplify large-scale deployments that rely on password credentials for connecting to databases. When this feature is configured, application code, batch jobs, and scripts no longer need embedded user names and passwords. Risk is reduced because such passwords are no longer exposed in the clear, and password management policies are more easily enforced without changing application code whenever user names or passwords change.”
Let See Example :
Source Host :
Step 1:
SQL> grant dba to scott; Grant succeeded. SQL> conn scott/tiger Connected. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE TEST TABLE SQL> select name from v$database; NAME --------- TESTDB SQL> select host_name from v$instance; HOST_NAME ---------------------------------------------------------------- netbackuptest SQL>
Client Host :
Step 2:
Create a wallet on the client using following syntex
[oracle@vcdb01 ~]$ mkdir -p /u01/app/oracle/product/wallet [oracle@vcdb01 ~]$ mkstore -wrl /u01/app/oracle/product/wallet/ -create Oracle Secret Store Tool : Version 11.2.0.1.0 - Production Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved. Enter password: Enter password again:
Step 3: Add source tns entries in client tnsnames.ora file
testdb = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =netbackuptest)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb) ) )
Step 4: Create database connection credentials in the wallet by using the following syntax at the command line
[oracle@vcdb01 ~]$ mkstore -wrl /u01/app/oracle/product/wallet/ -createCredential testdb scott tiger Oracle Secret Store Tool : Version 11.2.0.1.0 - Production Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Here : testdb --> tns alias name scott --> user name tiger --> password
Step 5:
[oracle@vcdb01 ~]$ mkstore -wrl /u01/app/oracle/product/wallet/ -listCredential Oracle Secret Store Tool : Version 11.2.0.1.0 - Production Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 1: testdb scott
Step 6: Add the following entries in sqlnet.ora
WALLET_LOCATION=(SOURCE=(METHOD=FILE) (METHOD_DATA = (DIRECTORY =/u01/app/oracle/product/wallet))) SQLNET.WALLET_OVERRIDE = TRUE
Note: For clients not using such authentication methods or wanting to override them for database authentication, a new parameter (SQLNET.WALLET_OVERRIDE) in sqlnet.ora can be set to TRUE. The default value forSQLNET.WALLET_OVERRIDE is FALSE, allowing standard use of authentication credentials as before.
Step 7: connect scott using without supplying password from client machine using wallet
[oracle@vcdb01 ~]$ sqlplus /@testdb SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 25 10:27:36 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$database; NAME --------- TESTDB SQL> select host_name from v$instance; HOST_NAME ---------------------------------------------------------------- netbackuptest SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE TEST TABLE SQL>
Step 8: Modify credential
Suppose to be, If DBA changed the password of scott user, can i able to connect from client machine using same wallet credential stored? No, you need to modify using the command line.
[oracle@vcdb01 ~]$ sqlplus /@testdb SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 25 10:29:35 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied [oracle@vcdb01 ~]$ mkstore -wrl /u01/app/oracle/product/wallet/ -modifyCredential testdb scott test Oracle Secret Store Tool : Version 11.2.0.1.0 - Production Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Modify credential Modify 1 [oracle@vcdb01 ~]$ sqlplus /@testdb SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 25 10:31:46 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
Ref :Oracle Document
Here I just showed how do we create new wallet using orapki utility or OWM ( Oracle Wallet Manager).
Step 1 : Login as oracle user, set the oracle_home path and make new directory for wallet stored.
[oracle@netbackuptest ~]$ export ORACLE_SID=testdb
[oracle@netbackuptest ~]$cd $ORACLE_HOME
[oracle@netbackuptest dbhome_2]$ mkdir -p wallets
Step 2: Create Wallete using orapki
[oracle@netbackuptest dbhome_2]$ orapki wallet create -wallet $ORACLE_HOME/wallets Oracle PKI Tool : Version 11.2.0.2.0 - Production Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved. Enter password: Enter password again: Note : PASSWORD_POLICY : Passwords must have minimum length of eight characters and contain alphabetic characters combined with numbers or special characters. [oracle@netbackuptest dbhome_2]$ ls -l wallets/ total 4 -rw------- 1 oracle oinstall 3512 Mar 20 04:07 ewallet.p12 to display the contents of a wallet [oracle@netbackuptest dbhome_2]$ orapki wallet display -wallet $ORACLE_HOME/wallets Oracle PKI Tool : Version 11.2.0.2.0 - Production Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Requested Certificates: User Certificates: Trusted Certificates: Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Using OWM :
Set Oracle home path and execute owm command
Open a existing wallet
Choose open and select a directory and put it a existing wallet password and then you can see the wallet information.
If you want to create a new wallet using OWM, you can choose new panel from left side panel and start to create wallet
Listener local os authentication parameter control the start or stop listener services through remotely.
Let see example,
Step 1: Source Host Listener status, here local os authentication is ON.
Step 2: set the new password for listener and save configuration
Step 3: look listener.ora file whether password file is stored or not.
Step 4: Go to remote server, remote Host :
Add following entries in tnsnames.ora file in target host
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
Step 5: reload the source listener from remote host
Set the password at remote host and then reload listener
Step 6: Go to source host, If I OFF the local os autentication, Can I able to reload/stop/start from remote host? No
go to listener.ora file and add the following entires
LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=netbackuptest.ace-ins.com)(PORT=1521)))
The command completed successfully
Step 7 : Go to remote host, reload/stop/start source listener
Step 8 : How do i remove password protection from listener.ora
remove the password listener entries from listener.ora file, though If you don’t want to protect local os authentication also, you set local_os_authentication_listenername=off
and after go to listener utility , reload the listener
Check the status of security off
PRODUCT_USER_PROFILE table to provide product level security that supplements the user-level security provided by the SQL GRANT andREVOKE commands and user roles.
This table in System account. We can insert the product based data here which user roles, grant and revoke command you want to restrict.
This table does not apply for SYSDBA or SYSOPER privileges.
The PUP table applies only to the local database. If accessing objects on a remote database through a database link, the PUP table for the remote database does not apply. The remote database cannot extract the username and password from the database link in order to determine that user’s profile and privileges.
More Detail from here : Oracle Document
Here I’m going to create user, roles and create table for this user.
SQL> conn / as sysdba
Connected.
SQL> create user fsmith identified by fsmith;
User created.
SQL> grant create session to fsmith;
Grant succeeded.
SQL> create role role2;
Role created.
SQL> grant create table to role2;
Grant succeeded.
SQL> grant role2 to fsmith;
Grant succeeded.
SQL> conn fsmith/fsmith
Connected.
SQL> create table emp(empno number,empname varchar2(20));
Table created.
Now I insert into product_user_profile table for product level security like roles and after create table for
this user, It has no privileges for create table because of the roles has disabled in product_user_profile table.
SQL> conn system/Admin123
Connected.
SQL> desc product_user_profile;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCT NOT NULL VARCHAR2(30)
USERID VARCHAR2(30)
ATTRIBUTE VARCHAR2(240)
SCOPE VARCHAR2(240)
NUMERIC_VALUE NUMBER(15,2)
CHAR_VALUE VARCHAR2(240)
DATE_VALUE DATE
LONG_VALUE LONG
SQL> insert into product_user_profile values('SQL*Plus','FSMITH','ROLES',NULL,NULL,'ROLE2',NULL,NULL);
1 row created.
SQL> commit;
Commit complete.
SQL> conn fsmith/fsmith
Connected.
SQL> create table role(role_name varchar2(20));
create table role(role_name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges
“If you’re granted select any table to a user when O7_DICTIONARY_ACCESSIBILITY is set to TRUE, the particular user can able to see the data dictionary tables.”
O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in “any schema” do not allow access to objects in the SYS schema.
For example, if O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT ANY TABLE privilege allows access to views or tables in any schema except the SYS schema (data dictionary tables cannot be accessed). The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except the SYS schema.
If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects:
SELECT_CATALOG_ROLEEXECUTE_CATALOG_ROLEDELETE_CATALOG_ROLEExample :
SQL> grant select any table to scott; Grant succeeded. SQL> show parameter O7_DICTIONARY_ACCESSIBILITY; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean FALSE SQL> conn scott/tiger Connected. SQL> select * from dba_users; select * from dba_users * ERROR at line 1: ORA-00942: table or view does not exist SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2226912 bytes Variable Size 687867168 bytes Database Buffers 905969664 bytes Redo Buffers 7348224 bytes Database mounted. Database opened. SQL> show parameter O7_DICTIONARY_ACCESSIBILITY; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean TRUE SQL> conn scott/tiger Connected. SQL> select count(username) from dba_users; COUNT(USERNAME) --------------- 45
Ref : Oracle Document
Feb 28
The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements.
Example :
SQL> create user employees identified by employees; User created. SQL> grant connect,resource to employees; Grant succeeded. SQL> conn employees/employees; Connected. SQL> create table empl_object as select * from all_objects; Table created. SQL> select count(*) from empl_object; COUNT(*) ---------- 55877 SQL> conn / as sysdba Connected. SQL> create user usera identified by usera; User created. SQL> grant connect to usera; Grant succeeded. SQL> create user userb identified by userb; User created. SQL> grant connect to userb; Grant succeeded. SQL> conn employees/employees; Connected. SQL> grant update,select on empl_object to usera; Grant succeeded. SQL> conn usera/usera Connected. SQL> update employees.empl_object set owner='test' where owner='SYS'; 23687 rows updated. SQL> COMMIT; Commit complete. SQL> conn userb/userb Connected. SQL> update employees.empl_object set owner='SYS' where owner='test'; 23687 rows updated. SQL> commit; Commit complete. Enable sql92_security parameter. SQL> show parameter sql92_security; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql92_security boolean FALSE SQL> alter system set sql92_security=TRUE scope=SPFILE; System altered. SQL> show parameter sql92_security; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql92_security boolean FALSE SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2226912 bytes Variable Size 687867168 bytes Database Buffers 905969664 bytes Redo Buffers 7348224 bytes Database mounted. Database opened. SQL> show parameter sql92_security; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql92_security boolean TRUE SQL> conn usera/usera Connected. SQL> update employees.empl_object set owner='test' where owner='SYS'; 23687 rows updated. SQL> commit; Commit complete. Now you can not update the table.SQL> conn userb/userb Connected. SQL> update employees.empl_object set owner='SYS' where owner='test'; update employees.empl_object set owner='test' where owner='SYS' * ERROR at line 1: ORA-01031: insufficient privilegesSQL>
Just read more detail from This blog
Feb 27
When you configuring the password for a user in database, it automatically calculating the password hash normally or bypassing the hash process and writing a value to the PASSWORD column of sys.user$.
Just a exmple :
SQL> create user imppassword identified by imppassword; User created. SQL> select name,password from sys.user$ where name='IMPPASSWORD'; NAME PASSWORD ------------------------------ ------------------------------ IMPPASSWORD 27D780816A86DC5C
When this user provides their credentials to Oracle to log in, Oracle will take the credentials, create the password hash, and then compare it with the value stored in SYS.USER$, find a match, and allow the user to connect.
SQL> grant connect,resource to imppassword; Grant succeeded. SQL> conn imppassword/imppassword; Connected.
If we created the user and explicitly set the password value, things would be different.
SQL> create user password identified by values 'password'; User created. SQL> grant connect,resource to password; Grant succeeded. SQL> select name,password from sys.user$ where name = 'PASSWORD'; NAME PASSWORD ------------------------------ ------------------------------ PASSWORD password
This time the value stored is the text string PASSWORD.This is an impossible password, because regardless of the input, the Oracle password hashing algorithm could never produce output that matches. Now you can not able to log in this user. Its called impossible password.
Why it need for implement?
If you would like to lock the user account, The attacker tries to login the these user account it shows the user account is locked , they may get a good information from this error. Locking default accounts tells an attacker what schemas and therefore what features are installed on a given database.This allows them to create a plan of attack before they ever penetrate the database, building a list of potential exploits with the benefit of knowing which potentially vulnerable features are installed.
SQL> conn scott/tiger ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ' ORACLE. SQL>
Furthermore, the attacker now knows the password for each account is set to the default value! Setting an impossible password and unlocking the accounts prevents an attacker from logging in, but also provides no information about the existence of each account/schema or their password.
SQL> conn password/password ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.
With impossible passwords, an attacker gets no information from an attempt to log in to the database with each default account and password. Less information is always better and it’s our responsibility to make a hacker’s job as difficult as possible.
Database Administrator, Blogger, Contributor
 | Altaf on Disaster Recovery:Restore data… |
 | Calvin on Flashback Data Archive |
 | vaibhav hemanshu kan… on ORA-28056: Writing audit recor… |
| Wallet open failed on How to Configure Clients to Us… | |
 | Abdulwajid on Error: loading jvm.dll while i… |
DBA 