用户：使用SCOTT用户登陆数据库，用户就是SCOTT；
角色：SCOTT以什么身份登陆(Normal、 DBA、 SYSOPER)，就是什么角色；
在SCOTT 用户下创建一张表T1，那么SCOTT 就是模式（因为表T1属于关系模式）对象，模式是数据库逻辑呈现方式；
用户与模式是一对一关系，可简单理解为用户就是模式。创建用户后会有对应的一套模式，模式下面去管理对象。
登陆 sys 查看数据库有多少用户？
select username from dba_users;
每个用户都会有自己的指定默认表空间，在创建任何数据库对象时（表、视图、索引等）数据都会保存在此表空间中，如果在创建用户的时候没有指定具体所属表空间，那么默认属于数据库级默认永久表空间（USERS),默认临时表空间(temp)。
col PROPERTY_NAME for a30 col PROPERTY_VALUE for a30 select property_name,PROPERTY_VALUE from database_properties where property_name like 'DEFAULT%TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------ DEFAULT_PERMANENT_TABLESPACE USERS DEFAULT_TEMP_TABLESPACE TEMP --默认undo SHOW PARAMETER UNDO_TABLESPACE NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ undo_tablespace string UNDOTBS1
SQL> select property_name,PROPERTY_VALUE from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------ DEFAULT_TEMP_TABLESPACE TEMP
提问1：如何证明创建用户时不指定表空间，这个用户就会使用默认 USERS 表空间？
1）创建用户 aaa 且不指定默认表空间
SQL> create user aaa identified by aaa;
2）查看 AAA 用户所属的表空间
set linesize 1000
set pages 1000
col username for a30
select username,default_tablespace,TEMPORARY_TABLESPACE from dba_users where username='AAA';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
AAA USERS TEMP
提问2：如何为用户指定表空间？
1) 创建一个新的表空间 TEST，大小10M
SQL> create tablespace test datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/test1.dbf'size 10m;
2)创建用户并指定默认表空间 TEST
create user bbb identified by bbb default tablespace test;
3) 查看 BBB 用户所属表空间
SQL> select username,default_tablespace,TEMPORARY_TABLESPACE from dba_users where username in('AAA','BBB');
4) 更改数据库默认表空间
SQL> alter database default tablespace test;
5) 更改用户默认表空间
SQL> alter user bbb default tablespace test;
6) 更改用户默认临时表空间
SQL>alter user app1 temporary tablespace temp_app1; 表空间配额(QUOTA)是表空间为用户的对应所使用的空间量(容量)，在建立用户时，需要考虑到用户表空间配额的限制。因为多个用户可以同时使用同一个表空间，如果不做任何限制，无限制的表空间配额可能会导致表空间不足甚至撑爆。
示例：表空间配额分配与回收
1）给用户aaa在表空间users上配额1M的空间 SQL> alter user aaa quota 1m on users; 2）给用户aaa在表空间users上配额无限空间 SQL> alter user aaa quota unlimited on users; 3）收回用户aaa在表空间users配额限制 SQL> alter user aaa quota 0 on users; 提问：表空间配额被回收之后，还能插入数据吗？
注意：要创建表，用户首先要有create session和create table权限，而且要有在该表使用的表空间上的配额。临时表空间和 UNDO 表空间不能针对用户设置配额。
1） 查看某用户是否分配磁盘配额
select tablespace_name,username,bytes/1024 as M from dba_ts_quotas where username='AAA';
**********************
实验：测试配额分配的场景
**********************
1）创建用户、授权、设置默认表空间 SQL> create user t1 identified by t1; User created. SQL> grant connect,resource to t1; Grant succeeded. SQL> alter user t1 default tablespace test; User altered. SQL> select username,default_tablespace,TEMPORARY_TABLESPACE from dba_users where username ='T1'; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ T1 TEST TEMP 2）在默认表空间TEST上创建表 SQL> create table t1(id int); Table created. 提问：明明没有分配表空间配额，为什么没有报错？ 3）在表T1中插入数据 SQL> insert into t1 values(1); insert into t1 values(1) * ERROR at line 1: ORA-01950: no privileges on tablespace 'TEST' 提问：为什么报错？ 4）给T1用户在TEST表空间上分配1M空间 SQL> alter user t1 quota 1m on test; User altered. 5）再次插入数据 SQL> insert into t1 values(1); 1 row created. SQL> commit; Commit complete.
作用是对用户访问数据库做一些限制。有几个要点：
概要文件（profile)具有两个功能，一个是实施口令限制，另一个是限制会话可以占用的资源。
始终要实施口令控制，而对于资源限制，则只有实例参数 RESOURE_LIMIT 为 TRUE时（默认是FALSE)才会生效
系统自动使用概要文件，有一个默认的default profile,限制很松，作用较小。
可以使用 create profile 为用户创建它自己的概要文件，没有说明的参数就从 defaultprofile 的当前版本中提取
1）查看默认profile参数
1、查看默认的profile参数 select * from dba_profiles where profile='DEFAULT'; 2、查看用户默认的profile select username,PROFILE from dba_users where username='APP1';
2）介绍一下profile里的参数
Password_parameter 部分：
Failed_login_attempts：指定在帐户被锁定之前所允许尝试登陆的的最大次数。 Password_lock_time：在到达 Failed_login_attempts 后锁定账户的天数 Password_life_time：口令过期前的天数，口令在过期后仍可以使用，具体取决于Password_grace_time Password_grace_time：口令过期（将生成更改口令的提示）后第一次成功登录后的天数，在此期间，旧口令仍然可用。 Password_reuse_time：可以重新使用口令前的天数 password_reuse_max：可以重新使用口令的次数 Password_verify_function：更改口令时运行的函数名，此函数一般用于检查新口令所需的复杂程度。
Resource_parameter 部分
Session_per_user：对同一个用户账户执行的并发登录数。 Cpu_per_session：在强制终止会话前，允许会话的服务器进程使用的 CPU 时间（厘秒）。 Cpu_per_call： 在强制终止某条 SQL 语句前，允许会话的服务器进程用于执行此语句的 CPU 时间。 Connect_time： 在强制终止会话前，会话的最长持续时间（分钟）。 Idle_time： 在强制终止会话前，允许会话处于闲置状态的最长时间（分钟）。 Logical_reads_per_session： 在强制终止会话前，会话可读取的块数（无论块在数据缓冲区还是磁盘）。 Logical_read_per_call： 在强制终止单个语句前，此语句可读取的块数（无论块在数据缓冲区还是磁盘）。 Private_sga： 对于通过共享服务器体系结构连接的会话，允许会话的会话数据在SGA 中占用的字节数(考点）。 Composite_limit：前面几个参数的加权和。
3) profile的管理
1、创建profile create profile userapp limit Password_life_time 10; 2、修改profle alter profile userapp limit FAILED_LOGIN_ATTEMPTS 10; 3、设置用户profile alter user AAA profile userapp; 4、删除profile DROP PROFILE userapp; ###补充:profile正在被使用，无法被删除，需要添加cascade参数,用户的profile会被调整为default SYS@pdb2> DROP PROFILE userapp; DROP PROFILE userapp * ERROR at line 1: ORA-02382: profile USERAPP has users assigned, cannot drop without CASCADE ##cascade强制删除 SYS@pdb2> SYS@pdb2> DROP PROFILE userapp cascade; Profile dropped. SYS@pdb2> SYS@pdb2> select username,profile from dba_users where username='AAA'; USERNAME PROFILE -------------------- ----------------------------------------------------------------------------------------------------------- AAA DEFAULT
针对生产环境上配置的几个常用参数，实验验证
4）以Failed_login_attempts为例，期望用户在登录2次之后被琐
1）创建一个自定义概要文件 userapp SQL> create profile userapp limit Password_life_time 30; Profile created 2）查看 AAA 用户默认使用的 profile SQL> select username,profile from dba_users where username='AAA'; USERNAME PROFILE ------------------------------ ------------------------------ AAA DEFAULT 3）将概要文件 userapp 分配给 aaa 用户加以限制 SQL> alter user AAA profile userapp; User altered. 4）再次查看 AAA 用户默认使用的 profile SQL> select username,profile from dba_users where username='AAA'; USERNAME PROFILE ------------------------------ ------------------------------ AAA USERAPP 5）新开一个窗口，尝试用 aaa 用户故意输错密码登陆两次 SQL> conn aaa/2323@pdb ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn aaa/2323@pdb ERROR: ORA-01017: invalid username/password; logon denied SQL> conn aaa/2323@pdb ERROR: ORA-28000: The account is locked. 两次失败后，这时候 aaa 账户已被锁定，再次登陆的时候，即使输入正确密码也无法登陆。 SQL> conn aaa/aaa@pdb ERROR: ORA-28000: the account is locked 6）查看用户AAA的状态 SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; Session altered. SQL> select username,ACCOUNT_STATUS,LOCK_DATE from dba_users where username='AAA'; USERNAME ACCOUNT_STATUS LOCK_DATE ------------------------------ -------------------------------- ------------------- AAA LOCKED(TIMED) 2023-06-29 00:39:08 7）登陆 sys 给 aaa 解锁 SQL> conn / as sysdba Connected. SQL> alter user aaa account unlock; User altered. 8）删除概要文件 userapp SQL> drop profile userapp cascade; 9）删除后，用户 aaa 又会绑定到 default profile SQL> select username,profile from dba_users where username='AAA'; USERNAME PROFILE ------------------------------ ------------------------------ AAA DEFAULT
5）设置密码复杂度示例
1）创建profile create profile PROF_MYPROFILE limit SESSIONS_PER_USER unlimited --每个用户拥有的会话数不限。 PASSWORD_REUSE_TIME 60 --密码重用次数。 FAILED_LOGIN_ATTEMPTS 10 --失败可尝试登录次数10次。 PASSWORD_LIFE_TIME 180 --密码180天过期。 PASSWORD_GRACE_TIME 7 --最多可以延迟7天必须修改密码。 PASSWORD_REUSE_MAX 1 --密码只能重用一次。 PASSWORD_LOCK_TIME 1/48 --锁定时间30分钟。 IDLE_TIME 4320 --空闲时间。 PASSWORD_VERIFY_FUNCTION verify_function_11G; --指定密码复杂度函数：最少8位,包含大写,小写,数字,特殊符号。 2）修改用户profile SYS@pdb2> alter user apps profile PROF_MYPROFILE; User altered. 3）修改用户密码 SYS@pdb2> alter user apps identified by oracle; alter user apps identified by oracle * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20000: password length less than 8 bytes 4）修改正确的密码 SYS@pdb2> alter user apps identified by oracle1234; User altered. 5）取消密码复杂度 alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION null;
思考：如何创建一个监控用户，要求密码永不过期
系统权限是针对 DATABASE 的相关权限，系统权限通常由 DBA 授予
查看 dba 系统权限
SQL> select distinct privilege from dba_sys_privs;
典型的系统权限：
DROP USER
CREATE USER
BACKUP ANY TABLE
SELECT ANY TABLE
CREATE ANY TABLE
用户需要的系统权限
CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE
实验1：用户需要什么权限才能登录数据库
1）pdb下，sys用户创建一个用户ccc，不授予任何权限 SQL> create user ccc identified by ccc; User created. 2）ccc用户尝试登录 SQL> conn ccc/ccc@pdb; ERROR: ORA-01045: user CCC lacks CREATE SESSION privilege; logon denied -->登录失败 3）给ccc授予create session权限 SQL> grant create session to ccc; Grant succeeded. 4）ccc用户登录成功 SQL> conn ccc/ccc@pdb; Connected.
实验2：用户需要什么权限才能创建表
1）ccc用户尝试创建表 SQL> create table t1(id number); create table t1(id number) * ERROR at line 1: ORA-01031: insufficient privileges 2）sys用户授予create table权限 SQL> grant create table to ccc; Grant succeeded. 3）ccc用户创建表成功 SQL> create table t1(id number); Table created.
CCC查看下自己有哪些系统权限：
SQL> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION CREATE TABLE
这两个权限，就是SYS刚刚授予的
思考题：CREATE TABLE 与 CREATE ANY TABLE 区别？
SYS 用户授权 CREATE TABLE 给用户时，用户即可在自己的模式下创建表。
如果授权 CREATE ANY TABLE 给用户时，用户不仅可以在自己的模式下创建表，也可以在其他模式下创建表。
例如：授权 hr 用户 create any table 权限
SQL> grant create any table to hr;
在其他用户下去创建表
SQL> create table ccc.t2(name char(5));
对象权限是针对 SCHEMA(用户)对象的权限
对象权限有 8 种： ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES,SELECT, UPDATE
用法：grant 权限 on 对象 to 用户;
查看用户所拥有的对象权限
select * from dba_tab_privs t where t.grantee='PRIV_TEST_USER'
比如：test1 用户下的 t1表，只能 test1来查（除 sys）。如果其他用户要查询，需要 test1的授权。
1) CCC用户尝试查询test1用户下的t1表 SQL> select * from test1.t1; select * from test1.t1 * ERROR at line 1: ORA-00942: table or view does not exist 2）test1用户授予查询t1表权限给ccc SQL> grant select on t1 to ccc; Grant succeeded. 3) CCC用户查询test1用户下的t1表成功 SQL> select * from test1.t1; ID ---------- 1 4）test1用户从ccc回收查询t1表权限 SQL> revoke select on t1 from aaa;
关于select授权，扩展一个知识点
select on table和read on table区别 1）ccc用户可以加for update参数 SQL> select * from test1.t1 for update; ID ---------- 1 2）test1用户查询 SQL> select * from test1.t1 for update; 。。。hang住 3）确认此时有行琐 SQL> set line 200 pagesize 9999 SQL> col event for a50 SQL> Select inst_id,event,count(*) from gv$session where status='ACTIVE' and wait_class#<> 6 group by inst_id,event order by 1; INST_ID EVENT COUNT(*) ---------- -------------------------------------------------- ---------- 1 SQL*Net message to client 1 1 enq: TX - row lock contention 1 4）回收select权限，再授权read权限，发现无法for udpate --test1用户执行 SQL> revoke select on t1 from ccc; Revoke succeeded. SQL> grant read on t1 to ccc; Grant succeeded. --ccc用户执行 SQL> select * from test1.t1 for update; select * from test1.t1 for update * ERROR at line 1: ORA-01031: insufficient privileges SQL> select * from test1.t1; ID ---------- 1
甚至也可以针对到列进行授权：
SQL> grant select,update(salary) on employees to aaa;
回收的时候不能针对列收回，应该 update 整个收回。
角色权限的引入是为了简化权限的管理
Oracle 数据库中的预定义角色： CONNECT,RESOURCE,DBA 等
比如我们查看 AAA用户的己授予角色有哪些：
SQL> select * from session_privs; PRIVILEGE --------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE
其中 CREATE SESSION 是通过 CONNECT 角色授权过来的，其他是通过RESOURCE 角色授权过来的。
比如新建用户 DDD，并授予 CONNECT 和 RESOURCE 角色，来查看是否和 SCOTT用户所包含的系统权限一致。
其中， UNLIMITED TABLESPACE 这个选项是针对所有表空间都可以无限制使用。所以建议收回此权限。
1）查看某个角色拥有的权限
--查看角色的对象权限 select * from role_tab_privs t where t.role='CONNECT' ; --查看角色的系统权限 select * from role_sys_privs t where t.role='CONNECT' ; 思考题：能不能自己创建一个角色，然后授予connect,resource的权限？再将该角色授权给新用户？ 参考 SQL> create role r1; Role created. SQL> grant connect,resource to r1; Grant succeeded. SQL> grant r1 to ccc; Grant succeeded.
授权： GRANT 权限|角色 TO 用户
回收： REVOKE 权限|角色 FROM 用户
注意：可以回收角色，但是角色里面的系统权限是无法单独回收的，但里面有个特例，就是 UNLIMITED TABLESPACE，它可以单独收回处理，其他不能。
实验：用户角色中的权限能否单独回收
1) 测试从connect角色中回收CREATE SESSION权限
1）要看test1用户授权的权限 PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE SET CONTAINER 11 rows selected 2）查看用户授予的角色 SQL> SELECT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE ='TEST1'; GRANTEE GRANTED_ROLE ------------------------------ ------------------------------ TEST1 CONNECT TEST1 RESOURCE 3) 用SYS用户尝试回收test1的CREATE SESSION SQL> revoke CREATE SESSION from test1; --》回收失败 revoke CREATE SESSION from test1 * ERROR at line 1: ORA-01952: system privileges not granted to 'TEST1' --查看角色拥有的系统权限 select * from role_sys_privs t where t.role='CONNECT' ; --查看角色拥有的对象权限 select * from role_tab_privs t where t.role='CONNECT' ;
因为系统权限是打包成角色授权给 test1用户的，所以无法针对里面单一的系统权限进行回收。
2) 针对 UNLIMITED TABLESPACE 可以单独回收
SQL> revoke unlimited tablespace from test1;
Revoke succeeded.
语法：
with admin option 针对系统权限级联授权
提问：级联授权的系统权限，如果回收了主用户的权限，其它用户的权限还在吗？
1）创建三个测试帐号 SQL> create user x identified by x; User created. SQL> create user y identified by y; User created. SQL> create user z identified by z; User created. sys用户授权create session给 X 用户 SQL> grant create session to x with admin option; Grant succeeded. 登录x用户 SQL> conn x/x Connected. 授权create session给y和z SQL> grant create session to y; Grant succeeded. SQL> conn y/y Connected. 因为授权给 Y 用户没有带 WITH 选项，所以 Y 只能顾自己，无法授权给别人 SQL> grant create session to z; grant create session to z * ERROR at line 1: ORA-01031: insufficient privileges 如果授权 Y 用户带了 WITH 选项，那么 Y 也可以把权限传递别人 SQL> conn x/x@pdb Connected. SQL> grant create session to y with admin option; Grant succeeded. SQL> conn y/y@pdb Connected. SQL> grant create session to z with admin option; Grant succeeded. 现在回收 X 权限， X 将无法登陆，但不影响 Y 和 Z SQL> revoke create session from x; Revoke succeeded. SQL> conn x/x ERROR: ORA-01045: user X lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE. SQL> conn y/y Connected. SQL> conn z/z Grant succeeded.
结论：除了X不能登录，Y和Z还是可以登录的，也就是系统权限不会级联回收
语法：
with grant option 针对对象权限级联授权
1）pdb下sys用户执行 SYS@pdb> grant select on test1.t1 to x with grant option; Grant succeeded. 2）授权成功后， X 可查看 test1 的 t1 表 sys@pdb> conn x/x@pdb Connected. Session altered. X@pdb> select count(*) from test1.t1; COUNT(*) ---------- 1 3) 同样 X 也可以把查看权限授权其他人 Y X@pdb> grant select on test1.t1 to y; Grant succeeded. 4) 连接y用户可以查到 SYS@ORCLCDB> conn y/y@pdb Connected. Session altered. Y@pdb> select count(*) from test1.t1; COUNT(*) ---------- 1 5）通过sys用户回收test1.t1表查询权限 SYS@pdb> revoke select on test1.t1 from x; Revoke succeeded. 6）回收权限后， X 和 Y 将都无法查看 X@pdb> select count(*) from test1.t1; select count(*) from test1.t1 * ERROR at line 1: ORA-00942: table or view does not exist Y@pdb> select count(*) from test1.t1; select count(*) from test1.t1 * ERROR at line 1: ORA-00942: table or view does not exist
•从PUBLIC中撤销不必要的权限
•使用acl (access control lists)控制网络访问
•限制对操作系统目录的访问
•限制具有管理权限的用户
•限制远程数据库认证
•启用统一审计
为了使用DBMS_PRIVILEGE_CAPTURE包，您必须被授予CAPTURE_ADMIN角色。无论您试图监视什么，DBMS_PRIVILEGE_CAPTURE包的基本用法都是相同的。
创建特权分析策略。(CREATE_CAPTURE)
启用它。(ENABLE_CAPTURE)
等待所需的分析周期。
禁用特权分析策略。(DISABLE_CAPTURE)
分析结果。(GENERATE_RESULT和查询字典视图)
如果不再需要策略和记录的数据，请删除该策略。(DROP_CAPTURE)
这个用户比较特殊，对它授予的权限，其它用户都会自动拥有
实验：在不给用户u1授权的情况下，让u1实现登录功能
SYS@pdb> create user u1 identified by u1; User created. 2、用户u1登录失败，提示无权限 [oracle@database ~]$ sqlplus u1/u1@pdb SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 30 02:11:05 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied Enter user-name: 3、给public用户授权create session SYS@pdb> grant create session to public; Grant succeeded. --查看public用户拥有的系统权限 SYS@pdb> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='PUBLIC'; GRANTE PRIVILEGE ------ ---------------------------------------- PUBLIC CREATE SESSION 4)再次尝试登录u1,成功 [oracle@database ~]$ sqlplus u1/u1@pdb SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 30 02:13:31 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Session altered. U1@pdb>
泡杯长岛冰茶
9923
泡杯长岛冰茶
6938
云贝教育
4772
AskGuo
4693
1菩提行者1
4149
云贝教育
4148
19941464235
junle.chen@yunbee.net
QQ群咨询：1080888483
点击加载更多