为什么在oracle10i中,我在系统管理员用户下,却无法将drop table 系统权限授予其他用户:SQL> connect sys/ab12345 as sysdba;
已连接。
SQL> grant drop table to gail;
grant drop table to gail
*
第 1 行出现错误:
ORA-00990: 权限缺失或无效
已连接。
SQL> grant drop table to gail;
grant drop table to gail
*
第 1 行出现错误:
ORA-00990: 权限缺失或无效
但是在系统特权中有一项特权 drop table的特权,为什么不能用呢?
--首先drop any table 权限的描述是这样的
--Drop or truncate tables or table partitions in any schema.--我们再看一下有没有drop table 这个权限--查询一下SYSTEM_PRIVILEGE_MAP 表
SQL> select * from SYSTEM_PRIVILEGE_MAP where name like 'DROP%TABLE' ; PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-44 DROP ANY TABLE 0
--没有drop table这个权限,怎么能删除自己建立的表呢?
--这是一个小例子
SQL> create user dex2013 identified by xiaojun default tablespace users ;User created.SQL> grant create session to dex2013 ;Grant succeeded.SQL> alter user dex2013 quota unlimited on users ;User altered.SQL> conn dex2013/xiaojun
Connected.
SQL> create table t (x int) ;Table created.SQL> drop table t ;Table dropped.--可以看到,只要你是对象的拥有者(must be in your own schema),就可以drop,view、SEQUENCE、INDEX同理.
Allows You to …
CREATE SESSION
Connect to a database.
CREATE SEQUENCE
Create a sequence. A sequence is a series of numbers, which are typically used to automatically populate a primary key column. You'll learn about sequences in the next chapter.
CREATE SYNONYM
Create a synonym. A synonym allows you to reference a table in another schema. You'll learn about synonyms later in this chapter.
CREATE TABLE
Create a table.
CREATE ANY TABLE
Create a table in any schema.
DROP TABLE
Drop a table.
DROP ANY TABLE
Drop a table from any schema.
CREATE PROCEDURE
Create a stored procedure.
EXECUTE ANY PROCEDURE
Execute a procedure in any schema.
CREATE USER
Create a user.
DROP USER
Drop a user.
CREATE VIEW
Create a view. A view is a stored query that allows you to access multiple tables and columns. You may then query the view as you would a table. You'll learn about views in the next chapter.
Note You can get the full list of system privileges in the Oracle SQL Reference manual.