At first glance it seems like a good idea to use the new RESTRICTIVE option
when creating a database to remove public access otherwise granted by
default. Then one could grant the required access to specific users.

But what about things like these which are normally granted by default to
public if the RESTRICTIVE option is not used:

1. EXECUTE WITH GRANT privilege on all functions and procedures in the
SYSPROC schema - Allows the user to invoke stored procedures and execute
functions in the SYSPROC schema and grant that permission to other users.

2. EXECUTE WITH GRANT privilege on all procedures in the SQLJ schema -
Allows the user to invoke stored procedures in the SYSPROC schema.

3. BIND and EXECUTE privilege on all packages created in NULLID schema -
Allows the user to BIND and EXECUTE packages in the NULLID schema

How does one grant these privileges back to a specific user without having
to name each function, procedure, package, etc that comes shipped with DB2?
I must be missing something.

On Aug 29, 1:56*pm, "Mark A" <some...@someone.comwrote:

At first glance it seems like a good idea to use the new RESTRICTIVE option
when creating a database to remove public access otherwise granted by
default. Then one could grant the required access to specific users.
>
But what about things like these which are normally granted by default to
public if the RESTRICTIVE option is not used:
>
1. EXECUTE WITH GRANT privilege on all functions and procedures in the
SYSPROC schema - Allows the user to invoke stored procedures and execute
functions in the SYSPROC schema and grant that permission to other users.
>
2. EXECUTE WITH GRANT privilege on all procedures in the SQLJ schema -
Allows the user to invoke stored procedures in the SYSPROC schema.
>
3. BIND and EXECUTE privilege on all packages created in NULLID schema -
Allows the user to BIND and EXECUTE packages in the NULLID schema
>
How does one grant these privileges back to a specific user without having
to name each function, procedure, package, etc that comes shipped with DB2?
I must be missing something.

I had once used RESTRICTIVE option to create a database that processed
financial data and the architects wanted to "lock down" as much as
possible.

I slightly different problem, but in similar lines happened when we
realized that even no privilege is granted to the packages that are
required for type 4 java connections. We rebound the packages required
granting proper privileges and the issue was resolved. At the time, it
seemed that they have taken it too far. But then, if you make a
database RESTRICTIVE, this is exactly what you wanted to achieve.
However, IBM should have included a script or option to help grant a
certain set of most commonly required privileges to a user (or
PUBLIC). Perhaps they thought about it and decided against it, as the
definition of most commonly used would have been too fuzzy.

In your case, I don't know of any other easier way than to execute
bunch of grant statements.

Cheers,
Sanjuro

"Sanjuro" <ashrujit@gmail.comwrote in message
news:e3129b6e-6879-4f93-9326-aa35bf76aea9@t54g2000hsg.googlegroups.com...

<snip>
But then, if you make a database RESTRICTIVE, this is exactly what you
wanted to achieve.
<snip>

No, that is not what I wanted to achieve, and it is inconceivable to me that
anyone would want to achieve restricting applications from using DB2 system
packages that no one really understands what they are for (nor should they
need to understand what they are for). Of course RESTRICTIVE also prevents
users from selecting the single row in the SYSIBM.SYSDUMMY1 table because
IBM doesn't want anyone stealing the super-secret "Y" in that table.

While I am ranting, how come IBM has not provided a way to grant insert,
update, delete, and select access (and another way to grant select access
only) to all tables and views in a particular schema? Surely, customers have
been asking for this for many years?

>>On 8/30/2008 at 10:38 PM, in message
<depuk.19286$rD2.1877@bignews4.bellsouth.net>, Mark A<someone@someone.com>
wrote:

"Sanjuro" <ashrujit@gmail.comwrote in message
news:e3129b6e-6879-4f93-9326-aa35bf76aea9@t54g2000hsg.googlegroups.com...

>
No, that is not what I wanted to achieve, and it is inconceivable to me
that
anyone would want to achieve restricting applications from using DB2
system
packages that no one really understands what they are for (nor should
they
need to understand what they are for). Of course RESTRICTIVE also
prevents
users from selecting the single row in the SYSIBM.SYSDUMMY1 table
because
IBM doesn't want anyone stealing the super-secret "Y" in that table.

Glad to see I am not the only one who thinks that the use of RESTRICTIVE is
a bit too, umm, restrictive!

Other problems I've seen on databases that are set up as RESTRICTIVE:

db2look needs SELECT access to many of the SYSIBM and SYSCAT tables and
views, as well as EXECUTE access on many of the NULLID packages/routines.

The Control Center 'Alter Table' command "doesn't work". It won't even
bring up information about the table (just kind of sits there spinning it's
wheels). I'm guessing this again has to do with missing rights for SYSIBM,
SYSCAT and NULLID, but I haven't been able to pin down what is missing.

Control Center SQL Assist doesn't work: Routine "SYSIBM.SQLTABLES" (specific
name "TABLES") has returned an error SQLSTATE with diagnostic test "SYSIBM:
CLI:-727".

Does anyone know if there's any kind of 'debug mode' I can turn on in
Control Center to see what the messages going back and forth are?

While I am ranting, how come IBM has not provided a way to grant insert,
>
update, delete, and select access (and another way to grant select
access
only) to all tables and views in a particular schema? Surely, customers
have
been asking for this for many years?

Would indeed be nice! For packages too.

Frank