Oracle 数据库基本操作——用户管理与文件管理

目录:

  1、初始状态

  2、登录数据库

  3、创建表空间

      1)概念

      2) 基本表空间

      3)表空间管理

  4、创建新用户

  5、删除用户

  6、用户的授权

      1)定义

      2)授予权限的方法

      3)权限分类

      4)授权注意

      5)权限回收

      6)角色

  7、概要文件

  8、拓展小知识:系统表之 dba_\ all_ \ user 


注:以下为 Oracle 11g Release 2 的例子


1、初始状态

1)安装数据库时设置的口令为 超级管理员密码

2)初始的数据库时可用的用户有:sys, system  其初始密码为 1)中设置的超级管理员密码

3)有一个用于示例的的 hr (人力资源)用户,里面有7个表,每个表中有一定的样例数据,但是这个用户是被锁定的,而且密码未知。

  可用如下方法进行访问:

-- 0、登录数据库
sqlplus /nolog -- 1、首先以超级管理员身份登录数据库,已获得解锁用户的权限
conn / as sysdba -- 2、更改用户密码
alter user hr identified by abc; -- 3、解锁账户
alter user hr unlock; -- 4、接着就可以登录了
conn hr/abc ;

解锁hr账户

2、登录数据库

如同上面1、3)中代码中提到的,在登录数据库可以直接不登录进入 sql 工作台:

sqlplus /nolog

也可以直接登录:

sqlplus  UserName/Password;

如果进入后再登录,语法如下:

conn UserName/Password @ 服务名

/*
其中服务名 可以使本机的服务名,也可以使远程的服务名
(服务名是最开始安装的时候向导里设置的,如果不知道自己的服务名,可以命令行打开 services.msc 找到里面以Oracle
开头的服务的 OracleServiceXXXX 的服务 XXXX 就是你的服务名,默认服务名为orcl,如果安装时修改了则XXXX为你自己
起的名字)
*/ --本地直接连接方式:(假如服务名为:orcl)
conn UserName/Password
conn UserName/Password@orcl
conn UserName/Password@localhost:1521/orcl --远程连接方式:(假如服务名为:orcl)
conn UserName/Password@ IP:port/orcl

3、创建表空间

表空间在Oracle数据库是一个很基本的概念,所有的数据文件都是建立在表空间之上的,具体的关系如下图:

Oracle 数据库基本操作——用户管理与文件管理

1) 概念:表空间是数据库中的基本逻辑结构,一系列数据文件的集合。

  • 一个数据库可以有多个表空间,大小为所属表空间大小的总和
  • 一个表空间可以有多个数据文件,大小为所属数据文件大小的总和
  • 一个数据文件只能从属于一个表空间

2) 基本表空间

  • 系统表空间:  主要存放数据字典和内部系统表基表
  • 临时表空间:  用来存放临时数据,如排序操作的临时空间
  • 回滚段表空间:  当数据库进行更新插入删除等操作的时候,新的数据被更新到原来的数据文件,而旧的数据(Before Image)就被放到回滚段中.
  • 工具表空间:  保存数据库工具软件的数据对象
  • 索引表空间:  存放索引
  • 用户表空间:  存放用户私有信息

3)表空间管理

表空间与数据文件对应

--查看表空间与其对应的文件
select file_name,tablespace_name
from dba_data_files
order by tablespace_name;

创建表空间

--创建表空间“shoppingtp”,物理位置为‘d:/shoppingtp01.dbf’ 大小100m
--空间用完后自动每次拓展100m,总共最大分配1G create tablespace shoppingtp datafile 'd:/shoppingtp01.dbf' size 100m
autoextend on next 100m maxsize 1G; --or
create tablespace shoppingtp datafile 'd:/shoppingtp01.dbf';

扩大数据库

--【直接扩大】表空间里的文件
alter database datafile 'filename.dbf' autoextend on next 20m maxsize 1000m; --为上面的表空间“shoppingtp”【新增加】一个表空间文件
alter tablespace shoppingtp add datafile 'd:/shoppingtp02.dbf';

删除表空间

--删除表空间:
drop tablespace tpname including contents and datafiles
--只读:
alter tablespace tpname READ ONLY
--离线在线:
alter tablespace tpname OFFLINE/ONLINE

查看【文件名和对应表空间】 /【数据表空间的大小的利用率】:

--文件名和对应表空间
select file_name,tablespace_name
from dba_data_files
order by tablespace_name;
--数据表空间的大小的利用率
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;

注:如果在关闭数据库后,强制删除已创建的表空间文件,启动数据库会出问题,解决办法参考:link

4、创建新用户

用户:

定义:        也叫方案,是一组逻辑对象的所有者。
验证方式:      数据库验证和操作系统验证
表空间和临时表空间: 表空间为用户创建数据对象的存储表空间;临时表空间用于创建临时段。
空间分配:      表空间中,必须要为一个用户指派空间配额,用于存储数据库对象。

创建语法:

Oracle 数据库基本操作——用户管理与文件管理

Oracle 数据库基本操作——用户管理与文件管理

--创建用户的必须语句,默认表空间均为users
create user USERNAME identified by PASSWORD ; --指定表空间方式
create user USERNAME identified by PASSWORD default tablespace TABLESPACENAME;

5、删除用户

语法定义如下:

Oracle 数据库基本操作——用户管理与文件管理

-- 删除用户:
Drop user annie;
--删除拥有对象的用户:
Drop user annie cascade; -- 注意:如果要删除的用户正在连接数据库,不能将其删除

6、用户的授权

1)定义:控制用户在数据库中所能进行的操作
2)授予权限的方法:
<1>直接授予用户:

grant create session to annie;

<2>先将权限授予角色,再将角色授予用户:

Create role teach;
grant select on class to teach;
grant teach to annie;

3)权限分类:

<1>系统权限(system Privilege):允许执行一组特定的命令

Select any、insert any、update any、delete  any是系统权限

<2>对象权限(objects Privilege) : 赋予在某一具体数据对象的操作能力

1) 9种对象权限:
Select 、Update、Delete、Insert、Execute、Index、reference、Alter、Read
2) 对象权限传递 权限: With Grant Option
被授予With Grant Option对象权限的用户,可将该对象权限授予其他用户

4)授权注意:

1)连接到数据库需要create session权限
删除其他用户的Table需要drop any table权限
2)使用 With admin option 进行系统权限传递
3) 谁有权授予对象权限给其他人?
对象的所有者\DBA\被授予With grant option的用户
4) 权限可以授予给谁?
其它用户\角色\Public
-------------------其他授权方式----------------------------
5)快速授权:(connect,resource,DBA 等详细见 6)角色 )
grant connect,resource to testuser;
6)具体表的授权
grant select on TABLENAME to testuser2;

5)权限回收

revoke select any  from annie;--回收系统权限
revoke select on class from annie; --回收对象权限

注:

一个用户被多用户授予权限后,其中一个用户收回权限,不影响其他用户授予的权限
收回With grant option 或 With admin option,要首先回收相应的权限,再重新授予该权限而不再授予With grant option 或 With admin option
传递权限With admin option给其他用后,如果此系统权限被回收,其他用户仍然拥有该系统权限
传递权限With grant option给其他用后,如果此对象权限被回收,其他用户该对象权限也被收回

6)角色

定义:角色是一系列权限的集合
功能:简化权限管理
创建角色:
Create role r_teach
为角色授权:
grant select on class to r_ teach
回收角色权限
revoke select on class from r_ teach
将角色授予用户
Grant r_ teach to annie
从用户回收角色
Revoke annie from r_teach
将角色授予角色
Grant r_teach to r_stu [with admin option]
从角色回收角色
Revoke r_teach from r_stu
删除角色
drop role r_teach

预定义的角色:

Oracle 数据库基本操作——用户管理与文件管理

查询角色信息:

查询角色信息
视图DBA_Roles: 角色信息
视图Role_role_privs:角色与传递权限信息
视图Role_SYS_privs:角色系统权限
视图Role_tab_privs:角色对象权限

7、概要文件

概要文件控制资源内容:
每个用户当前的会话数
CPU使用的时间
私有的SQL和PL/SQL区使用
逻辑读取的实现
用户连接到数据库的空闲时间
注意:resource_limit = TRUE时,启动资源限制。

定义语法:

Oracle 数据库基本操作——用户管理与文件管理

Oracle 数据库基本操作——用户管理与文件管理

控制资源参数

   SESSIONS_PER_USER : 限制用户当前会话的数量
CPU_PER_SESSION: 限制一个会话使用的CPU时间
CPU_PER_CALL :限制一个SQL语句使用的CPU时间
LOGICAL_READS_PER_SESSION:限制每个会话读取的数据库数据块数,包括从内存和磁盘读取的总和
LOGICAL_READS_PER_CALL:限制SQL语句读取的数据库数据块数,包括从内存和磁盘读取的总和
PRIVATE_SGA :SGA中私有区域的大小
CONNECT_TIME :指定一个会话连接到数据库的最大时间
IDLE_TIME DEFAULT:指定一个会话可以连续空闲的最长时间,单位:分钟
COMPOSITE_LIMIT:设置用户对系统资源的综合消耗。由: CPU_PER_SESSION、 LOGICAL_READS_PER_SESSION、
PRIVATE_SGA、CONNECT_TIME综合决定
FAILED_LOGIN_ATTEMPTS: 最大错误登录次数
PASSWORD_LOCK_TIME:登录失败后账户被锁天数
PASSWORD_LIFE_TIME:密码有效天数
PASSWORD_GRACE_TIME:用户密码被中止前多少天提醒用户修改密码
PASSWORD_REUSE_TIME:用户修改密码后多少天,用户才可以再次使用原来的密码
PASSWORD_REUSE_MAX:密码被重新使用后,可修改的次数PASSWORD_VERIFY_FUNCTION:密码复杂度审计函数

一个例子:

创建概要文件

CREATE PROFILE "TEMPPROFILE" LIMIT
CPU_PER_SESSION 1000
CPU_PER_CALL 1000
CONNECT_TIME 30
IDLE_TIME DEFAULT
SESSIONS_PER_USER 10
LOGICAL_READS_PER_SESSION 1000
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 16K
COMPOSITE_LIMIT 1000000
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 5
PASSWORD_GRACE_TIME 60
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_MAX   DEFAULT
PASSWORD_REUSE_TIME 30
PASSWORD_VERIFY_FUNCTION DEFAULT;

8、拓展小知识:系统表之 dba_\ all_ \ user 

dba_开头的是查全库所有的,all_开头的是查当前用户可以看到的,user_开头的是查当前用户的

如:

dba_users:     数据库的所有用户的信息
all_user: 当前用户可以看到的用户
user_users: 当前登录用户所拥有的用户
dba_tables:    数据库的所有的表
all_tables: 当前用户可以查看的所有表
user_tables: 当前登录用户所拥有的表
上一篇:self&super


下一篇:Codeforces Round #380 (Div. 1, Rated, Based on Technocup 2017 - Elimination Round 2)