Oracle笔记之创建模式及用户和角色

ゝ一纸荒年。 2022-02-22 15:30 852阅读 0赞

> 今天重新翻阅了一下大学的《数据库系统概论》这本教材，更加理解了模式这个概念，因为之前用MySQL的时候几乎没用过这个概念，用Oracle的时候才算真正遇到了这个。

在Oracle中想要创建一个模式，使用如下语句：

CREATE SCHEMA "myDB" AUTHORIZATION SYSTEM;

报了以下错误

[Err] ORA-02420: missing schema authorization clause

但是使用不指定模式名的语句却可以

CREATE SCHEMA  AUTHORIZATION SYSTEM;

查了资料，原来Oracle是不支持创建自定义模式的，想要创建模式的话只能新建一个用户，每个用户会有一个默认的和用户名相同的模式  
继续找了相应的创建用户的文章  
Oracle默认有两个已经建好的用户：system和sys，system有创建其他用户的权限，通过它就可以创建别的用户了。  
用户相关的语法：

#创建用户
    create user 用户名 identified by 口令[即密码];
    #如创建一个用户名和口令均为xmh的用户
    create user xmh identified by xmh;
    #更改用户
    alter user 用户名 identified by 口令[新口令];
    #如修改用户口令为
    alter user xmh identified by 123123;
    #删除用户
    drop user 用户名;
    #如删除xmh
    drop user xmh;

当某个用户下已经定义了数据库对象（如表、图等）时，删除用户要使用

drop user 用户名 cascade;
    #如
    drop user xmh cascade;

\#否则会报`[Err] ORA-01922: CASCADE must be specified to drop 'XMH'`错误  
**cascade**的意思为“级联”，表示的是在删除用户的同时，会把所有数据库对象也全部一起删除。

在删除用户的时候，可能会报以下错误

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

这是说明该用户已经被连接了，所以删除不了，可以使用

#找到连接的sid和端口号
    select sid,serial# from v$session where username='XMH';
    #杀死正在连接的session
    alter system kill session '40,2121';

上面两句可以将正在连接的会话进行强制关闭，两个号码是根据查出来的填入的。关闭会话之后就可以正常删除用户了。

创建好用户是需要进行授权。  
Oracle为了兼容以前的版本，提供了三种标准角色：connect、resource和dba  
三种作用分别是：

1.  connect（连接角色）：这种角色下只可以登录Oracle，不可用创建实体，也不可用创建数据库结构，即只能对其他人创建的表中的数据进行操作。
2.  resource(资源角色): 该角色可以创建实体，但是不可以创建数据库结构。 可以创建表、序列（sequence）、运算符(operator)、过程(procedure)、触发器(trigger)、索引(index)、类型(type)和簇(cluster)。
3.  dba（数据库管理员权限）：该角色拥有系统最高权限，只有DBA才可以创建数据库结构。包括无限制的空间限额和给其他用户授予各种权限的能力，system由dba用户拥有。  
    对于普通用户来说，授予connect和resource权限即可，只对dba授予connect、resource和dba权限。

授权的语法如下：

#授权
    grant connect, resource to 用户名;
    #如授权用户xmh权限connect、resource权限
    grant connect, resource to xmh;
    #撤销授权
    revoke connect, resource from 用户名;
    #如撤销xmh的权限
    revoke connect, resource from xmh;

除了三种系统角色，用户还可以自己创建角色，创建的角色可以由表或系统权限或者两者的组合构成。  
角色相关语法：

#创建角色
    create role 角色名;
    #如创建myRole角色
    create role myRole;
    
    #授权角色
    grant 权限 on 表名 to 角色名;
    #如使myRole获得了在mytable中使用select进行查询的权限
    grant select on mytable to myRole;
    #再比如为角色赋予创建会话的权限
    grant create session to myRole;
    
    #删除角色
    drop role 角色名;
    #如删除刚创建的myRole
    drop role myRole;
    #该句就是将myRole相关的权限从数据库中删除了