Oracle数据库的一些名词:
1、数据库和实例:
完整的Oracle数据库通常由两部分组成:Oracle数据库和数据库实例。数据库是一系列物理文件的集合(数据文件,控制文件,联机日志,参数文件等)。Oracle数据库实例则是一组Oracle后台进程/线程以及在服务器分配的共享内存区。
在启动Oracle数据库服务器时,实际上是在服务器的内存中创建一个Oracle实例(即在服务器内存中分配共享内存并创建相关的后台内存),然后由这个Oracle数据库实例来访问和控制磁盘中的数据文件。Oracle有一个很大的内存快,成为全局区(SGA)。我们访问Oracle都是访问一个实例,但这个实例如果关联了数据库文件,就是可以访问的,如果没有,就会得到实例不可用的错误。数据库实例名(instance_name)用于对外部连接。在操作系统中要取得与数据库的联系,必须使用数据库实例名。比如我们作开发,要连接数据库,就得连接数据库实例名:
jdbc:oracle:thin:@localhost:1521:orcl(orcl就为数据库实例名)
2、启动数据库(全局数据库):
启动数据库也叫全局数据库,是数据库系统的入口。我们在安装Oracle数据库时,会让我们选择安装启动数据库(即默认的全局数据库),就是一个数据库的标识,在安装时就要想好,以后一般不修改,修改起来也麻烦,因为数据库一旦安装,数据库名就写进了控制文件,数据库表,很多地方都会用到这个数据库名。它会内置一些高级权限的用户如SYS,SYSTEM等。我们用这些高级权限账号登陆就可以在数据库实例中创建表空间,用户,表了。
3、表空间:
Oracle数据库是通过表空间来存储物理表的,一个数据库实例可以有N个表空间,一个表空间下可以有N张表。有了数据库,就可以创建表空间(创建表空间时需指定物理文件路径:一个或者多个文件路径)。表空间对应一个或者多个数据文件,表空间的大小是它所对应的数据文件的大小总和。表空间是数据库的逻辑划分,每个数据库至少有一个表空间(称作SYSTEM表空间)。为了便于管理和提高运行效率,可以使用一些附加表空间来划分用户和应用程序。例如:USER表空间供一般用户使用,RBS表空间供回滚段使用。一个表空间只能属于一个数据库。
4、用户:
Oracle数据库建好后,要想在数据库里建表,必须先为数据库建立用户,并为用户指定表空间。有了用户,要想使用用户账号管理自己的表空间,还得给它分权限,有了数据库,表空间和用户,就可以用自定义的用户在自己的表空间创建表了。有了表,我们可以开发了。
常用命令:
1、需要开启的Oracle服务:
1)只用Oracle自带的sqlplus:启动OracleServiceORCL ;
2)使用PL/SQL Developer等第三方工具: 启动OracleOraDb11g_home1TNSListener ;
3)运行Enterprise Manager(企业管理器OEM):启动OracleDBConsoleorcl ;
2、登录数据库:sqlplus user_name
DBA用户名为'system'或者'sys',system密码为安装时设置的密码;
sys密码为:change_on_install as sysdba;
或者用:sqlplus /nolog
conn /as sysdba //此处登录为管理员,若为普通用户则输入:conn
解锁用户:alter user user_name account unlock ;
修改密码:alter user user_name identified by password;
3、查询当前数据库名:select name from v$database;
4、查询当前数据库实例名:select instance_name from v$instance;
5、查看已经创建好的表空间:select default_tablespace,temporary_tablespace,d.username from dba_users d;
6、创建表空间:create tablespace tablespace_name //表空间名称
datafile ‘tablespace_pathname’
//表空间的物理文件路径,可以有多个,文件格式.DBF
size tablespace_size[k|m] //表空间的大小,单位为M或K
autoextend on //开启表空间的自动扩展
7、查看所有角色:select * from dba_roles;
8、创建角色:create role role_name //角色名,初创的角色权限为空,需要授权
[not identified] //该角色由数据库授权,不需要口令使该角色生效
[identified by password | externally | globally] // 在用set role语句使该角色生效之前必须由指定的方法来授权一个用户。by password :创建一个局部用户,在使角色生效前,用户必须指定password定义的口令;externally:创建一个外部用户,在使角色生效前,必须由外部服务器来授权用户;globally:创建一个全局用户,利用set role语句使该角色生效或者登陆时,用户必须由企业目录服务器授权使用该角色。
9、修改角色:alter role role_name
[not identified]
[identified by password | externally | globally]
10、查询所有用户名:select username from dba_users; select username from all_users;
11、查询当前用户信息:select * from dba_ustats; select * from user_users;
12、创建用户:create user user_name //用户名
[identified by {password | externally | globally as'external_name}] //如何验证用户
[default tablespace tablespace_name] //默认表空间
[temporary tablespace tablespace_name] //临时段的表空间
[quota integer K | integer M | unlimited on tablespace_name]
//用户规定的表空间存储对象,最多可达到这个定额规定的尺寸
[profile profile_name] //指定的概要文件分配给用户
[password expire] //使用户的密码过期,这样用户登录时必须修改密码
[account {lock | unlock}] //账户是否锁定
13、修改用户:create user user_name
[identified by {password | externally | globally as'external_name}]
[default tablespace tablespace_name]
[temporary tablespace tablespace_name]
[quota integer K | integer M | unlimited on tablespace_name]
[profile profile_name]
[password expire]
[account {lock | unlock}]
14、删除用户:drop user user_name cascade; //需要指定级联cascade.
15、查看默认概要文件:select distinct profile from dba_profiles where profile='DEFAULT';
16、创建用户概要文件:create profile profile_name limit
connect_time int //指定会话保持连接时间,单位为秒
cpu_per_call int //限制事务内每个调用使用cpu的时间
cpu_per_session int // 限制会话中使用的总cpu时间
sessions_per_user int //指定用户可以打开的并发会话的最大数目
idle_time int //限制一个会话空闲的时间量
logical_reads_per_session int //限制数据块读取的总数目
logical_reads_per_call int //限制每个会话调用的总的逻辑读取数
failed_login_attempts int //用户可尝试的登录次数
password_life_time int //设置密码的生命周期
password_grace_time int
//设置一个时间段,宽限时间内发出密码过期警告,超过宽限时间密码失效
password_lock_time int //用户登录失败后被锁多少天
password_reuse_time int //指定可重新使用相同密码前要经过的天数
password_reuse_max int
//确定在可以重新使用某个特定密码前可更改该密码多少次
password_verify_function //指定自己创建的密码验证函数
17、指派用户概要文件:alter user user_name profile profile_name;
18、更改概要文件:alter profile profile_name limit ......;
19、删除概要文件:drop profile profile_name cascade; //删除后用户被指派为默认概要文件
20、给用户、角色授予权限:grant privilege[,...n] to {user_name[...n] | role_name | public } [with admin option];
public:Oracle中的公共用户组;
with admin option:表示权限可以传递。
21、取消用户、角色权限:revoke privilege[,...n] from {user_name[...n] | role_name | public } [with admin option];
注:Oracle对系统权限不具有级联收回的功能。
22、将角色授予用户:grant role_name[,...n] to {user_name | role_name | public } [with admin option];
23、收回用户的角色:revoke role_name[,...n] from {user_name | role_name | public } ;
24、启用和禁用用户的角色:set role { role_name[,...n] | all [ except role_name[,...n] ] | none] };
25、对象权限的授予:grant {object_privilege | all [ privilege ][(column[,...n])]} on [schema.]object_name to {user_name[...n] | role_name | public } [with admin option];
其中对象权限有select、update、delete、insert、execute、read、index、preferences、alter。
all表示授予该对象全部的对象权限,column来指定表的某列的权限(只有update可以指定列)。
示例:grant update(studentID,classID,grade) on selectiveinfo to teacher;
26、对象权限的收回:revoke {object_privilege | all [ privilege ][(column[,...n])]} on [schema.]object_name to {user_name[...n] | role_name | public } [cascade constraints];
注:与系统权限相反,Oracle会级联回收对象权限。
27、查看当前用户的缺省表空间 : select username,default_tablespace from user_users;
28、查看当前用户的角色 : select * from user_role_privs;
29、查看当前用户的系统权限和表级权限 : select * from user_sys_privs;select * from user_tab_privs;
30、查看用户下所有的表名 : select table_name from user_tables;
31、显示当前会话所具有的权限 : select * from session_privs;
32、显示指定用户所具有的系统权限 : select * from dba_sys_privs;
33、显示特权用户 : select * from v$pwfile_users;
34、查看oracle版本命令: select * from v$version;
35、查看视图信息:select * from user_views;
36、查看同义词的名称:select * from user_synonyms;
37、查看函数和过程状态:
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
38、删除函数和过程:
drop procedure procedure_name;
drop function function_name;
39、查看函数和过程的源代码:
select text from all_source where owner=user and name='SF_SPLIT_STRING';
40、查看表结构:desc table_name;
41、创建密码验证函数:
Oracle的启用默认密码验证函数:@?/rdbms/admin/utlpwdmg.sql,执行该命令后,创建默认密码验证函数,并且制定为默认概要文件的密码验证函数。根据密码验证函数的格式,再结合具体的需求可以自定义密码验证函数。
需求:1)要求密码至少包含大小写字母、数字和特殊字符(#%&*)中的一个;
2)并且不少于10位;
CREATE OR REPLACE FUNCTION my_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
isdigit boolean;
ischar boolean;
isspecial boolean;
digitarray varchar2(20);
chararray varchar2(52);
special varchar2(10);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
special:='#%&*';
-- Check for the minimum length of the password
IF length(password) < 10 THEN
raise_application_error(-20001, 'Password length less than 10');
END IF;
-- Check if the password contains at least one letter,one digit,one special
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20002, 'Password must contain at least one digit,one special ,one character');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findspecial;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20002, 'Password must contain at least one digit,one special , one character');
END IF;
-- 3. Check for the special
<<findspecial>>
isspecial:=FALSE;
FOR i IN 1..length(special) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(special,i,1) THEN
isspecial:=TRUE;
END IF;
END LOOP;
END LOOP;
IF isspecial = FALSE THEN
raise_application_error(-20002, 'Password must contain at least one digit,one special ,one character');
END IF;
-- Everything is fine; return TRUE;
RETURN(TRUE);
END;
/
alter profile student_profile limit
password_verify_function my_verify_function;
将创建函数的语句放在一个.sql文件中,然后在命令行执行:@file_path/xxx.sql即可。
42、数据库审计:
查看审计是否开启:show parameter audit; //如果audit_sys_operations值为FALSE就是没开审计)
开启审计功能:alter system set audit_trail=db_extended scope=spfile ;
alter system set audit_sys_operations=TRUEscope=spfile;
重启数据库实例:shutdown immediate ; //只有sys用户才有权限
startup force ;
语句级审计:audit table by user_name ;
权限级审计:audit all privilege ;
对象级审计:audit update any table;audit select on table_name ;
43、关闭审计:noaudit session ; no audit table by user_name ;