This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Dec 3, 2012 9:55 PM by jgarry RSS

doubt about a question

Roger25 Explorer
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    #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 Explorer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Yes. And it was specified at user creation: 'users'. So what's with this?
  • 14. Re: doubt about a question
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points