1 2 Previous Next 24 Replies Latest reply: Aug 27, 2014 11:46 AM by Mark D Powell RSS

    Insuficient privileges on own schema

    Raphael.Melo

      I will try to explain the situation and hope that someone could help me . Sorry for my bad english.

       

      Db version: 9.2.0.6

      OS: Solaris 10 sparc

       

      I have a production database that has a big issue. All the users I try to create don't have any privileges on his own schema.

      I gave all the permissions and still can't do anything. Older schemas can't do anything in his own objects too.

      The permissions of sys and system are a little mess to. I don't know what happened, but I got this problem, that is too old and I don't have backup.

      I try to exp the schemas as sysdba but something goes wrong and give me errors.

       

      I can migrate the data by creating a database link and doing create table as select. But and the others objects?

      MOS is don't giving me any solution, my SR is open like 3 weeks.

       

      Thanks in advance.

        • 1. Re: Insuficient privileges on own schema
          sol.beach

          Raphael.Melo wrote:

           

          I will try to explain the situation and hope that someone could help me . Sorry for my bad english.

           

          Db version: 9.2.0.6

          OS: Solaris 10 sparc

           

          I have a production database that has a big issue. All the users I try to create don't have any privileges on his own schema.

          I gave all the permissions and still can't do anything. Older schemas can't do anything in his own objects too.

          The permissions of sys and system are a little mess to. I don't know what happened, but I got this problem, that is too old and I don't have backup.

          I try to exp the schemas as sysdba but something goes wrong and give me errors.

           

          I can migrate the data by creating a database link and doing create table as select. But and the others objects?

          MOS is don't giving me any solution, my SR is open like 3 weeks.

           

          Thanks in advance.

           

           

          ERRORS? What Errors?

          I don't see any error.

           

          Is COPY & PASTE broken for you?

          • 2. Re: Insuficient privileges on own schema
            Raphael.Melo

            ERRORS? What Errors?

            I don't see any error.

            Everything I try to do shows error ORA-01031: insufficient privileges

             

            Is COPY & PASTE broken for you?

            Copy & paste? Where? I don't understand your question.

            • 3. Re: Insuficient privileges on own schema
              sybrand_b

              Everything? Please try to be a bit more vague, so we can help you better. Your organization already doesn't seem to care about anything (desupported software for ages, no backup) so why should anyone here care.

               

              Did you run

              select *

              from session_privs;

               

              select *

              from session_roles;

               

              Sybrand Bakker

              Senior Oracle DBA

              • 4. Re: Insuficient privileges on own schema
                Raphael.Melo

                When I say everything, I mean DML, the schema can't do anything in his own objects.

                 

                We have backup, but this issue is too old (about two months ago) and only now we see this error, because this database is used only twice in a year. We only keep the backup by one month.

                 

                All the permissions are right. The problem seems to be in the data dictionary.

                • 5. Re: Insuficient privileges on own schema
                  Mark D Powell

                  Raphael, you need to post actual commands you perform and the responses from Oracle so we have something to see.  Show us your connecting to a user. that an object belongs to the user, and the user getting an error trying to perform a valid action against the object.

                  - -

                  You may wish to verify that the database has no INVALID sys owned objects while you are at it.

                  col object_name format a30

                  select owner, object_name, object_type from dba_objects where status <> 'VALID'

                  - -

                  HTH -- Mark D Powell --

                  • 6. Re: Insuficient privileges on own schema
                    rp0428

                    Raphael.Melo wrote:

                     

                    When I say everything, I mean DML, the schema can't do anything in his own objects.

                    You haven't shown A SINGLE DML STATEMENT that fails. You can't just say 'help - I have a problem'.

                     

                    You have to show us

                    1. WHAT you are doing

                    2. HOW you are doing it

                    3. WHAT results you get

                    4. WHAT results you expected to get.

                    All the permissions are right. The problem seems to be in the data dictionary.

                    Really? Clearly all the permissions are NOT right if this is true:

                    Everything I try to do shows error ORA-01031: insufficient privileges

                    Oracle says that permissions are NOT right for whatever it is you are trying to do.

                     

                    We can NOT see your screen or what you do. If you refuse to post that info then we can't really help you.

                    • 7. Re: Insuficient privileges on own schema
                      Maran Viswarayar

                      I have come across this once but that was on 10.2.0.5 but your version is 9.2 so my case wont apply here

                       

                      My issue was- we upgraded from 10.2.0.4-->10.2.0.5 and suddenly all insufficient privileges - Thats becuase the roles with password were disabled be default in 10.2.0.5 and our access was controlled via db roles.

                       

                      So i am not sure if someone has upgraded your old 9.2 to 10.2.0.5

                       

                      Coming to your question

                       

                      Why cant you restore your old backup in another machine and check the privileges and see what exists in the system

                      • 8. Re: Insuficient privileges on own schema
                        rp0428
                        My issue was- we upgraded from 10.2.0.4-->10.2.0.5 and suddenly all insufficient privileges - Thats becuase the roles with password were disabled be default in 10.2.0.5 and our access was controlled via db roles.

                        That is NOT what OP said their issue was:

                        All the users I try to create don't have any privileges on his own schema.

                        Users don't need privileges or roles for basic operations in their own schema.

                        • 9. Re: Insuficient privileges on own schema
                          sol.beach

                          You don't need English; only SQL

                           

                          17:22:05 SQL> connect / as sysdba

                          Connected.

                          17:22:11 SQL> @noprivs

                          17:22:16 SQL> drop user noprivs cascade;

                           

                           

                          User dropped.

                           

                           

                          17:22:17 SQL> create user noprivs identified by noprivs default tablespace users;

                           

                           

                          User created.

                           

                           

                          17:22:17 SQL> grant create session to noprivs;

                           

                           

                          Grant succeeded.

                           

                           

                          17:22:17 SQL> ALTER USER noprivs    QUOTA 100M ON USERS;

                           

                           

                          User altered.

                           

                           

                          17:22:17 SQL> create table noprivs.fubar(id number);

                           

                           

                          Table created.

                           

                           

                          17:22:17 SQL> insert into noprivs.fubar values(0);

                           

                           

                          1 row created.

                           

                           

                          17:22:17 SQL> commit;

                           

                           

                          Commit complete.

                           

                           

                          17:22:18 SQL> connect noprivs/noprivs

                          Connected.

                          17:22:18 SQL> update fubar set id = 1;

                           

                           

                          1 row updated.

                           

                           

                          17:22:18 SQL> select * from  fubar;

                           

                           

                                  ID

                          ----------

                                   1

                           

                           

                          17:22:18 SQL> drop table fubar;

                           

                           

                          Table dropped.

                           

                           

                          17:22:54 SQL>

                           

                          The only privileged assigned to NOPRIVS  user was CREATE SESSION

                          • 10. Re: Insuficient privileges on own schema
                            Hemant K Chitale

                            > I mean DML, the schema can't do anything in his own objects.

                            Can you show examples, with a demonstration of how you connect to a schema. Also query the data dictionary to demonstrate that the objects are in the same schema.  Are some of the objects synonyms ? Are some plsql procedures / packages ?

                             

                             

                            What is wrong with the SYS and SYSTEM accounts ?

                             

                            Hemant K Chitale

                            • 11. Re: Insuficient privileges on own schema
                              Raphael.Melo

                              DML commands I try to run:

                               

                              SQL> conn fmtrel/fmtrel_prod

                              Connected.

                              SQL> update fmtrel.ge_tipo_dado set in_posicao_simbolo = 1 where id_tipo_dado = 653;

                              update fmtrel.ge_tipo_dado set in_posicao_simbolo = 1 where id_tipo_dado = 653

                              *

                              ERROR at line 1:

                              ORA-01031: insufficient privileges

                               

                               

                              SQL> insert into fmtrel.ge_tipo_dado (id_tipo_dado,no_tipo_dado,ti_entrada_dado,tx_formatador) values (721,'teste','select','br.com.bancoamazonia.fomento.util.formatadores.FormatadorString');

                              insert into fmtrel.ge_tipo_dado (id_tipo_dado,no_tipo_dado,ti_entrada_dado,tx_formatador) values (721,'teste','select','br.com.bancoamazonia.fomento.util.formatadores.FormatadorString')

                              *

                              ERROR at line 1:

                              ORA-01031: insufficient privileges



                              Then I try to create a new user to move the data.


                              SQL> create user teste_priv identified by teste_priv;

                               

                              User created.

                               

                              SQL> grant connect to teste_priv;

                               

                              Grant succeeded.

                               

                              SQL> grant resource to teste_priv;

                               

                              Grant succeeded.

                               

                              SQL> conn teste_priv/teste_priv

                              Connected.

                              SQL> create table teste(t integer);

                              create table teste(t integer)

                              *

                              ERROR at line 1:

                              ORA-01031: insufficient privileges



                              I already try to restore the backup, but the problem persists.

                              • 12. Re: Insuficient privileges on own schema
                                Harmandeep Singh

                                Hope, that below will resolve issue


                                ALTER USER teste_priv DEFAULT ROLE "CONNECT","RESOURCE";


                                https://community.oracle.com/thread/1131723?tstart=0


                                Regards,

                                Harman



                                • 13. Re: Insuficient privileges on own schema
                                  Maran Viswarayar

                                  THats interesting...

                                   

                                  Few things...

                                   

                                  1. login as sysdba and try creating

                                  2. Check the user has quota on user tablespace

                                  3. SELECT TABLESPACE_NAME, STATUS  FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';

                                  4. select privilege from role_sys_privs where role in ('CONNECT', 'RESOURCE');

                                  5..select guard_status from v$database; AdamMartin

                                  • 14. Re: Insuficient privileges on own schema
                                    sybrand_b

                                    Your statements show you are updating and inserting in different schema < refer to fml.<table name>

                                     

                                    Also the CONNECT and RESOURCE roles have been scaled down by Oracle.

                                    You need to verify this by issuing

                                    select * from session_privs

                                    from the affected accounts, and which I recommended before, and which advise you ignored.

                                    You will see you  don't have the required privileges.

                                     

                                     


                                    Sybrand Bakker

                                    Senior Oracle DBA

                                    1 2 Previous Next