DB2 privileges, Direct and Indirect SYSADM

[Rahul B]

Hi,

I am having the following issues while trying to restrict the current
user from creating any objects.
Below is the privileges for the user and response when i try to create
a table in that user.
Can anybody tell what is the difference between DIRECT SYSADM and
Indirect SYSADM and why is Indirect SYSADM is assigned to user by
default.

What should be done to prevent the normal user from creating any
objects?

When i try "revoke createin on schema UCLDEV1 from ucldev1", it says
that it doesn't hold the privilege.

Also, this user is member of staff and db2grp1, and whenever i try to
do "revoke createtab on database from db2grp1", it again says that
"db2grp1 doesn't hold that priveledge"

What should be done?

$ 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 = YES
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
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 ADCL 9.1.2

You can issue database manager commands and SQL statements from the
command
prompt. For example:
db2 =connect to sample
db2 =bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND
OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 =create table ucldev1.test12(var Integer)
DB20000I The SQL command completed successfully.
db2 =drop table ucldev1.test12
DB20000I The SQL command completed successfully.

[Knut Stolze]

Rahul B wrote:

Hi,

I am having the following issues while trying to restrict the current
user from creating any objects.
Below is the privileges for the user and response when i try to create
a table in that user.
Can anybody tell what is the difference between DIRECT SYSADM and
Indirect SYSADM and why is Indirect SYSADM is assigned to user by
default.

Indirect authorizations are authorizations that the user holds due to group
memberships. Since SYSADM etc. are defined via groups in the DBM CFG, a
user may have this authorization indirect. (And not all users are SYSADM
by default.)

What should be done to prevent the normal user from creating any
objects?

Revoke the respective privileges, for example CREATETAB, CREATEIN from the
user and all groups that the user belongs to. Note that each user belongs
automatically to the group PUBLIC. Furthermore, if a user has SYSADM
authorization, he can do pretty much anything anyway.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Rahul B]

On Aug 22, 1:35 pm, Knut Stolze <[e-mail]:

Rahul B wrote:

Hi,

I am having the following issues while trying to restrict the current
user from creating any objects.
Below is the privileges for the user and response when i try to create
a table in that user.
Can anybody tell what is the difference between DIRECT SYSADM and
Indirect SYSADM and why is Indirect SYSADM is assigned to user by
default.

Indirect authorizations are authorizations that the user holds due to group
memberships. Since SYSADM etc. are defined via groups in the DBM CFG, a
user may have this authorization indirect. (And not all users are SYSADM
by default.)

What should be done to prevent the normal user from creating any
objects?

Revoke the respective privileges, for example CREATETAB, CREATEIN from the
user and all groups that the user belongs to. Note that each user belongs
automatically to the group PUBLIC. Furthermore, if a user has SYSADM
authorization, he can do pretty much anything anyway.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Thanks Knut,

One more issue.
If i create a schema(SchemaA) and give authorization of that schema to
a particular user(A).
That way, the user A will be given Direct SYSADM and Direct DBADM
privileges.
but later on , i decide that i should not allow A to create objects in
SchemaA.

Is it possible for me to revoke the priviledge to create new objects
from A(even though when i created the SchemaA, i made A the owner of
the schema).
In other words, can i change the authorization of schemaA from user A
to Admn user, after the schema has been created?

Thanks.

Rahul
Is it possible to revoke

[Knut Stolze]

Rahul B wrote:

If i create a schema(SchemaA) and give authorization of that schema to
a particular user(A).
That way, the user A will be given Direct SYSADM and Direct DBADM
privileges.

What do you mean with "that way"? The user doesn't get SYSADM and/or DBADM
authorization just because you gave him/her some privileges on a schema.

but later on , i decide that i should not allow A to create objects in
SchemaA.

Is it possible for me to revoke the priviledge to create new objects
from A(even though when i created the SchemaA, i made A the owner of
the schema).
In other words, can i change the authorization of schemaA from user A
to Admn user, after the schema has been created?

You can use the TRANSFER OWNERSHIP statement to transfer the ownership of a
schema from one user to another.

p.s: I get the feeling that authorizations and privileges are not very clear
yet for you.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Rahul B]

On Aug 22, 4:46 pm, Knut Stolze <[e-mail]:

Rahul B wrote:

If i create a schema(SchemaA) and give authorization of that schema to
a particular user(A).
That way, the user A will be given Direct SYSADM and Direct DBADM
privileges.

What do you mean with "that way"? The user doesn't get SYSADM and/or DBADM
authorization just because you gave him/her some privileges on a schema.

but later on , i decide that i should not allow A to create objects in
SchemaA.

Is it possible for me to revoke the priviledge to create new objects
from A(even though when i created the SchemaA, i made A the owner of
the schema).
In other words, can i change the authorization of schemaA from user A
to Admn user, after the schema has been created?

You can use the TRANSFER OWNERSHIP statement to transfer the ownership of a
schema from one user to another.

p.s: I get the feeling that authorizations and privileges are not very clear
yet for you.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Yes,
I have started working on DB2 only recently, and i am not very clear
on authorization and privs.

Rahul

[Tomas]

So,

SYSADM is not a database level privilege, it's an instance wide
authorization level. It is not granted, it is defined in the database
manager configuration. Check out the GET DBM CFG and UPDATE DBM CFG
commands (parameter is SYSADM_GROUP).

DBADM is a database privilege, and can be granted and revoked.

On a per schema level, you can grant CREATE, ALTER, DROP of objects in
that schema. Those privileges can be revoked. Worth noting is that
the user will maintain some privileges, notably CONTROL, on the tables
they created. That will have to be revoked separately on a table-by-
table basis.

/T

On Aug 22, 6:34 am, Rahul B <[e-mail]:

On Aug 22, 4:46 pm, Knut Stolze <[e-mail]:

Rahul B wrote:

If i create a schema(SchemaA) and give authorization of that schema to
a particular user(A).
That way, the user A will be given Direct SYSADM and Direct DBADM
privileges.

What do you mean with "that way"? The user doesn't get SYSADM and/or DBADM
authorization just because you gave him/her some privileges on a schema.

but later on , i decide that i should not allow A to create objects in
SchemaA.

Is it possible for me to revoke the priviledge to create new objects
from A(even though when i created the SchemaA, i made A the owner of
the schema).
In other words, can i change the authorization of schemaA from user A
to Admn user, after the schema has been created?

You can use the TRANSFER OWNERSHIP statement to transfer the ownership of a
schema from one user to another.

p.s: I get the feeling that authorizations and privileges are not very clear
yet for you.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Yes,
I have started working on DB2 only recently, and i am not very clear
on authorization and privs.

Rahul- Hide quoted text -

- Show quoted text -