Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    4

    Unanswered: Insuffiecent Priviliges to create a table

    Hello,

    I've just recently installed Oracle 10G on a Gentoo Linux box. The install seems to have gove fine, but now I'm having trouble creating a table.

    After the install I opened Enterprise Manager and added a user named oracletest and assigned to it the default tablespace EXAMPLES which I think is the sample tables created by the installer. I clicked on the iSQL*Plus link on the EM main window, logged in as my user and issued this command:

    create table foo(id varchar2(20));

    to which oracle replied:

    ERROR at line 1:
    ORA-01031: insufficient privileges

    which seems straightforward enough and all, but I'm not sure how to fix it. I tried assigning the dba role to oracletest and also added the 'CREATE ANY TABLE' system privilige to the user, but both to not avail. All I want to do is create some sample tables so I can test some sql, but I've hit this dead end. Can anyone point me in the right direction? Thanks in advance.

    -exits

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Earlier Oracle versions had two nice roles - CONNECT and RESOURCE - which were just enough to be granted in order to make an user ready to work with. Those roles are, though, still here for backward compatibility, but have lost their functionality. Oracle also announced that those roles will be deprecated in future Oracle versions.

    Therefore, you'll have to grant all required privileges one by one, or create your own role(s) which would then be granted to all newly created users.

    Here are privileges which were granted to CONNECT and RESOURCE roles in Oracle 8i; try to grant them to your newly created user and see will it be OK. I hope I didn't miss critical ones ...
    Code:
    GRANT ALTER SESSION TO new_user;
    GRANT CREATE CLUSTER TO new_user;
    GRANT CREATE DATABASE LINK TO new_user;
    GRANT CREATE INDEXTYPE TO new_user;
    GRANT CREATE OPERATOR TO new_user;
    GRANT CREATE PROCEDURE TO new_user;
    GRANT CREATE SEQUENCE TO new_user;
    GRANT CREATE SESSION TO new_user;
    GRANT CREATE SYNONYM TO new_user;
    GRANT CREATE TABLE TO new_user;
    GRANT CREATE TRIGGER TO new_user;
    GRANT CREATE TYPE TO new_user;
    GRANT CREATE VIEW TO new_user;

  3. #3
    Join Date
    Nov 2006
    Posts
    4

    Thanks Littlefoot

    You're reply was very helpful. It worked like a charm though there was one additional step required: I had to modify the quota for the new user from 'none' to unlimited for the tablespace in question.

    One further, general question: when I grant priviliges through the EM each privilige has associated with it an "admin option" which can either be selected or not for the user in question. What effect does that have? Thanks again for your helpful response.

    -exits

  4. #4
    Join Date
    May 2006
    Posts
    132
    This should help explain the ADMIN OPTION.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •