DB2 v 8.1.9 linux中的特权 [英] Privileges in DB2 v 8.1.9 linux
问题描述
我刚刚创建了一个新用户并且仅在单个
视图中授予了连接和选择权限。当我连接到我的数据库时，新用户在整个数据库上至少具有
选择权限。我做错了什么或
误会？如何发现我的
数据库授予的所有权限？如何撤销所有权限，然后只恢复我想要的那些？b $ b？公共默认是否获得任何权限？
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
misunderstanding? 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 Stearns写道：
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
misunderstanding? 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，
你是如何测试你的假设的。我怀疑你试图从
SYSCAT视图或SYSIBM表中进行选择。
默认情况下，PUBLIC在目录对象上获得SELECT（SYSCAT，
SYSIBM，SYSFUN和SYSPROC）。
在DB2 9中有一个新的RESTRICT选项，可以很快地创建数据库
。
在DB2 V8上，在这些
对象上从PUBLIC撤销SEELCT的简单程序应该没问题。
类似于：
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（objecttype）WHEN''TABLE ''
然后从SYSCAT.TABLES WHERE''选择TABSCHEMA，TABNAME
TABSCHEMA喜欢''''SYS％''''
...
结束;
PREPARE stmt FROM curtxt;
OPEN cur;
LOOP
FETCH TABSCHEMA，TABNAME INTO OBJSCHEMA，OBJNAME;
如果SQLCODE = 100那么离开;结束IF;
SET revtxt =''REVOKE SELECT ON''|| objtype || ''''''|| objschema ||
''"。"''|| objname || "来自PUBLIC'';
EXECUTE IMMEDIATE revtxt;
END LOOP;
END
Well ，类似的......
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http://www.ibm.com/software/data/ond...ness/conf2006/
Serge Rielau写道：
Serge Rielau wrote:
Bob Stearns写道：
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
misunderstanding? 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?
鲍勃，
你是如何测试你的假设的。我怀疑你试图从SYSCAT视图或SYSIBM表中进行选择。
默认情况下，PUBLIC在目录对象（SYSCAT，
SYSIBM，SYSFUN和SYSPROC）上被授予SELECT。
在DB2 9中，有一个新的RESTRICT选项，可以非常紧密地创建数据库。
在DB2 V8上，从PUBLIC上撤销SEELCT的简单过程就可以了。
类似于：
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（objecttype）WHEN''TABLE''
那么'选择TABSCHEMA，来自SYSCAT.TABLES的TABNAME
TABSCHEMA喜欢'' ''SYS％'''''
......
结束;
PRETARE stmt FROM curtxt;
OPEN cur;
LOOP
FETCH TABSCHEMA，TABNAME INTO OBJSCHEMA，OBJNAME;
如果SQLCODE = 100那么离开; END IF;
SET revtxt =''REVOKE SELECT ON''|| objtype || ''''''|| objschema ||
''"。"''|| objname || "来自PUBLIC'';
EXECUTE IMMEDIATE revtxt;
结束循环;
结束
嗯，这样的东西....
干杯
Serge
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(objecttype) 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
实际上我在自己的桌子上尝试了一个选择，因为我根据我的一个给了一个VIEW，然后给了一个VIEW。桌子。
然而我弄清楚我做错了什么。这个新用户我上周遇到了很多麻烦，我抓到的其中一个吸引力就是让这个新用户像我的一些工作用户一样可能，
包括团体。这些组中至少有一个必须具有admin
授权。一旦我删除了不必要的组，用户ID
表现得如我所愿。
感谢您的程序，我会保留它以防将来需要。
群组中的每个人都有连接授权吗？是否有一种
的方式使一个模式对公众不可见？
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写道：
Bob Stearns wrote:
群组中的每个人都有连接授权吗？
是的，每个人都在PUBLIC小组中。
有没有一种方法可以让公众看不到架构？
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?
没有从PUBLIC撤销此架构中所有对象的权限：
no。
- < br $>
Knut Stolze
DB2信息集成开发
IBM德国
Short of revoking the privileges on all objects in this schema from PUBLIC:
no.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
这篇关于DB2 v 8.1.9 linux中的特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
