一、查看数据库表及其含义
(一)oracle查看数据库和数据库中的表
1、用户表和数据字典
(1)用户表:由用户创建,包含用户的内容;
(2)数据字典:由系统建立,包含数据库的信息;
2、数据字典表前缀:
(1)USER_ :由用户创建,显示用户拥有的所有对象。
(2)ALL_ :由授权的用户访问, 用户可以访问的对象名。
(3)DBA_ :由授权DBA权限的人访问,显示数据库的所有对象。
(4)V$ :由授权DBA权限的人访问,显示数据字典数据库服务器性能信息。通常是DBA用于显示系统的统计表和动态性能表。
3、数据字典所有表含义
DICTIONARY数据字典提供用户可以访问的数据字典表和视图的描述。
USER_OBJECTS显示用户拥有的对象。
USER_VIEWS显示用户拥有的视图。
USER_TABLES显示用户拥有的表。
ALL_TABLES显示用户可以访问的表。
ALL_VIEWS显示用户可以访问的视图。
USER_CATALOG显示用户拥有的所有表,视图,同义词和序列。
USER_CONS_COLUMNS显示带约束的列。
DBA_CONS_COLUMNS显示数据库里的所有表,视图和同义词。
USER_TAB_PRIVS_MADE:本用户赋给别的用户赋予权限的表
USER_TAB_PRIVS_RECD:其他用户给本用户赋予权限的表
USER_COL_PRIVS_MADE:本用户赋给别的用户赋予权限的字段
USER_COL_PRIVS_RECD:其他用户给本用户赋予权限的字段
ROLE_SYS_PRIVS:有什么系统权限赋给role
ROLE_TAB_PRIVS:有什么关于表的权限赋给role
USER_ROLE_PRIVS:role和用户的对应表
(二)数据库启动常用命令
1、启动数据库实例:startup
2、查看当前所有的数据库
select name from v$database;
注意:v$database代表当前数据。可以使用desc查看存放数据库信息的表的结构,即desc v$database;
3、查看当前数据库连接用户:show user;
4、进入某个数据库:可以使用sqlplus软件命令或者connect命令。sqlplus 用户名/密码;或者connect 用户名/密码;
5、查看所有用户实例:
select * from v$instance;
或 select instance_name from v$instance;
6、查看当前库的所有数据表
select table_name from all_tables;(查询当前数据库中的所有表)
select table_name from user_tables;(查询当前用户可以访问的表)
select table_name from all_tables where owner='用户名';
(三)SID和Service_Name的区别
1、SID:一个数据库可以有多个实例(如RAC),SID是用来标识这个数据库内部每个实例的名字,就好像一个部门里,每个人都有一个自己的名字。
2、SERVICE_NAME:是这个数据库对外宣称的名字,外面的人要想连接我这个数据库,你就在客户端的连接串里写上service_name。
总结:如果在一台机器上创建了多个数据库,通过Sqlplus想连接到其中的一个数据库,就需要指明ORACLE_SID:set ORACLE_SID=SIDNAME;
注意:通常碰到的12560错误一般就是因为实例名被错误修改或者服务没有被启动。
3、配置文件参数
(1)在init.ora中有DB_NAME,INSTANCE_NAME,SERVICE_NAME
(2)配置DG的时候,为了区分主备库,还要使用DB_UNIQUE_NAME
(3)在listener.ora中有SID_NAME,GLOBAL_DBNAME
(4)在tnsname.ora中有SERVICE_NAME,SID,NET SERVICE NAME
4、数据库名称
(1)数据库的标识是由Db_name和Db_domain两个参数共同决定的,避免了因为数据库重名而造成管理上的混乱。这类似于互连网上的机器名的管理。
(2)我们将Db_name和Db_domain两个参数用’.’连接起来,表示一个数据库,并将该数据库的名称称为Global_name,即它扩展了Db_name。
注意:Db_name参数只能由字母、数字、’_’、’#’、’$’组成,而且最多8个字符。
(四)sys和system用户的区别
1、sys所有oracle的数据字典的基表和视图都存放在sys用户中,这些基表和视图对于oracle的运行是至关重要的,由数据库自己维护,任何用户都不能手动更改。sys用户拥有dba,sysdba,sysoper等角色或权限,是oracle权限最高的用户。
2、system用户用于存放次一级的内部数据,如oracle的一些特性或工具的管理信息。system用户拥有普通dba角色权限。
3、“SYSOPER”权限,即数据库操作员权限,权限包括:
(1) 打开数据库服务器 关闭数据库服务器
(2)备份数据库 恢复数据库
(3) 日志归档 会话限制
4、“SYSDBA”权限,即数据库管理员权限,权限包括:
(1) 打开数据库服务器 关闭数据库服务器
(2) 备份数据库 恢复数据库
(3)日志归档 会话限制
(4)管理功能 创建数据库
5、normal 、sysdba、 sysoper区别
(1)normal 是普通用户,只有通过被sys授权之后才可以对数据库进行操作
(2)sysdba拥有最高的系统权限
(3)sysoper主要用来启动、关闭数据库,sysoper 登陆后用户是 public
(4)sysdba登陆后是sys,而且只能是SYS登录sysdba.
6、sysdba和syoper权限区分:
系统权限 | sysdba | sysoper |
区别 | Startup(启动数据库) | startup |
Shutdown(关闭数据库) | shutdown | |
alter database open/mount/backup | alter database open/mount/backup | |
改变字符集 | none | |
create database(创建数据库) | None不能创建数据库 | |
drop database(删除数据库) | none | |
create spfile | create spfile | |
alter database archivelog(归档日志) | alter database archivelog | |
alter database recover(恢复数据库) | 只能完全恢复,不能执行不完全恢复 | |
拥有restricted session(会话限制)权限 | 拥有restricted session权限 | |
可以让用户作为sys用户连接 | 可以进行一些基本的操作,但不能查看用户数据 | |
登录之后用户是sys | 登录之后用户是public |
注意:system如果正常登录,它其实就是一个普通的dba用户,但是如果以as sysdba登录,其结果实际上它是作为sys用户登录的,这一点类似Linux里面的sudo的感觉,从登录信息里面我们可以看出来。因此在as sysdba连接数据库后,创建的对象实际上都是生成在sys中的。
(五)dba和sysdba的区别
1、oracle服务的创建过程:
(1)创建实例
(2)启动实例
(3)创建数据库(system表空间是必须的)
2、数据库启动过程
(1)实例启动
(2)装载数据库
(3)打开数据库
注意:sysdba是管理oracle实例的,它的存在不依赖于整个数据库完全启动。
3、只要实例启动了,sysdba就已经存在,可以用sysdba身份登陆,装载数据库、打开数据库;
4、dba只是一个角色,只有整个数据库完全启动之后即数据库打开之后,dba角色才存在。
(六)在查询用户角色表的时候,没有看到sysdba等角色,为什么?
1、dba是Oracle里的一种对象,Role 和User一样,是实实在在存在在Oracle里的物理对象;
2、sysdba是指的一种概念上的操作对象,在Oracle数据里并不存在。
总结:这两个概念是完全不同的。dba是一种role对应的是对Oracle实例里对象的操作权限的集合,而sysdba是概念上的role,是一种登录认证时的身份标识而已。
三、oracle权限管理:
(一)oracle权限管理首先要区分好权限和角色的概念。
1、权限:oracle提供两种权限:系统权限(sys privilege)和对象权限(实体权限)(object privilege);
(1)系统权限:系统规定用户使用数据库的权限,允许用户执行某些管理功能。(系统权限是对用户而言)。
(2)对象权限:允许用户对某对象进行操作。(是针对表或视图而言的)。其中,实体权限分类:select, update, insert, alter, index, delete, execute(执行存储过程),references(关联),all(以上所有对象权限) 。
注意:all包括以上所有权限,grant all on product to public; // public用户表示所有的用户,这里的all权限不包括drop。
2、系统权限
系统权限,就是oracle里已经写死的权限,这些权限,我们是不能自己去扩展的,比如select any table, create any table等,这里的权限已经在oracle里全部规定好了,我们可以通过查看system_privilege_map这个数据字典表来查看所有的oracle系统内置的权限,语法如下:select * from system_privilege_map;
注意:系统权限只能由DBA用户授出即具有dba角色的用户;系统权限收回的时候是不会级联收回的,即A授予B权限,B授予C权限,如果A收回B的权限,C的权限不受影响;系统权限可以跨用户回收,即A可以直接收回C用户的权限。
(1)查询用户拥有哪里权限:
SQL> select * from dba_role_privs; 查看用户拥有的角色
SQL> select * from dba_sys_privs; 查看用户拥有的权限
SQL> select * from role_sys_privs; 查看角色拥有的权限
3、对象权限
对象权限是指用户对数据库表操作的功能权限。
4、为方便管理系统权限和对象权限,可使用角色管控,角色:是具有名称的一组系统权限或者对象权限的集合;
(二)角色分类:
角色是指系统权限或者对象权限集合。通常给某个用户授予权限时如果没有角色存在的话,那么需要一条一条的操作,角色的存在就是使得授权变得很方便。
1、oracle提供了三种预定义标准角色,具体情况如下:
(1)DBA: 拥有全部特权,是系统最高权限,即拥有全部系统权限,包括无限制的空间限额和给其他用户授予各种权限的能力。
(2)RESOURCE:拥有Resource权限的用户只可以创建实体,不可以创建数据库结构。resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。
(3)CONNECT:拥有Connect权限的用户只可以登录Oracle,不可以创建实体,不可以创建数据库结构。仅能够连接到ORACLE数据库中,并在对其他用户的表有访问权限时,做SELECT、UPDATE、INSERTT等操作。
总结:对于普通用户:授予connect, resource权限;对于DBA管理用户:授予connect,resource, dba权限。
(三)Oracle创建删除命令
1、创建角色语法:create role 角色名;
注意:角色创建之后,没有任何权限,需要对其授予相关权限才可以操作。
2、删除角色语法:drop role 角色名;
注意:角色删除后,原来拥用该角色的用户就不再拥有该角色了,相应的权限也就没有了。
(四)授权grant命令用法:
3、授权给角色:grant 系统权限 to 角色名或者用户名;grant 对象权限 on 表名 to 角色名或者用户名;
注意:只有dba用户可以给角色授予系统权限,只有表的拥有者才可以将表的操作权授予其他用户。原因:oralce对权限管理比较严谨,普通用户之间也是默认不能互相访问的,需要互相授权;
注意:grant命令可以给多个用户批量授权:grant 系统权限 to 用户名1 [,用户名2]....。
4、把角色授予用户:grant 角色名 to 用户名;
5、查看该用户或者角色所拥有的权限:select privilege from role_sys_privs;
注意:role_sys_privs表存储了用户或者角色的权限信息。
6、创建带有口令的角色(在生效带有口令的角色时必须提供口令):create role 角色名 identified by 密码;
7、修改角色是否需要有口令:
(1)alter role role1 not identified;
(2)alter role role1 identified by password1;
8、查询当前用户拥有的角色:select * from session_roles;
注意:session_roles表中存储了用户拥有的角色信息。
9、修改指定用户,设置其默认角色
sql>alter user user1 default role role1;
sql>alter user user1 default role all except role1;
10、with admin option用法:
(1)只适用于系统权限,取消权限时,不会级联;对于对象权限不可以使用;
(2)被授予用户可以授予或者回收其该系统权限或者角色,用户不可以回收自己的权限和角色;
(3)被授予用户可以使用该权限和角色的with admin option选项;
(4)被授予用户可以删除或者更改被授予的角色(drop or alter);
11、with grant option用法:
(1)只限于使用对象权限,当回收权限时,级联回收。即A用户把此权限授予B,但管理员收回A的权限时,B的权限也会失效;
(2)管理员收回用with grant option授权的用户的对象权限时,权限会因传播而失效,如:grant select on 表名 to A with grant option;,A用户把此权限授予B,但管理员收回A的权限时,B的权限也会失效,但管理员不可以直接收回B的SELECT ON TABLE 权限。
(五)撤销权限命令revoke用法:
1、撤销权限命令语法:revoke 实体权限名|all on 表名 from 用户名|角色名|public;或者 revoke 系统权限 from 用户名;
例如:REVOKE CONNECT, RESOURCE FROM 用户名;
注意:取消权限时,是级联的。
四、Oracle 用户管理
每个表都是属于指定用户的,假如要操作其他用户的表,必须先授予对应的权限;当访问其他用户的表时,需要加上前缀用户名即username,例如wangwu.mytable;对其它用户的表进行插入,更新操作时,须要进行提交(commit),否则表的拥有者不能对表结构进行改动,这是数据库的锁机制;
注意:只有表的拥有者才可以把对该表的操作权限授予其他用户。
oracle内部有两个建好的用户:system和sys。用户可直接登录到system用户以创建其他用户,因为system具有创建别的用户的权限。
注意:DBA用户可以操作全体用户的任意基表(无需授权,包括删除)
(一)oracle安全策略文件Profile文件
1、Profiles是Oracle安全策略的一个组成部分,当Oracle建立数据库时,如果未创建profile文件,oracle会自动创建一个名称为Default的profile,将defalut文件分配给用户。
2、Profile文件主要规定了资源使用的限制和密码(也就是口令)使用的规则,Profile定义之后,可以做用到每个用户之上,对每个用户的安全活动进行限制。
3、创建语法:create profile 文件名 limit ;
4、给用户指定资源限制文件命令:alter user 用户名 profile 文件名;
5、文件中参数含义:
FAILED_LOGIN_ATTEMPTS 3 //指定锁定用户的登录失败次数
PASSWORD_LOCK_TIME 5 //指定用户被锁定天数
PASSWORD_LIFE_TIME 30 //指定口令可用天数
参考文档:https://www.cnblogs.com/GreenLeaves/p/6612806.html
(二)创建用户命令
1、在创建用户的过程中可以指定的用户属性有:(1)认证方式;(2)认证密码;(3)默认的永久表空间,临时表空间;(4)表空间配额;(5)用户账号状态(locked or unlocked);(6)密码状态(expired or not)。其中expired表示过期。
2、创建用户语法:
create user 用户名 identified by 口令[即密码]
[default tablespace 表空间名] //指定默认表空间
[quota 10m on 表空间名] //指定表空间的大小
[temporary tablespace 临时表空间名] ; //指定临时表空间,注意,临时表空间不指定大小。
注意:创建用户需要先创建表空间,然后创建用户指定表空间。或者创建用户时,直接指定。
create user 用户名 identified by 口令[即密码] default tablespace 表空间名;
3、注意事项:
(1)对temporary tablespace不能指定配额。
(2)如果没有为用户指定默认表空间,将使用system表空间,强烈建议指定默认表空间。
(3)默认表空间不能是undo tablespace或者temporary tablespace。
(4)如果没有为用户指定默认表空间,临时表空间,用户将使用system表空作为默认表空间与临时表空,强烈避免出现此种状况。
4、修改用户语法: alter user 用户名 identified by 口令[改变的口令];
5、实例如下:
Create user user1 identified by password1 // 如果密码是数字,请用双引号括起来
default tablespace tablespace1 quota 50m on account
temporary tablespace temp_tablespace1;
6.ORACLE用户权限管理笔记整理
oracle系统存储管理用户、角色和权限的表信息:可使用以下表查询相关信息。
DBA_USERS 提供用户的信息
DBA_ROLES 显示数据库中所有角色
DBA_COL_PRIVS 显示列级对象授权
DBA_ROLE_PRIVS 显示用户及其角色
DBA_SYS_PRIVS 显示被授予系统权限的用户
DBA_TAB_PRIVS 显示用户及他们在表上的权限
ROLE_ROLE_PRIVS 显示授予角色的角色
ROLE_SYS_PRIVS 显示授予角色的系统权限
ROLE_TAB_PRIVS 显示授予角色的表权限
SESSION_PRIVS 显示允许用户使用的权限
SESSION_ROLES 显示当前允许用户使用的角色
select username from dba_users; 查询系统所有用户
select * from dba_users where username = 'XXXX';查询用户相关信息
select * from dba_roles; 查询系统所有角色
(1)DBA_ROLE_PRIVS:存储角色的权限信息。
select * from dba_role_privs wheregrantee ='SYS';
select * fromdba_role_privs where granted_role ='RESOURCE';
(2)DBA_SYS_PRIVS:存储系统管理员的权限信息。
select grantee,privilege from dba_sys_privs where grantee='XXXX';
(3)ROLE_SYS_PRIVS:存储授予角色的系统权限信息。
select* from role_sys_privs where role = 'RESOURCE';查询RESOURCE角色相关的权限
select * from session_privs;查询用户本身拥有的权限
select * from user_role_privs;查询用户本身拥有的角色
select * from dba_sys_privs where grantee='CONNECT'; 查询角色所拥有的权限
select * from role_sys_privs where role='CONNECT';查询角色所拥有的权限
查询用户拥有哪里权限:
SQL> select * from dba_role_privs;
SQL> select * from dba_sys_privs;
SQL> select * from role_sys_privs;
总结:
--查看用户系统权限
select * from dba_sys_privs;
select * from session_privs;
select * from user_sys_privs;
--查看用户对象权限
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
(三)修改用户:
SQL> Alter User 用户名 Identified by 口令
Default Tablespace tablespace1 Quota 表空间大小 on tablespace1
Temporary Tablespace tablespace
Profile profile_name;
1、修改口令字:
SQL>Alter user acc01 identified by "12345"; //注意口令为数字是要用双引号括起来
2、修改用户缺省表空间:
SQL> Alter user acc01 default tablespace 表空间名;
3、修改用户临时表空间
SQL> Alter user acc01 temporary tablespace 临时表空间名;
4、强制用户修改口令字:
SQL> Alter user acc01 password expire; //expire表过期
5、将用户加锁
SQL> Alter user acc01 account lock; // 加锁
SQL> Alter user acc01 account unlock; // 解锁
(四)删除用户
SQL>drop user 用户名; //用户没有建任何实体
SQL> drop user 用户名 CASCADE; // 将用户及其所建实体全部删除
注意: 当前正连接的用户不得删除。
(五)监视用户:
1、查询用户会话信息:
SQL> select username, sid, serial#, machine from v$session;
2、删除用户会话信息:
SQL> Alter system kill session 'sid, serial#';
3、查询用户SQL语句:
SQL> select user_name, sql_text from v$open_cursor;
五、sqlplus 登录数据库过程详解
(一)以sqlplus / as sysdba方式登录时,采用的是操作系统验证的方式,所以用户名/密码输与不输入是一样的?真的是这样的吗?
Oracle的用户信息一般来说是保存在数据字典里的,所以常规用户在Oracle数据库没有启动的时候是无法登陆的。但有两类用户例外,这就是具有sysdba或者sysoper权限的用户。Oracle sysdba或者sysoper用户的登陆有两种方式:一是通过OS认证,二是通过密码文件验证。
(二)究竟使用哪一种验证方式以及能否成功登陆取决于三个方面的因素:
1. sqlnet.ora中SQLNET.AUTHENTICATION_SERVICES的设置
2. 参数文件中REMOTE_LOGIN_PASSWORDFILE的设置
3. 密码文件 PWD%sid%.ora
(三)Oracle进行权限验证的大致顺序如下:
1. 根据SQLNET.AUTHENTICATION_SERVICES的值决定是进行os验证还是密码文件验证。
2. 如果是os验证,根据当前用户的用户组判断是否具有sysdba权限。如果os验证失败,则进行密码文件验证。
2. 如果是密码文件验证,REMOTE_LOGIN_PASSWORDFILE的值以及密码文件是否存在决定了验证是否成功。
sqlnet.ora
windows中sqlnet.ora文件为空时采用Oracle密码文件验证,Linux相反!
SQLNET.AUTHENTICATION_SERVICES= (NTS) 基于操作系统验证;
SQLNET.AUTHENTICATION_SERVICES= (NONE) 基于Oracle密码文件验证
SQLNET.AUTHENTICATION_SERVICES= (NONE,NTS) 二者并存,注意是半角,否则不识别(windows)
默认情况下Unix/Linux下的sqlnet.ora文件是没有SQLNET.AUTHENTICATION_SERVICES参数的,
此时是操作系统验证和Oracle密码验证并存,加上SQLNET.AUTHENTICATION_SERVICES这个参
数后,不管SQLNET.AUTHENTICATION_SERVICES设置为NONE还是NTS还是(NONE,NTS),都是
基于Oracle密码验证。
设定sqlnet.authentication_services:
none:作用是不允许通过os系统用户登录数据库,需要提供用户名及密码;
all:作用是允许所有的登录方式;
nts:作用是windows的本地操作系统用户认证;
注:需要说明的是据试验该用户名和密码是指具有sysdba权限的用户;在linux上若用系统用户oracle登录数据库需要设定为all或是注销该字段;
注:linux上默认是没有该文件的,可以手动创建,参考$ORACLE_HOME/network/admin/samples/sqlnet.ora内容,并将之设定在$ORACLE_HOME/network/admin/目录下。
如果是密码文件验证的话,需要确认密码文件是否存在!即:
$ORACLE_HOME/dbs/下有没有orapw$ORACLE_SID的文件
密码文件是可以通过参数REMOTE_LOGIN_PASSWORDFILE开启(EXCLUSIVE或者SHARED)或者是禁用(none)的
show parameter xxxxxxxxx--查看
alter system set remote_login_passwordfile = none scope=spfile;--修改
--查看具有sysdba或者sysoper权限的用户
select *
from v$pwfile_users;
(一)oracle的操作系统认证登录方式即OS认证登录
有一种oracle的登录方式是操作系统验证登录方式,即常说的OS验证登录方式,在SQL server中也有这种方式。
下面以常见的windows操作系统来说明看一下这个操作系统认证方式登录的原理。如果你的机器可以使用connect / as sysdba获取sysdba的权限,那么下面的每一个过程你的机器上都会得到验证,如果不能,按照下面的操作更改后,你也能以这种方式登录。
- 在命令行下敲入compmgmt.msc 进入计算机管理
- 选择本地用户和组—>组
- 看是不是有一个组的名字叫做ORA_DBA
- 双击改组可以看到里面是不是有administrator用户
- 想一想你是不是以administrator用户登录的呢?
- 再进入Oracle安装目录(即$ORACLE_HOME 一般是D:"oracle)"ora92"network"admin 找到sqlnet.ora文件看看里面的是不是有SQLNET.AUTHENTICATION_SERVICES= (NTS)
- 如果这些都对的话,你就能已操作系统认证的方式(connect / as sysdba)来登录Oracle
接下来的问题是,如果你的数据很重要,出于安全考虑,希望禁止这种操作系统认证的方式。那么该怎么做呢?
1、禁用操作系统认证登录:
找到在刚才的第6步骤中的sqlnet.ora文件,将SQLNET.AUTHENTICATION_SERVICES= (NTS)改为SQLNET.AUTHENTICATION_SERVICES=none即可。
六、sqlplus连接时的三种方式
(一)SQLPlus 在连接时通常有三种方式
1. sqlplus / as sysdba
操作系统认证,不需要数据库服务器启动listener,也不需要数据库服务器处于可用状态。比如我们想要启动数据库就可以用这种方式进入
sqlplus,然后通过startup命令来启动。
2. sqlplus username/password
连接本机数据库,不需要数据库服务器的listener进程,但是由于需要用户名密码的认证,因此需要数据库服务器处于可用状态才行。
3. sqlplus usernaem/password@orcl
通过网络连接,这是需要数据库服务器的listener处于监听状态。此时建立一个连接的大致步骤如下
a. 查询sqlnet.ora,看看名称的解析方式,默认是TNSNAME
b. 查询tnsnames.ora文件,从里边找orcl的记录,并且找到数据库服务器的主机名或者IP,端口和service_name
c. 如果服务器listener进程没有问题的话,建立与listener进程的连接。
d. 根据不同的服务器模式如专用服务器模式或者共享服务器模式,listener采取接下去的动作。默认是专用服务器模式,没有问题的话客户端
就连接上了数据库的server process。
e. 这时连接已经建立,可以操作数据库了。