创建用户：
create user +用户 default tablespace + 表空间名 identified + 验证方式
SQL> create user easthome profile default identified by oracle password expire default tablespace users temporary tablespace temp account unlock;
SQL> grant connect to easthome;
SQL> grant create session to easthome;
default tablespace：默认的表空间，用户默认使用的表空间
创建test表空间：
查找数据文件的路径：
SQL> select name from v$datafile
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 100M;
创建一个用户test，默认的表空间为test
SQL> create user test default tablespace test identified by oracle;
如果不指定default tablespace，会使用数据库的默认表空间：
数据库的默认表空间：
select property_name, property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
更改数据库的默认表空间：
select name from v$tablespace;
alter database default tablespace users;
如何更改口令
alter user scott identified by oracle;
alter user sys identified by oracle;
删除用户：
如果用户中没有数据，可以直接删除
SQL> drop user test;
如果有数据，使用cascade
SQL> grant create session,create table to test identified by oracle;
SQL> alter user test quota 10m on users;
SQL> conn test/oracle
SQL> create table t(id number);
SQL> insert into t values(1);
SQL> commit;
提交完成。
SQL> conn /as sysdba
SQL> drop user test;
SQL> drop user test cascade;
用户验证
sqlplus / as sysdba;
conn scott/tiger as sysdba;
show user;
操作系统验证
只有属于dba组的用户才能进行操作系统验证
新建用户easthome，让其属于dba组：
在root下执行：
useradd -g dba easthome
passwd easthome
cp /home/oracle/.bash_profile /home/easthome/.bash_profile
su - easthome
sqlplus / as sysdba
SQL> show user
关闭操作系统验证：
到此目录：/u01/app/oracle/product/11.2.0/db/network/admin
打开sqlnet.ora文件，更改如下内容:
SQLNET.AUTHENTICATION_SERVICES= (none)--把NTS改为none，关闭操作系统验证
sqlplus sys/oracle as sysdba;
口令验证
cd $ORACLE_HOME/dbs
rm -rf orapworcl
orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle
orapwd file=$ORACLE_HOME/dbs/orapworcl password=b force=y
select * from v$pwfile_users;
grant sysdba to scott;
sqlplus scott/tiger as sysdba;
普通用户的账号密码保存在数据字典中，因此只能当数据库打开时才能使用。
select username, password from dba_users;
alter user scott identified by tiger;
conn scott/tiger;
Linux账号的外部验证
打开操作系统验证
cd $ORACLE_HOME/network/admin/sqlnet.ora
删除SQLNET.AUTHENTICATION_SERVER=(none)
SQL> show parameter os_authent_prefix
SQL> create user ops$oracle default tablespace users identified externally;
ops$开头
存在oracle账号
su - oracle
sqlplus /
show user;
window账号外部验证：
1. 用户名有两部分组成：os_authent_prefix+操作系统用户
2. 验证方式必须是外部验证
SQL> create user ops$oracle identified externally
SQL> grant connect to ops$oracle;
SQL> exit
SQL> drop user ops$oracle cascade;
SQL> create user ops$zhaoming default tablespace users identified externally;
SQL> grant connect to ops$zhaoming;
[oracle@hndx ~]$ sqlplus /--不用用户名和密码
SQL> show user
USER is "OPS$ORACLE"
远程操作系统验证：
SQL> show parameter remote_os
SQL> alter system set remote_os_authent=true scope=spfile;
SQL> startup force
create user ops$admini identified externally;
SQL> alter system set remote_os_authent=false scope=spfile;
在客户端：
sqlplus /@orcl
授予和回收权限
系统权限
grant select any table to scott;
select grantee, privilege from dba_sys_privs where grantee = 'SCOTT' ;
desc user_tab_privs;
select grantee, owner, table_name, grantor, privilege from user_tab_privs where grantee = 'SYS';
对象权限
conn scott/tiger;
grant all on emp to hr;
conn hr/hr;
desc user_tab_privs;
select grantee,owner,table_name, grantor,privilege from user_tab_privs where table_name='EMP';
conn / as sysdba;
权限的级联回收
conn scott/tiger;
revoke all on emp from hr;
conn / as sysdba;
grant select any table to scott with admin option;
conn scott/tiger;
grant select any table to hr;
select count(*) from hr.employees;
conn hr/hr;
select count(*) from scott.emp;
conn / as sysdba;
revoke select any table from scott;
conn scott/tiger;
select count(*) from hr.employees;
conn hr/hr;
select count(*) from scott.emp;
系统权限不能被级联回收
conn / as sysdba;
revoke select any table from hr;
conn scott/tiger;
grant update on emp to hr with grant option;
conn hr/hr;
grant update on scott.emp to sh;
update scott.emp set sal=sal+100;
conn / as sysdba;
alter user sh account unlock identified by sh;
conn sh/sh;
update scott.emp set sal=sal+100;
conn scott/tiger;
revoke update on emp fro hr;
conn hr/hr;
grant update on scott.emp to sh;
conn sh/sh;
update scott.emp set sal=sal+100;
对象权限会被级联回收。
select table_name from dict where table_name like '%PRIVS%'
创建和管理角色
角色：是一组权限的集合，方便对权限的管理，可以实现权限的动态管理
查询数据库中的角色：
select role from dba_roles
创建角色
普通用户必须有create role的权限才能创建角色
conn /as sysdba
create role r1;
select role from dba_roles where role='R1';
查询角色中的系统权限：
SQL> select role,privilege,admin_option from role_sys_privs where role='R1';
查询角色中的对象权限：
SQL> select * from role_tab_privs where role='R1';
把系统权限授予角色r1：
SQL> grant select any dictionary to r1;
SQL> select role,privilege,admin_option from role_sys_privs where role='R1';
把对象权限授予角色r1：
SQL> grant delete on scott.emp to r1;
授权成功。
SQL> select * from role_tab_privs where role='R1';
将角色授予用户：
SQL> grant r1 to hr;--hr拥有r1角色中的所有的权限
SQL> conn hr/hr
SQL> delete scott.emp;
SQL> roll
SQL> select count(*) from dba_objects;
如何查询当前用户下有哪些角色？
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
查看resource中的系统权限：
SQL> select privilege from role_sys_privs where role='RESOURCE';
查看resource中的对象权限：
SQL> select role,owner,table_name,privilege from role_tab_privs where role='RESOURCE';
角色的转授：
SQL> conn /as sysdba
SQL> grant r1 to hr with admin option;
SQL> conn hr/hr
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
SQL> grant r1 to oe;
SQL> conn oe/oe
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
SQL> delete scott.emp;
SQL> roll
SQL> select count(*) from dba_objects;
角色级联回收：
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
SQL> conn oe/oe
SQL> select username,granted_role,admin_option,default_role from user_role_privs
SQL> delete scott.emp;
SQL> roll
SQL> select count(*) from dba_objects ;
从以上的实验可以看出角色不能被级联回收
默认角色：当连接用户时，默认启动的角色
SQL> conn /as sysdba
SQL> create user test default tablespace users identified by a;
SQL> create role r2;
SQL> grant select any dictionary to r2;
SQL> create role r3;
SQL> grant update on scott.emp to r3;
SQL> grant connect,r2,r3 to test;
SQL> conn test/a
SQL> select * from role_sys_privs where role='CONNECT';
SQL> select count(*) from dba_objects;
SQL> update scott.emp set sal=sal+100;
SQL> roll
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
SQL> conn /as sysdba
SQL> alter user test default role connect;
SQL> conn test/a
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
SQL> select count(*) from dba_objects; --error
SQL> update scott.emp set sal=sal+100; --error
SQL> conn /as sysdba
SQL> alter user test default role connect,r2;
SQL> conn test/a
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
SQL> select count(*) from dba_objects;
SQL> update scott.emp set sal=sal+100; --error
SQL> conn /as sysdba
SQL> alter user test default role connect,r2,r3;
SQL> conn test/a
SQL> select count(*) from dba_objects;
SQL> update scott.emp set sal=sal+100;
SQL> roll
回退已完成。
控制角色：
SQL> col role for a30
SQL> select * from session_roles;
SQL> set role connect,r2;
角色集
SQL> select * from session_roles;
SQL> select count(*) from dba_objects;
SQL> update scott.emp set sal=sal+100; --error
SQL> set role connect,r3;
SQL> roll
SQL> set role all;
SQL> select count(*) from dba_objects;
给角色指定密码：
SQL> drop role r2;
SQL> create role r2 identified by a;
SQL> grant select any dictionary to r2;
SQL> grant r2 to test;
SQL> conn test/a
SQL> set role r2; --error
SQL> set role r2 identified by a;
动态管理：
SQL> conn /as sysdba
SQL> grant r3 to oe;
SQL> conn oe/oe
SQL> update scott.emp set sal=sal+100;
SQL> roll
SQL> conn test/a
SQL> update scott.emp set sal=sal+100;
SQL> roll
SQL> conn /as sysdba
SQL> revoke update on scott.emp from r3;
SQL> conn test/a
SQL> update scott.emp set sal=sal+100; --error
SQL> conn oe/oe
SQL> update scott.emp set sal=sal+100; --error
SQL> conn /as sysdba
SQL> grant update on scott.emp to r3;
SQL> conn test/a
SQL> update scott.emp set sal=sal+100;
SQL> roll
SQL> conn oe/oe
SQL> update scott.emp set sal=sal+100;
SQL> roll
角色授予角色：
SQL> create role r1;
SQL> create role r2;
SQL> create role r3;
SQL> grant select any dictionary to r1;
SQL> grant update on scott.emp to r2;
SQL> grant r1 to r2;
SQL> grant r2 to hr;
SQL> conn hr/hr
SQL> select count(*) from dba_objects;
SQL> update scott.emp set sal=sal+100;
SQL> roll
SQL> conn /as sysdba
SQL> select role,granted_role from role_role_privs;
SQL> select role,granted_role from role_role_privs where role='R2';
SQL> grant r2 to r3;
SQL> select role,granted_role from role_role_privs where role='R2';
SQL> select role,granted_role from role_role_privs where role='R3';
SQL> grant r3 to r1; --error
r1->r2->r3->r1:不允许出现循环
一组权限的集合，可以使系统权限，对象权限或者两者混合。
conn / as sysdba;
desc dba_roles;
select role from dba_roles;
查看某角色有哪些权限
desc role_sys_privs
select privilege from role_sys_privs where role='CONNECT';
select privilege from role_tab_privs where role='CONNECT';
创建角色:
create role r1;
grant select any table to r1;
grant update on scott.emp to r1;
select privilege from role_sys_privs where role='R1';
select privilege from role_tab_privs where role='R1';
desc role_tab_privs;
select owner, table_name, privilege from role_tab_privs where role='R1';
将角色赋予用户
grant r1 to hr;
conn hr/hr;
select count(*) from scott.emp;
update scott.emp set sal=sal+100;
desc dict;
select table_name from dict where table like'%ROLE%';
当前用户有哪些角色
desc user_role_privs;
select granted_role,default_role from user_role_privs;
set role resource;
select * from session_roles;
set role all;
select * from session_roles;
预定义角色
CONNECT
CREATE SESSION
RESOURCE
CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE
SCHEDULER_ADMIN
CREATE ANY JOB, CREATE EXTERNAL JOB, CREATE JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM, MANAGE SCHEDULER
DBA
Most system privileges; several other roles. Do not grant to nonadministrators.
SELECT_CATALOG_ROLE
No system privileges; HS_ADMIN_ROLE and over 1,700 object privileges on the data dictionary
SQL> CREATE ROLE secure_application_role IDENTIFIED USING <security_procedure_name>;
VERIFY_FUNCTION_11G
路径为<oracle_home>/rdbms/admin/utlpwdmg.sql，可以确保密码
1.至少8个字符
2.不能与用户名，用户名+数字和用户名反转相同
3.不能与数据库名，数据库名+数字相同
4.必须包含字母和数字
5.必须有三个字符跟之前的密码不同
可以使用该脚本为模板创建个性化的密码验证
sysdba与sysoper的区别
使用sysdba登录数据库，用户是sys
[oracle@hndx ~]$ sqlplus / as sysdba
SQL> show user
使用sysoper登录数据库，用户是public
[root@hndx ~]# usermod -g oinstall -G dba,oper oracle
[root@hndx ~]# su - oracle
[oracle@hndx ~]$ sqlplus / as sysoper
SQL> show user
USER is "PUBLIC"
权利不同，都可以启动数据库，sysdba的权利比sysoper要大
SQL> show user
USER is "SYS"
SQL> shutdown immediate
SQL> startup
SQL> conn /as sysoper
Connected.
SQL> show user
USER is "PUBLIC"
SQL> shutdown immediate
SQL> startup
权限对比：
SYSDBA：
Perform STARTUP and SHUTDOWN operations
ALTER DATABASE: open, mount, back up, or change character set
CREATE DATABASE
DROP DATABASE
CREATE SPFILE
ALTER DATABASE ARCHIVELOG
ALTER DATABASE RECOVER
Includes the RESTRICTED SESSION privilege
Effectively, this system privilege allows a user to connect as user SYS.
SYSOPER
Perform STARTUP and SHUTDOWN operations
CREATE SPFILE
ALTER DATABASE OPEN/MOUNT/BACKUP
ALTER DATABASE ARCHIVELOG
ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)
Includes the RESTRICTED SESSION privilege
This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.
创建和管理概要文件(profiles)
概要文件：有两个作用：控制资源占用 和管理帐户状态和口令失效
查看数据中有哪些概要文件：
SQL> select distinct profile from dba_profiles;
对资源的限制：
conn / as sysdba;
SQL> desc dba_profiles
SQL> select resource_name, resource_type,limit from dba_profiles where profile='DEFAULT';
SQL> create profile p1 limit sessions_per_user 3;
SQL> alter user scott profile p1;
SQL> show parameter resource_limit;
SQL> alter system set resource_limit=true;
SQL> startup force
打开4个会话，第四个报错：
[oracle@hndx ~]$ sqlplus scott/tiger -- error
SQL> drop profile p1 cascade;
对口令的限制：
SQL> create profile p1 limit FAILED_LOGIN_ATTEMPTS 3;
SQL> alter user scott profile p1;
SQL> conn scott/a
SQL> conn scott/b
SQL> conn scott/c
SQL> conn scott/d
SQL> conn scott/tiger--输入正确的口令也不能进入，必须让dba解锁
SQL> conn /as sysdba
SQL> alter user scott account unlock;
SQL> drop profile p1 cascade;
口令验证函数：
VERIFY_FUNCTION_11G
通过脚本：$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
SQL> @?/rdbms/admin/utlpwdmg.sql
SQL> drop user hndx cascade;
SQL> create user hndx identified by a;
SQL> create user hndx identified by welcome;
SQL> create user hndx identified by database;
删除以上函数的限制：
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION null;
SQL> drop function verify_function;
Function dropped.
SQL> create user hndx identified by a;
User created.
password_verify_function null;
可以去掉验证
应用最少的权限
O7_DICTIONARY_ACCESSIBILITY=FALSE;
alter system set O7_DICTIONARY_ACCESSIBILITY=true;
startup force；
设置为true之后的影响
赋予select any给scott之后，scott可以访问数据字典。
conn sys/oracle; 不用sysdba也可以登录。
PUBLIC角色中不要给予不必要的权限
revoke select any table from scott;
grant update on scott.emp to public;
conn sh/sh;
update scott.emp set sal=sal+100;
create directory dir as '/u01/app/oracle/dir';
数据泵倒入导出时可以使用该目录。可以限制对目录的使用。
限制远程认证
REMOTE_OS_AUTHENT=FALSE
数据库用户账号
每一个数据库用户账号拥有
1.独立的用户名
2.特定的认证方法
3.默认的表空间
4.临时表空间
5.用户参数文件(profile)
6.初始消费者组
7.用户状态
Schema
是数据库用户拥有的一系列数据库对象的集合，与用户账号具有相同的名字
预定义管理账号
SYS账号
被授予DBA角色以及其他一些角色
拥有所有带ADMIN OPTION的权限
对于start,shutdown以及其他一些维护命令是必须的
拥有数据字典以及自动工作存储(AWR)
[oracle@stu1 ~]$ sqlplus / as sysdba
[grid@stu1 ~]$ sqlplus / as sysasm
SYSTEM
被授予DBA,MGMT_USER和AQ_ADMINISTRATOR_ROLE等角色
DBSNMP
被授予OEM_MONITOR角色
SYSMAN
被授予MGMT_USER, RESOURCE和SELECT_CATALOG_ROLE角色
这些账号不用于日常操作，后两者用于EM，均不具备SYSDBA权限。
创建用户
SQL> create user "mydba" profile "default" identified by ********
default tablespaces "users"
temporary tablespace "temp"
account unlock
SQL> grant "connect" to "mydba"
权限：分为系统权限和对象权限
系统权限
RESTRICTED SESSION：即使数据库处于RESTRICTED模式，也允许登录
SYSDBA和SYSOPER
SYSOPER可以执行基本操作，但是不能查看用户数据，两权限均具有如下权利
1.启动和关闭数据库
2.创建spfile
3.更改数据库为OPEN,MOUNT和BACKUP
4.更改数据库为归档模式(ARCHIVELOG)
5.更改数据库为恢复(RECOVER),仅为完全恢复，非完全恢复要求连接为SYSDBA
6.RESTRICTED SESSTION
SYSASM:允许启动，关闭和管理ASM实例
DROP ANY:允许删除其他用户拥有的对象
CREATE,MANAGE,DROP和ALTER TABLESPACE:允许管理管理表空间
CREATE LIBRARY:允许用户创建在PL/SQL中执行的外部代码，这些代码必须被命名为LIBRARY对象
CREATE ANY DIRECTORY:
处于安全考虑，代码所在的操作系统目录必须被链接到虚拟ORACLE目录对象。拥有该权限的用户可以潜在调用非安全的代码对象。
GRANT ANY OBJECT PRIVILEGE: 允许赋予非自己拥有的对象权限给其他用户
ALTER SYSTEM和ALTER DATABASE
SQL> GRANT <object_privilege> ON <object> TO <grantee clause> [WITH GRANT OPTION]
SQL> REVOKE <system_privilege> FROM <grantee clause>
备注：
使用最小权限原则并不足以保证数据库足够安全
