Forum Stats

  • 3,854,198 Users
  • 2,264,340 Discussions
  • 7,905,604 Comments

Discussions

ALTER SESSION SET CONTAINER can only be executed as sysdba ?

Orna Weisman
Orna Weisman Member Posts: 214 Blue Ribbon
edited Jul 23, 2013 11:17AM in Multitenant

    Hi

I createded a common user c##oracle ( identified externally.

Igranted it dba,cdb_dba,set container  - but still I cannot set the container to my pluggable database using that user :

sqlplus /

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 19 15:03:21 2013

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

Last Successful login time: Fri Jul 19 2013 14:58:54 +00: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> ALTER SESSION SET CONTAINER = CDB$ROOT;

Session altered.

SQL> alter session set container=edwt;
ERROR:
ORA-01031: insufficient privileges

only a sysdba it works :

sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 19 15:11:03 2013

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


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> alter session set container=edwt;

Session altered.

SQL>

What privilege do I need to give my common user so I can set hte container to a pluggable database ?

Answers

  • Orna Weisman
    Orna Weisman Member Posts: 214 Blue Ribbon

    I want to add that even with a regular common user ( not identified externally ) I get the same error

  • Orna Weisman
    Orna Weisman Member Posts: 214 Blue Ribbon

    Found the problem.

    I needed to grant the dba role as 'common '

    grant dba to c##oracle container=all;

    then the alter session works from c##oracle

  • Hi Orna,

    Yes, you've found that you need to have the correct privilege in every container you wish to start a session in. In Oracle Database 12c we introduce a new privilege called "set container". This is probably part of the dba role, which is why you found that by granting dba to your common user you succeeded. I hope this helps. For more information please consult the Multitenant White Paper, available here. http://www.oracle.com/technetwork/database/multitenant-wp-12c-1949736.pdf

    Regards, Patrick

  • Orna Weisman
    Orna Weisman Member Posts: 214 Blue Ribbon

    Actually , my user always had the DBA role.

    the confusing thing was that the way I granted the role :

    grant dba to c##oracle

    was NOT correct.

    Only when I granted it that way :

    grant dba to c##oracle container=all;

    the alter session was successfull.

  • Bryn.Llewellyn-Oracle
    Bryn.Llewellyn-Oracle Member Posts: 7 Green Ribbon

    You didn't say exactly what you wanted to achieve. To show you what I mean, suppose that I wanted to create a brand new common user that could connect ONLY to pdb1 and, from there, set container ONLY to pdb2. Assmuing that I already created a suitably privilaged admin user  c##Admin (see below on "with admin option"), then here's how I do it. I'm using Easy Connect where my CDB is called cdb1 and the listener is on a node called HoL (listening on port 1521).

    ------------------------------------------------------------
    CONNECT c##Admin/[email protected]/cdb1
    create user c##Test identified by p container = all
    /
    alter session set container = pdb1
    /
    grant Create Session to c##Test
    /
    alter session set container = pdb2
    /
    grant Set Container to c##Test
    /
    CONNECT c##Test/[email protected]/pdb1
    alter session set container = pdb2
    /
    ------------------------------------------------------------
    

    Notice that I don't have to grant either the Create Session or the Set Container privileges commonly. However, if I want to do the grant commonly (i.e. use the "container = all" syntax) then I must issue the "grant" statement when my current container is CDB$Root. (It's illegal anywhere else.)

    All the usual rules about "with admin option" for system privileges and "with grant option" for bject privileges apply here too.

    All that's new is the "container = all" syntax and the mandate to have CDB$Root as rthe current container if you want to use it.

    Bryn.Llewellyn-Oracle
  • ManojNair
    ManojNair Member Posts: 25

    Hi,

    While giving grand , you should provide container=ALL.

    regards,

    manoj.V

This discussion has been closed.