Unanswered: Insuffiecent Priviliges to create a table
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.
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 ...
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;
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.