The difference between role resource in 11g and 12c

Preface

In a 11gR2 environment, if a user is granted both the connect and resource roles, he or she can log in to the database to create objects. But in 12c, if the user is only granted these two roles, the object can be created, but the data cannot be inserted.

Experiment

Let's do a little experiment:

11g environment:

(1) create tablespaces

CREATE TABLESPACE test DATAFILE

'/ u01Applink oracle SIZE SIZE 01.dbf' oradat 5242880

AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO

(2) create users and authorize them

Create user test identified by "test" default tablespace test

Grant resource,connect to test

(3) create objects and insert data

[oracle@bond ~] $sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 18 13:56:26 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

Oracle Database Vault and Real Application Testing options

SQL > create table t_dict as

2 select * from dict where rownum create table t_dict as

2 select * from dict where rownum