Oracle database Proxy Authentication Example

SQL> create user c##guest identified by password;

User created.

SQL> alter user c##donghua grant connect through c##guest;

User altered.

oracle@solaris:~$ sqlplus c##guest[c##donghua]/password@orclcdb

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jun 9 23:37:29 2014

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

Last Successful login time: Mon Jun 09 2014 23:37:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user;
USER is "C##DONGHUA"

SQL> col session_user for a20
SQL> col proxy_user for a20
SQL> select sys_context('USERENV','SESSION_USER') session_user,
  2  sys_context('USERENV','PROXY_USER') proxy_user from dual;

SESSION_USER         PROXY_USER
-------------------- --------------------
C##DONGHUA           C##GUEST

 

Enabling Common Users to Access Data in Specific PDBs

 

Extracted from: http://docs.oracle.com/cd/E16655_01/network.121/e17607/authorization.htm#DBSEG806

To enable common users to access data about specific PDBs, you must run the ALTER USER statement in the root.

Example 4-6 shows how to run the ALTER USER statement to enable the common user c##hr_admin to view information pertaining to the CDB$ROOT, SALES_PDB, and hrpdb containers in all container data objects that he can access, using the V$SESSION view.

Example 4-6 Enabling a Common User to View Specific Object Data

CONNECT SYSTEM@root
Enter password: password
Connected.

ALTER USER c##hr_admin
SET CONTAINER_DATA = (CDB$ROOT, SALESPDB, HRPDB) 
FOR V$SESSION CONTAINER=CURRENT;

In this specification:

• 
  

  CDB$ROOT, SALES_PDB, hrpdb refer to the containers that must be accessible to user c##hr_admin. You must include CDB$ROOT.

  
  



• 
  

  FOR V$SESSION specifies the CONTAINER_DATA dynamic view, which common user c##hr_admin will query.

  
  



• 
  

  CONTAINER = CURRENT must be specified because when you are connected to the root, CONTAINER=ALL is the default for the ALTER USER statement, but modification of the CONTAINER_DATA attribute must be restricted to the root.

  
  

Oracle 11gR2 express edition on windows 64bit is out

Download from here: http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

1z0-060 Upgrade to Oracle Database 12c - Create and configure a CDB

With the Database Configuration Assistant (DBCA), a graphical tool:

1z0-060 Upgrade to Oracle Database 12c - Explain root and multitenant architecture

Extracted from: http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_intro.htm#ADMIN14145

A CDB includes the following components:

• Root

  The root, named CDB$ROOT, stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. A CDB has exactly one root.

• Seed

  The seed, named PDB$SEED, is a template that you can use to create new PDBs. You cannot add objects to or modify objects in the seed. A CDB has exactly one seed.

• PDBs

  A PDB appears to users and applications as if it were a non-CDB. For example, a PDB can contain the data and code required to support a specific application. A PDB is fully backward compatible with Oracle Database releases before Oracle Database 12c.

Each of these components is called a container. Therefore, the root is a container, the seed is a container, and each PDB is a container. Each container has a unique container ID and name within a CDB. Figure 36-1 shows a CDB with several PDBs.

You can easily plug a PDB into a CDB and unplug a PDB from a CDB. When you plug in a PDB, you associate the PDB with a CDB. When you unplug a PDB, you disassociate the PDB from a CDB. An unplugged PDB consists of an XML file that describes the PDB and the PDB's files (such as the data files and wallet file).

You can unplug a PDB from one CDB and plug it into a different CDB without altering your schemas or applications. A PDB can be plugged into only one CDB at a time.

Each PDB has a globally unique identifier (GUID). The PDB GUID is primarily used to generate names for directories that store the PDB's files, including both Oracle Managed Files directories and non-Oracle Managed Files directories.

1z0-060 Upgrade to Oracle Database 12c - Identify the benefits of the multitenant container database

Purpose of a Multitenant Environment

(Extracted from: http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_intro.htm#ADMIN14147)

A multitenant environment enables the central management of multiple PDBs in a single installation. By using a multitenant environment, you can accomplish the following goals:

• Cost reduction

  By consolidating hardware and sharing database memory and files, you reduce costs for hardware, storage, availability, and labor. For example, 100 PDBs on a single server share one database instance and one set of database files, thereby requiring less hardware and fewer personnel.

• Easier and more rapid movement of data and code

  By design, you can plug a PDB into a CDB, unplug the PDB from the CDB, and then plug this PDB into a different CDB. Therefore, you can easily move an application's database back end from one server to another.

• Easier management and monitoring of the physical database

  The CDB administrator can attend to one physical database (one set of files and one set of database instances) rather than split attention among dozens or hundreds of non-CDBs. Backup strategies and disaster recovery are simplified.

• Separation of data and code

  Although consolidated into a single physical CDB, PDBs mimic the behavior of traditional non-CDBs. For example, if a user error causes data loss, then a PDB administrator can use point-in-time recovery to retrieve the lost data without affecting other PDBs.

• Ease of performance tuning

  It is easier to collect performance metrics for a CDB than for multiple non-CDBs. It is easier to size one SGA than several SGAs.

• Support for Oracle Database Resource Manager

  In a CDB, one concern is contention for system resources among the PDBs running on the same server. Another concern is limiting resource usage for more consistent, predictable performance. To address such resource contention, usage, and monitoring issues, you can use Oracle Database Resource Manager

• Fewer patches and upgrades

It is easier to apply a patch to one CDB than to multiple non-CDBs and to upgrade one CDB than to upgrade several non-CDBs.

A multitenant environment is especially useful when you have many non-CDBs deployed on different hardware in multiple Oracle Database installations. These non-CDBs might use only a fraction of the hardware resources dedicated to them, and each one might not require a full-time database administrator to manage it.

By combining these non-CDBs into a CDB, you can make better use of your hardware resources and database administrator resources. In addition, you can move a PDB from one CDB to another without requiring changes to the applications that depend on the PDB.

Script used to re-grant privilege after revoking from public

Below script is enhanced version to skip these already granted privilege. The original script could be found here: Be Cautious When Revoking Privileges Granted to PUBLIC (Doc ID 247093.1)

SQL> select unique statements from
  2  (
  3   select nvl2(p.grantee,'-- ','')||'grant execute on '|| d.referenced_name||' to '||d.owner||';' statements
  4   from dba_dependencies d left join dba_tab_privs p
  5  on d.owner=p.grantee and p.privilege='EXECUTE' and d.referenced_name=p.table_name
  6   where d.referenced_owner in ('SYS','PUBLIC')
  7  and d.referenced_type in ('PACKAGE','SYNONYM')
  8  and d.referenced_name in
  9     (
10     'DBMS_RANDOM',
11     'DBMS_EXPORT_EXTENSION',
12     'UTL_FILE',
13     'DBMS_JOB',
14     'DBMS_LOB',
15     'UTL_SMTP',
16     'UTL_TCP',
17     'UTL_HTTP'
18     )
19  and d.owner <> 'SYS'
20  and d.owner <> 'PUBLIC'
21  )
22  order by replace(statements,'-- ','')
23  /

STATEMENTS
--------------------------------------------------------------------------------
-- grant execute on DBMS_JOB to APEX_030200;
-- grant execute on DBMS_JOB to APEX_040200;
grant execute on DBMS_JOB to DBSNMP;
grant execute on DBMS_JOB to XDB;
-- grant execute on DBMS_LOB to APEX_030200;
-- grant execute on DBMS_LOB to APEX_040200;
grant execute on DBMS_LOB to CTXSYS;
grant execute on DBMS_LOB to MDSYS;
-- grant execute on DBMS_LOB to ORDPLUGINS;
-- grant execute on DBMS_LOB to ORDSYS;
-- grant execute on DBMS_LOB to WMSYS;
grant execute on DBMS_LOB to XDB;
-- grant execute on DBMS_RANDOM to APEX_030200;
-- grant execute on DBMS_RANDOM to APEX_040200;
grant execute on DBMS_RANDOM to DBSNMP;
grant execute on DBMS_RANDOM to MDSYS;
-- grant execute on UTL_FILE to APEX_030200;
grant execute on UTL_FILE to MDSYS;
-- grant execute on UTL_FILE to ORACLE_OCM;
-- grant execute on UTL_FILE to ORDPLUGINS;
-- grant execute on UTL_FILE to ORDSYS;
-- grant execute on UTL_FILE to WMSYS;
grant execute on UTL_FILE to XDB;
-- grant execute on UTL_HTTP to APEX_030200;
-- grant execute on UTL_HTTP to APEX_040200;
grant execute on UTL_HTTP to MDSYS;
-- grant execute on UTL_HTTP to ORDPLUGINS;
-- grant execute on UTL_SMTP to APEX_030200;
-- grant execute on UTL_SMTP to APEX_040200;

29 rows selected.

----------------------------------------

select unique statements from
(
select nvl2(p.grantee,'-- ','')||'grant execute on '|| d.referenced_name||' to '||d.owner||';' statements
from dba_dependencies d left join dba_tab_privs p
on d.owner=p.grantee and p.privilege='EXECUTE' and d.referenced_name=p.table_name
where d.referenced_owner in ('SYS','PUBLIC')    
and d.referenced_type in ('PACKAGE','SYNONYM') 
and d.referenced_name in
    (
    'DBMS_RANDOM',
    'DBMS_EXPORT_EXTENSION',
    'UTL_FILE',
    'DBMS_JOB',
    'DBMS_LOB',
    'UTL_SMTP',
    'UTL_TCP',
    'UTL_HTTP'
    )
and d.owner <> 'SYS'
and d.owner <> 'PUBLIC'
)   
order by replace(statements,'-- ','')