How Oracle authorizes truncatetable to users

This article mainly introduces how Oracle authorizes truncatetable to users. It is very detailed and has a certain reference value. Friends who are interested must read it!

1. Perform trundate table operations on tables under other users

The developer said that there is no permission operation when calling the table under the shop user under dwetl. Google checked it and found that the oracle account could not directly grant truncate permission to a certain table. How to achieve it?

Under shop users, prepare the test data

SQL > create table Z_TRUNCATE_T (ID number); Table created.SQL > insert into Z_TRUNCATE_T select 1 from dual;1 row created.SQL > commit;Commit complete.SQL > select * from Zero Trunk; ID- 1SQL >

2. Rude and unsafe practices

The usual way to assign a truncate is to assign a drop any table directly to a user

SQL > grant drop any table to dwetl;Grant succeeded.SQL > SQL > grant select,insert,delete,update on Z_TRUNCATE_T to dwetl;Grant succeeded.SQL >

When you finish the work, you need to withdraw the permission immediately because the drop any table permission is too large. If you are not careful, it will be deleted by mistake, and it will be too late to cry.

SQL > revoke drop any table from dwetl;Revoke succeeded.SQL > revoke select,insert,delete,update on shop.PLAN6_TEMPLET_NODE_EDIT from dwetl;Revoke succeeded.SQL >

3. Safer practices

Create a stored procedure p_truncate, execute truncate table Zero truncated stored T in the stored procedure, and then give another user, dwetl, permission to execute the stored procedure.

The stored procedure p_truncate is as follows:

Create or replace procedure p_truncate as begin execute immediate 'truncate table Zhuan truncated Testament; end

Establish a stored procedure:

SQL > create or replace procedure p_truncate as beginexecute immediate 'truncate table Zero truncated Tests; 4 end; 5 / Procedure created.SQL >

Give the execution authority of the stored procedure to dwetl, and the permission to add, delete, modify and query the table, because after truncate, it is basically followed by insert, update and delete.

SQL > grant execute on p_truncate to dwetl;Grant succeeded.SQL > SQL > grant select,insert,delete,update on Z_TRUNCATE_T to dwetl;Grant succeeded.SQL >

Log in through the dwetl account, execute the stored procedure to check the effect, and see that the table Z _ TRUNCATE_T under the shop user has been emptied, ok, which also proves that this scheme is feasible through the stored procedure, and the table under other users can be operated by truncate table.

-View

SQL > call shop.p_truncate (); Call completed.SQL > select * from shop.Z_TRUNCATE_T;no rows selectedSQL > the above is all the content of the article "how Oracle authorizes truncatetable to users". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!