473,231 Members | 1,850 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

restrict privileges

I feel like an idiot for asking this, but neither I nor my DBA can figure
this out.
How do you create a database object that does not implicitly allow access by
any defined user?

For instance, I have a user who has been granted only the CONNECT privilege
to a certain database. But he is still able to do selects and updates and
other things to tables created by another user. Everything I've read
indicates this should not be allowed; that the user must be granted these
privileges explicitly. What are we missing?

================================================== ==========
About DB2 Administration Tools Environment
================================================== ==========
DB2 administration tools level:
Product identifier SQL09050
Level identifier 03010107
Level DB2 v9.5.0.808
Build level s071001
PTF NT3295
================================================== ==========
Java development kit (JDK):
Level IBM Corporation 1.5.0
================================================== ==========
Here's some more things that show what I am attempting:
connect to securedb user fswarbri using

Database Connection Information

Database server = DB2/NT 9.5.0
SQL authorization ID = FSWARBRI
Local database alias = SECUREDB
SELECT * FROM SYSIBMADM.PRIVILEGES WHERE OBJECTNAME = 'SECTEST1'

AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTSCHEMA OBJECTNAME OBJECTTYPE

------------------ ----------- --------- ------------ ---------- ----------
OPSUSER U UPDATE Y OPSUSER SECTEST1 TABLE

OPSUSER U REFERENCE Y OPSUSER SECTEST1 TABLE

OPSUSER U SELECT Y OPSUSER SECTEST1 TABLE

OPSUSER U INSERT Y OPSUSER SECTEST1 TABLE

OPSUSER U INDEX Y OPSUSER SECTEST1 TABLE

OPSUSER U DELETE Y OPSUSER SECTEST1 TABLE

OPSUSER U ALTER Y OPSUSER SECTEST1 TABLE

OPSUSER U CONTROL N OPSUSER SECTEST1 TABLE
8 record(s) selected.
SELECT * FROM OPSUSER.SECTEST1

COL1
--------------------
123
456
987

3 record(s) selected.

As you can see, only OPSUSER has any privileges on OPSUSER.SECTEST1, and yet
user FSWARBRI is able to query on the table (and update it, for that
matter).

Thanks!

Frank

Mar 4 '08 #1
6 2777
On Mar 4, 12:02 pm, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
wrote:
I feel like an idiot for asking this, but neither I nor my DBA can figure
this out.
How do you create a database object that does not implicitly allow access by
any defined user?

For instance, I have a user who has been granted only the CONNECT privilege
to a certain database. But he is still able to do selects and updates and
other things to tables created by another user. Everything I've read
indicates this should not be allowed; that the user must be granted these
privileges explicitly. What are we missing?

================================================== ==========
About DB2 Administration Tools Environment
================================================== ==========
DB2 administration tools level:
Product identifier SQL09050
Level identifier 03010107
Level DB2 v9.5.0.808
Build level s071001
PTF NT3295
================================================== ==========
Java development kit (JDK):
Level IBM Corporation 1.5.0
================================================== ==========

Here's some more things that show what I am attempting:
connect to securedb user fswarbri using

Database Connection Information

Database server = DB2/NT 9.5.0
SQL authorization ID = FSWARBRI
Local database alias = SECUREDB

SELECT * FROM SYSIBMADM.PRIVILEGES WHERE OBJECTNAME = 'SECTEST1'

AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTSCHEMA OBJECTNAME OBJECTTYPE

------------------ ----------- --------- ------------ ---------- ----------
OPSUSER U UPDATE Y OPSUSER SECTEST1 TABLE

OPSUSER U REFERENCE Y OPSUSER SECTEST1 TABLE

OPSUSER U SELECT Y OPSUSER SECTEST1 TABLE

OPSUSER U INSERT Y OPSUSER SECTEST1 TABLE

OPSUSER U INDEX Y OPSUSER SECTEST1 TABLE

OPSUSER U DELETE Y OPSUSER SECTEST1 TABLE

OPSUSER U ALTER Y OPSUSER SECTEST1 TABLE

OPSUSER U CONTROL N OPSUSER SECTEST1 TABLE

8 record(s) selected.

SELECT * FROM OPSUSER.SECTEST1

COL1
--------------------
123
456
987

3 record(s) selected.

As you can see, only OPSUSER has any privileges on OPSUSER.SECTEST1, and yet
user FSWARBRI is able to query on the table (and update it, for that
matter).

Thanks!

Frank
Could it be that you need to explicitly remove/revoke this user from
the PUBLIC role?

--Jeff
Mar 4 '08 #2
Now this is interesting. The DBA went in to one of the instances running on
Linux and did the following from Control Center:
Selected the group PUBLIC
Revoked all privileges for all types of objects (specifically SCHEMA and
PACKAGES. I don't think any tables has explicit rights to any tables.)
Granted back privileges for all packages owned by NULLID.

He also had to remove the user at an OS level from a certain group
(db2admin?).

And now the user is no longer able to access tables to which that user has
not been granted privileges.

But I tried the same thing on my Windows PC running DB2 Express-C (9.5) and
it is still not enforcing the restrictions.

Huh?

Frank

Mar 4 '08 #3
If a user has privileges to SELECT, DELETE, UPDATE, or INSERT data
without having gotten in diractly then the access must have been granted
to either a group (or role) the user belongs to or PUBLIC.

So, take a look at that end.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 4 '08 #4
Frank Swarbrick wrote:
db2admin? Well....
If you want to hide data from dbadm you will need to use LBAC.
(or encrypt)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 4 '08 #5
>>On 3/4/2008 at 2:42 PM, in message
<63*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
Frank Swarbrick wrote:
db2admin? Well....
If you want to hide data from dbadm you will need to use LBAC.
(or encrypt)
No, I was saying that the db2 removed the 'restricted' user for a certain
Linux group, and I believe that group was named 'db2admins'. But I'm
probably wrong...

Mar 5 '08 #6
Wow, even odder. Now things seem to be working fine on my work PC. I am
able to restrict the PUBLIC group appropriately and grant rights to specific
users as necessary.

Maybe last night's reboot cleared things up. Who knows! Oh well. I still
have no idea what I did to cause the problem, but...

Frank

Mar 5 '08 #7

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

Similar topics

4
by: Amardeep Verma | last post by:
Hi, I have a quick question. Which role/privileges are required before a user can give the statement "GRANT ALL PRIVILEGES"? Thanking you in Advance Have a nice day
0
by: Charles Cantrell | last post by:
I have recently set up mySQL on a Mandrake release of Linux (Version 7 of Mandrake, I believe), using the binary 4.0.13 standard release. The set up and start up all were normal, as far as I...
2
by: virgilio | last post by:
Hi all, "Administrator Guide Implementation" DB2 8.2, chapter 7, section "Indirect privileges through a package" states: (highlight >>>>!!!<<<<) "Privileges granted to individuals binding the...
7
by: tweak | last post by:
Can someone give me a short example as how to best use this keyword in your code? This is my understanding: by definition restrict sounds like it is suppose to restrict access to memory...
8
by: shorti | last post by:
Here is an example of what I want to do (syntax might not be entirely correct as this is just an example): CREATE TABLE ParentA ( name CHAR (6) NOT NULL; address CHAR(64); ) IN CUSTOMER_TS...
12
by: Me | last post by:
I'm trying to wrap my head around the wording but from what I think the standard says: 1. it's impossible to swap a restrict pointer with another pointer, i.e. int a = 1, b = 2; int *...
21
by: Niu Xiao | last post by:
I see a lot of use in function declarations, such as size_t fread(void* restrict ptr, size_t size, size_t nobj, FILE* restrict fp); but what does the keyword 'restrict' mean? there is no...
3
by: dion.naidoo | last post by:
Hi ,is there any way one can restrict users to copy files with extensions that we dont want on our networks or local pcs. Users are local administrators of their pcs. PS. If this is not possible...
0
by: EricBlair | last post by:
Hello, I wrote a windows service that is supposed to start an interactive GUI app. I realize a service will not readily do this so I've pieced together the code below to bypass that. However, the...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...

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.