Administering User Security
Objectives
After completing this lesson, you should be able to:
• Create and manage database user accounts:
– Authenticate users
– Assign default storage areas (tablespaces)
• Grant and revoke privileges
• Create and manage roles
• Create and manage profiles:
– Implement standard password security features
– Control resource usage by users
Database User Accounts
Each database user account has:
• A unique username
• An authentication method
• A default tablespace
• A temporary tablespace
• A user profile
• An initial consumer group
• An account status
A schema:
• Is a collection of database objects that are owned by a
database user
• Has the same name as the user account
Predefined Administrative Accounts
• SYS:超级用户DBA，可启动和关闭数据库
– Owns the data dictionary and the Automatic Workload Repository (AWR)
– Used for startup and shutdown of the database instance
• SYSTEM: Owns additional administrative tables and views 一般超级用户，不可启动和关闭数据库
• SYSBACKUP: Facilitates Oracle Recovery Manager (RMAN) backup and recovery operations
• SYSDG: Facilitates Oracle Data Guard operations
• SYSKM: Facilitates Transparent Data Encryption wallet operations
预定义的管理帐户
•系统：
–拥有数据字典和自动工作负载存储库（AWR）
–用于启动和关闭数据库实例
•系统：拥有额外的管理表和视图
•SYSBACKUP：促进Oracle Recovery Manager（RMAN）备份和恢复操作
•SYSDG：促进Oracle数据保护操作
•SYSKM：方便透明的数据加密钱包操作
Administrative Privileges
Privilege Description
SYSDBA Standard database operations, such as starting and shutting
down the database instance, creating the server parameter file
(SPFILE), and changing the ARCHIVELOG mode
Allows the grantee to view user data
SYSOPER（无法创建数据库） Standard database operations, such as starting and shutting
down the database instance, creating the server parameter file
(SPFILE), and changing the ARCHIVELOG mode
SYSBACKUP Oracle Recovery Manager (RMAN) backup and recovery
operations by using RMAN or SQL*Plus
SYSDG Data Guard operations by using the Data Guard Broker or the
DGMGRL command-line interface
SYSKM Manage Transparent Data Encryption wallet operations
Protecting Privileged Accounts
Privileged accounts can be protected by:
• Using a password file with case-sensitive passwords
• Enabling strong authentication for administrator roles
Authenticating Users
• Password: User definition includes a password that must be supplied when the user attempts to log in to the database
• External: Authentication by a method outside the database (operating system, Kerberos, or Radius)
• Global: Users are identified by using an LDAP-based directory service
Administrator Authentication
Operating system security:
• DBAs must have the OS privileges to create and delete files.
• Typical database users should not have the OS privileges to create or delete database files.
Administrator security:
• For SYSDBA and SYSOPER connections:
– DBA user by name is audited for password file and strong authentication methods.
– OS account name is audited for OS authentication.
– OS authentication takes precedence over password file authentication for privileged users.
– Password file uses case-sensitive passwords.
[oracle@yf admin]$ sqlplus sys as sysdba 通过数据库认证密码登陆
[oracle@yf admin]$ sqlplus / as sysdba 通过linux操作系统登陆
Managing创建 Users
SQL> drop user john cascade; 删除john用户
Privileges
There are two types of user privileges:
• System: Enables users to perform执行 particular特定 actions in the database
• Object: Enables users to access and manipulate操纵 a specific具体的 object
特权
有两种类型的用户权限：
•系统：允许用户在数据库中执行特定操作
•对象：允许用户访问和操作特定对象
[oracle@yf admin]$ sqlplus johe/123
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 12 15:47:48 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
ERROR:
ORA-01017: ???/????; ?????
系统授权是动态生效的，无需重新登陆
Enter user-name: john/123
???:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> create table t1 (col1 number); 创建t1表 设置为 number类型
with ADMIN OPTION 用于系统权限授权 此时级联授权可生效，级联回收不生效
revoke create table from joe;
授予对象权限
对象名一般是大写的
alter
debug
delete
flashback
index
insert
on commit refresh
query rewrite
read
references
select
under
update
with grant option 级联授权（被授权者可将自身权限授权给别人）
对象授权中级联性回收是生效的
with admin option 和 with grant option 的区别 (转)
1、with admin option 用于系统权限授权，with grant option 用于对象授权。
2、给一个用户授予系统权限带上with admin option 时，此用户可把此系统权限授予其他用户或角色，但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效，如授予A系统权限create session with admin option,然后A又把create session权限授予B,但管理员收回A的create session权限时，B依然拥有create session的权限，但管理员可以显式收回B create session的权限，即直接revoke create session from B.
而with grant option用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用with grant option授权的用户对象权限时，权限会因传播而失效，如：grant select on 表名 to A with grant option;，A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。
执行授权语句报错（ora-01031，ora-01929）时，可参考一下。
相同点：
- 两个都可以既可以赋予user 权限时使用，也可以在赋予role 时用
GRANT CREATE SESSION TO emi WITH ADMIN OPTION;
GRANT CREATE SESSION TO role WITH ADMIN OPTION;
GRANT role1 to role2 WITH ADMIN OPTION;
GRANT select ON customers1 TO bob WITH GRANT OPTION;
GRANT select ON customers1 TO hr_manager(role) WITH GRANT OPTION;
- 两个受赋予者，都可以把权限或者role 再赋予other users
- 两个option 都可以对DBA 和APP ADMIN 管理带来方便性，但同时，都带来不安全的因素
不同点：
- with admin option 只能在赋予 system privilege 的时使用
- with grant option 只能在赋予 object privilege 的时使用
- 撤消带有admin option 的system privileges 时，连带的权限将保留
例如：
1. DBA 给了CREATE TABLE 系统权限给JEFF WITH ADMIN OPTION
2. JEFF CREATES TABLE
3. JEFF grants the CREATE TABLE 系统权限给EMI
4. EMI CREATES A table
5. DBA 撤消CREATE TABLE 系统权限从JEFF
结果：
JEFF‘S TABLE 依然存在，但不能创建新的TABLE 了
EMI’S TABLE 依然存在，他还保留着CREATE TABLE 系统权限。
- 撤消带有grant option 的object privileges 时，连带的权限也将撤消
例如：
1. JEFF 给了SELECT object privileges 在EMP 上 WITH ADMIN OPTION
2. JEFF 给了SELECT 权限在EMP 上 TO EMI
3. 后来，撤消JEFF的SELECT 权限
结果：
EMI 的权限也被撤消了
Using Roles to Manage Privileges
• Roles角色:
– Used to group together privileges and roles
– Facilitate granting of multiple privileges or roles to users
• Benefits of roles:
– Easier privilege权限 management
– Dynamic动态的 privilege management
– Selective选择性 availability可用性 of privileges
Predefined预置 Roles
Role Privileges Included
CONNECT CREATE SESSION
DBA Most system privileges; several other roles. Do not
grant to non-administrators.
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
SELECT_CATALOG_ROLE SELECT privileges on data dictionary objects
授权时可查看sql语句，在SQL>中粘贴完成
给用户授予角色（静态授权）
变更角色后需要退出再进
Secure Roles
SET ROLE vacationdba; 打开和关闭角色
• Roles can be nondefault and enabled when required.
• Roles can be protected through authentication.
• Roles can also be secured programmatically.
CREATE ROLE secure_application_role
IDENTIFIED USING <security_procedure_name>;
[oracle@yf admin]$ sqlplus sys as sysdba
SQL> create role secure_role identified by 123; 创建角色secure_role 口令123
在图形化界面中将secure_role授权给john
重新登陆john用户
SQL> grant select on hr.job_history to secure_role; 将查询权限授予grant给角色secure_role
SQL> set role secure_role identified by 123; 打开 secure_role角色
Profiles配置文件、概要文件 and Users
Users are assigned only
one profile at a time.
Profiles:
• Control resource
consumption
• Manage account
status and password
expiration
Note: RESOURCE_LIMIT must be set to TRUE before profiles can impose
resource limitations.
[oracle@yf admin]$ sqlplus sys as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 12 19:41:50 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter password:
???:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> show parameter resource 判断参数是否已经打开
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE 此处显示该参数已经打开
resource_manage_goldengate boolean FALSE
resource_manager_cpu_allocation integer 2
resource_manager_plan string
Supplied Password Verification Functions
• The following functions are created by the
$ORACLE_HOME/rdbms/admin/utlpwdmg.sql script:
– VERIFY_FUNCTION_11G
– ORA12C_VERIFY_FUNCTION
– ORA12C_STRONG_VERIFY_FUNCTION
• The functions require the following of passwords:
– Have a minimum number of characters
– Not be the username, username with a number, or username reversed
– Not be the database name or the database name with a number
– Have at least one alphabetic and one numeric character
– Differ from the previous password by at least three letters
Modifications to the Default Profile
The utlpwdmg.sql script also modifies the DEFAULT profile
as follows:
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
Assigning Quotas to Users
• Users who do not have the UNLIMITED TABLESPACE system privilege must be given a quota before they can create objects in a tablespace.
• Quotas can be:
– A specific value in megabytes or kilobytes
– Unlimited
为用户分配配额
•没有无限表空间系统特权的用户必须先获得配额，然后才能在表空间中创建对象。
•配额可以是：
–以兆字节或千字节为单位的特定值
–无限制
对空间授权后，新用户才能对表进行操作
SQL> insert into t1 values (123); 在t1表中增一行 值为 123
Applying the Principle of Least Privilege
• Protect the data dictionary:
O7_DICTIONARY_ACCESSIBILITY=FALSE
• Revoke unnecessary privileges from PUBLIC.
• Use access control lists (ACL) to control network access.
• Restrict the directories accessible by users.
• Limit users with administrative privileges.
• Restrict remote database authentication:
REMOTE_OS_AUTHENT=FALSE
Quiz
1：All passwords created in Oracle Database are not case-sensitive by default. 错的，11g后口令是大小写敏感的
2：A database role:
a. Can be enabled or disabled 对的，角色可被打开和关闭
b. Can consist of system and object privileges 对的，角色可以包含系统权限和对象权限
c. Is owned by its creator 错的，角色不属于任何人
d. Cannot be protected by a password 错的，角色可通过命令打开 SQL> set role secure_role identified by 123; 打开 secure_role角色 密码为123
3：With RESOURCE_LIMIT set at its default value of FALSE, profile password limitations are ignored被忽略. 错的，RESOURCE_LIMIT只对资源部分起限定作用，与口令无关
4：Applying应用 the principle原理 of least privilege is not enough to harden the Oracle database. 应用最小特权原则不足以强化Oracle数据库。对的
练习题
1.建立一个用户名jsmith，指定自己的口令
2.给jsmith用户授权可用查询hr.job_history表
3.建立一个新的profile, my_profile
资源的idle时间为1分钟，修改参数使这个功能生效
在my_profile中添加复杂口令保护，使用12c的口令验证函数
把my_profile与用户jsmith绑定，使用jsmith测试idle时间是否生效；然后修改jsmith用户口令，查查口令验证函数是否生效
SQL> desc ora12c_verify_function 校验函数
FUNCTION ora12c_verify_function RETURNS BOOLEAN
???? ?? ??/??????
------------------------------ ----------------------- ------ --------
USERNAME VARCHAR2 IN
PASSWORD VARCHAR2 IN
OLD_PASSWORD VARCHAR2 IN
SQL> alter user jsmith identified by 12345678; 修改 jsmith 的密码为12345678