Oracle 数据库用户管理

在数据库中各种对象被以用户（方案）的方式组织管理

在创建对象和访问对象前，首先创建对象的拥有者---用户

用户有相关的属性，有些必须明确设置，有些可以用默认值

下面是用户的相关属性

--用户名

--口令

--默认表空间

--临时表空间

--账户状态

--空间配额

--概要文件

其中用户名和口令是必须要设置的

数据库当中的对象是一schema的方式组织管理的

数据库当中存在哪些类型的对象

对象是放在user下，通常称之为用户下的对象，又称作schema。

模式对象指的就是用户下的对象。例如要访问SCOTT用户下的emp表，使用sys用户去访问----就是这样描述scott.emp，该用户下的所有对象必须是指定相应的用户才可以访问，不能直接访问，除非就在当前的用户下。

创建用户的步骤如下：

1. 确定用户对象在表空间上的分布，从而确定表空间配额

2. 确定用户的默认表空间和临时表空间

3. 确定用户的口令管理规则和资源闲置规则，设置profile

4. 创建用户，设置口令，并设置用户的其他属性

5. 给用户赋予必要的角色

6. 给用户直接赋予其他的直接权限

创建用户linda，指定密码linda

Linda用户的其他值是默认分配的。用户视图如下：

SQL> desc dba_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)

PASSWORD_VERSIONS VARCHAR2(8)

EDITIONS_ENABLED VARCHAR2(1)

AUTHENTICATION_TYPE VARCHAR2(8)

查看LINDA用户的默认属性

SQL> select username,password,account_status,default_tablespace,temporary_tablespace from dba_users where username='LINDA';

USERNAME PASSWORD

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

ACCOUNT_STATUS DEFAULT_TABLESPACE

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

TEMPORARY_TABLESPACE

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

LINDA

OPEN //帐号状态是打开<允许登陆的意思> USERS //默认表空间是users

TEMP //默认临时表空间

添加profile 字段

SQL> c/from/,profile from

1* select username,password,account_status,default_tablespace,temporary_tablespace ,profile from dba_users where username='LINDA'

SQL> /

USERNAME PASSWORD

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

ACCOUNT_STATUS DEFAULT_TABLESPACE

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

TEMPORARY_TABLESPACE PROFILE

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

LINDA

OPEN USERS

TEMP DEFAULT

如何查看默认的表空间

SQL> select * from database_properties where property_name like '%DEF%';

PROPERTY_NAME

PROPERTY_VALUE

DESCRIPTION

DEFAULT_TEMP_TABLESPACE //默认的临时表空间就是TEMP

TEMP

Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE //默认的表空间

USERS

Name of default permanent tablespace

PROPERTY_NAME

PROPERTY_VALUE

DESCRIPTION

DEFAULT_EDITION

ORA$BASE

Name of the database default edition

DEFAULT_TBS_TYPE

SMALLFILE

PROPERTY_NAME

PROPERTY_VALUE

DESCRIPTION

Default tablespace type

如何去修改默认的表空间?，首先必须要存在一个表空间，创建一个users1的表空间

SQL> create tablespace users1 datafile '/opt/oracle/oradata/orcl/user1.dbf' size 100m autoextend on;

Tablespace created

修改用户的默认表空间

SQL> alter database default tablespace users1;

Database altered.

查看是否修改用户的默认表空间

SQL> select * from database_properties where property_name like '%DEF%';

PROPERTY_NAME

PROPERTY_VALUE

DESCRIPTION

DEFAULT_TEMP_TABLESPACE

TEMP

Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE

USERS1

Name of default permanent tablespace

PROPERTY_NAME

PROPERTY_VALUE

DESCRIPTION

DEFAULT_EDITION

ORA$BASE

Name of the database default edition

DEFAULT_TBS_TYPE

SMALLFILE

PROPERTY_NAME

PROPERTY_VALUE

DESCRIPTION

Default tablespace type

系统当中的默认值不建议修改，在改回去

SQL> alter database default tablespace users;

Database altered.

如何指定单个用户的默认表空间，用户xiaobai的默认表空间为users1

SQL> create user xiaobai identified by xiaobai default tablespace users1;

User created.

如果不指定默认的表空间，则oracle就将users设为默认表空间。

如何查看用户的默认表空间

SQL> select username,default_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE

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

MGMT_VIEW SYSTEM

SYS SYSTEM

SYSTEM SYSTEM

DBSNMP SYSAUX

SYSMAN SYSAUX

U1 USERS

LINDA USERS

XIAOBAI USERS1

OUTLN SYSTEM

关于临时表空间

默认的临时表空间：所有的用户创建完之后使用哪个临时表空间来存放排序的临时数据，排序一般都是在内存中排序，如果内存中无法完成排序工作，就在临时表空间中完成。

查看临时表空间

SQL> desc database_properties

Name Null? Type

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

PROPERTY_NAME NOT NULL VARCHAR2(30)

PROPERTY_VALUE VARCHAR2(4000)

DESCRIPTION VARCHAR2(4000)

SQL> select property_name,property_value from database_properties where property_name like '%TEMP%';

PROPERTY_NAME

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

PROPERTY_VALUE

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

DEFAULT_TEMP_TABLESPACE

TEMP

临时表空间的管理

1. 如何建立临时表空间

2. 如何指定排序的表空间

建立临时表空间

SQL> create temporary tablespace temp1 tempfile '/opt/oracle/oradata/orcl/temp1.dbf' size 100m;

Tablespace created.

查看当前的临时表空间都有哪些？

SQL> select name from v$tempfile;

NAME

/opt/oracle/oradata/orcl/temp01.dbf

/opt/oracle/oradata/orcl/temp1.dbf

查看当前的临时表空间都有哪些？

SQL> select tablespace_name from dba_temp_files;

TABLESPACE_NAME

TEMP

TEMP1

如何建立临时表空间组

SQL> create temporary tablespace temp3 tempfile '/opt/oracle/oradata/orcl/temp2.dbf' size 100m tablespace group tempteam;

Tablespace created.

查看临时表空间组

确定用户的口令管理规则和资源闲置规则，默认是使用default作为资源概要文件。设置profile----用户的安全管理

给用户赋予必要的角色—在oracle中创建用户之后，默认情况下是不能直接登陆的，必须赋予必要的权限或必要的角色或给用户赋予其他直接的权限。

刚才创建的用户linda是无法登陆的

SQL> conn linda/linda

ERROR:

ORA-01045: user LINDA lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

为用户linda赋予create session权限。

SQL> conn /as sysdba

Connected.

SQL> grant create session to linda;

Grant succeeded.

SQL> conn linda/linda

Connected.

建立用户时，应该赋予哪些角色给用户呢？

SQL> conn /as sysdba

SQL> grant connect,resource to linda;

Grant succeeded.

使用linda用户登陆

SQL> conn linda/linda

Connected.

SQL> create table xinxi (id number);

Table created.

SQL> insert into xinxi values(1);

1 row created.

SQL> commit;

Commit complete. //这是linda用户就有了使用数据库的基本权限了。

查询当前用户<linda>拥有的权限

SQL> select * from session_privs;

PRIVILEGE

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

CREATE SESSION

UNLIMITED TABLESPACE

CREATE TABLE

CREATE CLUSTER

CREATE SEQUENCE

CREATE PROCEDURE

CREATE TRIGGER

CREATE TYPE

CREATE OPERATOR

CREATE INDEXTYPE

10 rows selected.

查询linda用户拥有的角色

SQL> select * from session_roles;

ROLE

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

CONNECT

RESOURCE

修改用户属性

用户大多数的属性是可以被修改的，只有用户名称不能修改

修改用户口令;

SQL> alter user linda identified by linda123;

User altered.

重新设置用户的默认表空间和临时表空间

SQL> conn /as sysdba

Connected.

SQL> SQL> alter user linda default tablespace system temporary tablespace temp;

User altered.

锁定用户账户linda

SQL> conn /as sysdba

Connected.

SQL> alter user linda account lock;

User altered.

用linda用户连接，提示用户已被锁定

SQL> conn linda/linda123

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

SQL> select username,account_status from dba_users where username='LINDA';

USERNAME ACCOUNT_STATUS

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

LINDA LOCKED

为linda用户解锁

SQL> conn /as sysdba

Connected.

SQL> alter user linda account unlock;

User altered.

SQL> conn linda/linda123

Connected.

SQL>

使linda账户口令过期

SQL> conn /as sysdba

Connected.

SQL> alter user linda password expire;

User altered.

SQL> conn linda/linda123

ERROR:

ORA-28001: the password has expired

Changing password for linda

New password:

Retype new password:

Password changed

Connected.

删除用户

如果用户不再需要，可以使用DROP命令删除用户 DROP USER linda;

如果用户模式中包含了对象，则必须使用CASCADE子句，表示删除用户的同时删除了用户模式中包含的所有对象。

当前连接的用户不能删除

删除linda用户

SQL> drop user linda;

drop user linda

*

ERROR at line 1:

ORA-01922: CASCADE must be specified to drop 'LINDA'

提示必须使用CASCADE命令来删除linda用户

SQL> drop user linda cascade;

User dropped.

此时用户linda下的所有schema都会被删除，例如创建的xinxi表都会被删除

ORACLE的权限

权限是用于确保数据安全性的，有两种安全性保障需要通过权限提供：

系统安全

数据安全

为了实现数据库的安全性，oracle提供了两种类型的用户权限：

系统权限：使用户在数据库中能够执行特定的任务。

对象权限：使用户可以访问和操作特定的对象。

为了使用户能够具有某种权限，执行相应的任务，DBA可以通过GRANT命令将权限授予一个用户或者一组用户。DBA可以通过REVOKE命令将权限从用户身上移走。

系统权限描述

用户对数据库作操作，首先需要具有相应的系统权限，没有系统权限，用户对数据库根本无法访问。

在系统权限中有一类包含any关键字的权限，这种权限代表用户对所有用户的模式对象中都有某个特定的权限

通常数据库管理员sys和system具有较高的系统权限，能够完成很多管理任务。例如：

---创建新的用户

---删除用户

---创建表空间

---删除日志组

---给用户赋予或移除权限等

系统权限的授予

一旦用户被创建，DBA就需要给用户授予特定的系统权限，授予系统权限的语法如下：

GRANT privilege [,privilege….]

TO user [,user | role ,public…]

[WITH ADMIN OPTION];

其中，with admin option选项可以将授予能力进一步传递下去。

通常一个用户至少会有下面的一些系统权限：

---CREATE SESSION

---ALTER SESSION

---CREATE TABLE

---CREATE SEQUENCE

---CREATE VIEW

---CREATE PROCEDURE

---UNLIMITED TABLESPACE

以上权限都是由create connect ,resource角色定义出来的。

授予系统权限的例子

可以一次将多个权限授予用户

GRANT GREATESESSION，CREATE TABLE，CREATESEQUENCE，CREATE VIEW，CREATEPROCEDURE，UNLIMITED TABLESPACE TO testuser;

可以在授予系统权限的同时将权限的授予能力赋给用户， ALTER SESSION是系统权限，with admin option的意义是testuser用户可以将alter session系统权限赋给其他用户。

CRANT ALTER SESSION TO testuser WITH ADMIN OPTION；

系统权限的授予

Sysdba和sysoper是两种特权身份，也代表了一系列特殊的权限，这些权限不能直接授予用户，只能通过特权身份传递

Sysdba和sysoper着两种身份所代表的权限如下：

SYSOPER

---STARTUP

---SHUTDOWN

---ALTER DATABASE OPEN |MOUNT

---ALTER DATABASE BACKUP CONTROLFILE TO

---RECOVER DATABASE

---ALTER DATABASE ARCHIVELOG

---RESTRICTED SESSION

SYSDBA:

---SYSOPER PRIVILEGES WITH ADMIN OPTION

---CREATE DATABASE

---ALTER TABLESPACE BEGIN/END BACKUP

---RECOVER DATABASE UNTIL

系统权限的回收

如果用户不再需要某些系统权限，可以通过REVOKE命令从用户上移除

系统管理员sys和system都具有移除权限的能力，另外具有admin option选项的用户已可以移除系统权限

系统权限具有级联授予，非级联移除的特性。

移除系统权限的语法如下：

REVOKE {privilege [,privilege….]}

FROM {USER[，USER…]|role|public}

举列：

SQL> create user testuser identified by test1;

User created.

SQL> grant connect,resource to testuser;

Grant succeeded.

SQL> grant alter session to testuser with admin option;

Grant succeeded.

SQL> create user testuser1 identified by test11;

User created.

SQL> conn testuser/test1

Connected.

SQL> grant alter session to testuser1; //testuser用户给testuser1用户授予alter session权限级联授予

Grant succeeded.

SQL> grant connect,resource to testuser1;

grant connect,resource to testuser1

ERROR at line 1:

ORA-01031: insufficient privileges //提示testuser没有足够的权限授予

SQL> conn /as sysdba //使用sysdba或sysoper身份赋予testuser1权限。

Connected.

SQL> grant connect,resource to testuser1;

Grant succeeded.

如何查看oracle中的系统权限，这是在sysdba上看到的系统权限

SQL> select * from user_sys_privs;

用testuser用户登陆

SQL> conn testuser/test1 //查看当前用户的系统权限

Connected.

SQL> /

USERNAME PRIVILEGE ADM

TESTUSER ALTER SESSION YES

TESTUSER UNLIMITED TABLESPACE NO

查看系统当前用户的role权限

SQL> select * from role_sys_privs;

ROLE PRIVILEGE ADM

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

RESOURCE CREATE SEQUENCE NO

RESOURCE CREATE TRIGGER NO

RESOURCE CREATE CLUSTER NO

RESOURCE CREATE PROCEDURE NO

RESOURCE CREATE TYPE NO

CONNECT CREATE SESSION NO

RESOURCE CREATE OPERATOR NO

RESOURCE CREATE TABLE NO

RESOURCE CREATE INDEXTYPE NO

9 rows selected.

从testuser用户回收alter session权限

从SQL> conn /as sysdba

Connected.

SQL> revoke alter session from testuser;

Revoke succeeded

SQL> revoke alter session from testuser1;

Revoke succeeded.

从testuser1用户回收role，此时testuser1只有建立会话的权限。

SQL> revoke resource from testuser1;

Revoke succeeded.

级联授予权限，testuser具有级联授权的能力，testuser将alter session权限授予testuser1用户，同样testuser用户也可以回收授予权限的能力。

SQL> grant alter session to testuser with admin option;

Grant succeeded.

SQL> conn testuser/test1

Connected.

SQL> grant alter session to testuser1; //级联授予权限

Grant succeeded.

SQL> revoke alter session from testuser1; //testuser有授予权限的能力也具有回收授予权限的能力

Revoke succeeded.

关于级联授予权限需要注意的

创建两个用户分别是zhangsan和lisi

SQL> create user zhangsan identified by zhangsan;

User created.

SQL> create user lisi identified by lisi;

User created.

SQL> grant create session to zhangsan;

Grant succeeded.

SQL> grant create session to lisi;

Grant succeeded.

SQL> grant alter session to zhangsan with admin option;

Grant succeeded.

SQL> grant alter session to lisi;

Grant succeeded.

SQL> revoke alter session from lisi; //zhangsan具备授予权限的能力，也具备回收能力。

Revoke succeeded.

如果再将alter session权限赋给lisi用户

SQL> grant alter session to lisi;

Grant succeeded.

SQL> conn lisi/lisi

Connected.

SQL> select * from session_privs;

PRIVILEGE

CREATE SESSION

ALTER SESSION

此时我使用sysdba的身份回收权限。

SQL> conn /as sysdba

Connected.

SQL> revoke alter session from zhangsan; //回收zhangsan用户的alter session权限

Revoke succeeded.

此时，默认情况下lisi用户的alter session权限还是存在的。

SQL> conn lisi/lisi

Connected.

SQL> select * from session_privs;

PRIVILEGE

CREATE SESSION

ALTER SESSION

SQL> conn zhangsan/zhangsan

Connected.

SQL> revoke alter session from lisi;

revoke alter session from lisi

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn /as sysdba

Connected.

SQL> revoke alter session from lisi;

Revoke succeeded.

此时查看lisi用户的权限时发现alter session权限没有了。

SQL> conn lisi/lisi

Connected.

SQL> select * from session_privs;

PRIVILEGE

CREATE SESSION

权限的授予和回收必须是具备了sysdba或sysoper的系统权限的用户才可以授予，当然也包括DBA的role也可以。如果是授予系统权限时加了with admin option，这种用户也具备权限的授予和回收能力。

小插曲

关于unlimited tablespace权限的一个小实验

SQL> create user xiaoli identified by xiaoli;

User created.

SQL> grant connect,resource to xiaoli;

Grant succeeded.

SQL> select * from dba_role_privs a where a.grantee='XIAOLI';

GRANTEE GRANTED_ROLE ADM DEF

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

XIAOLI RESOURCE NO YES

XIAOLI CONNECT NO YES

SQL> select * from dba_sys_privs a where a.grantee='XIAOLI';

GRANTEE PRIVILEGE ADM

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

XIAOLI UNLIMITED TABLESPACE NO

SQL> revoke unlimited tablespace from xiaoli;

Revoke succeeded.

SQL> select * from dba_sys_privs a where a.grantee='XIAOLI';

no rows selected

SQL> create role prole1;

Role created.

SQL> grant unlimited tablespace to prole1;

Grant succeeded.

SQL> grant unlimited tablespace to xiaoli;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee='PROLE1';

GRANTEE PRIVILEGE ADM

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

PROLE1 UNLIMITED TABLESPACE NO

SQL> select * from dba_sys_privs where grantee='XIAOLI';

GRANTEE PRIVILEGE ADM

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

XIAOLI UNLIMITED TABLESPACE NO

SQL> revoke resource from xiaoli;

Revoke succeeded.

SQL> select * from dba_sys_privs where grantee='XIAOLI';

no rows selected

SQL> grant resource to prole1;

Grant succeeded.

SQL> grant prole1 to xiaoli;

Grant succeeded.

SQL> select * from dba_role_privs a where a.grantee='XIAOLI';

GRANTEE GRANTED_ROLE ADM DEF

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

XIAOLI PROLE1 NO YES

XIAOLI CONNECT NO YES

SQL> select * from dba_sys_privs a where a.grantee='XIAOLI';

no rows selected

对象权限

对象权限是对某个特定对象的访问权限

不同类型的对象具有不同的对象权限

管理员，对象的拥有者以及具有with grant option选项的用户可以将对象权限授予其他人

用户对自己拥有的对象具有所有的对象权限。

用户xiaoli要访问scott用户下的emp表

SQL> conn xiaoli/xiaoli

Connected.

SQL> select * from scott.emp;

select * from scott.emp

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn /as sysdba

Connected.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

SQL> show user;

USER is "SYS"

SQL> grant select on scott.emp to xiaoli;

Grant succeeded.

SQL> conn xiaoli/xiaoli

Connected.

SQL> select * from tab;

no rows selected

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

20

查看当前用户具备什么样的对象权限

SQL> desc user_tab_privs;

Name Null? Type

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

GRANTEE NOT NULL VARCHAR2(30)

OWNER NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

GRANTOR NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

GRANTABLE VARCHAR2(3)

HIERARCHY VARCHAR2(3)

SQL> select owner,table_name,privilege from user_tab_privs;

OWNER TABLE_NAME

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

PRIVILEGE

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

SCOTT EMP

SELECT //对scott用户的EMP表有查询权限。

SQL> conn /as sysdba

Connected.

SQL> revoke select on scott.emp from xiaoli;

Revoke succeeded.

SQL> grant select on scott.emp to xiaoli with grant option;

Grant succeeded.

SQL> conn xiaoli/xiaoli;

Connected.

SQL> grant select on scott.emp to lisi;

Grant succeeded.

SQL> conn xiaoli/xiaoli;

Connected.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

授予xiaoli和lisi用户对scott.emp表具有insert,update权限

SQL> conn /as sysdba

Connected.

SQL> grant insert,update on scott.emp to testuser,lisi;

Grant succeeded.

给用户只能更新emp表中的sal列，其他列不可以更新

SQL> grant update(sal) on scott.emp to xiaoli;

Grant succeeded.

SQL> conn xiaoli/xiaoli

Connected.

SQL> update scott.emp set sal=sal+1;

14 rows updated.

SQL> commit;

当用户xiaoli去修改emp表中的comm列的时候，会失败，原因是权限不足。

SQL> update scott.emp set comm=100 where ename='KING';

update scott.emp set comm=100 where ename='KING'

*

ERROR at line 1:

ORA-01031: insufficient privileges

如何查询列的权限

SQL> desc user_col_privs;

Name Null? Type

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

GRANTEE NOT NULL VARCHAR2(30)

OWNER NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

COLUMN_NAME NOT NULL VARCHAR2(30)

GRANTOR NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

GRANTABLE VARCHAR2(3)