1

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;
user10518
  • 117
  • 2
  • 11
  • Hope that after giving resource grant to the user, you have relogin with the user. Means disconnect and connect the user and try creating the table. Also, grant resource should be executed from sys user. – Popeye Oct 21 '19 at 03:49
  • You are right, I need to reconnect after granting resource, but not for granting create table. Thank you! – user10518 Oct 21 '19 at 03:58

1 Answers1

0

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!!

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • If this answer resolved your issue then accept it so that this question is marked as resolved. – Popeye Oct 21 '19 at 05:04