?
Solved

Grant privileges to truncate a table

Posted on 2006-04-18
12
Medium Priority
?
38,042 Views
Last Modified: 2011-08-18
dear club members

I want to grant "truncate table privileges" to a  non owner user.

If any body know please let me know

THanks
0
Comment
Question by:saleemlhr
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16476800
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16477060

Unfortunately the only way to grant TRUNCATE TABLE permissions to no-owner is ver dangerous:

GRANT DROP ANY TABLE TO TheUser;

0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 750 total points
ID: 16477069

PS: Another way is to create a stored procedure in the table owners schema that truncates the table and then: GRANT EXECUTE to non-owner.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:csachdeva
ID: 16480478
You cannot grant or revoke permissions for update statistics at the column level. You must have the sso_role to run update statistics or delete statistics on sysroles, syssrvroles, and sysloginroles security tables.

By default, users with the sa_role have permission to run update statistics and delete statistics on system tables other then sysroles, syssrvroles and sysloginroles, and can transfer this privilege to other users.

The partial syntax for grant and revoke is:

grant [truncate table | update statistics | delete statistics] on table_name to {user_name | role_name}

revoke [truncate table | update statistics | delete statistics] on table_name from {user_name | role_name}

You can also issue grant all to grant permissions on update statistics, delete statistics, and truncate table.

For example, the following allows user “harry” to use truncate table and updates statistics on the authors table:

grant truncate table on authors to harry
grant update statistics on authors to harry

The following revokes truncate table and update statistics privileges from “harry” on the authors table:

revoke truncate table on authors from harry
revoke update statistics on authors from harry

The following allows user “billy” to use the delete statistics command on the authors table:

grant delete statistics on authors to billy

The following revokes the delete statistics privileges from user “billy” on the authors table:

revoke delete statistics on authors from billy

The following grants truncate table and update and delete statistics privileges to all users with the oper_role (if users “billy” and “harry” possess the oper_role, they can now run these commands on authors):

grant truncate table on authors to oper_role
grant update statistics on authors to oper_role
grant delete statistics on authors to oper_role

The following revokes truncate table and update and delete statistics privileges from all users with the oper_role:

revoke truncate table on authors from oper_role
revoke update statistics on authors from oper_role
revoke delete statistics on authors from oper_role

Users “billy” and “harry” can no longer run these commands on authors.

You can also implicitly grant permissions for truncate table, delete statistics, and update statistics through a stored procedure. For example, assuming “billy” owns the authors table, he can run the following to grant “harry” privileges to run truncate table and update statistics on authors:

create procedure sproc1
as
truncate table authors
update statistics authors
go
grant execute on sproc1 to harry
go

You can also implicitly grant permissions at the column level for update statistics and delete statistics through stored procedures.

Hope this will satisfy your query.

Regards,
Chetan Sachdeva
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16480517
MikeOM_DBA,

>>PS: Another way is to create a stored procedure in the table owners schema that truncates the table and then: GRANT EXECUTE to non-owner.<<
Nope, that will not help.  As aneeshattingal has stated from BOL:
"TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable."
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16480564
Actually if you have SQL Server 2005 "you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause. "
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16480576
Here is an example from BOL using SQL Server 2005:

CREATE PROCEDURE TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16480785

Well, you never said it was WinDoze M$ SQL Server -- I posted the Oracle way.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16483197
>>I posted the Oracle way.<<
You are absolutely right, I stand corrected:  The questioner is in fact using Oracle.
0
 

Author Comment

by:saleemlhr
ID: 16485646
Thanks all for their comments. I think solution of  csachdeva will  not work. I personnaly tried to grant truncate table  permissions but failed.

I solved this problem by granting drop any table permissions however it is not good ideas as pointed out by MikeOM_DBA.

meanwhile I want this question  to remian open after more R&D points will be awarded. I also tried to create dbProcedure as mentioned by  csachdeva but truncate command does not work in dbprocedure

thanks
0
 

Author Comment

by:saleemlhr
ID: 16485669
We use Oracle 10G. this for your knowledge thanks
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16529377

Try something like this:

CREATE PROCEDURE TABOWNER.TRUNC_MYTAB
IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE MYTAB';
END;
/

GRANT EXECUTE ON TABOWNER.TRUNC_MYTAB TO NONOWNER
/
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question