nerdexam
Oracle

1Z0-052 · Question #91

View the Exhibit and examine the user information. The user has been granted CONNECT and RESOURCE roles and no individual system privileges. The SL_REP user executes this command to create a table…

The correct answer is B. The command executes successfully and creates the table in the PURCHASE_SPACE tablespace. In Oracle 11g the RESOURCE role includes both CREATE TABLE privilege and an implicit UNLIMITED TABLESPACE grant, so the command succeeds and creates the table in the specified PURCHASE_SPACE tablespace.

Administering User Security

Question

View the Exhibit and examine the user information. The user has been granted CONNECT and RESOURCE roles and no individual system privileges. The SL_REP user executes this command to create a table:

SQL> CREATE TABLE orders ( oid number(6), odate date, ccode number(4), oamt number(10,2) ) TABLESPACE purchase_space; The PURCHASE_SPACE tablespace already exists in the database. Which statement describes the effect of the command?

Exhibit

1Z0-052 question #91 exhibit

Options

  • AThe command executes successfully and creates the table in the USERS tablespace.
  • BThe command executes successfully and creates the table in the PURCHASE_SPACE tablespace.
  • CThe command produces an error because the user does not have the privilege to createthe table.
  • DThe command produces an error because the user does not have quota in the PURCHASE_SPACE

How the community answered

(46 responses)
  • A
    4% (2)
  • B
    76% (35)
  • C
    13% (6)
  • D
    7% (3)

Why each option

In Oracle 11g the RESOURCE role includes both CREATE TABLE privilege and an implicit UNLIMITED TABLESPACE grant, so the command succeeds and creates the table in the specified PURCHASE_SPACE tablespace.

AThe command executes successfully and creates the table in the USERS tablespace.

The CREATE TABLE statement explicitly specifies TABLESPACE purchase_space, so Oracle uses that tablespace rather than the user's default USERS tablespace.

BThe command executes successfully and creates the table in the PURCHASE_SPACE tablespace.Correct

The RESOURCE role includes the CREATE TABLE system privilege, satisfying the object creation requirement. In Oracle 11g, granting RESOURCE also implicitly grants the UNLIMITED TABLESPACE system privilege, which provides quota on every tablespace including PURCHASE_SPACE, so the explicit TABLESPACE clause is honored and the table is created there without error.

CThe command produces an error because the user does not have the privilege to createthe table.

The RESOURCE role grants CREATE TABLE as one of its included privileges, so the user has sufficient authorization to create the table.

DThe command produces an error because the user does not have quota in the PURCHASE_SPACE

In Oracle 11g, the RESOURCE role implicitly grants UNLIMITED TABLESPACE, giving the user effective quota on all tablespaces including PURCHASE_SPACE, so no quota violation occurs.

Concept tested: Oracle RESOURCE role and implicit UNLIMITED TABLESPACE privilege

Source: https://docs.oracle.com/cd/E11882_01/network.112/e36292/admusers.htm

Topics

#RESOURCE role#tablespace quota#CREATE TABLE privilege#user roles

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-052 Practice