473,714 Members | 2,505 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Create Database with RESTRICTIVE Option

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.
Aug 29 '08 #1
3 5345
On Aug 29, 1:56*pm, "Mark A" <some...@someon e.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
Aug 31 '08 #2
"Sanjuro" <as******@gmail .comwrote in message
news:e3******** *************** ***********@t54 g2000hsg.google groups.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.SYSDUMMY 1 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?
Aug 31 '08 #3
>>On 8/30/2008 at 10:38 PM, in message
<de************ ******@bignews4 .bellsouth.net> , Mark A<so*****@someo ne.com>
wrote:
"Sanjuro" <as******@gmail .comwrote in message
news:e3******** *************** ***********@t54 g2000hsg.google groups.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.SYSDUMMY 1 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.SQLTABL ES" (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

Sep 2 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
13058
by: Shashikant Khandelwak | last post by:
Hi ! I am trying to install oracle 9i Standard edition on a windows 2000 SP4 machine. I get through the entire installation up to running the Database Configuration Assistant. While it tries to make the database, it gets to the "Creating and starting Oracle instance" stage, and then the errors start. I get: ORA-01503: CREATE CONTROLFILE failed. ORA-00205: error in identifying control file. check alertlog for more
1
1487
by: CV | last post by:
Hi, I am not getting option as 'new view' and 'new table' when I right click on views and tables option in VisualStudio.net IDE server explorer sqlservers database to create new objects. Looks like some setup issue in my database. Thanks for your help in advance.
3
4082
by: Leinad Kong | last post by:
I'm using Access 2002, as front-end and back-end as well: 1) I faced database corrupted problems, when more than 1 user edit concurrently. I'm using All-records Locking, and open-exclusively as default. But, still face the same problem: Database corrupted! (But, it allows to be repaired) 2) I found that the relationship linkage between those related tables(which corrupted) was disconnected/lost after problem (1)
7
8863
by: dog | last post by:
I've seen plenty of articles on this topic but none of them have been able to solve my problem. I am working with an Access 97 database on an NT4.0 machine, which has many Access reports. I want my users to be able to select a report, click on a command button on a form, which will then automatically create the report as a pdf file and save it to the user's machine. I am using Adobe Acrobat (5.0 I think) and have Adobe Distiller as a
6
1953
by: Vijay Kumar R Zanvar | last post by:
Ref: K&R section 8.7 ..... so for a C99 complying implementation, if I've understood correctly, the most restrictive type is long long? Vijay
8
4521
by: Ted Miller | last post by:
Hi folks, I'm looking at moving a large base of C++ code to .Net under tight time constraints. The code runs in mission-critical environments, and I am extremely concerned about the loader lock problem and the potential for deadlocks. After pouring over the available information, and trying a few experiments, I am still left with a few questions and issues I hope someone out there can shed some light on.
4
12438
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is this: can Access create the document and place it as an OLE object to the relevant table? Any help is greatly appreciated. Ricky
5
6483
by: Dave | last post by:
I need to filter an Access 2000 result set in ASP 30 using the ADO recordset.filter. I build the filter in pieces. The first clause of the filter is this... WHERE word LIKE 'S%' ... to which other clauses are appended with AND. This all works fine as long as I provide a condition for the first clause
10
2594
by: AAaron123 | last post by:
I want to create a database with one table on the host. I can't user SQL Server Management Studio to do it so I guess I have to do it programmatically. I have in mind that in the session start (or maybe application start would be better) I'd check to see it the database exists and if not create it. The problem is that I have no idea how to do that.
0
8796
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8704
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9170
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9009
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6627
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5943
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4462
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4715
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2105
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.