7 Replies Latest reply on Feb 26, 2019 9:53 AM by Khalid Kizhakkethil

    Issue in grants

    966638

      Hi All,

       

      I have CDB/PDB database and its on cloud.

       

      I am facing issue in grants. Please suggest any solution

       

      Connected to PDS and users exist in PDB and all steps performed from PDB

       

      users : TESTA, TESTB

       

      1-Conn TESTA

         a - created table in TESTPRIV

       

      2- Conn system

         a - create role TESTPR

         b- create public synonym TESTPRIV for TESTA.TESTPRIV

         c - grant select on TESTA.TESTPRIV to TESTPR

         d- grant TESTPR to TESTB

       

      3-Conn TESTB

        a- select * from TESTPRIV -  > ORA-00942: table or view does not exist

        b - select * from TESTA.TESTPRIV ->  ORA-00942: table or view does not exist

        • 1. Re: Issue in grants
          Khalid Kizhakkethil

          Hi,

           

          I just tried the same and able to query the table. Only I just granted create session priv to testb user.

           

          [oracle@arb02dbadm02 ~]$ sqlplus testb/testb@radev

          SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 25 11:35:24 2019

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


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

          SQL> select * from  TESTA.TESTPRIV;

          no rows selected

          SQL>

           

          Regards,

          Khalid

          • 2. Re: Issue in grants
            Khalid Kizhakkethil

            Additional priv which I granted is only create session priv to testb user.

            • 3. Re: Issue in grants
              Khalid Kizhakkethil

              Please find below steps which I performed:

               

              SQL> alter session set container=RADEV;

              Session altered.

              SQL> create user TESTA identified by TESTA;

              User created.

              SQL> grant create table to TESTA;

              Grant succeeded.


              SQL> create table TESTA.TESTPRIV (a number);

              Table created.

               

              SQL> grant create session to testa;

              Grant succeeded.

              SQL> create user testb identified by testb;

              User created.


              SQL>  create role TESTPR
                2  ;

              Role created.

              SQL>  grant select on TESTA.TESTPRIV to TESTPR;

              Grant succeeded.

              SQL> grant TESTPR to TESTB;

              Grant succeeded.

              SQL> grant create session to testb;

              Grant succeeded.


              SQL> insert into TESTA.TESTPRIV values (10);
              insert into TESTA.TESTPRIV values (10)
                                *
              ERROR at line 1:
              ORA-01950: no privileges on tablespace 'USERS'


              SQL> alter user testa quota unlimited on users;

              User altered.

              SQL>  insert into TESTA.TESTPRIV values (10);

              1 row created.

              SQL> commit;

              Commit complete.

              SQL>

               

               

              Verification:

               

              [oracle@arb02dbadm02 ~]$ sqlplus testb/testb@radev

              SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 25 11:43:01 2019

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

              Last Successful login time: Mon Feb 25 2019 11:35:25 +03:00

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

              SQL> select * from  TESTA.TESTPRIV;

                       A
              ----------
                      10

              SQL> select * from TESTPRIV;

                       A
              ----------
                      10

              SQL>

              • 4. Re: Issue in grants
                966638

                Thanks  a lot. Its working now

                • 5. Re: Issue in grants
                  Khalid Kizhakkethil

                  Could you please put my answer as correct and helpful.

                  • 6. Re: Issue in grants
                    L. Fernigrini

                    Remember to read Community Posting Etiquette

                     

                    Don't beg for points or pressure members into marking your response correct or helpful

                    Asking for positive responses to your posts in an effort to elevate your profile status can be construed as "point begging" and will not be permitted.

                    • 7. Re: Issue in grants
                      Khalid Kizhakkethil

                      Understood and will take care.