Let's see an example and then we guess what privilege we need. Please note that, CREATE INDEX is not a valid privilege, but CREATE ANY INDEX is. In the above section, we have granted CREATE TABLE to THOMAS, which enables Thomas the ability to CREATE INDEX in his schema. SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id) SQL> grant references on hr.employees to thomas We should grant the privilege to him either by HR or privileged users. The right privilege to reference other's data is not SELECT, it's REFERENCES. SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id) Īlter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id)
SQL> grant select on hr.employees to thomas User Thomas wants to add a constraint so as to make a reference to another user's data, so we grant SELECT on that table to THOMAS by instinct. That's why there's no such GRANT ALTER TABLE TO user.
Please note that, ALTER TABLE is not a privilege, but ALTER ANY TABLE is. So the cause of ORA-01031 in ALTER TABLE is not obvious like we thought. Implicitly, he also has the right to ALTER TABLE on schema-level. In the above section, we have granted CREATE TABLE to THOMAS.
DIGIKAM APPLYING RECURSIVE TAG UPDATE
That is to say, not only SELECT, but also INSERT, UPDATE or DELETE privilege you should have to manipulate tables owned by other users. SQL> grant insert,update,delete on hr.t1 to sh Īs we can see, the grantor grants 3 object privileges INSERT, UPDATE and DELETE on the table to the grantee at a time.Ĭommit complete. You may ask for DBA or the object owner to grant the privilege to you. Solution to ORA-01031Ĭlearly, the right privilege is INSERT, UPDATE or DELETE at object-level.
This is because you lack INSERT, UPDATE or DELETE privilege to modify on that table which is usually owned by others. For example, INSERT INTO some data like this: SQL> insert into hr.t1 values (4) SQL> conn sh/shīut you may not have the right to modify the table. You may have the right to select other's table. If you use EXECUTE IMMEDIATE to run CREATE TABLE in a stored procedure, you may check ORA-01031 in EXECUTE IMMEDIATE section in this post. SQL> create table test1 (id number, e_id number) The solution is simple, just grant him CREATE TABLE, a schema-based privilege or CREATE ANY TABLE, a system-wide privilege. Immediately, ORA-01031: insufficient privileges shows up, which tells THOMAS he doesn't have the right privilege to do that. C:\Users\edchen>sqlplus create table test1 (id number, e_id number) Ĭreate table test1 (id number, e_id number) Let's see what will happen if the new user wants to create a table. SQL> create user thomas identified by thomas Īs you can see, a new user THOMAS is created, but we only grant CREATE SESSION to him, which allows him to connect to the database. ORA-01031 is very common when a new user wants to create a table. You may click whichever situation you encountered. There're several error patterns of ORA-01031 in this post. So I can only do my best to collect cases for you. Since this error can be seen almost in every kind of SQL statement, sometimes you would never know what privilege you lack. ORA-01031: Insufficient Privileges means that the current user did not use the right privilege to process the SQL statement.
0 kommentar(er)
