分类目录归档：数据库

DBA_SYS_PRIVS 用户所拥有的系统权限

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 16 13:26:09 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user xff identified by xifenfei;

User created.

SQL> grant connect to xff;

Grant succeeded.

SQL> select * from DBA_SYS_PRIVS where grantee='XFF';

no rows selected

SQL> REVOKE CONNECT FROM XFF;

Revoke succeeded.

SQL> grant create session to xff;

Grant succeeded.

SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO

SQL> grant select  on chf.t_1 to xff;

Grant succeeded.

SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO
--说明只能查询系统权限，不能查询角色，不能查询用户权限

DBA_SYS_PRIVS 用户所拥有的角色

SQL> grant resource to xff;

授权成功。

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='XFF';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
XFF                            RESOURCE                       NO  YES

ROLE_SYS_PRIVS 角色所拥有的系统权限

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='CONNECT';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';

no rows selected

SQL> SELECT * FROM SESSION_ROLES;

ROLE
------------------------------
CONNECT

SQL> CONN / AS SYSDBA
Connected.
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

8 rows selected.
--很多时候只能使用sysdba(或者具体特定权限)才能够查询角色有哪些系统权限

ROLE_ROLE_PRIVS: 角色被赋予的角色

SQL> SELECT *FROM ROLE_ROLE_PRIVS WHERE ROLE='DBA';

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            OLAP_DBA                       NO
DBA                            SCHEDULER_ADMIN                YES
DBA                            DELETE_CATALOG_ROLE            YES
DBA                            EXECUTE_CATALOG_ROLE           YES
DBA                            WM_ADMIN_ROLE                  NO
DBA                            EXP_FULL_DATABASE              NO
DBA                            SELECT_CATALOG_ROLE            YES
DBA                            JAVA_DEPLOY                    NO
DBA                            GATHER_SYSTEM_STATISTICS       NO
DBA                            JAVA_ADMIN                     NO
DBA                            XDBADMIN                       NO

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            IMP_FULL_DATABASE              NO
DBA                            XDBWEBSERVICES                 NO

13 rows selected.

SESSION_PRIVS 当前用户所拥有的全部权限

SQL> conn xff/xifenfei
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> conn / as sysdba
Connected.
SQL> revoke create session from xff;

Revoke succeeded.

SQL> grant connect to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
--只能查看系统权限或者角色中包含的系统权限，不能查看用户权限 

SESSION_ROLES: 当前用户被激活的角色

SQL> SELECT * from SESSION_ROLES;

no rows selected

SQL> show user;
USER is "SYS"
SQL> conn xff/xifenfei
Connected.
SQL> SELECT *FROM SESSION_ROLES;

ROLE
------------------------------
CONNECT
--sysdba查询无role选项，全部是由系统权限构成

查询某用户的所有系统权限

SQL> SELECT PRIVILEGE, ADMIN_OPTION
  2    FROM DBA_SYS_PRIVS
  3   WHERE GRANTEE = &USERNAME
  4  UNION
  5  --角色转换为权限
  6  SELECT PRIVILEGE, ADMIN_OPTION
  7    FROM ROLE_SYS_PRIVS
  8   WHERE ROLE IN
  9         (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
 10  UNION
 11  --角色的角色转为权限
 12  SELECT PRIVILEGE, ADMIN_OPTION
 13    FROM ROLE_SYS_PRIVS
 14   WHERE ROLE IN (SELECT GRANTED_ROLE
 15                    FROM ROLE_ROLE_PRIVS
 16                   WHERE ROLE IN (SELECT GRANTED_ROLE
 17                                    FROM DBA_ROLE_PRIVS
 18                                   WHERE GRANTEE = &USERNAME));
输入 username 的值:  'XFF'
原值    3:  WHERE GRANTEE = &USERNAME
新值    3:  WHERE GRANTEE = 'XFF'
输入 username 的值:  'XFF'
原值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
新值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'XFF')
输入 username 的值:  'XFF'
原值   18:                                  WHERE GRANTEE = &USERNAME))
新值   18:                                  WHERE GRANTEE = 'XFF'))

PRIVILEGE                                ADM
---------------------------------------- ---
CREATE CLUSTER                           NO
CREATE INDEXTYPE                         NO
CREATE OPERATOR                          NO
CREATE PROCEDURE                         NO
CREATE SEQUENCE                          NO
CREATE SESSION                           NO
CREATE TABLE                             NO
CREATE TRIGGER                           NO
CREATE TYPE                              NO
UNLIMITED TABLESPACE                     NO

已选择10行。

表相关权限视图

SELECT *FROM TABLE_PRIVILEGES;
SELECT * FROM dba_TAB_PRIVS;
SELECT * FROM ROLE_TAB_PRIVS;

一、sql操作

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 15 15:00:15 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             285216048 bytes
Database Buffers          121634816 bytes
Redo Buffers                8466432 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode


SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

二、alert日志内容

Tue Nov 15 15:00:18 2011
Adjusting the default value of parameter parallel_max_servers
from 320 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.
ORACLE_HOME = /opt/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      node1.srtcloud.com
Release:        2.6.18-238.19.1.el5
Version:        #1 SMP Fri Jul 15 07:31:24 EDT 2011
Machine:        x86_64
Using parameter settings in server-side spfile /opt/oracle/product/11.2.0/db_1/dbs/spfilet1.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 400M
  control_files            = "/opt/oracle/oradata/t1/control01.ctl"
  control_files            = "/opt/oracle/fast_recovery_area/t1/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "/opt/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4122M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=t1XDB)"
  local_listener           = "LISTENER_T1"
  audit_file_dest          = "/opt/oracle/admin/t1/adump"
  audit_trail              = "DB"
  db_name                  = "t1"
  open_cursors             = 300
  diagnostic_dest          = "/opt/oracle"
Tue Nov 15 15:00:22 2011
PMON started with pid=2, OS id=26704 
Tue Nov 15 15:00:22 2011
PSP0 started with pid=3, OS id=26706 
Tue Nov 15 15:00:23 2011
VKTM started with pid=4, OS id=26708 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Tue Nov 15 15:00:23 2011
GEN0 started with pid=5, OS id=26712 
Tue Nov 15 15:00:23 2011
DIAG started with pid=6, OS id=26714 
Tue Nov 15 15:00:23 2011
DBRM started with pid=7, OS id=26716 
Tue Nov 15 15:00:23 2011
DIA0 started with pid=8, OS id=26718 
Tue Nov 15 15:00:23 2011
MMAN started with pid=9, OS id=26720 
Tue Nov 15 15:00:23 2011
DBW0 started with pid=10, OS id=26722 
Tue Nov 15 15:00:23 2011
LGWR started with pid=11, OS id=26724 
Tue Nov 15 15:00:23 2011
CKPT started with pid=12, OS id=26726 
Tue Nov 15 15:00:23 2011
SMON started with pid=13, OS id=26728 
Tue Nov 15 15:00:23 2011
RECO started with pid=14, OS id=26730 
Tue Nov 15 15:00:23 2011
MMON started with pid=15, OS id=26732 
Tue Nov 15 15:00:23 2011
MMNL started with pid=16, OS id=26734 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/oracle
Tue Nov 15 15:00:23 2011
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 2578048199
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Tue Nov 15 15:00:33 2011
drop database
ORA-12719 signalled during: drop database...
Tue Nov 15 15:00:47 2011
Stopping background process MMNL
Stopping background process MMON
Starting background process MMON
Tue Nov 15 15:00:49 2011
MMON started with pid=15, OS id=26788 
Starting background process MMNL
Tue Nov 15 15:00:49 2011
MMNL started with pid=16, OS id=26790 
ALTER SYSTEM enable restricted session;
Tue Nov 15 15:01:06 2011
drop database
Deleted file /opt/oracle/oradata/t1/system01.dbf
Deleted file /opt/oracle/oradata/t1/sysaux01.dbf
Deleted file /opt/oracle/oradata/t1/undotbs01.dbf
Deleted file /opt/oracle/oradata/t1/users01.dbf
Deleted file /opt/oracle/oradata/t1/redo01.log
Deleted file /opt/oracle/oradata/t1/redo02.log
Deleted file /opt/oracle/oradata/t1/redo03.log
Deleted file /opt/oracle/oradata/t1/temp01.dbf
Deleted file /opt/oracle/product/11.2.0/db_1/dbs/snapcf_t1.f
Shutting down archive processes
Archiving is disabled
Create Relation ADR_CONTROL
Create Relation ADR_INVALIDATION
Create Relation INC_METER_IMPT_DEF
Create Relation INC_METER_PK_IMPTS
USER (ospid: 26761): terminating the instance
Instance terminated by USER, pid = 26761
Tue Nov 15 15:01:18 2011
Deleted file /opt/oracle/oradata/t1/control01.ctl
Deleted file /opt/oracle/fast_recovery_area/t1/control02.ctl
Completed: drop database
Shutting down instance (abort)
License high water mark = 1
Tue Nov 15 15:01:32 2011
Instance shutdown complete

三、后续工作
1、清除相关日志trace文件$ORACLE_BASE/diag
2、删除fast_recovery_area文件$ORACLE_BASE/fast_recovery_area
3、删除归档日志(根据配置)
4、删除/etc/oratab中关于该数据库的记录(t1:/opt/oracle/product/11.2.0/db_1:N)

四、补充说明
1、在能够使用dbca删除数据库的情况下，应该选择dbca，这个删除的更加干净
2、dbca删除数据库也需要清理部分文件(如：归档日志)
3、如果对数据库的存储结构比较了解，可以人工关闭数据库后，手工删除相关文件
4、drop database使用于10g及其以上版本