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?
A.
The command executes successfully and creates the table in the USERS tablespace.
B.
The command executes successfully and creates the table in the PURCHASE_SPACE tablespace.
C.
The command produces an error because the user does not have the privilege to createthe table.
D.
The command produces an error because the user does not have quota in the PURCHASE_SPACE tablespace.
Why do you know that user has privileges on this tablespace?
Because RESOURCE role is assigned. The RESOURCE role has UNLIMITED TABLESPACE privilege in it and this means a user with this role assigned can create tables in any tablespace. This makes assigning the RESOURCE role to general end user accounts a bad idea and probably why Oracle plan to remove this role in upcoming releases. Best practice is to create custom roles with only the privileges necessary assigned to them.
Role Resource give you this privs.
‘Resource’ includes the ‘UNLIMITED TABLESPACE’ system priv.
I think user does not have privilege ‘Create table’. Am I right?
ok. I can see. Role resource provides the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE.
SQL> CREATE USER user1 IDENTIFIED by oracle;
User created.
SQL> GRANT CONNECT, RESOURCE TO user1;
Grant succeeded.
SQL> CONN user1/oracle
Connected.
SQL> SELECT * FROM SESSION_PRIVS;
PRIVILEGE
—————————————-
CREATE SESSION
**UNLIMITED TABLESPACE**
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
10 rows selected.