>>Script Language and Platform: Oracle

In applications, some users need privileges to truncate tables in others' schemas.

Only the schema owner can truncate his/her owned tables. Any user with DBA role granted can truncate tables in others' schemas. Any user with "drop any table" system privilege can truncate tables in others' schemas.

Without granting DBA role or DROP ANY TABLE privilege to users, any user can not truncate third party tables.

It is not recommended in Production environments to grant DBA role or "drop any table" privilege to users so that the users can truncate tables in others schemas.

The work around for this impasse is creating a procedure in sys or system schema and grant execute privileges on the procedure to users.

Again, users having execute permission on the truncate procedure can truncate any table in the database.

With added checks and privileges, this modified truncate table procedure is more secure and sage in mission critical production environments.

This procedure need to be created in each schema. The schema owner need to grant execute permission on the procedure to the user, and the delete permission on specified tables also to the user.

Then the user can execute the procedure to truncate tables.

During the execution of the procedure, the procedure check whether the user is having "DELETE" privilege on the specified table, the user is trying to truncate. If the "DELETE" privilege is granted to the user, then the procedure truncates the table under the schema. As the procedure is SCHEMA specific, only tables under a specific schema can only be truncated. Also, any user trying to truncate tables, need to have execute privilege on the specific schema's truncate table procedure. This way, the users are calling a specific schema's truncate table procedure.

User A owns the table temp_jp.
User A create the procedure trunc_tab_proc.
User B need the truncate privilege on A.TEMP_JP table.

Issue the following commands:

Author: JP Vijaykumar

Connect as A



GRANT DELETE ON A.TEMP_JP TO B;

GRANT EXECUTE ON A.TRUNC_TAB_PROC TO B;



Connect as B

EXEC A.TRUNC_TAB_PROC('TEMP_JP')



******************************************************************/

CREATE OR REPLACE PROCEDURE TRUNC_TAB_PROC( v_name IN varchar2 ) 

as 



/********************************** 

AUTHOR JP Vijaykumar 

ORACLE DBA

**********************************/ 



v_num number(10):=0; 

v_owner varchar2(100);

v_user varchar2(100);

sql_stmt varchar2(2000); 



begin 



select username into v_owner from user_users;



select sys_context('USERENV','SESSION_USER') into v_user from dual;



sql_stmt:=' truncate table '||v_owner||'.'|| v_name; 



if (v_owner = v_user) then



execute immediate sql_stmt; 

else



select count(*) into v_num 

from all_tab_privs 

where table_name = upper(v_name) 

and table_schema = v_owner

and grantee = v_user 

and privilege in 'DELETE'; 



if (v_num > 0) then 



execute immediate sql_stmt; 



else 



raise_application_error(-20001,'Insufficient privileges.'); 



end if;

end if; 



exception 



when others then 



raise_application_error(-20001,'Insufficient privileges'); 



end;

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home