The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.
Here’s an excerpt:
The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 89,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 4 days for that many people to see it.
1. For DBAs and Developers, the words READ and SELECT have been for years somehow synonyms. In 12c, is there now any difference?
2. Before pluggable databases, selecting data from the SALES table for instance meant selecting data from a table called SALES in a certain SCHEMA within the database. How about if a table called SALES belongs to several pluggable databases under the same schema name?
The aim of this blog post is to shed some light on these new concepts.
1. New READ privilege.
Until Oracle 12.1.0.2. the SELECT object privilege allowed users to perform the following two operations in addition to just reading data from the SALES table:
LOCK TABLE sales IN EXCLUSIVE MODE; SELECT ... FROM sales FOR UPDATE;
These 2 commands enabled the users to lock the rows of the SALES table.
The READ object privilege does not provide these additional privileges. For better security, grant users the READ object privilege if you want to restrict them to performing queries only.
In addition to the READ object privilege, you can grant users the READ ANY TABLE privilege to enable them to query any table in the database.
When a user who has been granted the READ object privilege wants to perform a query, the user still must use the SELECT statement. There is no accompanying READ SQL statement for the READ object privilege.
The GRANT ALL PRIVILEGES TO user SQL statement includes the READ ANY TABLE system privilege. The GRANT ALL PRIVILEGES ON object TO user statement includes the READ object privilege.
If you want the user only to be able to query tables, views, materialized views, or synonyms, then grant the READ object privilege. For example:
GRANT READ ON SALES TO julian;
2. Querying a table owned by a common user across all PDBs.
Consider the following scenario:
– The container database has several pluggable databases, i.e., it has a separate PDB for each different office location of the company.
– Each PDB has a SALES table that tracks the sales of the office, i.e., the SALES table in each PDB contains different sales information.
– The root container also has an empty SALES table.
– The SALES table in each container is owned by the same common user.
To run a query that returns all of the sales across the company connect to each PDB as a common user, and create a view with the following statement:
CREATE OR REPLACE VIEW sales AS SELECT * FROM sales;
The common user that owns the view must be the same common user that owns the sales table in the root. After you run this statement in each PDB, the common user has a view named sales in each PDB.
With the root as the current container and the common user as the current user, run the following query with the CONTAINERS clause to return all of the sales in the sales table in all PDBs:
SELECT * FROM CONTAINERS(sales);
You can also query the view in specific containers. For example, the following SQL statement queries the view in the containers with a CON_ID of 3 and 4:
SELECT * FROM CONTAINERS(sales) WHERE CON_ID IN (3,4);
3. Delegate.
Something else: staring 12.1.0.2, when granting a role to a user, you can specify the WITH DELEGATE OPTION clause. Then the grantee can do the following two things:
A) Grant the role to a program unit in the grantee’s schema
B) Revoke the role from a program unit in the grantee’s schema
Julian Dontcheff's Database Blog 