Oracle learning Summary 4

1. Create a user
Create user Tom
Identified by password
Default tablespace
Temporary tablespace temporary tablespace
Quota 20 m on tablespace
Password expire;
Quota indicates that only the disk space of a specified tablespace can be used.
Change its quota
Alter user Tom quota new value on tablespace.
2. Delete the user drop user Tom;

3. Profile
The profile is mainly used to restrict resources or passwords.
For example, create profile ABC limit
Sessions_per_user 8
CPU _ per_session 16800
Logical_reads_per_session 23688
Connect_time 268
Idele_time 38;

It indicates that each session can only open eight sessions, each session can use a maximum of 168 S of CPU time, and each session can read a maximum of 23688 data blocks.
The connection time is 268 minutes. The idle time of each session cannot exceed 38 minutes.

4. Password profile management
The typical number of times that failed_login_attempts can fail to log on
Passwrod_lock_time unlimited the account will be locked after the specified number of failed attempts to log on.
Passwrod_life_time password valid for several days
How many days after the password_reuse_time password is voided can be reused.

5. grant system permissions.
Create user Tom identified by password;
To transfer authorization, you must add the with admin option clause.
View permissions of a user
Select * From dba_sys_privs where grantee = 'Tom '(uppercase );

Revoke permissions
Revoke permission to be reclaimed from user;
View the information of authorized users and authorized users
Select * From user_col_privs_made;

6. Manage Roles
Create role sales identified by password;
Grant role Permissions
Grant create session, create table, create view to sales;
View role Permissions
Select * From role_sys_privs where role in ('sales ');
 
Modify default roles
Alter user Tom default role all;
Alter user Tom default role all roles t sales; (except sales)

The application of the default role.
For example, there are two roles: Sales and manager. If sales has the select permission, the manager has the update and delete permissions,
If you set the manager to a non-default role (alter user Tom default role all execept Manager)

Assume that the user Tom of the sales role has only the select permission,
Required for Tom to have the update and delete permissions
Set role Manager identified by password;
Activate the manager role

Activate or disable a role
First, view the user's system permissions.
Select * From session_privs;
Set role all roles t Manager (activate all roles except Manager)
Role Revocation
Revoke manager, sales from Tom;