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.
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
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;
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