Feed aggregator

A schema and a user are not the same in PostgreSQL

Yann Neuhaus - Wed, 2019-11-27 11:56

When people with an Oracle background attend our PostgreSQL DBA Essentials training there is always a bit of confusion about schemas and users. In Oracle a schema and a user is a one to one relationship and there is no real distinction between a user and a schema. In PostgreSQL the situation is different: All the objects a user is creating are created in a specific schema (or namespace). Other users may or may not have permissions to work with this objects or even to create new objects in a specific schema. Compared to Oracle there is one layer more.

The hierarchy in PostgreSQL is this:

|-------------------------------------------|---|
| PostgreSQL instance                       |   |
|-------------------------------------------| U |
|     Database 1      |     Database 2      | S |
|---------------------|---------------------| E |
| Schema 1 | Schema 2 | Schema 1 | Schema 2 | R |
|----------|----------|----------|----------| S |
| t1,t2,t3 | t1,t2,t3 | t1,t2,t3 | t1,t2,t3 |   |
-------------------------------------------------

What this little ASCII image shall tell you: Users (and roles) in PostgreSQL are global objects and are not defined in a database but on the instance level. Schemas are created by users in a specific database and contain database objects. Where a lot of people get confused with is this:

postgres@centos8pg:/home/postgres/ [pgdev] psql -X postgres
psql (13devel)
Type "help" for help.

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# 

Nothing in this create table statement references a schema but according to what I just said above all objects must go to a schema. Where did this table go then? Each PostgreSQL database comes with a public schema by default and if you do not explicitly specify a schema the new object will go there. There are several ways of asking PostgreSQL for the schema of a given table but probably the two most used ones are these (the first one is asking a catalog view and the second one is using a psql shortcut)

postgres=# select schemaname from pg_tables where tablename = 't1';
 schemaname 
------------
 public
(1 row)

postgres=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

Btw: The public schema is a special schema in PostgreSQL and you should either remove it or at least revoke permission from public on the public schema. Check here for more information on that.

So what happens when you drop the public schema and try to create a table afterwards?

postgres=# drop schema public cascade;
NOTICE:  drop cascades to table t1
DROP SCHEMA
postgres=# create table t1 ( a int );
ERROR:  no schema has been selected to create in
LINE 1: create table t1 ( a int );
                     ^
postgres=# 

As we do not have a single schema anymore:

postgres=# \dn
List of schemas
 Name | Owner 
------+-------
(0 rows)

… PostgreSQL has no idea where to put the table. At this point it should already be clear that a schema in PostgreSQL is not the same as a user. We are connected as the “postgres” user, but we do not have a schema to create our objects in. Lets create the first schema and right afterwards the same table as above:

postgres=# create schema my_schema;
CREATE SCHEMA
postgres=# create table t1 ( a int );
ERROR:  no schema has been selected to create in
LINE 1: create table t1 ( a int );
                     ^
postgres=# 

… again PostgreSQL is not able to create the table. The question is: Why did it work when then public schema was there? We did not specify the public schema above but it worked. This is where the search_path comes into the game:

postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

postgres=# 

By default the search_path contains you current username and public. As none of these schemas exist right now the create table statement will fail. There are two options to fix that. Either use the fully qualified name:

postgres=# create table my_schema.t1 ( a int );
CREATE TABLE
postgres=# \d my_schema.t1
               Table "my_schema.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

… or adjust the search_path so that your preferred schema comes first:

postgres=# set search_path = 'my_schema',"$user",public;
SET
postgres=# show search_path ;
        search_path         
----------------------------
 my_schema, "$user", public
(1 row)

postgres=# create table t2 ( a int );
CREATE TABLE
postgres=# \d t2
               Table "my_schema.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

postgres=# 

That all might look a bit strange at the beginning, especially when you are used to Oracle, but it also provides great flexibility:

  • A user can create many different schemas, no need to create separate users
  • A user can grant permission to create objects in one of his schemas to someone else
  • You can logically divide your application
  • (no, there are no synonyms in PostgreSQL)
  • The are default privileges you can use

Cet article A schema and a user are not the same in PostgreSQL est apparu en premier sur Blog dbi services.

Goal seek excel Function – A Step By Step Tutorial

VitalSoftTech - Wed, 2019-11-27 10:33

Microsoft Excel is one of the most widely used applications for data analysis. Professionals from financial industry to information technology and researchers to academics make extensive use of Excel on a day to day basis. There is no denying the fact that the range of features that Microsoft Excel offers is truly amazing. This is […]

The post Goal seek excel Function – A Step By Step Tutorial appeared first on VitalSoftTech.

Categories: DBA Blogs

Scalable Distributed BI Architecture

Dylan's BI Notes - Tue, 2019-11-26 19:15
Incorta, a scalable distributed BI system...
Categories: BI & Warehousing

What Does PDF Stand For?

VitalSoftTech - Tue, 2019-11-26 09:52

The acronym PDF is widely used while converting, downloading documents, and browsing on the internet. You might have wondered what does PDF stand for. In today’s day and age, jargonization has enveloped every part of our life, including writing and browsing. It is, however, very convenient to use these acronyms instead of pronouncing the entire […]

The post What Does PDF Stand For? appeared first on VitalSoftTech.

Categories: DBA Blogs

CVE-2019-2638, CVE-2019-2633, Oracle Payday Vulnerabilities - AppDefend Protection

Two Oracle E-Business Suite security vulnerabilities (CVE-2019-2638, CVE-2019-2633) fixed in April 2019 Oracle Critical Patch Update (CPU) have been recently publicized. These vulnerabilities allow an attacker to execute arbitrary SQL statements in the Oracle E-Business Suite data that can result in complete compromise of the environment including fraudulent transactions, changing of bank accounts, and circumvention of application security controls. Integrigy’s AppDefend, the application firewall for Oracle E-Business Suite, is the only solution that provides virtual patching for and proactive defense against these vulnerabilities.

These two vulnerabilities are in the Oracle E-Business Suite (EBS) TCF Server, which provides services to the professional Forms interface for a limited set of Forms. TCF Server is implemented and enabled in all versions of Oracle E-Business Suite including 11i, 12.0, 12.1, and 12.2. It can not be disabled without a customization to Oracle EBS.

TCF Server is a servlet running as part of the standard Oracle EBS web application server and communicates using HTTP or HTTPS between the Forms Java client and the web application server. For R12, the servlet is available at the URL /OA_HTML/AppsTCFServer. It uses a proprietary application-level protocol to communicate between the Forms client and server.

The risk is that unlike most Oracle EBS SQL injection vulnerabilities that only allow for fragments of SQL statements to be appended to standard Oracle EBS SQL statements being executed, these security bugs allow execution of complete SQL statements as the Oracle EBS APPS database account. When evaluating the risk of these vulnerabilities in your environment, it is important to differentiate between external access to the Oracle EBS environment through the Internet when modules like iSupplier, iStore, and iRecruitment are being used and internal access from only your internal network. The risk from external access is critical and should be immediately addressed. The internal risk is still high and dependent on the security posture of your internal network. It is important to realize that non-Oracle EBS aware web application firewalls, database security tools, and other network security products will not provide any protection from successful exploitation of these vulnerabilities.

Integrigy AppDefend is the only solution that provides virtual patching for and proactive defense against these TCF Server vulnerabilities as well other Oracle EBS security vulnerabilities. Integrigy recognized the potential issues with TCF Server and even the first release of AppDefend for R12 in 2007 blocked external access to the TCF Server by default.

AppDefend provides multiple layers of protection against TCF Server vulnerabilities as follows -

  1. Blocks all access to TCF Server externally (since 2007).
  2. Enforces Oracle EBS access control for TCF Server allowing only authorized EBS users to access to the TCF Server (since 2018).
  3. Whitelists the functions accessible through TCF Server (since 2018).
  4. Blocks specific vulnerabilities in TCF Server (2018, 2019).
  5. Advanced SQL injection protection optimized specifically for Oracle EBS will detect and block most of the SQL statements used in TCF Server and other 0-day attacks. (since 2007).

If you do not have AppDefend, applying the latest Oracle Critical Patch Update for Oracle EBS will remediate these specific vulnerabilities and for external sites it is critical that the Oracle EBS URL Firewall is implemented as documented in Appendix E of My Oracle Support Note ID 380490.1. However, these solutions will not protect you prior to applying the security patches or against future TCF Server vulnerabilities and other Oracle EBS 0-day attacks.

Please let us know if you have any questions regarding the latest Oracle EBS security vulnerabilities at info@integrigy.com.

SQL Injection, Oracle E-Business Suite, Oracle Critical Patch Updates
Categories: APPS Blogs, Security Blogs

Believer | Keyboard Performance | by Dharun at Improviser Music Studio

Senthil Rajendran - Sun, 2019-11-24 07:08

My Son Dharun Performing at Improviser Music Studio

Believer



Please subscribe to our channel Improviser Music Studio

How to run a java software which needs RXTX on a raspberry pi

Dietrich Schroff - Sat, 2019-11-23 12:35
In my last posting i wrote about migrating my aircraft noise measurement station to alpine linux. There i had some problems with getting the RXTX library for Java running on a linux, which uses musl and not GNU libc6.

Why does my java application require RXTX? As stated on the RXTX page:
RXTX is a Java library, using a native implementation (via JNI), providing serial and parallel communication for the Java Development Toolkit (JDK).Now i wanted to move to a raspberry pi. But this runs on ARM and the RXTX is only provided for x86/x64 systems.

But there is another way: ser2net


With this proxy /dev/ttyUSB0 can be mapped to a tcp port and java can access this without using RXTX.

DOAG 2019: Cloud or Kubernetes on premise and CI/CD pipeline at the top of (my) interest

Yann Neuhaus - Fri, 2019-11-22 11:33

The DOAG 2019 is just over now and if I check the subject of sessions I was attending, I have the feeling the Cloud and Kubernetes on-premise deployments and CI/CD pipelines were the top subjects interesting people or may be just me !!

I started by a devOps session and then followed a lot of Kubernetes or containers related sessions. The three following sessions are those I directly think about when trying to summarize.

“The Pillars of Continuous Delivery” – Lykle Thijssen

A very interesting session about continuous delivery.

“How to create a solid foundation for Continuous Delivery, based on four pillars: Agile, Microservices, Automation and Cloud. Lykle explained how agile development can reduce time to production by short development cycles, how Microservices make the deployment process easier with less dependencies, how automation can improve test, build and deployment and how cloud can help with the necessary infrastructure. It all hangs together and without a solid foundation, there is always the risk of building a house of cards.”

“Containers Demystified” – Jan Karremans and Daniel Westermann

“If you are looking at implementing Dev/Ops, infrastructure as a code, if you want to adopt the Cloud or seeking to go with Microservices, you will find Containers on you path. After the opportunities that virtualization brought, Containers are the next best thing! Also (and perhaps specifically) looking at databases with containers, it brings specific challenges. Where containers infrastructures are built to fail and database rely on persistency, you have challenges.”

Watch My Services: Prometheus in Kubernetes – Thorsten Wussow

“The monitoring of microservices is a special challenge. In the lecture the problem of a monitoring of microservices will be discussed. Furthermore, various products are briefly considered, with which one can still carry out such a monitoring. Then a demo shows how to set up monitoring in a Kubernetes cluster with the help of Prometheus and Grafana and what to look for.”

A Kubernetes cluster was used in the Oracle Infrastructure Cloud to demonstrate the deployment and the configuration of Promotheus and Grafana.
With Thorsten, it always looks simple when following the demonstration. Now it is is time to implement it.

Kubernetes im Vergleich: Google, AWS, Oracle, Microsoft – Michael Schulze & Borys Neselovskyi

“The presentation gives a brief overview of the architecture and construction of Kubernetes. In addition, useful application scenarios are presented in selected use cases. Later in the talk, we will compare existing Kubernetes cloud solutions from leading vendors (Google, AWS, Oracle and Microsoft). Here, a customer scenario is used as the basis for the comparison. The following criteria play a role: installation, maintenance, performance, monitoring and, of course, costs.”

A nice comparison between the different cloud solutions.

There were some other very interesting sessions but I will not list all of them now.

DOAG 2019 is over. See you next year there in Nuremberg

Cet article DOAG 2019: Cloud or Kubernetes on premise and CI/CD pipeline at the top of (my) interest est apparu en premier sur Blog dbi services.

Oracle Database 18c: New Features asmcmd

Michael Dinh - Fri, 2019-11-22 11:11
============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showversion
ASM version         : 19.4.0.0.0
[oracle@ol7-19-rac1 ~]$ 

============================================================
OLD:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd -V
asmcmd version 19.4.0.0.0
[oracle@ol7-19-rac1 ~]$

============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showpatches
---------------
List of Patches
===============
29401763
29517242
29517247
29585399
29834717
29850993
29851014

[oracle@ol7-19-rac1 ~]$ asmcmd showpatches -l
Oracle ASM release patch level is [2037353368] and 
the complete list of patches [29401763 29517242 29517247 29585399 29834717 29850993 29851014 ] have been applied on the local node. 
The release patch string is [19.4.0.0.0].
[oracle@ol7-19-rac1 ~]$

============================================================
OLD:
============================================================

### MISSING from OLD are previous 19.3 version:
29517247; ACFS RELEASE UPDATE 19.3.0.0.0	
29585399; OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242; Database Release Update : 19.3.0.0.190416 (29517242)

[oracle@ol7-19-rac1 ~]$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [2037353368] and 
the complete list of patches [29401763 29517242 29517247 29585399 29834717 29850993 29851014 ] have been applied on the local node. 
The release patch string is [19.4.0.0.0].
[oracle@ol7-19-rac1 ~]$

[oracle@ol7-19-rac1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
29851014;ACFS RELEASE UPDATE 19.4.0.0.0 (29851014)
29850993;OCW RELEASE UPDATE 19.4.0.0.0 (29850993)
29834717;Database Release Update : 19.4.0.0.190716 (29834717)
29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)

OPatch succeeded.
[oracle@ol7-19-rac1 ~]$

============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showversion --softwarepatch
ASM version         : 19.4.0.0.0
Software patchlevel : 2037353368
[oracle@ol7-19-rac1 ~]$

============================================================
OLD:
============================================================

[oracle@ol7-19-rac1 ~]$ crsctl query crs softwarepatch
Oracle Clusterware patch level on node ol7-19-rac1 is [2037353368].
[oracle@ol7-19-rac1 ~]$

============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showversion --active
Oracle ASM active version on the cluster is [19.0.0.0.0]. 
The cluster upgrade state is [NORMAL]. 
The cluster active patch level is [2037353368].
[oracle@ol7-19-rac1 ~]$

============================================================
OLD:
============================================================

[oracle@ol7-19-rac1 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [19.0.0.0.0]. 
The cluster upgrade state is [NORMAL]. 
The cluster active patch level is [2037353368].
[oracle@ol7-19-rac1 ~]$

============================================================
NEW:
============================================================

[oracle@ol7-19-rac1 ~]$ asmcmd showversion --releasepatch
ASM version         : 19.4.0.0.0
Information about release patchlevel is unavailable since no ASM instance connected

[oracle@ol7-19-rac1 ~]$ asmcmd
ASMCMD> showversion --releasepatch
ASM version         : 19.4.0.0.0
Release patchlevel  : 2037353368
ASMCMD>

============================================================
OLD:
============================================================

[oracle@ol7-19-rac1 ~]$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [2037353368] and 
the complete list of patches [29401763 29517242 29517247 29585399 29834717 29850993 29851014 ] have been applied on the local node. 
The release patch string is [19.4.0.0.0].
[oracle@ol7-19-rac1 ~]$

Basically, the new features for asmcmd existed for crsctl query and use the ones best suited.

Nightly process slowing down.

Tom Kyte - Thu, 2019-11-21 11:50
Hi. We have a process that runs every night that is beginning to slow down and we need some help to find the resources to analyse the problem. In our setup, unfortunately both transaction schemas and warehousing (statistics) schemas are kept on...
Categories: DBA Blogs

Golden Signals for performance

Tom Kyte - Thu, 2019-11-21 11:50
Hello Tom I am a member of an site reliability team (SRE) and we are trying to develop SRE "golden signals" for an Oracle 11g/12c database. These signal are: 1) Throughput 2) Latency 3) Response Time 4) Error rate (not sure about this one...
Categories: DBA Blogs

Difference between DBRM and IORM

Tom Kyte - Thu, 2019-11-21 11:50
Dear Sir, Please help me to know below points. 1)difference between DBRM and IORM? 2)difference between ACFS,ADVM and DBFS? Thanks Pradeep
Categories: DBA Blogs

SQL*Loader-503 in 19c for UNC names

Tom Kyte - Thu, 2019-11-21 11:50
Hi TOM, next Month we are migrating from Oracle 12.1.0.2 to Oracle 19, so I installed the new Oracle Client 19c on my PC. Using SQLLDR with (all in one line) sqlldr userid=user/passwd@server control=\\server\share$\folder\controlfile.ini ...
Categories: DBA Blogs

future of dba

Tom Kyte - Thu, 2019-11-21 11:50
Dear, I am Core Oracle DBA. I do not have experience in RAC,Golden Gate. Sometimes I worried about future of oracle dba in upcoming years in India for all industry like medium and large company. Can you give some recommendation or new technology n...
Categories: DBA Blogs

questions about sampling

Tom Kyte - Thu, 2019-11-21 11:50
<b>a. how does sampling work internally ?</b> meaning: how is it determined by Oracle which blocks are read by a sample-query and which not ? <code>create table t as select level i, rpad('a', 1000, 'a') s from dual connect by level <= 1e5; cre...
Categories: DBA Blogs

Local temporary tablespace

Tom Kyte - Thu, 2019-11-21 11:50
Since Oracle 12.2, Oracle has introduced a new feature of local temporary tablespace. The documentation does not provide any information on the pros and cons of this new feature. Can you please provide some information on why this feature was introdu...
Categories: DBA Blogs

Is Your Print Screen not Working? We are here to help!

VitalSoftTech - Thu, 2019-11-21 09:54

Print Screen is a hotkey present on your keyboard that helps you take the snapshot of your desktop screen and save it on the computer. All you have to do is press this button, open Paint, press ‘Ctrl+V,’ crop the image to your setting and Voila! Computers make everything we do more comfortable, including the […]

The post Is Your Print Screen not Working? We are here to help! appeared first on VitalSoftTech.

Categories: DBA Blogs

Oracle Security Training Manuals for Sale

Pete Finnigan - Wed, 2019-11-20 20:50
We have one set of Manuals for the recent training we held here in York and one from 2018. These can be bought as individual books as follows: This manual is from the York class in October 2019 and can....[Read More]

Posted by Pete On 19/11/19 At 03:05 PM

Categories: Security Blogs

Installing Oracle GoldenGate Microservices – Part 2 of 2

DBASolved - Wed, 2019-11-20 14:32

In the my last post, I showed you how to install the binaries for Oracle GoldenGate Microservices.  Then at the end of the post, I mentioned that you will need to run Oracle GoldenGate Configuration Assistant to finish the configuration of Oracle GoldenGate Microservices.  Let’s take a look at how to do this. After installing […]

The post Installing Oracle GoldenGate Microservices – Part 2 of 2 appeared first on DBASolved.

Categories: DBA Blogs

Installing Oracle GoldenGate Microservices – Part 1 of 2

DBASolved - Wed, 2019-11-20 10:43

Recently I was exchanging emails with a few community members about Oracle GoldenGate Microservices.  One of them commented on that Oracle GoldenGate Microservices was difficult to install; at the time, I was puzzled.  The reason I was puzzled was due to the install process for Oracle GoldenGate hasn’t changed since Oracle GoldenGate 12.1 came out […]

The post Installing Oracle GoldenGate Microservices – Part 1 of 2 appeared first on DBASolved.

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator