Oracle notes (ii), security management

1. Create a user

Ii. user authorization

Iii. Role Mechanism

Iv. Configure templates and resource restrictions

Appendix

The role of database security is to control whether users can perform operations on the database and its objects. To connect to the Oracle database, you need to create a user account, which can grant different operation permissions as needed.

1. Create a userCreate/alter/drop user XXXX [help documentation]

Create user prod4
Identified by ssss
Default tablespace users
Temporary tablespace temp;

When a user is deleted, the mode object owned by the user is deleted. Drop user prod4 cascade;

Ii. user authorizationGrant/Revoke [help documentation]
Privilege is the right to execute specific types of SQL statements. If you do not have any permissions, new users cannot log on to the Oracle database.

1. System Permissions
System privileges allow users to perform certain database operations.

Grant connect, resource, DBA to prod4
[With admin option];

The connect, resource, and DBA roles encapsulate most of the system permissions;
The with admin option allows authorized users to grant the permission to other users. (Cascade authorization, a-> B, B-> C)

PS. For more system permissions, see the help document grant-Table 18-1 SYSTEM privileges.

2. Mode object permissions
Schema Object Privilege allows you to perform specific operations on a specific object. To grant object permissions, you must meet one of the following conditions:
● The User owns the specified object;
● You can also grant option (cascade authorization.

SYS:
Grant select on SYS. Test to prod
[With grant option];
Prod:
Select * From SYS. test;

PS. For more mode object permissions, see the help document grant-Table 18-3 object privileges available for special objects.

Iii. Role MechanismCreate/alter/drop role XXX [help documentation]
A role is similar to a user group in an operating system. It encapsulates Multiple permissions. Users can inherit permissions through roles to simplify permission management and control. We recommend that you use role-based authorization to control users.

Note:
● The role does not belong to any schema ). Therefore, users who create a role can be removed without affecting the role.
● A role can be granted to another role. However, a role cannot be granted to itself, nor can a closed loop be formed between roles. For example, if Role B has been granted Role A, role a cannot be granted Role B.

-- Create a role myrole and grant permissions to the role, and then grant the role to the user prod (inherit the role )--
Create role myrole;
Grant select on SYS. Test to myrole;
Grant select on SYS. test4 to myrole;
Grant myrole to prod;

Iv. Configure templates and resource restrictionsCreate/alter/drop profile xxx
Each user corresponds to a set of configuration templates (profiles), which describe the limits for users to use a variety of system resources, including:

● Number of concurrent sessions that the user can establish
● CPU processing time available for one Oracle call using user sessions and SQL statements
● The number of logical I/O (logical I/O) available when a user session and SQL statement call Oracle
● The maximum idle time of a user session (idle time)
● Connect time)
● Password restriction rules:
Account lock when multiple attempts to log on failed
Password expiration time (expiration period) and grace period (grace period)
Password reuse and complexity restrictions

Appendix
1. Schema: A logical set of objects owned by a user.

2. [switch to] Oracle to view User Permissions

In Oracle, data dictionary Views are divided into three categories, with different prefixes: User, all, and DBA. Many data dictionary views contain similar information.

● User _ *: information about the object owned by the user, that is, information about the object created by the user
● All _ *: Information about objects accessible to users, that is, information about the objects created by users plus the objects created by other users, but the information that the user has the right to access
● DBA _ *: Information about objects in the entire database

(Here * Can be tables, indexes, objects, users, etc)

1. View All Users:
Select * From dba_user;
Select * From all_users;
Select * From user_users;

2. view the user's system permissions:
Select * From dba_sys_privs;
Select * From all_sys_privs;
Select * From user_sys_privs;

3. View user object permissions:
Select * From dba_tab_privs;
Select * From all_tab_privs;
Select * From user_tab_privs;

4. view all roles:
Select * From dba_roles;

5. view the roles owned by the user:
Select * From dba_role_privs;
Select * From user_role_privs;

6. view the default tablespace of the current user
Select username, default_tablespace from user_users;

7. view the specific permissions of a role, such as grant connect, resource, create session, and create view to test;
View the permissions of a resource, and use select * From dba_sys_privs where grantee = 'resource ';

3. [transfer] object permissions

Mode object/permission	Alter	Delete	Execute	Index	Insert	Read	Reference	Select	Update
Directory						√
Function			√
Procedure			√
Package			√
DB object			√
Libary			√
Operation			√
Sequence	√
Table	√	√		√	√		√	√	√
Type			√
View		√			√			√	√

An object has more than one permission. special permissions can be granted or revoked. For example, table's all permissions include:
Select, insert, update, and delete, as well as index, alter, and reference.

4. [Switch] Oracle 9i predefined roles

Role name	Description
Connect	Database connection role, used to connect to the database, with the right to create clusters, database links, sequences, synonyms, tables and views, and modify sessions
DBA	The database administrator role has all system permissions created using the admin option. You can grant system permissions to other users or roles.
Delete_catalog_role	Deletes a directory role. You can delete or recreate a data dictionary.
Execute_catalog_role	Execute directory role to execute all system packages
Exp_full_database	Ability to use exportProgramPerform full and incremental export of the database
Imp_full_database	Ability to use the import program to execute full Database Import
Resource	You can create clusters, tables, sequences, and PL/SQL programming program objects, including processes, packages, and triggers.
Select_catalog_role	Query a data dictionary table or view

ReferenceArticle:
Oracle concepts Chinese version (10g R2) -- Chapter 20th, database security