Check if unified auditing is enabled:
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
Check what options are enabled:
SELECT up.AUDIT_OPTION, uep.SUCCESS, uep.FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES uep, AUDIT_UNIFIED_POLICIES up where uep.ENTITY_NAME = 'ALL USERS' and uep.ENABLED_OPTION='BY USER' and uep.POLICY_NAME = up.POLICY_NAME and up.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
To set up an Oracle Database for development I install the database in a docker container and then modify password policies in the default user profile as follows:
ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT UNLIMITED PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITED;
this prevents passwords from expiring and users from being locked.
(more…)First I installed Oracle 19c in Docker container , enabled unified auditing, and created multiple pluggable databases:
export ORACLE_SID=ORCLCDB cd $ORACLE_HOME/bin #connect to root container ./sqlplus / as sysdba
CREATE PLUGGABLE DATABASE testpdb1 ADMIN USER admin1 IDENTIFIED BY dbpasswd1
FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed', '/opt/oracle/oradata/ORCLCDB/testpdb1');
Pluggable database created.
CREATE PLUGGABLE DATABASE testpdb2 ADMIN USER admin2 IDENTIFIED BY dbpasswd2
FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed', '/opt/oracle/oradata/ORCLCDB/testpdb2');
Pluggable database created.(more…)
First I installed Ubuntu Server 18.04 as a Hyper-V machine on Windows 10.
While creating the virtual machine I selected Generation 1, because Generation 2 resulted in some strange effects.
Then I created a separate user with sudo permission:
sudo useradd -d /home/singh -m --shell "/bin/bash" singh sudo passwd singh sudo usermod -aG sudo singh(more…)
I configured DNS on my Windows Server 2016 (takes some time, probably 15 minutes):
ipconfig /registerdns
Below I provided the results of two following queries:
SELECT USERNAME, USER_ID FROM DBA_USERS ORDER BY USER_ID DESC; SELECT ROLE, ROLE_ID FROM DBA_ROLES ORDER BY ROLE_ID DESC;
that display Oracle Database 18 users, roles and their IDs:
(more…)Download binaries (redistributables) and C++ SDK from Oracle website. For 64bit platform they are respectively:
instantclient-basic-windows.x64-18.5.0.0.0dbru.zip instantclient-sdk-windows.x64-18.5.0.0.0dbru.zip
Extract them, create a C++ project in VS2017 and link oraocci18.lib in Release configuration and oraocci18d.lib in Debug configuration.
(more…)While investigating of how to get all the privileges of an Oracle database user I played with the following queries:
select COUNT(*) from DICTIONARY; select USERNAME from SYS.ALL_USERS; select * from DBA_USERS; select * from DBA_ROLES; select * from DBA_SYS_PRIVS where grantee = 'CONNECT'; select * from DBA_SYS_PRIVS where grantee = 'RESOURCE'; select * from DBA_SYS_PRIVS; select * from SESSION_PRIVS; select * from DBA_TAB_PRIVS where GRANTEE <> 'PUBLIC'; select count(*) from DBA_TAB_PRIVS; select * from DBA_COL_PRIVS; select * from ROLE_TAB_PRIVS;(more…)
I installed sample Oracle database schemas with the following steps:
Cloned the repository, changed the directories in the installation script, and started my docker container with the mapped folder:
sudo su - oracle mkdir repos cd repos git clone https://github.com/oracle/db-sample-schemas.git cd db-sample-schemas perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat exit sudo docker run -d -it --rm --name oracle18se -v /home/oracle/oradata18:/opt/oracle/oradata -v /home/oracle/repos:/home/oracle/repos -p 1521:1521 -p 5500:5500 oracle/database-se:18.3.0 sudo docker logs oracle18se(more…)