1、精選文檔Oracle 用戶及角色 介紹 一. 用戶管理1.1 建立用戶（數據庫驗證）CREATE USER DAVE IDENTIFIED BY pwdDEFAULT TABLESPACE usersTEMPORARY TABLESPACE tempQUOTA 5m ON users;1.2 修改用戶ALTER USER DAVE QUOTA 0 ON SYSTEM;1.3 刪除用戶DROP USER DAVE; DROP USER DAVE CASCADE;1.4 顯示用戶信息SELECT * FROM DBA_USERSSELECT * FROM DBA_TS_QUOTAS二.系統權限系統

2、權限作用CREATE SESSION連接到數據庫CREATE TABLE建表CREATE TABLESPACE建立表空間CREATE VIEW建立視圖CREATE SEQUENCE建立序列CREATE USER建立用戶系統權限是指執行特定類型SQL命令的權利，用于把握用戶可以執行的一個或一類數據庫操作。（新建用戶沒有任何權限）2.1 授予系統權限GRANT CREATE SESSION,CREATE TABLE TO DAVE; GRANT CREATE SESSION TO DAVE WITH ADMIN OPTION; 選項：ADMIN OPTION 使該用戶具有轉授系統權限的權限。2.2

3、 顯示系統權限查看全部系統權限：Select * from system_privilege_map;顯示用戶所具有的系統權限：Select * from dba_sys_privs;顯示當前用戶所具有的系統權限：Select * from user_sys_privs;顯示當前會話所具有的系統權限：Select * from session_privs;2.3 收回系統權限REVOKE CREATE TABLE FROM DAVE;REVOKE CREATE SESSION FROM DAVE;三 .角色：角色是一組相關權限的命名集合，使用角色最主要的目的是簡化權限管理。3.1 預定義角色。

4、CONNECT自動建立，包含以下權限：ALTER SESSION、CREATE CLUSTER、CREATE DATABASELINK、CREATE SEQUENCE、CREATE SESSION、CREATE SYNONYM、CREATE TABLE、CREATEVIEW。RESOURCE自動建立，包含以下權限：CREATE CLUSTER、CREATE PROCEDURE、CREATE SEQUENCE、CREATE TABLE、CREATE TRIGGR。3.2 顯示角色信息，§ROLE_SYS_PRIVS§ROLE_TAB_PRIVS§ROLE_ROLE_

5、PRIVS§SESSION_ROLES§USER_ROLE_PRIVS§DBA_ROLES四. Oracle用戶角色每個Oracle用戶都有一個名字和口令,并擁有一些由其創建的表、視圖和其他資源。Oracle角色（role）就是一組權限（privilege）(或者是每個用戶依據其狀態和條件所需的訪問類型)。用戶可以給角色授予或賜予指定的權限，然后將角色賦給相應的用戶。一個用戶也可以直接給其他用戶授權。數據庫系統權限（Database System Privilege）允許用戶執行特定的命令集。例如，CREATE TABLE權限允許用戶創建表，GRANT ANY P

6、RIVILEGE權限允許用戶授予任何系統權限。數據庫對象權限（Database Object Privilege）使得用戶能夠對各個對象進行某些操作。例如DELETE權限允許用戶刪除表或視圖的行，SELECT權限允許用戶通過select從表、視圖、序列（sequences）或快照 （snapshots）中查詢信息。4.1 創建用戶Oracle內部有兩個建好的用戶：SYSTEM和SYS。用戶可直接登錄到SYSTEM用戶以創建其他用戶，由于SYSTEM具有創建別的用戶的權限。在安裝Oracle時，用戶或系統管理員首先可以為自己建立一個用戶。例如：create user user01 identif

7、ied by u01;該命令還可以用來設置其他權限。要轉變一個口令，可以使用alter user命令：alter user user01 identified by usr01;現在user01的口令已由“u01”改為“usr01”。除了alter user命令以外，用戶還可以使用password命令。假如使用password命令，用戶輸入的新口令將不在屏幕上顯示。有dba特權的用戶可以通過password命令轉變任何其他用戶的口令；其他用戶只能轉變自己的口令。當用戶輸入password命令時，系統將提示用戶輸入舊口令和新口令，如下所示：passwordChanging password fo

8、r user01Old password:New password:Retype new password:當成功地修改了口令時，用戶會得到如下的反饋：Password changed4.2 刪除用戶刪除用戶，可以使用drop user命令，如下所示：drop user user01;假如用戶擁有對象，則不能直接刪除，否則將返回一個錯誤值。指定關鍵字CASCADE，可刪除用戶全部的對象，然后再刪除用戶。下面的例子用來刪除用戶與其對象：drop user user01 CASCADE;4.3 3種標準角色Oracle為了兼容以前的版本，供應了三種標準的角色（role）：CONNECT、RESOU

9、RCE和DBA。4.3.1. CONNECT Role(連接角色)臨時用戶，特殊是那些不需要建表的用戶，通常只賜予他們CONNECT role。CONNECT是使用Oracle的簡潔權限，這種權限只有在對其他用戶的表有訪問權時，包括select、insert、update和delete等，才會變得有意義。擁有CONNECT role的用戶還能夠創建表、視圖、序列（sequence）、簇（cluster）、同義詞（synonym ）、會話（session）和與其他數據庫的鏈（link）。4.3.2. RESOURCE Role(資源角色)更牢靠和正式的數據庫用戶可以授予RESOURCE role

10、。RESOURCE供應應用戶另外的權限以創建他們自己的表、序列、過程（procedure）、觸發器（trigger）、索引（index）和簇（cluster）。4.3.3. DBA Role(數據庫管理員角色)DBA role擁有全部的系統權限-包括無限制的空間限額和給其他用戶授予各種權限的力量。SYSTEM由DBA用戶擁有。一些DBA經常使用的典型權限。1. grant（授權）命令grant connect, resource to user01;2. revoke（撤消）權限revoke connect, resource from user01;一個具有DBA角色的用戶可以撤消任何別的用

11、戶甚至別的DBA的CONNECT、RESOURCE 和DBA的其他權限。當然，這樣是很危急的，因此，除非真正需要，DBA權限不應任憑授予那些不是很重要的一般用戶。撤消一個用戶的全部權限，并不意味著從Oracle中刪除了這個用戶，也不會破壞用戶創建的任何表；只是簡潔禁止其對這些表的訪問。其他要訪問這些表的用戶可以象以前那樣地訪問這些表。五、創建角色除了前面講到的三種系統角色-CONNECT、RESOURCE和DBA，用戶還可以在Oracle創建自己的role。用戶創建的role可以由表或系統權限或兩者的組合構成。為了創建role，用戶必需具有CREATE ROLE系統權限。5.1 創建rolec

12、reate role STUDENT;這條命令創建了一個名為STUDENT的role。5.2 對role 授權一旦創建了一個role，用戶就可以給他授權。給role授權的grant命令的語法與對對用戶的語法相同。在給role授權時，在grant命令的to子句中要使用role的名稱，如下所示：grant select on CLASS to STUDENT;現在，擁有STUDENT角色的全部用戶都具有對CLASS表的select權限。5.3 刪除角色要刪除角色，可以使用drop role命令，如下所示：drop role STUDENT;指定的role連同與之相關的權限將從數據庫中全部刪除。六.

13、 oracle sys system 用戶的區分sys是Oracle數據庫中權限最高的帳號，具有create database的權限，而system沒有這個權限，sys的角色是sysdba，system的角色是sysoper。其余就是他們兩個用戶共有的權限了：startup/shutdown/dba兩個用戶都是可以管理的。平常用system來管理數據庫就可以了。這個用戶的權限對于一般的數據庫管理來說已經足夠權限了。七. 查看權限和角色ORACLE中數據字典視圖分為3大類, 用前綴區分，分別為：USER，ALL 和 DBA，很多數據字典視圖包含相像的信息。USER_*: 有關用戶所擁有的對象信息

14、，即用戶自己創建的對象信息ALL_*： 有關用戶可以訪問的對象的信息，即用戶自己創建的對象的信息加上其他用戶創建的對象但該用戶有權訪問的信息DBA_*：有關整個數據庫中對象的信息（這里的*可以為TABLES， INDEXES， OBJECTS， USERS等）。比如：只知道scott用戶的密碼，需要查看一下scott的一些信息7.1、查scott用戶的創建時間、用戶狀態、使用的默認表空間、臨時表空間等信息SQL> conn scott/admin已連接。SQL>select * from user_users;另：select * from all_users;(scott用戶可以

15、訪問其他數據庫用戶對信息的用戶名)另：select * from all_users;(全部數據庫的用戶信息，各用戶的密碼、狀態、默認表空間、臨時表空間等)7.2、查看scott用戶自己擁有什么角色SQL> select * from user_role_privs;USERNAME GRANTED_ROLE ADM DEF OS_- - - - -SCOTT CONNECT NO YES NOSCOTT RESOURCE NO YES NO注：“ADM”表示這個用戶是否可以把該具有的角色賜予給其他的用戶另：沒有all_role_privs這個視圖另：select * from dba_

16、role_privs（全部數據庫用戶具有哪些角色，這個視圖只有dba角色的權限才可以查詢）7.3、查看scott用戶自己具有什么的權限SQL> select * from session_privs;7.4、查看scott用戶具有什么的系統權限呢SQL>select * from user_sys_privs;另：沒有all_sys_privs視圖另：select * from dba_sys_privs;(全部數據庫用戶、角色所用于的系統權限)7.5、查看scott用戶中，都哪些用戶把對象授予給scott用戶呢（讀取其他用戶對象的權限）SQL>select * from u

17、ser_tab_privs;另：select * from all_tab_privs; select * from dba_tab_privs; 7.6、查看scott用戶中擁有的resource角色都具有什么權限呢SQL> select * from role_sys_privs where role='RESOURCE'ROLE PRIVILEGE ADM- -RESOURCE CREATE SEQUENCE NORESOURCE CREATE TRIGGER NORESOURCE CREATE CLUSTER NORESOURCE CREATE PROCEDURE

18、 NORESOURCE CREATE TYPE NORESOURCE CREATE OPERATOR NORESOURCE CREATE TABLE NORESOURCE CREATE INDEXTYPE NO已選擇8行。7.7、scott用戶自己擁有多少的表SQL>select * from user_tables;另：select * from all_tables; 其他用戶所擁有的表另：select * from dba_tables；數據庫中全部用戶的表7.8、查看scott用戶已經使用多大的空間，允許使用的最大空間是多少SQL> select tablespace_na

19、me,bytes,max_bytes from user_ts_quotas;另：select * from dba_ts_quotas;(全部的數據庫用戶在每個表空間已使用的空間，最大空間)7.9、把自己的表賜予給其他用戶SQL>grant select on emp to mzl;查看都把哪些表什么權限賜予了其他用戶SQL>select * from user_tab_privs_made7.10、把表的某一列操作權限賜予給其他用戶SQL>grant update(job) on emp to mzl;注：查看數據庫中全部的角色select * form. dba_rol

20、es;7.11、sys授予scott用戶dba角色SQL> conn /as sysdba已連接。SQL> grant dba to scott;授權成功。另：假如這樣SQL> grant dba to scott with admin option;授權成功。scott用戶就可以把dba的權限授予給其他的用戶了。7.12 sys回收scott用戶的dba角色SQL> revoke dba from scott;撤銷成功。八Oracle 用戶及作用介紹Oracle 官方文檔對Oracle 的用戶分成了三類：(1) PredefinedAdministrative Acc

21、ounts(2) PredefinedNon-Administrative User Accounts(3) PredefinedSample Schema User Accounts8.1 Predefined Administrative AccountsA default OracleDatabase installation provides a set of predefined administrative accounts. These are accounts that have specialprivileges required to administer areas of

22、 the database, such as the CREATEANY TABLE or ALTER SESSION privilege, or EXECUTE privilegeson packages owned by the SYSschema. The default tablespace foradministrative accounts is either SYSTEM or SYSAUX.Table 3-1 Predefined Oracle DatabaseAdministrative User AccountsUser AccountDescriptionStatus A

23、fter InstallationANONYMOUSAccount that allows HTTP access to Oracle XML DB. It is used in place of theAPEX_PUBLIC_USER account when the Embedded PL/SQL Gateway (EPG) is installed in the database.EPG is a Web server that can be used with Oracle Database. It provides the necessary infrastructure to cr

24、eate dynamic applications.Expired and lockedCTXSYSThe account used to administer Oracle Text. Oracle Text enables you to build text query applications and document classification applications. It provides indexing, word and theme searching, and viewing capabilities for text.See Oracle Text Applicati

25、on Developer's Guide.Expired and lockedDBSNMPThe account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.See Oracle Enterprise Manager Grid Control Installation and Basic Configuration.OpenPassword is created at installation or database crea

26、tion time.EXFSYSThe account used internally to access the EXFSYS schema, which is associated with the Rules Manager and Expression Filter feature. This feature enables you to build complex PL/SQL rules and expressions. The EXFSYS schema contains the Rules Manager and Expression Filter DDL, DML, and

27、associated metadata.See Oracle Database Rules Manager and Expression Filter Developer's Guide.Expired and lockedLBACSYSThe account used to administer Oracle Label Security (OLS). It is created only when you install the Label Security custom option.See "Enforcing Row-Level Security with Orac

28、le Label Security" and Oracle Label Security Administrator's Guide.Expired and lockedMDSYSThe Oracle Spatial and Oracle Multimedia Locator administrator account.See Oracle Spatial Developer's Guide.Expired and lockedMGMT_VIEWAn account used by Oracle Enterprise Manager Database Control.

29、OpenPassword is randomly generated at installation or database creation time. Users do not need to know this password.OLAPSYSThe account that owns the OLAP Catalog (CWMLite). This account has been deprecated, but is retained for backward compatibility.Expired and lockedOWBSYSThe account for administ

30、rating the Oracle Warehouse Builder repository.Access this account during the installation process to define the base language of the repository and to define Warehouse Builder workspaces and users. A data warehouse is a relational or multidimensional database that is designed for query and analysis

31、.See Oracle Warehouse Builder Installation and Administration Guide.Expired and lockedORDPLUGINSThe Oracle Multimedia user. Plug-ins supplied by Oracle and third-party, format plug-ins are installed in this schema.Oracle Multimedia enables Oracle Database to store, manage, and retrieve images, audio

32、, video, DICOM format medical images and other objects, or other heterogeneous media data integrated with other enterprise information.See Oracle Multimedia User's Guide and Oracle Multimedia Reference.Expired and lockedORDSYSThe Oracle Multimedia administrator account.See Oracle Multimedia User

33、's Guide, Oracle Multimedia Reference, and Oracle Multimedia DICOM Developer's Guide.Expired and lockedOUTLNThe account that supports plan stability. Plan stability prevents certain database environment changes from affecting the performance characteristics of applications by preserving exec

34、ution plans in stored outlines. OUTLN acts as a role to centrally manage metadata associated with stored outlines.See Oracle Database Performance Tuning Guide.Expired and lockedSI_INFORMTN_SCHEMAThe account that stores the information views for the SQL/MM Still Image Standard.See Oracle Multimedia U

35、ser's Guide and Oracle Multimedia Reference.Expired and lockedSYSAn account used to perform database administration tasks.See Oracle Database 2 Day DBA.OpenPassword is created at installation or database creation time.SYSMANThe account used to perform Oracle Enterprise Manager database administr

36、ation tasks. The SYS and SYSTEM accounts can also perform these tasks.See Oracle Enterprise Manager Grid Control Installation and Basic Configuration.OpenPassword is created at installation or database creation time.SYSTEMA default generic database administrator account for Oracle databases.For prod

37、uction systems, Oracle recommends creating individual database administrator accounts and not using the generic SYSTEM account for database administration operations.See Oracle Database 2 Day DBA.OpenPassword is created at installation or database creation time.TSMSYSAn account used for transparent

38、session migration (TSM).Expired and lockedWK_TESTThe instance administrator for the default instance, WK_INST. After you unlock this account and assign this user a password, then you must also update the cached schema password using the administration tool Edit Instance Page.Ultra Search provides un

39、iform search-and-location capabilities over multiple repositories, such as Oracle databases, other ODBC compliant databases, IMAP mail servers, HTML documents managed by a Web server, files on disk, and more.See Oracle Ultra Search Administrator's Guide.Expired and lockedWKSYSAn Ultra Search dat

40、abase super-user. WKSYS can grant super-user privileges to other users, such as WK_TEST. All Oracle Ultra Search database objects are installed in the WKSYS schema.See Oracle Ultra Search Administrator's Guide.Expired and lockedWKPROXYAn administrative account of Oracle9i Application Server Ultr

41、a Search.See Oracle Ultra Search Administrator's Guide.Expired and lockedWMSYSThe account used to store the metadata information for Oracle Workspace Manager.See Oracle Database Workspace Manager Developer's Guide.Expired and lockedXDBThe account used for storing Oracle XML DB data and metad

42、ata.Oracle XML DB provides high-performance XML storage and retrieval for Oracle Database data.See Oracle XML DB Developer's Guide.Expired and locked8.2 PredefinedNon-Administrative User AccountsNon-administrativeuser accounts only have the minimum privileges needed to perform their jobs.Their d

43、efault tablespace is USERS.Table 3-2 PredefinedOracle Database Non-Administrative User AccountsUser AccountDescriptionStatus After InstallationAPEX_PUBLIC_USERThe Oracle Database Application Express account. Use this account to specify the Oracle schema used to connect to the database through the da

44、tabase access descriptor (DAD).Oracle Application Express is a rapid, Web application development tool for Oracle Database.See Oracle Database Application Express User's Guide.Expired and lockedDIPThe Oracle Directory Integration and Provisioning (DIP) account that is installed with Oracle Label

45、 Security. This profile is created automatically as part of the installation process for Oracle Internet Directory-enabled Oracle Label Security.See Oracle Label Security Administrator's Guide.Expired and lockedFLOWS_30000The account that owns most of the database objects created during the inst

46、allation of Oracle Database Application Express. These objects include tables, views, triggers, indexes, packages, and so on.See Oracle Database Application Express User's Guide.Expired and lockedFLOWS_FILESThe account that owns the database objects created during the installation of Oracle Data

47、base Application Express related to modplsql document conveyance, for example, file uploads and downloads. These objects include tables, views, triggers, indexes, packages, and so on.See Oracle Database Application Express User's Guide.Expired and lockedMDDATAThe schema used by Oracle Spatial fo

48、r storing Geocoder and router data.Oracle Spatial provides a SQL schema and functions that enable you to store, retrieve, update, and query collections of spatial features in an Oracle database.See Oracle Spatial Developer's Guide.Expired and lockedORACLE_OCMThe account used with Oracle Configur

49、ation Manager. This feature enables you to associate the configuration information for the current Oracle Database instance with OracleMetaLink. Then when you log a service request, it is associated with the database instance configuration information.See Oracle Database Installation Guide for your

50、platform.Expired and lockedSPATIAL_CSW_ADMIN_USRThe Catalog Services for the Web (CSW) account. It is used by Oracle Spatial CSW Cache Manager to load all record-type metadata and record instances from the database into the main memory for the record types that are cached.See Oracle Spatial Develope

51、r's Guide.Expired and lockedSPATIAL_WFS_ADMIN_USRThe Web Feature Service (WFS) account. It is used by Oracle Spatial WFS Cache Manager to load all feature type metadata and feature instances from the database into main memory for the feature types that are cached.See Oracle Spatial Developer's Guide.Expired and l