[SOLVED] Grant Resource to user to create table

Grant Resource to user to create table

I am trying to give privileges to a db user, and want to allow the user to create tables. Checking on "RESOURCE" privilege, it contains the following:

SELECT privilege FROM dba_sys_privs WHERE grantee = 'RESOURCE';

Resource

CREATE TABLE
CREATE OPERATOR
CREATE TYPE
CREATE CLUSTER
CREATE TRIGGER
CREATE INDEXTYPE
CREATE PROCEDURE
CREATE SEQUENCE

As I noted that CREATE TABLE is included, I assumed it will be possible to grant RESOURCE to my user and the user will be able to create tables in his own schema.

GRANT RESOURCE TO User1;

On User1, I run the following:

    CREATE TABLE testable (
        sessionID varchar2(32 char) not null,
        attributeA varchar(10) ,
        attributeB varchar2(50)
    );

However I get this:

Error: ORA-01031: insufficient privileges

SQLState:  42000
ErrorCode: 1031

If I manually grant "CREATE TABLE" to User1 instead, it works. Why is this so?

GRANT CREATE TABLE TO User1;

Solution

You will need to reconnect with your user once it is given resource grant(from sys user) as your current session can not identify the newly given grant (resource).

Cheers!!