By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

createin privilege

P: n/a
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.

Apr 27 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<re***********@gmail.com> a écrit dans le message de news:
11**********************@u72g2000cwu.googlegroups. com...
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.


Apr 27 '06 #2

P: n/a
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

Apr 27 '06 #3

P: n/a
In article <11**********************@j33g2000cwa.googlegroups .com>,
re***********@gmail.com says...
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


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.
Apr 27 '06 #4

P: n/a
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.

Apr 27 '06 #5

P: n/a
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.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<re***********@gmail.com> a écrit dans le message de news:
11**********************@j33g2000cwa.googlegroups. com...
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


Apr 27 '06 #6

P: n/a
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.

Apr 27 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.