↓跳到主要内容

Oracle角色与用户（创建、授权、锁表解锁）

·5306 字·11 分钟
Oracle 角色 用户 Oracle基础

Oracle创建用户、创建角色、单表授权、批量授权、锁表解锁、实战导入数据库操作

1.Oracle用户与角色 #

安装Oracle时，若没有设置默认密码，则以下为默认账户的密码：

用户名 / 密码登录身份说明
sys/change_on_installSYSDBA 或 SYSOPER不能以 NORMAL 登录，可作为默认的系统管理员,超级管理员，负责数据库系统的管理
system/managerSYSDBA 或 NORMAL普通管理员，负责数据库对象的管理，如：创建用户，授权，创建数据库等等
sysman/oem_tempsysman 为 oms 的用户名
scott/tigerNORMAL普通用户
aqadm /aqadmSYSDBA 或 NORMAL高级队列管理员
Dbsnmp/dbsnmpSYSDBA 或 NORMAL复制管理员
scottSYSDBA 或 NORMAL该用户主要用于给我们学习的时候使用。如果要使用该用户就必须解锁，并且修改密码
hrSYSDBA 或 NORMAL测试用户。该用户有一些hr相关的测试数据。如果要使用该用户就必须解锁，并且修改密码

cmd窗口连接数据库时，运行cmd命令行

--无用户名登录
sqlplus /nolog
--连接到数据本地数据
conn /as sysdba
--修改System 密码  为password
alter user system identified by password

1.1查看所有用户信息 #

dba_users：保存系统中所有用户的详细信息。

select * from dba_users;

1.2创建用户 #

--[]中代表可以省略
create user 用户名
identified by 密码 
[default tablespace 表空间]
[temporary tablespace 临时表空间]
[profile DEFAULT];

注：Oracle系统中的profile可以用来对用户所能使用的数据库资源进行限制。如果把该profile分配给用户，则该用户所能使用的数据库资源都在该profile的限制之内。具体管理内容有：CPU的时间、I/O的使用、IDLE TIME（空闲时间）、CONNECT TIME（连接时间）、并发会话数量、口令机制等。

--例子
create user testAnarkh
identified by 123456
default tablespace test01;

1.3授予权限 #

--授权
grant connect,resource to user;
grant dba to user;
--撤销授权
revoke connect,resource from user;
revoke dba from user;

--例子
--进入 system 用户下给用户赋予 dba 权限，否则无法正常登陆
grant dba to testAnarkh;

登录之前，需要给testAnarkh用户授予权限，不然登录不了。

Oracle 中已存在三个重要的角色：connect 角色，resource 角色，dba 角色。

①CONNECT 角色： –是授予最终用户的典型权利，最基本的。

权限描述
ALTER SESSION修改会话
CREATE CLUSTER建立集群
CREATE DATABASE LINK建立数据库链接
CREATE SEQUENCE建立序列
CREATE SESSION建立会话
CREATE SYNONYM建立同义词
CREATE VIEW建立视图

②RESOURCE 角色： –是授予开发人员的。

权限描述
CREATE CLUSTER建立集群
CREATE PROCEDURE建立过程
CREATE SEQUENCE建立序列
CREATE TABLE建表
CREATE TRIGGER建立触发器
CREATE TYPE建立类型

③DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统权限也需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除。

1.4设置用户密码 #

--该方式不能修改为纯数字密码
--cmd打开window命令窗口
--输入命令
	sqlpuls /nolog
--输入命令
	conn /as sysdba
--输入命令
alter user 要修改密码的用户名 identified by 新密码;

注意：sqlplus不是内部命令的错误，是因为oracle的环境变量配置有问题。（将oracle的bin目录配置到path环境变量中，因为windows使用命令窗口时，会从path环境变量中检索目录，寻找可执行程序。如果没有配置，检索不到可执行程序，就会报错。）

--例子
alter user testAnarkh identified by abc123456;

1.5解锁用户 #

--锁定用户
alter user 用户名 account lock;
--解锁用户
alter user 用户名 account unlock;

--例子
--锁定testAnarkh测试用户
alter user testAnarkh account lock;
--解锁testAnarkh测试用户
alter user testAnarkh account unlock;

1.6删除用户 #

--如果删除的用户中已经有数据，那么删除用户的时候，就必须要指定cascade关键字。
drop user 用户名 [cascade];

--例子
drop user testAnarkh [cascade];

1.7系统权限 #

系统权限就是创建数据库对象的权限。系统权限都是以create开头的权限。

只有管理员才可以授予用户系统权限。

–例如：创建会话、创建表、创建索引、创建序列等等。 create session、create table、create index、create sequence等等。

--授予用户系统权限。
--只有管理员才可以授予用户系统权限。
grant create 权限 to 用户;

--例子
--授予用户testAnarkh创建会话、创建表的系统权限。
grant create session,create table 
to testAnarkh;

1.8对象权限 #

对象权限就是对数据库对象的操作权限。

–例如：添加表数据、查询表、删除表等等操作。 select、insert、update、delete、all。

--授予用户对象权限。 
grant 操作权限 
on 用户1.表名
to 用户2;

--例子
--授予用户testAnarkh查询和插入scott用户的emp表的权限。
grant select,insert
on scott.emp
to testAnarkh;

1.9查看用户权限 #

dba_sys_privs：保存所有用户的系统权限；

dba_tab_privs：保存所有用户的对象权限；

--查看用户TESTANARKH所有的系统权限
select * from dba_sys_privs where grantee ='TESTANARKH';

--查看用户TESTANARKH所有的对象权限
select * from dba_tab_privs where grantee ='TESTANARKH';

1.10回收权限 #

--回收用户的系统权限：
revoke create 权限 from 用户;
--回收用户的对象权限： 
revoke 操作 on 用户1.表名 from 用户2;

--例子
--回收用户TESTANARKH创建表的系统权限
revoke create table from TESTANARKH;
--回收用户TESTANARKH对scott用户下emp表的插入的对象权限
revoke insert on scott.emp from TESTANARKH;

1.11角色 #

角色就是一组权限的集合。

角色的作用：简化授权操作。

Oracle 中已存在三个重要的角色：connect 角色，resource 角色，dba 角色（上面已提及，不再赘述）。

1.11.1使用角色 #
--第一步：创建角色；
create role 角色名;
 
--第二步：给角色授权；
grant create 权限to 角色; --授予角色系统权限 
grant 操作 on 用户.表名 to 角色; --授予角色对象权限
 
--第三步：把角色赋给用户；
grant 角色 to 用户;

--例子
--第一步：创建角色t_role；
create role t_role;
 
--第二步：给角色授权；
grant create table to t_role; --授予角色系统权限
grant select on scott.emp to t_role; --授予角色对象权限

--第三步：把角色t_role赋给用户testAnarkh；
grant t_role to testAnarkh;
1.11.2查询用户角色 #

dba_role_privs：保存所有用户的角色信息。

--例子
--查询用户TESTANARKH所有的角色信息
select * from dba_role_privs where grantee ='TESTANARKH';
1.11.3回收角色 #
revoke 角色名 from 用户;

--例子
--回收用户testAnarkh的t_role角色
revoke t_role from testAnarkh;
1.11.4删除角色 #
--删除角色之后，那么拥有该角色的用户就会自动地把该角色取消。
drop role 角色名;

--例子
--删除角色t_role
drop role t_role;

2.实战中的一些授权语句 #

2.1非批量授权语句（多为单表） #

--在A用户中给B授权：
grant select, insert, update, delete, alter, index on TF20 to B;
grant all on TF20 to B;
--在系统管理员中把A权限授权给B：
grant select, insert, update, delete, alter, index on A.TF20 to B;
grant all on A.TF20 to B;
--查询数据库中的所有用户
select * from dba_users;
--锁住用户nnjy
alter user nnjy account lock;
--给用户nnjy解锁
alter user nnjy account unlock; 
--建立用户nnjy（密码也是nnjy）
create user nnjy identified by nnjy;
--用户授权
--赋予数据库登录连接权限和资源操纵权限
grant connect,resource,dba to nnjy;
--将table1的查询权限授给nnjy用户
grant select on tabel1 to nnjy;
--将存储过程procedure1的执行权限授予用户nnjy
grant execute on procedure1 to nnjy;
--收回用户nnjy对table1的select（查询）权限
revoke select on table1 from nnjy;
--收回用户nnjy对table1的所有权限
revoke all on table1 from nnjy;
--收回用户nnjy的connect权限
revoke connect from nnjy;
--查询用户nnjy拥有的对象权限
select table_name,privilege from dba_tab_privs where grantee='nnjy';
--查询用户nnjy拥有的系统权限
select * from dba_sys_privs where grantee='nnjy';
--当前会话有效的系统权限
select * from session_privs;

--角色
--建立anarkh1角色
create role anarkh1;
--将nnjyu33用户下的up_lemis_menu表的insert（插入）权限授予角色anarkh1
grant insert on nnjyu33.up_lemis_menu to anarkh1;
--收回角色anarkh1的nnjyu33用户下的up_lemis_menu表的insert（插入）权限
revoke insert on nnjyu33.up_lemis_menu from anarkh1;
--将anarkh1角色的权限授权给用户nnjy
grant anarkh1 to nnjy;
--建立anarkh2角色
create role anarkh2;
--将角色anarkh1的权限授权给角色anarkh2
grant anarkh1 to anarkh2; 
--修改用户默认角色
alter user nnjy default anarkh1,anarkh2;
--删除角色anarkh1；
DROP ROLE anarkh1;
--查询anarkh1角色下有什么系统权限
select * from role_sys_privs where role='anarkh1';
--查询anarkh1角色下有什么角色权限
select granted_role,admin_option from role_role_privs where role='anarkh1';
--查询用户nnjy下的多有角色
select * from dba_role_privs where grantee='nnjy';

2.2批量授权语句 #

2.2.1system账号的授权语句 #
2.2.1.1授权表上的读写权限 #
select 'grant all on '||owner||'.'||table_name||' to NNJY;' from dba_tables where owner = 'SYSTEM' and table_name NOT LIKE '%$%';
2.2.1.2授权视图上的读写权限 #
select 'grant all on '||owner||'.'||view_name||' to NNJY;' from dba_views where owner = 'SYS';
2.2.1.3授权函数和存储过程的读写权限 #
select DISTINCT 'grant execute on '||owner||'.'||name||' to NNJY;' from dba_source where owner = 'SYS' and type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','TYPE') AND name NOT LIKE '%$%'
2.2.1.4授权序列的读写权限 #
select 'grant all on '||sequence_owner||'.'||sequence_name||' to NNJY;' from dba_sequences where sequence_owner = 'SYSTEM' AND sequence_name NOT LIKE '%$%';
2.2.1.5建同义词（未使用过） #
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms where table_owner='SYS' and synonym_name NOT LIKE '%$%';

select 'create or replace public synonym '||view_name||' for '||owner||'.'||view_name||' ;' from dba_views where owner = 'SYS' and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%') ;
2.2.2普通用户的一些授权操作 #
2.2.2.1授权一个用户所有表的所有权限给另一个用户 #
--将NNJY用户上所有表的读写权限授权给NNJYU33用户
select 'grant all on ' || owner || '.' || table_name || ' to NNJYU33;' from dba_tables where owner = 'NNJY';
--或者直接在NNJY用户下执行以下语句
select 'GRANT ALL ON '||table_name||' to NNJYU33;'  from user_tables;
2.2.2.2授权一个用户所有视图的所有权限给另一个用户 #
--将NNJY用户上所有视图的所有权限授权给NNJYU33用户
select 'grant all on ' || owner || '.' || view_name || ' to NNJYU33;' from dba_views where owner = 'NNJY';
--或者直接在NNJY用户下执行以下语句
select 'GRANT ALL ON '||view_name||' to NNJYU33;'  from user_views;
2.2.2.3授权一个用户所有同义词的所有权限给另一个用户 #
--将NNJY用户下所有同义词的所有权限授权给NNJYU33用户
select 'grant all on ' || owner || '.' || synonym_name || ' to NNJYU33;' from dba_synonyms where owner = 'NNJY';
--或者直接在NNJY用户下执行以下语句
select 'GRANT ALL ON '||synonym_name||' to NNJYU33;'  from user_synonyms;
2.2.2.4授权一个用户所有函数和存储过程的所有权限给另一个用户 #
--将NNJY用户下所有的函数和存储过程的所有权限授权给NNJYU33用户
select 'grant execute on ' || owner || '.' || name || ' to NNJYU33;' from dba_source where owner = 'NNJY' and type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','TYPE');
2.2.2.5授权一个用户所有的序列的所有权限给另一个用户 #
--将NNJY用户下所有的序列的所有权限授权给NNJYU33用户
select 'grant all on '||sequence_owner||'.'||sequence_name||' to NNJYU33;' from dba_sequences where sequence_owner = 'NNJY' ;
2.2.2.6一个用户下的所有视图在另一个用户下建立同义词 #
--将NNJY用户下所有视图在另一个用户下创建同义词。
--执行以下语句，得到批量语句在NNJYU33用户下执行，就可以在NNJYU33用户下建立所有NNJY用户下所有视图的同义词
select 'create or replace synonym '||view_name||' for '||owner||'.'||view_name||' ;' from dba_views where owner = 'NNJY' and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%') ;
2.2.2.7一个用户下的所有表在另一个用户下建立同义词 #
--将NNJY用户下所有表在另一个用户下创建同义词。
--执行以下语句，得到批量语句在NNJYU33用户下执行，就可以在NNJYU33用户下建立所有NNJY用户下所有表的同义词
select 'create or replace synonym '||table_name||' for '||owner||'.'||table_name||' ;' from dba_tables where owner='NNJY' ;
2.2.2.8一个用户下的所有的同义词在另一个用户下建立同义词 #
--将NNJY用户下所有同义词在另一个用户下创建同义词。
--执行以下语句，得到批量语句在NNJYU33用户下执行，就可以在NNJYU33用户下建立所有NNJY用户下所有同义词的同义词
select 'create or replace synonym '||synonym_name||' for '||owner||'.'||synonym_name||' ;' from dba_synonyms where owner='NNJY' ;

3.实战中查询锁表与解锁 #

--查询那些对象被锁：
select object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session s where l.object_id　=　o.object_id and l.session_id=s.sid;

--解锁语句：
alter system kill session 'sid, serial#'; 
--例子
--其中23,1647分别是上面查询出的sid,serial#
alter system kill session '23, 1647';

4.实战中的创建用户，导入数据库的一整套流程 #

在导入数据之前，可以查询一下要导出的数据库的表空间，导入的表空间可以与原数据库表空间对应上。

查询当前用户表空间

select username,default_tablespace from user_users;

**查询指定的表空间信息 **

SELECT * FROM Dba_Data_Files ddf WHERE ddf.tablespace_name = 'TablespaceName';
--例子
SELECT * FROM Dba_Data_Files ddf WHERE ddf.tablespace_name = 'RSRCV1DATA';

查询表空间使用情况

select
　　a.a1 表空间名称,
　　c.c2 类型,
　　c.c3 区管理,
　　b.b2/1024/1024 表空间大小M,
　　(b.b2-a.a2)/1024/1024 已使用M,
　　substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
　　from
　　(select tablespace_name a1, sum(nvl(bytes,0) a2 from dba_free_space group by tablespace_name) a,
　　(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
　　(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
　　where a.a1=b.b1 and c.c1=b.b1;

4.1创建数据库表空间 #

create tablespace RSRCV1DATA datafile'D:\ORACLE\APP\ANARKH\ORADATA\ORCL\RSRCV1DATA.DBF' size 12g;

4.2创建用户 #

create user eedsrsrc
identified by eedsrsrc
default tablespace RSRCV1DATA
temporary tablespace TEMP
profile DEFAULT;

create user eedsrsrcu33
identified by eedsrsrcu33
default tablespace RSRCV1DATA
temporary tablespace TEMP
profile DEFAULT;

4.3授权 #

grant dba to eedsrsrcu33;
grant dba to eedsrsrc;

4.4导入数据库 #

imp eedsrsrc/eedsrsrc@dl30 file=E:\eedsrsrc.dmp fromuser=eedsrsrc touser=eedsrsrc grants=n
imp eedsrsrcu33/eedsrsrcu33@dl30 file=E:\eedsrsrcu33.dmp fromuser=eedsrsrcu33 touser=eedsrsrcu33 grants=n

实战中，数据库上有些用户是从现场或者其他用户导过来的，表、大字段、索引表空间都还是原先的表空间，需要改到现在用户的表空间下，导完用户或者表看看是不是都在默认的表空间，不是的话参考下面的语句重建一下即可。

4.5表不在默认表空间的重建语句 #

select u.username,
       u.default_tablespace,
       s.segment_name,
       s.segment_type,
       s.tablespace_name,
       'alter table ' || segment_name || ' move tablespace ' ||
       u.default_tablespace || ';'
  from dba_users u, dba_segments s
 where u.username = s.owner
   and u.default_tablespace != s.tablespace_name
   and u.username ='CCRCWEB'
   and s.segment_type = 'TABLE';

4.6大字段表空间不在默认表空间中的重组语句 #

SELECT T.TABLE_NAME,
       T.COLUMN_NAME,
       T.SEGMENT_NAME,
       T.TABLESPACE_NAME,
       'ALTER TABLE ' || T.TABLE_NAME || ' MOVE LOB(' || T.COLUMN_NAME || ') STORE AS ' || T.SEGMENT_NAME || ' (TABLESPACE ' || 'CCRCDATA' || ' DISABLE STORAGE IN ROW  CHUNK 16384  RETENTION  FREEPOOLS 1  NOCACHE);'
        FROM USER_LOBS T
 WHERE T.TABLESPACE_NAME <> 'CCRCDATA';

4.7索引重建语句 #

select s.segment_name,
       s.segment_type,
       s.tablespace_name,
       'alter index ' || segment_name || ' rebuild tablespace CCRCDATA;'
  from user_segments s
 where s.segment_type = 'INDEX' and tablespace_name<>'CCRCDATA';

--实战使用的重建索引语句
select 'alter index ' || index_name ||
       ' rebuild tablespace WHRSRCDATA;'
  from user_indexes;

4.8删除用户语句 #

drop user nmgjy cascade;

4.9注意： #

如果表空间不够，导入的时候会提示挂起状态（suspended。ORA-39171: Job is experiencing a resumable wait），此时需要给表空间扩容。

先查一下表空间使用率：

SELECT a.tablespace_name "表空间名",
       
       total / 1024 / 1024 "表空间大小单位M",
       
       free / 1024 / 1024 "表空间剩余大小单位M",
       
       (total - free) / 1024 / 1024 "表空间使用大小单位M",
       
       Round((total - free) / total, 4) * 100 "使用率   [[%]]"FROM
       
       (SELECT tablespace_name, Sum(bytes) free
          FROM DBA_FREE_SPACE
         GROUP BY tablespace_name) a,
       
       (SELECT tablespace_name,
               
               Sum(bytes) total
          FROM DBA_DATA_FILES
         GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;

再查一下表空间是否是自增的：

select tablespace_name,file_name,autoextensible from dba_data_files;

将大小不够的表空间进行扩容：

alter tablespace RLZY_DATA add datafile 'F:/oracletablespace/RLZY_DATA.DBF' size 12g AUTOEXTEND on next 100m;
Anarkh
作者
Anarkh
博学之 审问之 慎思之 明辨之 笃行之
70 Basic icons by Xicons.co
←→ Oracle的导出和导入 Java基础系列 集合 →←