I just created a new user and granted connect and select on a single
view, only. When I connect to my database, the new user has at least
select privileges on the whole database. What am I doing wrong or
misunderstandin g? How do I discover all the privileges granted on my
database? How do I revoke all privileges and then restore just the ones
I want? Does public get any privileges by default? 14 1727
Bob Stearns wrote: I just created a new user and granted connect and select on a single view, only. When I connect to my database, the new user has at least select privileges on the whole database. What am I doing wrong or misunderstandin g? How do I discover all the privileges granted on my database? How do I revoke all privileges and then restore just the ones I want? Does public get any privileges by default?
Bob,
How did you test your hypothesis. I suspect you tried to select from a
SYSCAT view or a SYSIBM table.
By default PUBLIC gets granted SELECT on the catalog objects (SYSCAT,
SYSIBM, SYSFUN and SYSPROC).
In DB2 9 there is a new RESTRICT option that creates the database very
tight to begin with.
On DB2 V8 a simple procedure revoking SEELCT from PUBLIC on these
objects should do just fine.
Something like:
CREATE PROCEDURE revokepublic(IN objecttype VARCHAR(20))
BEGIN
DECLARE revtxt VARCHAR(1000);
DECLARE curtxt VARCHAR(1000);
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE objname VARCHAR(128);
DECLARE objschema VARCHAR(128);
DECLARE stmt STATEMENT;
DELCARE cur CURSOR FOR stmt;
SET curtxt = CASE UCASE(objecttyp e) WHEN 'TABLE'
THEN 'SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE
TABSCHEMA LIKE ''SYS%'''
...
END;
PREPARE stmt FROM curtxt;
OPEN cur;
LOOP
FETCH TABSCHEMA, TABNAME INTO OBJSCHEMA, OBJNAME;
IF SQLCODE = 100 THEN LEAVE; END IF;
SET revtxt = 'REVOKE SELECT ON ' || objtype || ' "' || objschema ||
'"."' || objname || '" FROM PUBLIC';
EXECUTE IMMEDIATE revtxt;
END LOOP;
END
Well, something like that....
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference http://www.ibm.com/software/data/ond...ness/conf2006/
Serge Rielau wrote: Bob Stearns wrote:
I just created a new user and granted connect and select on a single view, only. When I connect to my database, the new user has at least select privileges on the whole database. What am I doing wrong or misunderstandin g? How do I discover all the privileges granted on my database? How do I revoke all privileges and then restore just the ones I want? Does public get any privileges by default?
Bob,
How did you test your hypothesis. I suspect you tried to select from a SYSCAT view or a SYSIBM table. By default PUBLIC gets granted SELECT on the catalog objects (SYSCAT, SYSIBM, SYSFUN and SYSPROC). In DB2 9 there is a new RESTRICT option that creates the database very tight to begin with. On DB2 V8 a simple procedure revoking SEELCT from PUBLIC on these objects should do just fine. Something like: CREATE PROCEDURE revokepublic(IN objecttype VARCHAR(20)) BEGIN DECLARE revtxt VARCHAR(1000); DECLARE curtxt VARCHAR(1000); DECLARE SQLCODE INTEGER; DECLARE SQLSTATE CHAR(5); DECLARE objname VARCHAR(128); DECLARE objschema VARCHAR(128); DECLARE stmt STATEMENT; DELCARE cur CURSOR FOR stmt; SET curtxt = CASE UCASE(objecttyp e) WHEN 'TABLE' THEN 'SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA LIKE ''SYS%''' ... END; PREPARE stmt FROM curtxt; OPEN cur; LOOP FETCH TABSCHEMA, TABNAME INTO OBJSCHEMA, OBJNAME; IF SQLCODE = 100 THEN LEAVE; END IF; SET revtxt = 'REVOKE SELECT ON ' || objtype || ' "' || objschema || '"."' || objname || '" FROM PUBLIC'; EXECUTE IMMEDIATE revtxt; END LOOP; END
Well, something like that....
Cheers Serge
Actually I tried a select on one of my own tables, since I granted
SELECT to a VIEW based on one of my tables.
However I figured out what I did wrong. This the new user I was having
so much trouble with last week and one of the straws I grasped was to
make this new user as like some of my working users as possible,
including groups. At least one of those groups must have admin
authorization. As soon as I removed the unnecessary groups, the userid
behaved as I wish.
Thanks for the procedure, I will keep it against future need.
Is everyone with connect authorization in the group public? Is there a
way to make a schema invisible to public?
Bob Stearns wrote: Is everyone with connect authorization in the group public?
Yes, everyone is in the PUBLIC group.
Is there a way to make a schema invisible to public?
Short of revoking the privileges on all objects in this schema from PUBLIC:
no.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Knut Stolze wrote: Bob Stearns wrote:
Is everyone with connect authorization in the group public?
Yes, everyone is in the PUBLIC group.
Is there a way to make a schema invisible to public?
Short of revoking the privileges on all objects in this schema from PUBLIC: no.
Can I revoke/exclude someone from the PUBLIC group?
Knut Stolze wrote: Bob Stearns wrote:
Is everyone with connect authorization in the group public?
Yes, everyone is in the PUBLIC group.
Is there a way to make a schema invisible to public?
Short of revoking the privileges on all objects in this schema from PUBLIC: no.
PUBLIC has no privileges on any object (according to the error message)
that I don't wish my user to see. When he lists tables/views from my
schema, I want him to see only those he can use.
Bob Stearns wrote: Can I revoke/exclude someone from the PUBLIC group?
No because this wouldn't be a public group any longer.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Bob Stearns wrote: PUBLIC has no privileges on any object (according to the error message) that I don't wish my user to see. When he lists tables/views from my schema, I want him to see only those he can use.
Then you could do a join with SYSCAT.TABAUTH and filter-out all tables/views
on which the user has no direct or indirect privileges.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Knut Stolze wrote: Bob Stearns wrote:
PUBLIC has no privileges on any object (according to the error message) that I don't wish my user to see. When he lists tables/views from my schema, I want him to see only those he can use.
Then you could do a join with SYSCAT.TABAUTH and filter-out all tables/views on which the user has no direct or indirect privileges.
I am not displaying the list, the odbc application is. The users
involved will have access to about a dozen views, out of over 150 tables
and views in the schema (even more when the table display routine shows
all tables/views from all schemas, intermixed). They don't need to see
the large number of "irrelevant " tables (I believe it was Bismarck who
said "Laws are like sausages. It's better not to see them being made."
It applies here to.)
Bob Stearns wrote: I am not displaying the list, the odbc application is. The users involved will have access to about a dozen views, out of over 150 tables and views in the schema (even more when the table display routine shows all tables/views from all schemas, intermixed). They don't need to see the large number of "irrelevant " tables
Well, in a way that sounds to me as if your application is not doing what it
should, i.e. request only the "relevant" tables in the first place.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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 could tell, with no
warnings or error messages.
In nearly all respects, the database appears to be running as expected. I
have the book "PHP and mySQL Web Development" by Luke Welling and Laura
Thomson, and have been working through the examples...
|
by: maxwell |
last post by:
mysql Ver 3.23.54
Having read, as best I could, the docs at http://www.mysql.com/documentation ...,
on host (Linux from scratch, kernel 2.4.20) "mysql.here.com" I enter:
mysql> GRANT ALL PRIVILEGES ON db.* TO root@'max.here.com';
Query OK, 0 rows affected (0.00 sec)
but ...
|
by: usaims |
last post by:
Hello:
I have just created a user with specific permissions to certain tables
in different databases. Now I have to create serveral more users with
the same permission. Is there a command in MySQL that allows me to
create the new user by copying the privileges from a template user?
MySQL 4.0.16
usaims
|
by: Andy S. |
last post by:
Hi,
I'm new to DB2. I want to view all the exisiting privileges (i.e. which
users have which privilegens) for a given stored procedure that exists
on the database.
How do I do this?
Thanks in advance!
| |
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 package and to PUBLIC
are used for authorization checking when static SQL is bound.
Privileges granted through groups are >>>>not!!!!<<<< used for
authorization checking when static SQL is bound. The user with a valid
authID who binds a package...
|
by: steingold |
last post by:
Hi All.
Is it possible to define an external stored procedure to be executed
not with the executing user privileges, but instead with the user who
created the stored procedure privileges in db2 version 8.1 on
linux/unix ?
Thanks in advance
Yaron
|
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 call to CreateProcessAsUser always returns a 1314 error: 'A required privilege is not held by the client.'
I realize from the doc's that the calling process must have SE_TCB_NAME privilaege to assign new privileges to the new process and I have done...
|
by: Frank Swarbrick |
last post by:
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;...
|
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...
|
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,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
|
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();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
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...
| |