>>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