Able to alter or drop a table but cannot create the table, when no permissions are given to that user

[Rahul B]

Hi,

I have a user UCLDEV1 which is a part of staff and a
group(db2schemagrp1) to which i have not given any permissions.

The authorizations of that user are shown as

db2 =get authorizations

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = NO
Direct CREATETAB authority = NO
Direct BINDADD authority = NO
Direct CONNECT authority = YES
Direct CREATE_NOT_FENC authority = NO
Direct IMPLICIT_SCHEMA authority = NO
Direct LOAD authority = NO
Direct QUIESCE_CONNECT authority = NO
Direct CREATE_EXTERNAL_ROUTINE authority = NO
Direct SYSMON authority = NO

Indirect SYSADM authority = NO
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = NO
Indirect BINDADD authority = YES
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = NO
Indirect LOAD authority = NO
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = NO

db2 =create table test15(num1 INTEGER)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0552N "UCLDEV1" does not have the privilege to perform operation
"CREATE
TABLE". SQLSTATE=42502
db2 =alter table test12 add num3 integer
DB20000I The SQL command completed successfully.
db2 =drop table test12
DB20000I The SQL command completed successfully.
db2 =>
However, as the above commands show, i am not able to create any table
with that user, however, i can alter the table or drop the table.
The following command from an admin user gives
/home/db2inst1>"db2 revoke alterin on schema ucldev1 from ucldev1"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0556N An attempt to revoke a privilege, security label, or
exemption from
"UCLDEV1" was denied because "UCLDEV1" does not hold this privilege,
security
label, or exemption. SQLSTATE=42504

What could be the reason for it?

Thanks a lot.

Rahul

[Serge Rielau]

Rahul B wrote:

Hi,

I have a user UCLDEV1 which is a part of staff and a
group(db2schemagrp1) to which i have not given any permissions.

The authorizations of that user are shown as

db2 =get authorizations

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = NO
Direct CREATETAB authority = NO
Direct BINDADD authority = NO
Direct CONNECT authority = YES
Direct CREATE_NOT_FENC authority = NO
Direct IMPLICIT_SCHEMA authority = NO
Direct LOAD authority = NO
Direct QUIESCE_CONNECT authority = NO
Direct CREATE_EXTERNAL_ROUTINE authority = NO
Direct SYSMON authority = NO

Indirect SYSADM authority = NO
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = NO
Indirect BINDADD authority = YES
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = NO
Indirect LOAD authority = NO
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = NO

db2 =create table test15(num1 INTEGER)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0552N "UCLDEV1" does not have the privilege to perform operation
"CREATE
TABLE". SQLSTATE=42502
db2 =alter table test12 add num3 integer
DB20000I The SQL command completed successfully.
db2 =drop table test12
DB20000I The SQL command completed successfully.
db2 =>
However, as the above commands show, i am not able to create any table
with that user, however, i can alter the table or drop the table.
The following command from an admin user gives
/home/db2inst1>"db2 revoke alterin on schema ucldev1 from ucldev1"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0556N An attempt to revoke a privilege, security label, or
exemption from
"UCLDEV1" was denied because "UCLDEV1" does not hold this privilege,
security
label, or exemption. SQLSTATE=42504

What could be the reason for it?

Could it be UCLDEV1 was the owner of the altered/dropped table?
The user could have either created the table before the create privilege
was taken away, or the user could have received ownership through
TRANSFER OWNER.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Rahul B]

On Sep 4, 5:57 pm, Serge Rielau <[e-mail]:

Rahul B wrote:

Hi,

I have a user UCLDEV1 which is a part of staff and a
group(db2schemagrp1) to which i have not given any permissions.

The authorizations of that user are shown as

db2 =get authorizations

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = NO
Direct CREATETAB authority = NO
Direct BINDADD authority = NO
Direct CONNECT authority = YES
Direct CREATE_NOT_FENC authority = NO
Direct IMPLICIT_SCHEMA authority = NO
Direct LOAD authority = NO
Direct QUIESCE_CONNECT authority = NO
Direct CREATE_EXTERNAL_ROUTINE authority = NO
Direct SYSMON authority = NO

Indirect SYSADM authority = NO
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = NO
Indirect BINDADD authority = YES
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = NO
Indirect LOAD authority = NO
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = NO

db2 =create table test15(num1 INTEGER)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0552N "UCLDEV1" does not have the privilege to perform operation
"CREATE
TABLE". SQLSTATE=42502
db2 =alter table test12 add num3 integer
DB20000I The SQL command completed successfully.
db2 =drop table test12
DB20000I The SQL command completed successfully.
db2 =>

However, as the above commands show, i am not able to create any table
with that user, however, i can alter the table or drop the table.
The following command from an admin user gives
/home/db2inst1>"db2 revoke alterin on schema ucldev1 from ucldev1"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0556N An attempt to revoke a privilege, security label, or
exemption from
"UCLDEV1" was denied because "UCLDEV1" does not hold this privilege,
security
label, or exemption. SQLSTATE=42504

What could be the reason for it?

Could it be UCLDEV1 was the owner of the altered/dropped table?
The user could have either created the table before the create privilege
was taken away, or the user could have received ownership through
TRANSFER OWNER.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Exactly Serge,

UCLDEV1 was the owner of the tables when i created.
So, i'll need to do a "Transfer Ownership" command.
A few more things.

1. If i use the TRANSFER OWNERSHIP Statement, what are the possible
problems that could come up(I am asking it to find out whether or not
some tables will go a pending state or any other problems that could
come, so that i can beforehand find out how much(if any) work i'll
need to be done after the transfer ownership)

2. Currently, ucldev1 cannot do a select, insert etc on tables created
by the admin user. So, whenever i create a table in UCLDEV1 through
admin, i need to give select, insert, update privs to UCLDEV1. Also,
will the other database objects like functions, procedures also
require some permissions to be accessed/called.
Is there some way that i can give the DML permissions of all objects
in schema UCLDEV1 to UCLDEV1.

3. I would also like to find out if it is possible to track DDL
statements on schema UCLDEV1 by any user.
I thought there could be some "BEFORE ALTER IN ON SCHEMA" clause on
trigger.
P.S. I am quite reluctant to use the TRANSFER OWNERSHIP Statement
without a prior knowledge of what problems could come up because i
could end up making a number of people wait while i try to sort out
the problems.

Thanks a lot.

Rahul

[Serge Rielau]

Rahul B wrote:

1. If i use the TRANSFER OWNERSHIP Statement, what are the possible
problems that could come up(I am asking it to find out whether or not
some tables will go a pending state or any other problems that could
come, so that i can beforehand find out how much(if any) work i'll
need to be done after the transfer ownership)

For tables there are very few things that could go wrong.
Essentially the new owner has to be able to "handle" the new object.
In case of a table I'd think the owner needs to have access to any UDF
used in the tables check constraints or generated columns. RI
constraints would be another topic.

Things get more interesting when you transfer the ownership of routines
or views since they typically reference all sorts of other objects to
which the new owner needs access.

2. Currently, ucldev1 cannot do a select, insert etc on tables created
by the admin user. So, whenever i create a table in UCLDEV1 through
admin, i need to give select, insert, update privs to UCLDEV1. Also,
will the other database objects like functions, procedures also
require some permissions to be accessed/called.
Is there some way that i can give the DML permissions of all objects
in schema UCLDEV1 to UCLDEV1.

I don't think so. What you can do is grant DML privileges to a group (or
role in DB2 Viper 2). As long as UCLDEV1 is part of the group or role it
can perform the actions.
I do wonder whether you may be able to take a different approach.
DB2 has a variation of the unix "su" command which allows an
administrator to do DDL on someone else's behalf.
Take a look at SET SESSION USER:
http://publib.boulder.ibm.com/infoce...c/r0011139.htm

I think (never tried myself) you will then find that the DEFINER of the
object will be the administrator, but the OWNER will be UCLDEV1.

3. I would also like to find out if it is possible to track DDL
statements on schema UCLDEV1 by any user.
I thought there could be some "BEFORE ALTER IN ON SCHEMA" clause on
trigger.

DB2 has auditing capabilities. These have been greatly improved in Db2
Viper 2. There are no DDL triggers.

P.S. I am quite reluctant to use the TRANSFER OWNERSHIP Statement
without a prior knowledge of what problems could come up because i
could end up making a number of people wait while i try to sort out
the problems.

Fair enough.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab