oracle 分配权限无效果怪异

显示全部楼层 · 2010-2-11 01:01:06

oracle 分配权限无效果
我在oracle上进行了如下操作：
1。CREATE TABLESPACE "MY_SPACE" LOGGING DATAFILE '/PATH/MY_SPACE.ora' SIZE 400M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO
2。create user dvoss identified by dvoss default tablespace MY_SPACE Temporary TABLESPACE Temp;
3。grant connect,resource,dba to dvoss
4。commit;
退出
然后
SQL> conn dvoss/dvoss
Connected.
SQL> create table aa(ID NUMBER);
create table aa(ID NUMBER)
*
ERROR at line 1:
ORA-01031: insufficient privileges
显示无权限
但是我已经分配了connect,resource,dba给它
请问各位大侠，这是什么原因大致的呢

千问 · 2010-2-11 01:01:06

grant connect,resource,dba to dvoss
这句执行了？后面似乎没有分号。
用dvoss用户登陆后
执行
select * from session_roles

千问 · 2010-2-11 01:01:06

grant connect,resource,dba to dvoss
这句执行了？后面似乎没有分号。
用dvoss用户登陆后
执行
select * from session_roles
＝＝＝＝＝＝＝＝＝＝＝＝＝
逗号后来加了，这没有问题。
SQL> select * from session_roles
2;
ROLE
------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
10 rows selected.

千问 · 2010-2-11 01:01:06

指定一下表空间试试？

千问 · 2010-2-11 01:01:06

我已经指定了表空间啊MY_SPACE

千问 · 2010-2-11 01:01:06

建表的时候指定

千问 · 2010-2-11 01:01:06

问题在于你的这个oracle用户对于/PATH/MY_SPACE.ora'有没有写权限啊？

千问 · 2010-2-11 01:01:06

最初由 npsajax 发布
[B]grant connect,resource,dba to dvoss
这句执行了？后面似乎没有分号。
用dvoss用户登陆后
执行
select * from session_roles
＝＝＝＝＝＝＝＝＝＝＝＝＝
逗号后来加了，这没有问题。
SQL> select * from session_roles
2;
ROLE
------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
10 rows selected. [/B]
你既然授权RESOURCE角色，为什么这里会没有？
最好的办法是把所有步骤一步步的完整贴出来，包括Oracle所有的输出

千问 · 2010-2-11 01:01:06

这是我创建的全部的过程
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
$ bash
bash-2.03$ ps
pspsigpstackpstop
bash-2.03$ pwd
/export/home/osdba
bash-2.03$ ls
DirectViewOSSOraHome1 cao.sqlgrant.sqlnsmail oradata
Mail admin
dvoss.dmpjre
oraInventory oui
bash-2.03$ cd /export/
bash-2.03$ ls
home
bash-2.03$ ls -la
total 6
drwxr-xr-x 3 root sys
512 Dec 132002 .
drwxr-xr-x22 root root 512 Sep 13 16:23 ..
drwxr-xr-x17 root root 512 Jan 17 14:27 home
bash-2.03$ cd home/
bash-2.03$ ls -la
total 320198
drwxr-xr-x 2 root other512 Dec 132002
drwxr-xr-x17 root root 512 Jan 17 14:27 .
drwxr-xr-x 3 root sys
512 Dec 132002 ..
drwxr-xr-x 189 root other 4096 Aug 10 11:02 8_Recommended
-rw-r--r-- 1 root other163822737 Aug 16 15:59 8_Recommended.zip
drwxr-xr-x 2 root root 512 Dec 142002 TT_DB
drwxr-xr-x 2 ak47 staff512 Jan 19 19:08 akazam
drwxr-xr-x 2 apache adm
512 Dec 142002 apache
drwxr-xr-x 5 bill bill 512 Dec 162002 bill
drwxr-xr-x 3 cscape staff512 Dec 202002 cscape
drwxr-xr-x 2 root other512 Jun52003 data1
drwxr-xr-x 2 ak47 staff512 Apr32004 dtsai
drwxr-xr-x 2 root other512 Aug 16 15:53 files
drwxr-xr-x13 jzhu other 1024 Jan 17 11:58 jzhu
drwx------ 9 root root8192 Dec 132002 lost+found
drwxr-xr-x14 osdbaadm
512 Jan 20 03:44 osdba
drwxr-x--- 3 tuxedo staff512 Dec 192002 tuxedo
drwxr-xr-x 3 root other512 May 102003 wen
bash-2.03$ cd osdba/
bash-2.03$ ls -la
total 108
drwxr-xr-x14 osdbaadm
512 Jan 20 03:44 .
drwxr-xr-x17 root root 512 Jan 17 14:27 ..
-rw------- 1 osdbaadm
133 Dec 182002 .TTauthority
-rw------- 1 osdbaadm
244 Dec 182002 .Xauthority
-rw------- 1 osdbaadm 54 Dec 182002 .bash_history
drwxr-xr-x11 osdbaadm
512 Dec 182002 .dt
-rwxr-xr-x 1 osdbaadm 5111 Dec 142002 .dtprofile
drwx------ 5 osdbaadm
512 Dec 142002 .netscape
-rw-r--r-- 1 osdbaadm
825 Dec 192002 .profile
drwx------ 2 osdbaadm
512 Dec 182002 .solregis
drwxrwxr-x17 osdbaadm
512 Jan 17 13:49 DirectViewOSS
drwx------ 2 osdbaadm
512 Dec 142002 Mail
drwxr-xr-x60 osdbaadm 1536 Jan 17 12:52 OraHome1
drwxr-xr-x 3 osdbaadm
512 Dec 192002 admin
-rw-r--r-- 1 osdbaadm 3585 Jan 20 03:44 cao.sql
-rw-r--r-- 1 osdbaadm24576 Jan 20 02:52 dvoss.dmp
-rw-r--r-- 1 osdbaadm 1194 Jan 20 03:35 grant.sql
drwxrwxr-x 4 osdbaadm
512 Dec 142002 jre
drwx------ 2 osdbaadm
512 Dec 142002 nsmail
drwxrwxr-x12 osdbaosdba512 Jan 17 13:50 oraInventory
drwxr-xr-x 3 osdbaadm
512 Jan 20 22:16 oradata
drwxrwxr-x 6 osdbaadm
512 Jan 17 13:09 oui
bash-2.03$ cd oradata/
bash-2.03$ ls -ls
total 2
2 drwxr-xr-x 4 osdbaadm
512 Jan 20 04:25 tb3
bash-2.03$ ls -la
total 6
drwxr-xr-x 3 osdbaadm
512 Jan 20 22:16 .
drwxr-xr-x14 osdbaadm
512 Jan 20 03:44 ..
drwxr-xr-x 4 osdbaadm
512 Jan 20 04:25 tb3
bash-2.03$ cd tb3/
bash-2.03$ ls -la
total 10101272
drwxr-xr-x 4 osdbaadm
512 Jan 20 04:25 .
drwxr-xr-x 3 osdbaadm
512 Jan 20 22:16 ..
-rw-r----- 1 osdbaadm419438592 Jan 20 22:43 OSS_SPACE.ora
-rw-r--r-- 1 osdbaadm2097160192 Jan 20 05:00 TBILL_DATA.dbf
-rw-r--r-- 1 osdbaadm1048584192 Jan 20 05:00 TBILL_INDX.dbf
-rw-r--r-- 1 osdbaadm524296192 Jan 20 05:00 TBILL_TEST.dbf
drwxr-xr-x 2 osdbaadm
512 Jan 17 12:53 archive
-rw-r--r-- 1 osdbaadm1613824 Jan 20 22:45 control01.ctl
-rw-r--r-- 1 osdbaadm1613824 Jan 20 22:45 control02.ctl
-rw-r--r-- 1 osdbaadm1613824 Jan 20 22:45 control03.ctl
-rw-r----- 1 osdbaadm104858112 Jan 20 22:44 redo01.log
-rw-r----- 1 osdbaadm104858112 Jan 20 05:00 redo02.log
-rw-r----- 1 osdbaadm104858112 Jan 20 05:00 redo03.log
drwxr-xr-x 2 osdbaadm
512 May 272003 standby
-rw-r--r-- 1 osdbaadm419438592 Jan 20 22:18 system01.dbf
-rw-r--r-- 1 osdbaadm104865792 Jan32003 temp01.dbf
-rw-r--r-- 1 osdbaadm26222592 Jan 20 05:00 tools01.dbf
-rw-r--r-- 1 osdbaadm209723392 Jan 20 22:37 undotbs01.dbf
bash-2.03$ x
bash: x: command not found
bash-2.03$ xs
bash: xs: command not found
bash-2.03$ cd
bash-2.03$ pwd
/export/home/osdba
bash-2.03$ ls
DirectViewOSSOraHome1 cao.sqlgrant.sqlnsmail oradata
Mail admin
dvoss.dmpjre
oraInventory oui
bash-2.03$ ls -la
total 108
drwxr-xr-x14 osdbaadm
512 Jan 20 03:44 .
drwxr-xr-x17 root root 512 Jan 17 14:27 ..
-rw------- 1 osdbaadm
133 Dec 182002 .TTauthority
-rw------- 1 osdbaadm
244 Dec 182002 .Xauthority
-rw------- 1 osdbaadm 54 Dec 182002 .bash_history
drwxr-xr-x11 osdbaadm
512 Dec 182002 .dt
-rwxr-xr-x 1 osdbaadm 5111 Dec 142002 .dtprofile
drwx------ 5 osdbaadm
512 Dec 142002 .netscape
-rw-r--r-- 1 osdbaadm
825 Dec 192002 .profile
drwx------ 2 osdbaadm
512 Dec 182002 .solregis
drwxrwxr-x17 osdbaadm
512 Jan 17 13:49 DirectViewOSS
drwx------ 2 osdbaadm
512 Dec 142002 Mail
drwxr-xr-x60 osdbaadm 1536 Jan 17 12:52 OraHome1
drwxr-xr-x 3 osdbaadm
512 Dec 192002 admin
-rw-r--r-- 1 osdbaadm 3585 Jan 20 03:44 cao.sql
-rw-r--r-- 1 osdbaadm24576 Jan 20 02:52 dvoss.dmp
-rw-r--r-- 1 osdbaadm 1194 Jan 20 03:35 grant.sql
drwxrwxr-x 4 osdbaadm
512 Dec 142002 jre
drwx------ 2 osdbaadm
512 Dec 142002 nsmail
drwxrwxr-x12 osdbaosdba512 Jan 17 13:50 oraInventory
drwxr-xr-x 3 osdbaadm
512 Jan 20 22:16 oradata
drwxrwxr-x 6 osdbaadm
512 Jan 17 13:09 oui
bash-2.03$ pwd
/export/home/osdba
bash-2.03$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on D??ú?? 1?? 20 22:46:56 2006
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> CREATE TABLESPACE "MY_SPACE" LOGGING DATAFILE '/export/home/osdba/oradata/tb3/MY_SPACE.ora' SIZE 400M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO
2;
Tablespace created.
SQL> create user dvoss identified by dvoss default tablespace OSS_SPACE Temporary TABLESPACE Temp quota unlimited on MY_SPACE
2;
create user dvoss identified by dvoss default tablespace OSS_SPACE Temporary TABLESPACE Temp quota unlimited on MY_SPACE
*
ERROR at line 1:
ORA-00959: tablespace 'OSS_SPACE' does not exist

SQL> create user dvoss identified by dvoss default tablespace MY_SPACE Temporary TABLESPACE Temp quota unlimited on MY_SPACE
2;
User created.
SQL> grant connect,resource,dba to DVOSS
2;
Grant succeeded.
SQL> commit;
Commit complete.
SQL> disconn
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> conn dvoss/dvoss
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
11 rows selected.
SQL> select * from session_privs
2;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
SELECT ANY TABLE
ALTER DATABASE
EXECUTE ANY PROCEDURE
6 rows selected.
SQL> CREATE TABLE "DVOSS"."TEST" ("ID" VARCHAR2(10) NOT NULL, "REMARK" VARCHAR2(10) NOT NULL)TABLESPACE "MY_SPACE" ;
CREATE TABLE "DVOSS"."TEST" ("ID" VARCHAR2(10) NOT NULL, "REMARK" VARCHAR2(10) NOT NULL)TABLESPACE "MY_SPACE"
*
ERROR at line 1:
ORA-01031: insufficient privileges
还是这样，没有权限，很奇怪！
我使用同样的命令，在我一台winxp的机器上没有问题

千问 · 2010-2-11 01:01:06

没有create table 权限，当然创建不了表。
但很奇怪，明明有DBA 权限，怎么session_privs只有这几个权限？

oracle 分配权限无效果 怪异

oracle 分配权限无效果怪异