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…)Category Archives: Databases
Oracle Database user and role IDs
How to change MySQL 5.7 root password
First, check MySQL version:
SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name | Value |
+-------------------------+-------------------------+
| innodb_version | 5.7.25 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.25-0ubuntu0.16.04.2 |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+-------------------------+
Accessing Oracle Database in C++ with MS VS2017.
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…)How to get all the privileges of an Oracle database user.
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;
Installing sample Oracle Database schemas.
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
Oracle 19c Audit changes.
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';
Configuring Oracle Database for Auditing.
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
Running Oracle database (v12,18,19) in a docker container on Ubuntu 18.04.
Clone the repository with the official Oracle docker images:
git clone https://github.com/oracle/docker-images
Install docker, download Oracle Database, put it to the directory docker-images/OracleDatabase/SingleInstance/dockerfiles/18.3.0 containing the Dockerfile and run the following commands to build and run Enterprise Edition:
cd docker-images/OracleDatabase/SingleInstance/dockerfiles/18.3.0
mv ~/Downloads/LINUX.X64_180000_db_home.zip .
# Fix a small bug in Dockerfile, see https://github.com/oracle/docker-images/issues/1468
sed -i 's/V981623-01/LINUX.X64_180000_db_home/g' Dockerfile
sudo docker build -t oracle/database:18.3.0 --build-arg DB_EDITION=EE .
sudo docker run -d -it --rm --name oracle18 oracle/database:18.3.0
sudo docker logs oracle18 --tail 100
sudo docker logs oracle18 | grep -i password
and the following commands to build and run Standard Edition 2 (it does not require V981623-01.zip):
sudo docker build -t oracle/database-se:18.3.0 --build-arg DB_EDITION=SE2 .
sudo docker run -d -it --rm --name oracle18se oracle/database-se:18.3.0
sudo docker logs oracle18se --tail 100
the first string of the docker output contains the generated password for SYS, SYSTEM and PDBADMIN.
(more…)Backing up and restoring all the PHP MySQL websites on a Linux server
All the following commands assumes we saved MySQL root password into MROOTPASS variable:
export MROOTPASS=<mysql root password>
The most straight forward method to backup all the MySQL databases and all the website files (PHP scripts, images, etc..) stored in the /home directory is the following:
mysqldump --all-databases -u root -p$MROOTPASS | gzip > all-databases-$(date '+%Y-%m-%d_%H-%M-%S').sql.gz tar -cvzf home.tar.gz /home
If we backup some individual database (probably not as root) and change its user while restoring it, it might make a sense to remove DEFINER from the output script:
sed -e 's/*]*\*/\*/'
The following commands restore all the websites from the archives:
gunzip -c all-databases-2017-05-23_15-31-00.sql.gz | mysql -u root -p$MROOTPASS cd / sudo tar -xvzf home.tar.gz
After migration from MySQL version 14.14 Distrib 5.5.54 to 14.14 Distrib 5.7.18 (I do not know what is the difference between them) I got the following error: “ERROR 1805 (HY000) at line 1: Column count of mysql.user is wrong. Expected 45, found 42. The table is probably corrupted” while trying to drop some user, and fixed it by running:
mysql_upgrade -u root -p$MROOTPASS service mysql restart
Compiling GDAL on Ubuntu Linux with SQLite and MySQL support
Fist install MySQL client libraries and check their location:
apt-get install libmysqlclient-dev find / -name '*libmysqlclient*'
Then install SQLite:
sudo apt-get install sqlite3 libsqlite3-dev
Extract GDAL sources and configure supported modules (MySQL and SQLite are not compiled by default, so we need to specify them explicitly):
unzip gdal211.zip cd gdal-2.1.1/ ./configure --with-sqlite3 --with-mysql
this will output “MySQL support: yes” and “SQLite support: yes” along with other information.
