Thanks for the question, Arun.
Asked: March 01, 2017 - 11:53 am UTC
Last updated: March 02, 2017 - 4:59 am UTC
Version: 12c
Viewed 1000+ times
You Asked
Hi,
I have given the below privileges to the newly created user(mydbauser) in Oracle 12c, but still the user cannot create constraint in a column to reference another table in create table operation and says insufficient privileges.
EXECUTE IMMEDIATE 'CREATE USER '||P_USER_NAME||' IDENTIFIED BY '||P_PASSWORD;
EXECUTE IMMEDIATE 'ALTER USER '||P_USER_NAME||' QUOTA 300M ON USERS';
EXECUTE IMMEDIATE 'GRANT CONNECT,RESOURCE TO '||P_USER_NAME;
EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES TO '||P_USER_NAME;
EXECUTE IMMEDIATE 'GRANT DBA TO '||P_USER_NAME||' WITH ADMIN OPTION';
I want the user to do anything.
I got insufficient privilege error in the below operation,
CREATE TABLE mydbauser.employee_salaries (
employee_id NUMBER NOT NULL,
change_date DATE NOT NULL,
salary NUMBER(8,2) NOT NULL,
CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
REFERENCES OE.employees (employee_id)
ON DELETE CASCADE)
/
OP:
Error report -
ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
and Connor said...
There isn't a system level 'references' privilege. It needs to be granted on the object itself. Not even sysdba has that global ability
SQL> conn sys/admin as sysdba
Connected.
SQL> CREATE TABLE employee_salaries (
2 employee_id NUMBER NOT NULL,
3 change_date DATE NOT NULL,
4 salary NUMBER(8,2) NOT NULL,
5 CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
6 CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
7 REFERENCES scott.emp (empno)
8 ON DELETE CASCADE)
9 /
REFERENCES scott.emp (empno)
*
ERROR at line 7:
ORA-01031: insufficient privileges
You could do something to cycle through your tables, eg
SQL> begin
2 EXECUTE IMMEDIATE 'CREATE USER demo IDENTIFIED BY demo';
3 EXECUTE IMMEDIATE 'ALTER USER demo QUOTA 300M ON USERS';
4 EXECUTE IMMEDIATE 'GRANT CONNECT,RESOURCE TO demo';
5 EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES TO demo';
6 EXECUTE IMMEDIATE 'GRANT DBA TO demo WITH ADMIN OPTION';
7
8 for i in ( select table_name from dba_tables where owner = 'SCOTT' ) loop
9 execute immediate 'grant references on scott.'||i.table_name||' to demo';
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> conn demo/demo
Connected.
SQL> CREATE TABLE employee_salaries (
2 employee_id NUMBER NOT NULL,
3 change_date DATE NOT NULL,
4 salary NUMBER(8,2) NOT NULL,
5 CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
6 CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
7 REFERENCES scott.emp (empno)
8 ON DELETE CASCADE)
9 /
Table created.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment