oracle10g在grant create session 后，怎样限制只能查看本用户的表和过程？

[hlilna]

oracle10g在grant create session 后，怎样限制只能查看本用户的表和过程？

我create user testhl
identified by testhl
default tablespace TBS_DATA_0
profile DEFAULT;

grant create session to testhl;


然后 可以查看
select * from zhwd.table1 where rownum< 3;
select text from dba_source where owner = 'ZHWD' and name= 'SP_TEST_PROC';

怎样限制？谢谢，在线等

[小灰狼W]

select any table这个权限赋给了public...

[小灰狼W]

用DBA账户或sys执行
revoke select any table from public

[IndependentDeveloper]

[Quote=引用 8 楼 hlilna 的回复:]
select * from user_sys_privs;

USERNAME                      |PRIVILEGE                              |ADM
------------------------------|----------------------------------------|---
PUBLIC                        |DEBUG CONNECT SESSION                  |NO
PUBLIC                        |SELECT ANY TABLE                        |NO
PUBLIC                        |SELECT ANY DICTIONARY                  |NO
TESTHL                        |CREATE SESSION                          |NO

这个session权限怎样去掉？
[/Quote]
我晕,你这是有人+了public权限.
根本就不是该用户的......
revoke select any table from public;

[hlilna]

是否取消public权限，就可以解决呢，public用户权限用sys账户可以删除吗？

这样取消以后，其他用户例如：test2的用户，也自动没有了create any table 权限了，这样对大多用户有影响，怎么办？

[小灰狼W]

public拥有的权限所有用户都有
没办法
你可以删除public权限，然后再把权限赋给相关用户或角色

[小灰狼W]

你把其他需要访问别的用户表权限的用户，统一赋予一个角色
将这些权限赋给这个角色
不要用public

[IndependentDeveloper]

[Quote=引用 14 楼 wildwave 的回复:]
你把其他需要访问别的用户表权限的用户，统一赋予一个角色
将这些权限赋给这个角色
不要用public
[/Quote]
分给他吧,说的不错

[hlilna]

明白了，感谢两位的帮助，wildwave和 jinxino_o

[呦呦]

楼主，你这分怎么分的？真晕

[hlilna]

完了，搞错了，中午有点晕，真是抱歉，这个分好像不能改了？

[小灰狼W]

。。没关系，嘿嘿

[hlilna]

抱歉，重新开了个帖子，请wildwave去留言一下

http://topic.csdn.net/u/20090903/14/fac0afdf-95d3-440a-bf1e-8270c1442442.html?94735

[IndependentDeveloper]

如果你没有给该用户select any table 这个权限
那么默认该用户只能看到自己的object

[hlilna]

但是实际情况不是这样呀，我也非常奇怪

[IndependentDeveloper]

SQL> create user test10 identified by test                                                      
  2  default tablespace users                                                                   
  3  temporary tablespace temp;                                                                 

User created.

SQL> grant create session to test10;                                                            

Grant succeeded.

SQL> conn test10/test                                                                           
Connected.
SQL> show user                                                                                  
USER is "TEST10"
SQL> select * from scott.emp;                                                                   
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from tab;                                                                         

no rows selected
SQL> conn scott/tiger                                                                           
Connected.
SQL> show user                                                                                  
USER is "SCOTT"
SQL> select * from tab;                                                                         

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

你的profile文件看下

SQL> select * from dba_profiles where profile='DEFAULT';                                        

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

16 rows selected.

[IndependentDeveloper]

SQL> create user test11 identified by test                                                      
  2  default tablespace users                                                                   
  3  temporary tablespace temp                                                                  
  4  profile default;                                                                           

User created.

SQL> grant create session to test11;                                                            

Grant succeeded.

SQL> conn test11/test                                                                           
Connected.
SQL> show user                                                                                  
USER is "TEST11"
SQL> select * from scott.emp;                                                                   
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

我试了下,还是没问题啊,你的测试环境说下?

[hlilna]

select * from dba_profiles where profile='DEFAULT';

PROFILE |RESOURCE_NAME |RESOURCE|LIMIT
------------------------------|--------------------------------|--------|----------------------------------------
DEFAULT |COMPOSITE_LIMIT |KERNEL |UNLIMITED
DEFAULT |SESSIONS_PER_USER |KERNEL |UNLIMITED
DEFAULT |CPU_PER_SESSION |KERNEL |UNLIMITED
DEFAULT |CPU_PER_CALL |KERNEL |UNLIMITED
DEFAULT |LOGICAL_READS_PER_SESSION |KERNEL |UNLIMITED
DEFAULT |LOGICAL_READS_PER_CALL |KERNEL |UNLIMITED
DEFAULT |IDLE_TIME |KERNEL |UNLIMITED
DEFAULT |CONNECT_TIME |KERNEL |UNLIMITED
DEFAULT |PRIVATE_SGA |KERNEL |UNLIMITED
DEFAULT |FAILED_LOGIN_ATTEMPTS |PASSWORD|10
DEFAULT |PASSWORD_LIFE_TIME |PASSWORD|UNLIMITED
DEFAULT |PASSWORD_REUSE_TIME |PASSWORD|UNLIMITED
DEFAULT |PASSWORD_REUSE_MAX |PASSWORD|UNLIMITED
DEFAULT |PASSWORD_VERIFY_FUNCTION |PASSWORD|NULL
DEFAULT |PASSWORD_LOCK_TIME |PASSWORD|UNLIMITED
DEFAULT |PASSWORD_GRACE_TIME |PASSWORD|UNLIMITED

[hlilna]

问题仍旧存在，没招了，

[小灰狼W]

你用这个账户看看有哪些权限
select * from session_privs

[hlilna]

强人呀，
select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION


select * from user_sys_privs;

USERNAME |PRIVILEGE |ADM
------------------------------|----------------------------------------|---
PUBLIC |DEBUG CONNECT SESSION |NO
PUBLIC |SELECT ANY TABLE |NO
PUBLIC |SELECT ANY DICTIONARY |NO
TESTHL |CREATE SESSION |NO

这个session权限怎样去掉？