1 2 Previous Next 19 Replies Latest reply: Dec 3, 2012 11:55 PM by jgarry RSS

    doubt about a question

    Roger25
      Hi,
      Recently, Oracle University posted on his Facebook's wall a question:
      You created a new user on the database by executing the following command:
      
      SQL> CREATE USER user01 IDENTIFIED BY user01
      DEFAULT TABLESPACE users
      TEMPORARY TABLESPACE temp;
      
      Then you granted the following privileges to user01
      by executing the following command:
      
      SQL> GRANT CREATE SESSION, CREATE TABLE TO user01;
      
      Which statement is true in this scenario?
      
      1. The user can connect to the database
      2. The user will be able to create a table
      3. The user will be able to query tables only
      4. The user will not be able to create a table if
      DEFAULT_PERMANENT_TABLESPACE is not set
      After many comments with possible correct answers (most of people said 1 and 2), Oracle University replied: Many conflicting opinions here - the correct answer is #4 - The user will not be able to create a table if DEFAULT_PERMANENT_TABLESPACE is not set.

      My question is.. WHY 4? Why not 1? Because the user can connect to the database. Also 2 is correct! It can create a table, but having no quota assigned, he cannot insert data into the table. So why 1 and 2 are not correct?
        • 1. Re: doubt about a question
          Osama_Mustafa
          No the user will not be able to create table WHY !!!

          0 You give privilege but with out quota on that table Because of this the user will be able to connect but without create any table since Quota not specified .
          • 2. Re: doubt about a question
            Aman....
            Roger22 wrote:
            Hi,
            Recently, Oracle University posted on his Facebook's wall a question:
            You created a new user on the database by executing the following command:
            
            SQL> CREATE USER user01 IDENTIFIED BY user01
            DEFAULT TABLESPACE users
            TEMPORARY TABLESPACE temp;
            
            Then you granted the following privileges to user01
            by executing the following command:
            
            SQL> GRANT CREATE SESSION, CREATE TABLE TO user01;
            
            Which statement is true in this scenario?
            
            1. The user can connect to the database
            2. The user will be able to create a table
            3. The user will be able to query tables only
            4. The user will not be able to create a table if
            DEFAULT_PERMANENT_TABLESPACE is not set
            After many comments with possible correct answers (most of people said 1 and 2), Oracle University replied: Many conflicting opinions here - the correct answer is #4 - The user will not be able to create a table if DEFAULT_PERMANENT_TABLESPACE is not set.

            My question is.. WHY 4? Why not 1? Because the user can connect to the database. Also 2 is correct! It can create a table, but having no quota assigned, he cannot insert data into the table. So why 1 and 2 are not correct?
            Not sure why but they are the correct choices. The user, with the create session and create table, can connect to the database and also can create a table but won't have any segment created to it if the version is 11g.

            Aman....
            • 3. Re: doubt about a question
              JustinCave
              Can you post a link to the discussion in question?

              1 is obviously true
              If we're talking about a default 11g database where deferred segment creation is enabled (deferred_segment_creation = true), 2 is true. If we're dealing with an earlier version or deferred segment creation is disabled, the user won't be able to create a table because the user has no quota on any tablespace. If this is an 11g database with deferred segment creation enabled, the user will be able to create a table but will not be able to insert any data.
              3 is false assuming that 2 is true
              4 is false. default_permanent_tablespace is irrelevant since the default tablespace was specified explicitly when the user was created.

              Justin
              • 4. Re: doubt about a question
                Fran
                go to test it (oracle version 11.2.0.1 on windows):

                option 1:
                SQL> CREATE USER user01 IDENTIFIED BY user01
                  2  DEFAULT TABLESPACE users
                  3  TEMPORARY TABLESPACE temp;
                
                Usuario creado.
                
                SQL> conn user01/user01
                ERROR:
                ORA-01045: user USER01 lacks CREATE SESSION privilege; logon denied
                
                
                Advertencia: íYa no estß conectado a ORACLE!
                no rol connect, the user can't connect to the database.

                Option 2
                2. The user will be able to create a table
                the user can't connect to the database so he can't create anything.

                If he has the rol connect-:
                SQL> conn / as sysdba
                Conectado.
                SQL> grant connect to user01;
                
                Concesi¾n terminada correctamente.
                
                SQL> conn user01/user01
                Conectado.
                SQL> create table test(id number, name varchar2(25));
                create table test(id number, name varchar2(25))
                *
                ERROR en lÝnea 1:
                ORA-01031: privilegios insuficientes
                Still not enough privileges.

                Option 3
                3. The user will be able to query tables only
                the user must have "grant select table XXXXX" , to query all tables you must grant select on all tables, something similar like this:

                FOR x IN (SELECT * FROM user_tables)
                LOOP
                EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO <<someone>>';
                END LOOP;

                *remember that with this user creation query, the user can't connect, so he also can't select anything.

                Edited by: Fran on 03-dic-2012 0:23
                • 5. Re: doubt about a question
                  Roger25
                  This is confusing for me too.
                  The database is 11g.

                  http://www.facebook.com/oracleedu/posts/10151158880402169

                  I don't know who is this 'Oracle University' on facebook but i assume that someone from OU

                  Edited by: Roger22 on 03.12.2012 10:22
                  • 6. Re: doubt about a question
                    Samuel G. Cristobal
                    #1 Correct, User can connect,

                    #2 depends oracle version, only in oracle =>11g
                    we have deferred segment creation(no segment is created, no initial extent
                    is allocated, no storage is reserved)
                    you can create a table, with no quota on ts, without errors.
                    for insert data you need quota, as you said.

                    regards,
                    sgc
                    • 7. Re: doubt about a question
                      Roger25
                      I've tested it at home (now i'm at work), and i successfully created a table with user01
                      Oracle Database 11.2.0.3

                      Well but why OU says the answer is 4?
                      • 8. Re: doubt about a question
                        Osama_Mustafa
                        Check this
                        sqlplus / as sysdba
                        SQL> select name from v$tablespace ;
                        
                        NAME
                        ------------------------------
                        SYSTEM
                        SYSAUX
                        UNDOTBS1
                        USERS
                        TEMP
                        
                        
                        5 rows selected.
                        
                        
                        SQL> CREATE USER user01 IDENTIFIED BY user01
                          2  DEFAULT TABLESPACE users
                          3  TEMPORARY TABLESPACE temp;
                        
                        User created.
                        
                        SQL> GRANT CREATE SESSION, CREATE TABLE TO user01;
                        
                        Grant succeeded.
                        
                        
                        SQL> conn user01/user01
                        Connected.
                        
                        SQL> create table test (id number(1)) ;
                        create table test (id number(1))
                        *
                        ERROR at line 1:
                        ORA-01950: no privileges on tablespace 'USERS'
                        • 9. Re: doubt about a question
                          Fran
                          Option 4:
                          SQL> select property_value from database_properties where property_name='DEFAULT
                          _PERMANENT_TABLESPACE';
                          
                          PROPERTY_VALUE
                          --------------------------------------------------------------------------------
                          
                          USERS
                          If you tried to change to null:
                          SQL> ALTER DATABASE DEFAULT TABLESPACE '';
                          ALTER DATABASE DEFAULT TABLESPACE ''
                                                            *
                          ERROR en lÝnea 1:
                          ORA-02216: nombre de tablespace esperado
                          Default tablespace must be set.
                          • 10. Re: doubt about a question
                            Osama_Mustafa
                            But after the below change
                            SQL> conn / as sysdba
                            Connected.
                            SQL>
                            SQL>
                            SQL> alter user user01 QUOTA  100M ON USERS ;
                            
                            User altered.
                            
                            SQL>
                            SQL>
                            SQL> conn user01/user01 ;
                            Connected.
                            SQL> create table test (id number);
                            
                            Table created.
                            • 11. Re: doubt about a question
                              Roger25
                              What do you want to say with this?
                              Default tablespace was specified explicitly when the user was created!
                              • 12. Re: doubt about a question
                                Osama_Mustafa
                                Roger22 wrote:
                                What do you want to say with this?
                                Default tablespace was specified explicitly when the user was created!
                                Default Tablespace for the users if you are not set it in the create option will be USER TBS
                                • 13. Re: doubt about a question
                                  Roger25
                                  Yes. And it was specified at user creation: 'users'. So what's with this?
                                  • 14. Re: doubt about a question
                                    Osama_Mustafa
                                    Also it will be on USER TBS , you can use Default Tablespace Attribute if you want to change to default tablespace , in the above example its called demonstration to explain everything in case users or traniee doesn't know what is the default tablespace is
                                    1 2 Previous Next