Forum Stats

  • 3,875,295 Users
  • 2,266,907 Discussions
  • 7,912,141 Comments

Discussions

12c Multitenant Queries

932976
932976 Member Posts: 8
edited Aug 11, 2014 9:48PM in Multitenant

I created a common user and granted it select on CDB% views. When I run the follwing query while connected to CDB$ROOT I don't get any results:

SELECT pdb, network_name FROM CDB_SERVICES WHERE pdb is not null AND con_id>2;

But when I connect as sysdba I get the appropriate results. Why is this?

I want to create a common user that can create PDBs without sysdba privileges and I want that user to be able to query the status of all PDBs.

Answers

  • Unknown
    edited Aug 7, 2014 6:36PM

    Wrong forum!

    Please mark this question ANSWERED and repost it in the Multitenant forum.

    When you repost you need to SHOW us WHAT you did and HOW you did it.

    I created a common user and granted it select on CDB% views.

    So post the actual commands you executed and the actual results you got.

    When I run the follwing query while connected to CDB$ROOT I don't get any results

    Again - don't TELL us what you got - SHOW us what you got. You should ALWAYS get results from that query even if it is an 'empty' result set with no rows.

    But when I connect as sysdba I get the appropriate results

    Once more - SHOW us what results you are getting. We can't visualize what your screen is showing you.

  • 932976
    932976 Member Posts: 8

    SQL> CREATE USER c##cdb_man IDENTIFIED BY *;

    SQL> GRANT CONNECT, RESOURCE, SYSOPER, CREATE PLUGGABLE DATABASE,

         SET CONTAINER, SYSBACKUP, CDB_DBA, SELECT_CATALOG_ROLE

         TO c##cdb_man CONTAINER=ALL;

    SQL> CREATE ROLE c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_CONSTRAINTS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_DATA_FILES TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_HIST_PDB_INSTANCE TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_INDEXES TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_IND_COLUMNS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_OBJECTS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_PDB_HISTORY TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_PDBS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_REDO_LOG TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_SERVICES TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_TABLES TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_TABLESPACES TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_TAB_COLS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_TAB_COLUMNS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_TEMP_FILES TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_USERS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON CDB_VIEWS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON DBA_PDBS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT SELECT ON V_$PDBS TO c##cdb_admin CONTAINER=ALL;

    SQL> GRANT c##cdb_admin TO c##cdb_man CONTAINER=ALL;

    $ sqlplus c##cdb_man/*

    SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

    SQL> SELECT name, open_mode, restricted, open_time FROM V$PDBS;

    no rows selected

    SQL> connect / as sysdba

    SQL> SELECT name, open_mode, restricted, open_time FROM V$PDBS;

    NAME            OPEN_MODE  RESTRICTED OPEN_TIME

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

    PDB$SEED        READ ONLY  NO         04-AUG-14 07.23.34.163 PM -06:00

    SITETESTB       READ WRITE NO         05-AUG-14 11.56.29.083 AM -06:00

    SITE9_PDB       READ WRITE NO         05-AUG-14 01.31.25.097 PM -06:00

  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy

    Hi,

    as sysdba why don't you execute the following command:

    SQL> GRANT SELECT ON V$PDBS TO c##cdb_admin CONTAINER=ALL;


    by the way in your thread you stated the following statement, which i think has a typo (spelling mistake) of the view:


    SQL> GRANT SELECT ON V_$PDBS TO c##cdb_admin CONTAINER=ALL;


    Regards,

    Emad



  • 932976
    932976 Member Posts: 8

    There was a typo in the above. c##cdb_admin has select on v$pdbs but queries against it do not return rows but the same query from sysdba does.


  • Unknown
    edited Aug 11, 2014 9:48PM

    Thanks for reposting in the multitenant forum and providing the specifics of the statements you executed.

    Assuming those are the ONLY statements you executed you ARE getting the 'appropriate results'. Those are just not the results that you 'expected' or 'wanted'.

    I want to create a common user that can create PDBs without sysdba privileges

    Then you need to grant the CREATE PLUGGABLE DATABASE privilege and you have NOT granted that in the code you posted. See the Sql Language doc

    CREATE PLUGGABLE DATABASE

    To specify the <span class="codeinlineitalic">create_pdb_clone</span> clause:
    
      If <span class="codeinlineitalic">src_pdb_name</span> refers to a PDB in the same CDB, then you must have the CREATE PLUGGABLE DATABASEsystem privilege in the root of the CDB in which the new PDB will be created and in the PDB being cloned.
      If <span class="codeinlineitalic">src_pdb_name</span> refers to a PDB in a remote database or a non-CDB, then you must have the CREATE PLUGGABLE DATABASE system privilege in the root of the CDB in which the new PDB will be created and the remote user must have the CREATE PLUGGABLE DATABASE system privilege in the PDB or non-CDB to which <span class="codeinlineitalic">src_pdb_name</span> refers.
    
     

    I only copied the notes for the 'clone' clause but the privilege is needed to create any pluggable database.

     and I want that user to be able to query the status of all PDBs.

    Then you need to alter that user to specify the appropriate container objects they should have access to.

    What you have tried might be fine for 11g and before but Oracle 12c does even some very basic things VERY differently if you are using the multitenant architecture.

    Bear with me - this could get painful.

    For Oracle 12c it is MANDATORY that developers RTFM! Note - this is NOT the usual 'look it up yourself' recommendation.

    Since you actually have created a proper common user you have clearly picked that up somewhere. That common vs local user thing is just ONE of MANY significant differences between 12c and previous versions.

    If you haven't yet done so  you need to read and understand chapters 17 and 18 of the Concepts doc about the new multitenant architecture of 12c.

    http://docs.oracle.com/cd/E16655_01/server.121/e17633/part_consol.htm#CHDGDBHJ

    Those chapters introduce you to the new architecture and BEGIN (and I stress the word 'begin') the process of enlightenment about the new differences. They talk about some of the differences between common and local users.

    Next visit would be to the Database Reference section 'About Dynamic Performance Views'.

    About Dynamic Performance Views

    I'll just quote the first section:

    V$ Views

    The actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. Database administrators and other users should access only the V$ objects, not the V_$ objects.
    
    The dynamic performance views are used by Oracle Enterprise Manager, which is the primary interface for accessing information about system performance. After an instance is started, the V$ views that read from memory are accessible. Views that read data from disk require that the database be mounted, and some require that the database be open.
    
    V$ views are CONTAINER_DATA objects. When a user connected to the root queries a V$ view, the query results will depend on the CONTAINER_DATA attribute for users for the view. The CONTAINER_DATA clause of the SQL ALTER USER statement is used to set and modify users' CONTAINER_DATA attribute.
     

    See the first sentence of that last paragraph? THAT is your  issue..

    V$ views are CONTAINER_DATA objects. You have NOT altered that user to set the CONTAINER_DATA attribute appropriately.

    Query the DBA_CONTAINER_DATA view as sys to see a sample of what you have now.

    DBA_CONTAINER_DATA

    DBA_CONTAINER_DATA displays default (user-level) and object-specific CONTAINER_DATA attributes for container data objects.
    
    Objects created with the CONTAINER_DATA clause include CONTAINER_DATA attributes.
     

    Next we visit the Security Guide for an example of altering a user

    Managing Security for Oracle Database Users

    Example 2-4 shows how to use the ALTER USER statement to alter the common user c##hr_admin so that this user can access the container data objects in the EM_DB and HR_DB PDBs when connected to the root.
    Example 2-4 Altering a User Account
    CONNECT [email protected]  Enter password: password  Connected.    ALTER USER c##hr_admin  DEFAULT TABLESPACE data_ts  TEMPORARY TABLESPACE temp_ts  QUOTA 100M ON data_ts  QUOTA 0 ON test_ts  SET CONTAINER_DATA = (EMP_DB, HR_DB) FOR V$SESSION CONTAINER = CURRENT;  

    The ALTER USER statement here changes the security settings for the user c##hr_admin as follows:

    •   DEFAULT TABLESPACE and TEMPORARY TABLESPACE are set explicitly to data_ts and temp_ts, respectively.
    •   QUOTA 100M gives the data_tstablespace 100 MB.
    •   QUOTA 0 revokes the quota on the test_tstablespace.
    •   SET CONTAINER_DATA enables user c##hr_admin to have access to data related to the EMP_DB and HR_DB PDBs as well as the root when he queries the V$SESSION view from the root.

    See that last bullet about that SET CONTAINER_DATA clause and how it enables access to two PDBs AND the V$SESSION view from the root?

    Finally we get to the ALTER USER statement in the Sql Language doc

    ALTER USER

    Again - I will only quote PART of the relevant section

    SET CONTAINER_DATA Use this clause to set the default CONTAINER_DATA attribute or an object-specific CONTAINER_DATA attribute for a common user. When you specify this clause, you replace the existing value, if any, of the CONTAINER_DATA attribute.
    Use <span class="codeinlineitalic">container_name</span> to specify one or more containers that will be accessible to the user.
    Use ALL to specify that all current and future containers in the CDB will be accessible to the user.
    Use DEFAULT to specify the default behavior, which is as follows:
    
      For a default CONTAINER_DATAattribute, the current container, that is, the root, and the CDB as a whole will be accessible to the user.
      For an object-specific CONTAINER_DATA attribute, the database will use the user's default CONTAINER_DATA attribute.
    
    Note:
    CONTAINER_DATA attributes that are set to
    DEFAULT are not visible in the
    DBA_CONTAINER_DATA view.
    
     

    Wasn't that fun!

    The net result of all of that is this (not recommended) way of being able to see those rows you want to see.

    alter user c##cdb_man SET CONTAINER_DATA = all CONTAINER = CURRENT

    You SHOULD NOT use such a statement in production. Instead of the word 'ALL' you should specify the minimum objects actually needed for that user.

    Maybe now you see why the first response many will get in this forum will be RTFM. Because if those docs mentioned above haven't yet been reviewed there is just no way at all to answer a question like yours by just providing a query like the above ALTER statement. It just won't make any sense at all.

This discussion has been closed.