User privileges

shore · #1 (**permalink**) 09-05-11, 06:58

Hi,

I want to create an user which will be able to create tables,views and other database objects but it will not be able to drop those objects.

Is it possible to revoke drop privilege of an object from its creator???

DB2 V9.5

przytula_guy · #2 (**permalink**) 09-05-11, 07:14

see : DROPIN
Revokes the privilege to drop objects in the schema.
REVOKE (Schema Privileges)

sathyaram_s · #3 (**permalink**) 09-05-11, 07:16

I don't think it is possible to revoke drop privilege from the user.

Why do you want to do this ?

You can transfer the ownership of the object to someone else and grant the original creator only selective priivileges. Look at TRANSFER OWNERSHIP command.

-

sathyaram_s · #4 (**permalink**) 09-05-11, 07:19

Guy,
As per documentation : (http://publib.boulder.ibm.com/infoce...Fr0000988.html )

Quote:

When dropping objects that allow two-part names, the privileges held by the authorization ID of the statement must include at least one of the following:

DROPIN privilege on the schema for the object
Owner of the object, as recorded in the OWNER column of the catalog view for the object
CONTROL privilege on the object (applicable only to indexes, index specifications, nicknames, packages, tables, and views) <LI class=li>Owner of the user-defined type, as recorded in the OWNER column of the SYSCAT.DATATYPES catalog view (applicable only when dropping a method that is associated with a user-defined type)
SYSADM or DBADM authority

So, the owner will be able to drop the object

Sathyaram

shore · #5 (**permalink**) 09-05-11, 07:29

Thanks for the info...

but my application require such ID. Is there any alternate way to achive the same??

sathyaram_s · #6 (**permalink**) 09-05-11, 07:58

So, you don't want the application to inadvertantly drop the table ?

Here, when you create a table, you can create 'WITH RESTRICT ON DROP'
or ALTER the table to be RESTRICT ON DROP

Code:

 db2 "create table t1(i int) with restrict on  drop"
DB20000I  The SQL command completed successfully.
 
 db2 drop table t1
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0672N  Operation DROP not allowed on table "DB2INST1.T1".  SQLSTATE=55035
 
 db2 "alter table t1   drop restrict on  drop"
DB20000I  The SQL command completed successfully.
 
  db2 drop table t1
DB20000I  The SQL command completed successfully.

As you can see, unless you alter to remove restrict on drop, the table cannot be dropped

HTH

shore · #7 (**permalink**) 09-05-11, 08:01

Thanks Satya... This will work...

Mathew_paul · #8 (**permalink**) 09-07-11, 12:39

satya

but the user who is going to create the with this option has the privileges to alter the table to remove this option right and he can drop the table.

regs
Paul

sathyaram_s · #9 (**permalink**) 09-07-11, 19:11

Yes, you are right Paul.

If you don't want the user to have any 'automatic' drop privilege on the table, Transfer ownership( the user doesnt even have to be real)
If you want to prevent the user from dropping the table by mistake(an application logic error,say) , then alter the table to restrict drops

If the requirement is any different, then the solution will be too

HTH

Internet Services

Web Development

Digital Marketing

Consumer Tech