createin privilege

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

reachsamdurai@gmail.com - 27 Apr 2006 01:18 GMT

I have a requirement such that "user1" should be able to create tables
(or any db objects) only on a particular schema say "etl" so I tried
the following
1) Revoked createtab authority from public
revoke CREATETAB on database from public
2) granted createin privilege to user1
grant createin on schema etl to user user1
But in this case user1 is unable to create any tables as he is not
having the CREATETAB authority.

So I tried the following
1) Revoked createtab authority from public
revoke CREATETAB on database from public
2) granted createtab authority to user1
grant CREATETAB on database to user1
3) granted createin privilege to user1
grant createin on schema etl to user user1
But in this case user1 is able to create tables on all schema.

Any help to resolve this requirement is appreciated.

PS: Env : DB2 UDB 8.2 , AIX 5.3

Thanks,
Sam.

Reply to this Message

Pierre Saint-Jacques - 27 Apr 2006 03:25 GMT

This is because, by default, PUBLIC has the IMPLICIT_SCHEMA privilege and
user1, also having CREATETAB, can now create tables anywhere.
To your last option, also REVOKE IMPLICIT_SCHEMA from PUBLIC and you should
have what you nedd.

HTH, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

>I have a requirement such that "user1" should be able to create tables
> (or any db objects) only on a particular schema say "etl" so I tried
[quoted text clipped - 21 lines]
> Thanks,
> Sam.

Reply to this Message

reachsamdurai@gmail.com - 27 Apr 2006 19:21 GMT

I tried the following but still user (etluser) is able to create tables
in all schema.

As SYSADM
db2 revoke CREATETAB on database from public
db2 revoke IMPLICIT_SCHEMA on database from public
db2 create schema test authorization etluser
db2 grant createin on schema test to etluser

As ETLUSER
db2 "create table card.tab1(no integer)"
db2 "create table test.tab1(no integer)"

but still both the commands created table successfully.

Thanks, Sam

Reply to this Message

Gert van der Kooij - 27 Apr 2006 19:42 GMT

> I tried the following but still user (etluser) is able to create tables
> in all schema.
[quoted text clipped - 12 lines]
>
> Thanks, Sam

If you give the command 'db2 get authorizations' after connecting to the
database you can check if any of the authorizations might give you the
right to create tables.

Reply to this Message

reachsamdurai@gmail.com - 27 Apr 2006 19:49 GMT

ETLUSER is able to create tables in all the schema but I want to
restrict him to create table only on a particular schema.

btw. Here is the o/p of get authorizations.
ETLUSER /home/etluser > 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 = YES
Direct BINDADD authority = NO
Direct CONNECT authority = NO
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 = NO
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = NO
Indirect LOAD authority = YES
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = NO

Thanks,
Sam.

Reply to this Message

Pierre Saint-Jacques - 27 Apr 2006 19:48 GMT

Does the instance owning the db have sysadm_group defined with a group name
of which etluser is a part of???
In this case, etluser is sysadmin and has all privileges.
Check also that etluser is not part of a group which has those privileges.

If etluser is just another user then:
db2 connect to <thedb>
db2 select * from syscat.dbauth where grantee='ETLUSER' (Note upper case).
db2 select * from syscat.schemaauth where grantee='ETLUSER'
This should list all the privileges that etluser has at the db level
(Specifically CREATETAB which is a db level authority).
Scemaauth should reveal what etluser has.
From your commands, it should show that GRANTEE ETLUSER has G privilege on
schema test. This means it has CREATEIN,ALTERIN,DROPIN with GRANT option
(It means it owns the schema),
Granting CREATEIN seperately to ETLUSER does not do anything ahs it already
has it.

HTH, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

>I tried the following but still user (etluser) is able to create tables
> in all schema.
[quoted text clipped - 12 lines]
>
> Thanks, Sam

Reply to this Message

reachsamdurai@gmail.com - 27 Apr 2006 20:05 GMT

No, ETLUSER is not part of SYSADM group.

ETLUSER /home/etluser > db2 get dbm cfg | grep SYSADM_GROUP
SYSADM group name (SYSADM_GROUP) = CARDIADM
ETLUSER /home/etluser > id
uid=214(etluser) gid=208(cetlusrt) groups=1(staff)
ETLUSER /home/etluser >

Also here is the results of other commands
db2 => select * from syscat.dbauth where grantee='ETLUSER'

GRANTOR GRANTEE GRANTEETYPE DBADMAUTH CREATETABAUTH
BINDADDAUTH     CONNECTAUTH NOFENCEAUTH IMPLSCHEMAAUTH LOADAUTH
EXTERNALROUTINEAUTH QUIESCECONNECTAUTH LIBRARYADMAUTH SECURITYADMAUTH
------------- ------------- ----------------- ---------------
--------------------- ------------------- ----- -----------
----------- ----------- -------------- -------- -------------------
------------------ --------------
---------------------------------------------------------------------------
CARD30 ETLUSER U         N         Y         N
        N             N         N          N      N
            N              N      N

1 record(s) selected.

db2 => select * from syscat.schemaauth where grantee='ETLUSER'

GRANTOR GRANTEE GRANTEETYPE SCHEMANAME ALTERINAUTH CREATEINAUTH
DROPINAUTH
-------------------------------------------------------------------------------------------------------------------------------

CARD30 ETLUSER U         TEST N             Y
         N
SYSIBM ETLUSER U         TEST G             G
        G

2 record(s) selected.

Thanks,
Sam.

Reply to this Message