oracle 基本概念和常用操作
表空间的概念和基本操作
Oracle表空间分类
永久表空间
表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。
临时表空间
主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间。
操作
进入oracle 命令行界面：
sqlplus sys as sysdba
1-- 查找表空间文件的路径
2select * from dba_data_files;
3
4-- 删除非空表空间，包含物理文件
5drop tablespace TS_TEST including contents and datafiles;
6drop tablespace TS_TEST_TEMP including contents and datafiles;
7-- 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上 cascade constraint
8-- 例如：drop tablespace TS_TEST including contents and datafiles cascade constraint;
9
10-- 创建数据表空间
11create tablespace TS_TEST datafile 'ts_test.dbf' size 200M autoextend on;
12-- 创建数据表空间:设定表空间名、对应文件、初始大小、递增大小
13create tablespace test_tablespace datafile '/data/oracle-data/test_tablespace.dbf' size 512M autoextend on next 64M permanent online;
14
15
16-- 创建临时表空间(可选)
17create temporary tablespace TS_TEST_TEMP tempfile 'ts_test_temp.dbf' size 200M autoextend on;
18create temporary tablespace test_tablespace_temp tempfile '/data/oracle-data/test_tablespace_temp.dbf' size 256M autoextend on;
用户创建及授权
oralce的权限体系
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构（一般这个角色给开发人员使用）；例如：
- create sequence 创建序列
- create trigger 创建触发器
- create cluster 创建集群
- create procedure 创建存储过程
- create type 创建类型权限
- create operator 创建运营商
- create table 创建表
- create indextype 创建索引类型
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
操作
1-- 查找用户
2select * from dba_users;
3
4-- 删除用户
5drop user test cascade;
6
7-- 创建用户，并指定密码、默认表空间
8create user test identified by 123456 default tablespace TS_TEST temporary tablespace TS_TEST_TEMP;
9create user zc identified by def_password default tablespace test_tablespace temporary tablespace test_tablespace_temp;
10
11-- 给用户授予权限（resource为开发者角色，可以创建表、过程、触发器等对象，但是不能创建视图）
12-- grant connect,resource,dba to test;
13grant connect,resource,CREATE VIEW to test;
14grant connect,resource,CREATE VIEW to zc;
15
16-- 以下是分步骤创建用户、指定密码和表空间
17
18-- 创建用户，并指定密码
19create user test identified by test;
20
21-- 修改用户密码
22alter user test identified by 123456;
23
24-- 修改用户的默认表空间
25alter user test default tablespace TS_TEST;
26
27-- 修改用户的默认临时表空间
28alter user test temporary tablespace TS_TEST_TEMP;
29
30-- 指定某个用户拥有其他表空间：
31-- 用户可以指定多个其他表空间，但是默认表空间只有一个
32
33ALTER USER test QUOTA UNLIMITED ON TS_CGPT;
说明
在Oracle中当我们创建一个用户时，系统会自动为该用户创建一个默认的schema，并且用user的名字作为这个schema的名字。
需要注意的是，在Oracle中，使用者不能直接创建schema，想要创建schema就只能通过创建一个user来实现。
所以，可以得出一个结论：在Oracle中user的个数与schema的个数是相等的。
但是在Oracle中可以修改用户默认的模式：
ALTER SESSION SET CURRENT_SCHEMA = scott;
每个用户除了拥有一个默认的schema之外，还可以拥有其他的schema，这就意味着一个用户除了可以操作自己名下的表，也可以通过授权来操作其他用户的表。
即，每个schema中的对象都是可以被其他用户共享的，当然，前提是其他用户要有相应的权限。
既然一个用户可以操作多个schema中的对象，那么，当我们用某个特定的用户名和密码与数据库建立连接后，系统是怎么确认我们操作的是哪张表呢？
很简单，类似于在java中通过 对象名.方法名 来调用某个对象的方法一样，Oracle中可以用 模式名.对象名 来唯一确定要操作的对象。
例如，通过scott用户与数据库建立连接，执行 select * from emp 时，此时没有指定schema，则默认操作的是scott用户下的默认schema中的emp表，即scott.emp 。
如果scott用户下还有一个名为test的schema，我们想要操作test下的emp表，就需要指定模式名：select * from test.emp
Schema 与tablespace之间没有什么联系，同一个schema的对象能够存储在不同的表空间中，同一个表空间中能够存储不同schema的对象。
而user和tablespace之间也没有啥关系，在实际使用中只需要注意user和schema之间的关系即可。
oracle 的 DML 和 DDL
1-- 在默认表空间下创建表：
2create table student (id number(16) not null, name varchar2(32));
3
4-- 添加主键：
5alter table student add constraint pk_id primary key (id);
6
7-- 添加注释：
8comment on column student.id is '主键';
9comment on column student.name is '姓名';
10
11-- 添加数据:
12insert into student values (1, '汤姆');
13insert into student values (2, 'Jerry');
14
15-- 查询数据:
16select * from student;
17
18-- 在非默认的表空间下创建表，需要指定表空间的名字：
19create table TABLE_NAME (xx xxx) tablespace TABLESPACE_NAME;
授予权限
在Oracle中，我们可以使用GRANT语句为用户授予权限。GRANT语句的基本语法如下：
GRANT <权限列表> TO <用户或角色>;
其中，<权限列表>表示要授予的权限，可以是一个或多个权限；<用户或角色>表示要赋予权限的用户或角色。
以下是一些常见的权限及其解释：
- SELECT：允许用户查询表的数据；
- INSERT：允许用户向表中插入数据；
- UPDATE：允许用户更新表中的数据；
- DELETE：允许用户删除表中的数据；
- CREATE：允许用户创建表、视图等对象；
- ALTER：允许用户修改表、视图等对象的结构；
- DROP：允许用户删除表、视图等对象；
- ALL：允许用户拥有所有权限。
示例
假设我们有两个用户，USER_A和USER_B，分别拥有自己的模式SCHEMA_A和SCHEMA_B。
我们希望将SELECT和UPDATE权限授予USER_A，并将INSERT和DELETE权限授予USER_B。下面是具体操作步骤：
首先，使用管理员账号登录到数据库。
1-- 为USER_A授予SELECT和UPDATE权限：
2GRANT SELECT, UPDATE ON SCHEMA_A.TABLE_NAME TO USER_A;
3
4-- 为USER_B授予INSERT和DELETE权限：
5GRANT INSERT, DELETE ON SCHEMA_B.TABLE_NAME TO USER_B;
6
7-- 向用户授予访问特定表的权限：
8GRANT SELECT ON schema1.table1 TO user1;
9GRANT INSERT, UPDATE, DELETE ON schema2.table2 TO user2;
10
11-- 向用户授予访问所有表的权限：
12GRANT SELECT ANY TABLE TO user1;
13GRANT INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO user2;
14
15-- 向用户授予创建表的权限：
16GRANT CREATE TABLE TO user1;
17GRANT CREATE ANY TABLE TO user2;
18
19-- 向用户授予执行存储过程的权限：
20GRANT EXECUTE ON schema1.procedure1 TO user1;
21GRANT EXECUTE ANY PROCEDURE TO user2;
22
23-- 同时授予多个权限：
24GRANT SELECT, INSERT, UPDATE ON schema1.table1 TO user1;
25
26-- 向用户授予所有权限：
27GRANT ALL PRIVILEGES ON schema1.table1 TO user1;
除了以上的基本权限授予，Oracle还支持更细粒度的权限控制，例如对特定的列、视图、存储过程等进行授权。
oracle把一个用户的所有表的查询权限给另一个用户：
以管理员权限的用户登录数据库，并在SQL Plus中执行以下语句，以创建一个包含所有表的脚本：
1SELECT 'GRANT SELECT ON schema1.' || TABLE_NAME || ' TO user2;'
2FROM ALL_TABLES
3WHERE OWNER = 'schema1';
执行后会生成一系列GRANT语句，将生成的GRANT语句复制到SQL Plus中，并执行它们；像这样：
1GRANT SELECT ON schema1.table1 TO user2;
2GRANT SELECT ON schema1.table2 TO user2;
3GRANT SELECT ON schema1.table3 TO user2;
4...
上面生成的是查询权限；如果加别的权限可以用下面的sql进行查询：
1SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON schema1.' || TABLE_NAME || ' TO user2;'
2FROM ALL_TABLES
3WHERE OWNER = 'schema1';
注意：此操作将授予新用户对所有表的查询权限，包括可能包含敏感信息的表。
在执行此操作之前，请确保已经仔细考虑了安全风险并采取必要的措施。
撤销权限
如果需要撤销用户的权限，可以使用REVOKE语句。REVOKE语句的基本语法如下：
REVOKE <权限列表> FROM <用户或角色>;
以下是示例操作：
1REVOKE SELECT, UPDATE ON SCHEMA_A.TABLE_NAME FROM USER_A;
2REVOKE INSERT, DELETE ON SCHEMA_B.TABLE_NAME FROM USER_B;
3
4-- 撤销用户对表的权限：
5REVOKE SELECT ON schema1.table1 FROM user1;
6REVOKE INSERT, UPDATE, DELETE ON schema2.table2 FROM user2;
7
8-- 撤销用户的所有权限：
9REVOKE ALL PRIVILEGES ON schema1.table1 FROM user1;
检查用户权限
可以使用以下查询语句来检查用户的权限：
1-- 查询用于检查用户的系统权限
2SELECT *
3FROM USER_SYS_PRIVS
4WHERE GRANTEE = 'user1';
5
6-- 查询用于检查用户对表的权限
7SELECT *
8 FROM USER_TAB_PRIVS
9WHERE GRANTEE = 'user1';
总结
本文我们介绍了如何在Oracle数据库中为用户授予不同模式下的权限。
通过使用GRANT语句，我们可以为用户分配特定的权限，使他们能够在各自的模式中进行操作。
权限授予是数据库管理的重要环节之一，合理地进行权限管理可以提高数据库的安全性和管理效率。
在实际应用中，根据具体需求和安全要求，我们可以灵活地设置用户的权限，控制他们对数据库对象的访问和操作范围。
在设计数据库时，我们也应该考虑到用户权限的合理分配，以保护数据的完整性和安全性。
评论