Stephen Yang, July 13, 2016 - 9:44 am UTC
Stephen Yang, July 13, 2016 - 9:54 am UTC
thanks so much for your prompt response.
but I had granted 'alter any index to system'. it still did not work.
about rebuilding index, I only do that when the index size is large than table size. but I am under great pressure from project team. they ask me to rebuild indexes weekly. it is a shame for me to knuckle under.
I will try to create my owner user to do a test.
July 13, 2016 - 12:24 pm UTC
Point the project team to the links above ;)
Concept of Least Privilege
Rajeshwaran, Jeyabal, July 13, 2016 - 12:57 pm UTC
SQL> grant alter any index to chris;
Grant succeeded.
SQL> conn chris/xxxx
Connected.
SQL> exec p;
PL/SQL procedure successfully completed.
Once we grant 'ALTER ANY INDEX' to "chris" then "chris" gains the privilege to rebuild any index in all schema.
why not just grant the execute privilege on that procedure "P" to user "chirs" - that way user "chris" has only the privilege to execute the procedure ( and that is supposed to do , what is put inside it).
demo@ORA11G> create table t as select * from all_users;
Table created.
demo@ORA11G> create index t_idx on t(user_id);
Index created.
demo@ORA11G> create or replace procedure rebuild_index as
2 begin
3 execute immediate ' alter index t_idx rebuild ';
4 end;
5 /
Procedure created.
demo@ORA11G> grant execute on rebuild_index to scott;
Grant succeeded.
demo@ORA11G> conn scott/tiger@ora11g
Connected.
scott@ORA11G> alter index demo.t_idx rebuild ;
alter index demo.t_idx rebuild
*
ERROR at line 1:
ORA-01031: insufficient privileges
scott@ORA11G> exec demo.rebuild_index;
PL/SQL procedure successfully completed.
scott@ORA11G>
July 13, 2016 - 1:58 pm UTC
Once we grant 'ALTER ANY INDEX' to "chris" then "chris" gains the privilege to rebuild any index in all schema.
Yes. The poster was running this in SYSTEM, so I'm guessing that's what they wanted to be able to do!
For only granting privileges on the procedure to work, the procedure must be in the same schema as the index. Admittedly this is a better approach than using system!
it didn't work for the owner itself in Oracle 12c
Martin Goebbels, May 04, 2022 - 7:41 pm UTC
the mission was to create a table using a procedure
CREATE or replace PROCEDURE MYTEST
IS
strSQL varchar2(2000);
BEGIN
strSQL := 'CREATE TABLE X (X1 VARCHAR2(10), X2 NUMBER)';
execute immediate strSQL ;
END MYTEST;
BEGIN
MYTEST;
END;
Error report -
ORA-01031: insufficient privileges
ORA-06512: at "MG67610.MYTEST", line 7
ORA-06512: at line 2
01031. 00000 - "insufficient privileges"
the resulrt was the same insuficient privileges, although the user having create table rights and being the owner
PRIVILEGE
----------------------------------------
SET CONTAINER
CREATE TYPE
CREATE TRIGGER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE VIEW
CREATE SYNONYM
CREATE TABLE
CREATE SESSION
May 09, 2022 - 2:33 pm UTC
How is the CREATE TABLE privilege gained? The user needs to be granted the privilege directly, not via a role.
ORA-01031: insufficient privileges
TAJAY, September 04, 2022 - 7:44 pm UTC
SQL> CREATE TABLE employees
2 ( employee_id number(10) NOT NULL,
3 employee_name varchar2(50) NOT NULL,
4 city varchar2(50),
5 CONSTRAINT employees_pk PRIMARY KEY (employee_id)
6 );
CREATE TABLE employees
*
ERROR at line 1:
ORA-01031: insufficient privileges
September 05, 2022 - 5:36 am UTC
grant CREATE TABLE to my_user;
alter user my_user quota 1G on users;
should get you going
HOW TO SET PRIVILEGES TO NEW USER
TAJAY, September 04, 2022 - 7:48 pm UTC
SQL> create table dept(
2 deptno number(2,0),
3 dname varchar2(14),
4 loc varchar2(13),
5 constraint pk_dept primary key (deptno)
6 );
ERROR at line 1:
ORA-01031: insufficient privileges