Oracle用户管理

一、用户管理：

 

SYS/CHANGE_ON_INSTALL | SYSTEM/MANAGER | SCOTT/TIGER | SYSMAN/OEM_TEMP | INTERNAL/ORACLE

 

NORMAL | SYSOPER | SYSDBA → SQL> SHOW USER.

 

SYSOPER: STARTUP、SHUTDOWN、ALTER DATABASE MOUNT|OPEN、ALTER TABLESPACE BEGIN|END BACKUP、ALTER DATABASE BACKUP CONTROLFILE、RECOVER DATABASE、ALTER DATABASE ARCHIVELOG、RESTRICTED SESSION.

 

SYSDBA: SYSOPER WITH ADMIN OPTION、CREATE DATABASE、RECOVER DATABASE UNTIL.

 

  1.USER:

 

SQL> CREATE/ALTER USER <User_Name> IDENTIFIED BY <Password>

     DEFAULT TABLESPACE <Tablespace_Name>

     TEMPORARY TABLESPACE <Tablespace_Name>

     QUOTA UNLIMITED/<n> [K|M|G] ON <Tablespace_Name>

     ACCOUNT LOCK/UNLOCK PROFILE <Profile_Name>;

 

SQL> DROP USER <User_Name> [CASCADE];

 

SQL> DESC DBA_USERS [ALL_USERS/USER_USERS]

 

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

USERNAME                                  NOT NULL VARCHAR2(30)

USER_ID                                   NOT NULL NUMBER

PASSWORD                                           VARCHAR2(30)

ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)

LOCK_DATE                                          DATE

EXPIRY_DATE                                        DATE

DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)

CREATED                                   NOT NULL DATE

PROFILE                                   NOT NULL VARCHAR2(30)

INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)

EXTERNAL_NAME                                      VARCHAR2(4000)

 

SQL> ALTER DATABASE DEFAULT [TEMPORARY] TABLESPACE <Tablespace_Name>;

 

SQL> SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS';

 

PROFILE              RESOURCE_NAME          RESOURCE_TYPE LIMIT

-------------------- ---------------------- ------------- ---------------------

DEFAULT              FAILED_LOGIN_ATTEMPTS  PASSWORD      10

MONITORING_PROFILE   FAILED_LOGIN_ATTEMPTS  PASSWORD      UNLIMITED

 

SQL> DESC DBA_PROFILES

 

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

PROFILE                                   NOT NULL VARCHAR2(30)

RESOURCE_NAME                             NOT NULL VARCHAR2(32)

RESOURCE_TYPE                                      VARCHAR2(8)

LIMIT                                              VARCHAR2(40)

 

SQL> ALTER USER User_Name PROFILE Profile_Name;

 

SQL> DROP USER User_Name;

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

SQL> SELECT SID,SERIAL# FROM V$SESSION; → SQL> ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

 

--显示用户表空间配额：

SQL> SELECT tablespace_name,username,bytes,max_bytes FROM DBA_TS_QUOTAS;

 

SQL> SELECT * FROM V$PWDFILE_USERS; --Password_File.

 

USERNAME                    SYSDBA  SYSOPER

------------------------------ ------------ --------------

SYS                                TRUE      TRUE

 

  2.Privileges:

 

System Privilege: Enables users to perform particular actions in the database.

 

SQL> GRANT Privilege[|Role_Name]TO User_Name[Role_Name|PUBLIC] [WITH ADMIN|GRANT OPTION];

 

Object Privilege: Enables users to access and manipulate a specific object.

 

SQL> GRANT {Object Privilege:1.ALTER;2.EXECUTE;3.INDEX;4.REFERENCES;5.INSERT/UPDATE/REFERENCES(Column_Name)|ALL}

     ON Object_NameTO User_name;

 

# WITH ADMIN OPTION: Enables the grantee to grant the system privilege or role to other users or roles.

 

# WITH GRANT OPTION: Enables the grantee to grant the object privilege to other users or roles.

 

SQL> SELECT * FROM DBA_SYS_PRIVS;         --用户/角色的系统权限（USER_SYS_PRIVS）.

 

SQL> SELECT * FROM DBA_TAB_PRIVS;         --用户的对象权限（USER_/ALL_TAB_PRIVS）.

 

SQL> SELECT * FROM SYSTEM_PRIVILEGE_MAP; --显示所有系统权限.

 

SQL> SELECT * FROM SESSION_PRIVS;         --显示当前会话所具有的系统权限.

 

SQL> SELECT * FROM USER_TAB_PRIVS_MADE;  --显示用户授出的对象权限(USER_COL_PRIVS_MADE).

 

SQL> SELECT * FROM USER_TAB_PRIVS_RECD;  --显示用户拥有的对象权限(USER_COL_PRIVS_RECD).

 

--显示所有对象权限：

SQL> SELECT * FROM TABLE_PRIVILEGES;

 

SQL> SELECT * FROM DBA_COL_PRIVS; → SQL> GRANT UPDATE(SAL) ON SCOTT.EMP TO GDCSDB;

 

SQL> REVOKE Privilege[|Role_Name] FROM User_Name[Role_Name|PUBLIC];

 

 3.ROLE:

 

SQL>CREATE/DROP ROLE Role_Name; → SQL> GRANT Role_Name TO User_Name;

 

--Oracle中的角色信息：

SQL> SELECT * FROM DBA_ROLES;

 

SQL> SELECT * FROM DBA_ROLE_PRIVS;  --用户/角色所有拥有的角色（USER_ROLE_PRIVS）.

 

SQL> SELECT * FROM ROLE_SYS_PRIVS;  --显示角色所拥有的系统/对象权限(ROLE_TAB_PRIVS).

 

SQL> SELECT * FROM ROLE_ROLE_PRIVS; --显示角色所被赋予的其他角色.

 

SQL>CREATE/ALTER ROLE Role_Name [NOT IDENTIFIED|IDENTIFIED BY Password];

 

ERROR at line 1:

ORA-01955: DEFAULT ROLE 'ROLE_1' not granted to user

SQL> GRANT ROLE_1 TO SCOTT;

 

SQL> ALTER USER User_Name DEFAULT ROLE Role_Name_1[,Role_Name_2,...|ALL <EXCEPT Role_Name>|NONE];

 

SQL> SET ROLE Role_Name; ==> SQL> EXECDBMS_SESSION.SET_ROLE('Role_Name INDENTIFIED BY Password');

 

SQL> SET ROLE Role_Name IDENTIFIED BY Password; ==> SQL> EXECDBMS_SESSION.SET_ROLE('Role_Name');

 

SQL> SELECT * FROM SESSION_ROLES; | SQL>SET ROLE NONE;

 

DEFAULT ROLE:<1>.CREATE/ALTER ROLE ... IDENTIFIED BY Password; → NOT IDENTIFIED;<2>.GRANT Role_1,Role_2,Role_3 TO User_Name; →DEFAULT ROLE(Role_1,Only) → SET ROLE Role_2|Role_3;

 

#  Others ：

 

Database Schema: (1)A schema is a named collection of object.(2)A user is created,and a corresponding schema is created.(3)A user can be associated with only one schema.(4)Username and Schema are often used interchangeably.

 

需要注意，当初始化参数O7_DICTIONARY_ACCESSIBILITY被设置为FALSE时，除了SYSDBA和DBA用户之外，即使用户具有SELECT ANY TABLE权限，也不能访问数据字典基表和数据字典视图DBA_*。

 

Unlimited Tablespace(System Privilege)和With Grant Option(Object Privilege)不能授予ROLE。不能在同一条GRANT语句中同时授予System Privilege和Object Privilege。

 

With Admin|Grant Option → System|Object Privileges → Revoke NoCascade|Cascade.

 

Privileges → Roles → Users. | 预定义角色是在创建数据库时，Oracle执行如下脚本时建立的。

SQL.BSQ(CONNECT、RESOURCE、DBA、EXECUTE_CATALOG_ROLE、SELECT_CATALOG_ROLE、DELETE_CATALOG_ROLE);

CATEXP.SQL(EXP_FULL_DATABASE、IMP_FULL_DATABASE)、CATALOG.SQL(RECOVERY_CATALOG_OWNER).

 

EXECUTE_CATALOG_ROLE: PL/SQL Package(DBMS_*)的Execute权限;SELECT_CATALOG_ROLE: Data Dictionary(DBA_*)的SELECT权限;DELETE_CATALOG_ROLE:SYS.AUD$上的DELETE权限.