Sunday, April 9, 2023

Grants for new role DB_DEVELOPER_ROLE in Oracle 23c

 

Granted ROLE Privileges

SQL> set tab off
SQL> column grantee for a17
SQL> select * from dba_role_privs where grantee='DB_DEVELOPER_ROLE';

GRANTEE           GRANTED_ROLE      ADM DEL DEF COM INH
----------------- ----------------- --- --- --- --- ---
DB_DEVELOPER_ROLE SODA_APP          NO  NO  YES YES YES
DB_DEVELOPER_ROLE CTXAPP            NO  NO  YES YES YES

Granted SYSTEM Privileges

SQL> select * from dba_sys_privs where grantee='DB_DEVELOPER_ROLE';

GRANTEE           PRIVILEGE                      ADM COM INH
----------------- ------------------------------ --- --- ---
DB_DEVELOPER_ROLE CREATE DOMAIN                  NO  YES YES
DB_DEVELOPER_ROLE CREATE MLE                     NO  YES YES
DB_DEVELOPER_ROLE CREATE ANALYTIC VIEW           NO  YES YES
DB_DEVELOPER_ROLE CREATE HIERARCHY               NO  YES YES
DB_DEVELOPER_ROLE CREATE ATTRIBUTE DIMENSION     NO  YES YES
DB_DEVELOPER_ROLE EXECUTE DYNAMIC MLE            NO  YES YES
DB_DEVELOPER_ROLE CREATE CUBE BUILD PROCESS      NO  YES YES
DB_DEVELOPER_ROLE CREATE CUBE                    NO  YES YES
DB_DEVELOPER_ROLE CREATE CUBE DIMENSION          NO  YES YES
DB_DEVELOPER_ROLE CREATE MINING MODEL            NO  YES YES
DB_DEVELOPER_ROLE CREATE JOB                     NO  YES YES
DB_DEVELOPER_ROLE DEBUG CONNECT SESSION          NO  YES YES
DB_DEVELOPER_ROLE ON COMMIT REFRESH              NO  YES YES
DB_DEVELOPER_ROLE CREATE DIMENSION               NO  YES YES
DB_DEVELOPER_ROLE CREATE TYPE                    NO  YES YES
DB_DEVELOPER_ROLE CREATE MATERIALIZED VIEW       NO  YES YES
DB_DEVELOPER_ROLE CREATE TRIGGER                 NO  YES YES
DB_DEVELOPER_ROLE CREATE PROCEDURE               NO  YES YES
DB_DEVELOPER_ROLE FORCE TRANSACTION              NO  YES YES
DB_DEVELOPER_ROLE CREATE SEQUENCE                NO  YES YES
DB_DEVELOPER_ROLE CREATE VIEW                    NO  YES YES
DB_DEVELOPER_ROLE CREATE SYNONYM                 NO  YES YES
DB_DEVELOPER_ROLE CREATE TABLE                   NO  YES YES
DB_DEVELOPER_ROLE CREATE SESSION                 NO  YES YES

24 rows selected.

Granted Object Privileges

SQL> select * from dba_tab_privs where grantee='DB_DEVELOPER_ROLE';

GRANTEE           OWNER      TABLE_NAME                GRANTOR    PRIVILEGE  GRA HIE COM TYPE         INH
----------------- ---------- ------------------------- ---------- ---------- --- --- --- ------------ ---
DB_DEVELOPER_ROLE SYS        JAVASCRIPT                SYS        EXECUTE    NO  NO  NO  MLE LANGUAGE NO
DB_DEVELOPER_ROLE SYS        V_$STATNAME               SYS        READ       NO  NO  YES VIEW         YES
DB_DEVELOPER_ROLE SYS        V_$PARAMETER              SYS        READ       NO  NO  YES VIEW         YES
DB_DEVELOPER_ROLE SYS        DBA_PENDING_TRANSACTIONS  SYS        SELECT     NO  NO  YES VIEW         YES

Script to extract ROLE DDL and dbms_metadata.get_granted_ddl bug for Oracle 23c

 

Script to extract ROLE DDL

set long 8000 
set longchunksize 2000

with role_name as 
  (select 'DBA' as name from dual)
select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
from   dba_roles r, role_name
where  r.role = role_name.name
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp, role_name
where  rp.grantee = role_name.name
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp, role_name
where  sp.grantee = role_name.name
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp, role_name
where  tp.grantee = role_name.name
and    rownum = 1
/

Bug in Oracle 23c (23.2)

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','CONNECT')
--------------------------------------------------------------------------------

  BEGIN NULL; END
  BEGIN NULL; END

There is no such funny bug in Oracle 21c (21.9)

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.9.0.0.0


SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','CONNECT')
--------------------------------------------------------------------------------

  GRANT CREATE SESSION TO "CONNECT"
  GRANT SET CONTAINER TO "CONNECT"

DROP and CREATE in Oracle 23c with IF EXISTS and IF NOT EXISTS

 

DROP and CREATE in Oracle 21c

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.9.0.0.0

SQL> drop table t;
drop table t
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table t (id int);

Table created.

SQL> create table t (id int);
create table t (id int)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

DROP and CREATE in Oracle 23c with IF EXISTS and IF NOT EXISTS

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> drop table if exists t;

Table dropped.

SQL> create table t (id int);

Table created.

SQL> create table if not exists t (id int);

Table created.

CREATE OR REPLACE can't coexist with NOT EXISTS

SQL> create synonym if not exists s for t;

Synonym created.

SQL> create or replace synonym s for t;

Synonym created.
SQL> create or replace if not exists s for t;
create or replace if not exists s for t
                  *
ERROR at line 1:
ORA-00922: missing or invalid option
SQL>

Saturday, April 8, 2023

Set Search Path at database level for PostgreSQL

There are quite a lot examples on how to set search_path at user level, here is one at the database level.

Check the setting before change

dev=> select * from pg_db_role_setting where setdatabase=(select oid from pg_database where datname='dev');
 setdatabase | setrole | setconfig
-------------+---------+-----------
(0 rows)
dev=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

Change the default search path at database level

The setting can be overwritten by user level setting and session level setting.

dev=> alter database dev set search_path="$user",appuser,public;
ALTER DATABASE
dev=> select * from pg_db_role_setting where setdatabase=(select oid from pg_database where datname='dev');
 setdatabase | setrole |                 setconfig
-------------+---------+--------------------------------------------
       16400 |       0 | {"search_path=\"$user\", appuser, public"}
(1 row)
-- the setting doesn't take effect without reconnection
dev=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

Reconnect

dev=> \c dev

psql (14.3, server 14.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
You are now connected to database "dev" as user "postgres".
dev=> show search_path;
       search_path
--------------------------
 "$user", appuser, public
(1 row)

Tuesday, April 4, 2023

Install Oracle 23c Free Developer Edition on Oracle Linux 8

 

  1. Download Oracle Database 23c Free Developer Release here. OTN login is no longer required.
wget https://yum.oracle.com/repo/OracleLinux/OL8/developer/x86_64/getPackage/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
wget https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23c-1.0-1.el8.x86_64.rpm
  1. Perform the installation on Oracle Linux 8 (steps on RHEL8 and CentOS 8 shall be similar)
sudo yum install ./oracle-database-free-23c-1.0-1.el8.x86_64.rpm ./oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
  1. Configure Oracle database.
[oracle@ol stage]$ sudo /etc/init.d/oracle-free-23c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database FREE.
Enter SYS user password:
************
Enter SYSTEM user password:
*******
Enter PDBADMIN User Password:
*******
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
33% complete
36% complete
39% complete
43% complete
Completing Database Creation
47% complete
49% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log" for further details.

Connect to Oracle Database using one of the connect strings:
     Pluggable database: ol.lab/FREEPDB1
     Multitenant container database: ol.lab

By default, Oracle installation to /opt/oracle/product/23c/dbhomeFree and data file created in /opt/oracle/oradata/. To change the data file location, modify this configure file /etc/sysconfig/oracle-free-23c.conf prior to run /etc/init.d/oracle-free-23c configure.

#This is a configuration file to setup the Oracle Database.
#It is used when running '/etc/init.d/oracle-free-23c configure'.

# LISTENER PORT used Database listener, Leave empty for automatic port assignment
LISTENER_PORT=

# Character set of the database
CHARSET=AL32UTF8

# Database file directory
# If not specified, database files are stored under Oracle base/oradata
DBFILE_DEST=

# DB Domain name
DB_DOMAIN=

# SKIP Validations, memory, space
SKIP_VALIDATIONS=false
  1. Connect to Oracle database
[oracle@ol stage]$ . oraenv
ORACLE_SID = [orcl21c] ? FREE
The Oracle base has been changed from /u01/app/oracle to /opt/oracle
[oracle@ol stage]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Apr 4 16:41:46 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0