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:

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.

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

In Oracle 19c the following fields of AUDIT_UNIFIED_ENABLED_POLICIES table were renamed:

• ENABLED_OPT renamed with ENABLED_OPTION
• USER_NAME renamed with ENTITY_NAME

So the audit options query in Oracle 19c looks like this:

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 enable Unified Auditing in Oracle Database 12 and 18 on Oracle Linux I did this:

export ORACLE_SID=ORCLCDB
cd $ORACLE_HOME/bin
./sqlplus sys as sysdba
SHUTDOWN IMMEDIATE
EXIT
./lsnrctl stop
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
cd $ORACLE_HOME/bin
./lsnrctl start
./sqlplus sys as sysdba
STARTUP

In 2008 when I worked with some Oracle databases under Solaris and AIX, I spent some time to figure out how to make the database backup and restore and decided to use cold backup as the most straightforward method. As far as I remember, to backup the database I shutted down Oracle and then simply archive the database files using “zip” command. To restore the database I used the following shell script that extracts archived files and adjust some Oracle settings:

(more…)