ORACLE user, Profile and permissions

User Management

Create a user : The new user created has no permissions, and even the right to log on to the database does not have the right to specify the appropriate permissions.

Sql> Create User username
Identified by password
Default tablespace tablespace
Temporary tablespace tablespace
Profile profile
Quota integer/unlimited on tablespace;

Cases:
sql> Create User Acc01
identified by ACC01//If the password is a number, enclose it in double quotes
Default Tablespace Account
Temporary Tablespace Temp
Profile default
Quota 50m on account;

Sql> Grant Connect, resource to ACC01;

[*] Query user default table space, temporary tablespace
Sql> Select Username, Default_tablespace, temporary_tablespace from Dba_users;

[*] Query system resource file name:
Sql> select * from Dba_profiles;

Resource files are similar to tables and are saved in the database once they are created.
Sql> Select Username, profile, Default_tablespace, temporary_tablespace from Dba_users;
Sql> Create profile common limit
Failed_login_attempts 5
Idle_time 5;
sql> Alter user Acc01 profile Common;

Second, modify the user:

Sql> Alter User Username
Identified password
Default tablespace tablespace
Temporary tablespace tablespace
Profile profile
Quota integer/unlimited on tablespace;

1, modify password Word: Alter user ACC01 identified by "12345";
2, modify user default table space: Alter user acc01 default tablespace users;
3, modify user temporary table space: Alter user acc01 temporary tablespace temp_data;
4, force the user to modify the password Word: Alter user acc01 password expire;
5, add the user lock: Alter user acc01 account lock; Lock
Alter user acc01 account unlock; Unlock

Third, delete users
Sql>drop user username; User does not build any entities
Sql>drop user username CASCADE; Remove all users and their established entities
*1. Users currently connected are not allowed to delete.

Four, monitor the user:
1, query user session information: Select username, sid, serial#, machine from v$session;
2, delete user session information: Alter system kill Sessions ' SID, Serial# ';

3, query User SQL statements: Select User_name, sql_text from V$open_cursor;

User profile

First, Profile purpose:

The profile in Oracle systems can be used to limit the database resources that users can use, create a profile using the build Profile command, and use it to limit the usage of database resources, if the profile is assigned to a user, The database resources that the user is able to use are within the profile limit. The specific management contents are: CPU time, I/O use, IDLE time (idle), connect time (connection times), number of concurrent sessions, password mechanism, etc.

second, the conditions:

Create profile must have system permissions for creating profile. To specify a resource limit for the user, you must:

1. Use alter system dynamically or use initialization parameters Resource_limit to make resource restrictions effective. This change is not valid for password resources and password resources are always available.
Sql> Show Parameter Resource_limit
Sql> alter system set resource_limit=true;
2. Create a profile that defines a constraint on the database resource using creation profile.
3. Use the CREATE user or alter USER command to assign profile to the user.

Third, view the default profile of the system

1, through the Dba_profiles view of the system by default are what profile
After the database is created, there will only be a default profile named Default in the system, and after the user is created, the profile for each user will default to the default profile, if no special designation is made.
Sys@oral> SELECT distinct profile from Dba_profiles;

2. Full view of the limitations defined in this profile
Sys@oral> Col profile for A12
Sys@oral> Col resource_name for A25
Sys@oral> Col LIMIT for A15
Sys@oral> SELECT * from Dba_profiles ORDER by 1, 2;

3. Conclusion: The DEFAULT profile only limits the number of password errors (up to 10 times the wrong password). If you want to change the restrictions, there are two ways: the first approach is to personalize the default profile; The second method is to customize a new profile.

The syntax for creating profile is as follows:

CREATE Profile Profile
LIMIT {resource_parameters Restrictions on resources
| Password_parameters Restrictions on passwords
}... ;

<resource_parameters>
{{{sessions_per_user the number of concurrent sessions per user name
| Cpu_per_session the available CPU time per session, in units of 0.01 seconds
| Cpu_per_call the allowable CPU time for a SQL call (Parse, execute, and get)
| Connect_time Session connection time (minutes)
| Idle_time session idle time (minutes), exceeding will disconnect
| Logical_reads_per_session
| Logical_reads_per_call
| Composite_limit "Combination Play"
}
{integer | Unlimited | DEFAULT}
| Private_sga
{Integer [K | M] | Unlimited | DEFAULT}
}

< Password_parameters >
{{{{{failed_login_attempts number of trial errors before being locked
| Password_life_time password use days, default 180 days
| Password_reuse_time Password Reusable time interval (combined with Password_reuse_max)
| Password_reuse_max Password Maximum number of changes (combined with password_reuse_time)
| Password_lock_time number of times after trial and error, the number of days locked, default 1 days
| The number of days after the Password_grace_time password expires and the original password can be used
}
{expr | Unlimited | DEFAULT}
| Password_verify_function
{function | NULL | DEFAULT}
}

v. Part of the explanation:

Profile: The name of the configuration file. The Oracle Database forces resource restrictions in the following ways:
1. If the user exceeds the Connect_time or Idle_time session resource limit, the database rolls back the current transaction and ends the session. The user executes the command again, and the database returns an error
2. If the user attempts to perform an operation that exceeds other session resource limits, the database discards the operation, rolls back the current transaction, and returns an error immediately. After the user can commit or roll back the current transaction, the session must end.
Tip: You can divide a piece into segments, such as 1 hours (1/24 days) to limit time, and you can specify resource limits for the user, but the database does not enforce restrictions until the parameters are in effect.

Unlimited: Users assigned to this profile have no restrictions on the use of resources, unlimited means there is no restriction on parameters when using password parameters.
Default: Specifying default means ignoring some resource restrictions in profile, the default profile initial definition is not limited to resources and can be changed by Alter Profile command.

Resource_parameter part

Logical_reads_per_session: The number of data blocks that are allowed to be read per session, including all blocks of data read from memory and disk.
Logical_read_per_call: The maximum number of blocks that are allowed to read in SQL (parse, execute, and extract) calls at a time.
PRIVATE_SGA: Specifies the maximum space, in bytes, that a session can allocate in a shared pool (SGA). (This limit is valid only when using a shared server structure, where the private space in the SGA includes private SQL and pl/sql, but not shared SQL and Pl/sql).
Composite_limit: Specifies the total resource consumption of a session, expressed in service units units. Oracle databases calculate cpu_per_session,connect_time,logical_reads_per_session and PRIVATE-SGA total service in a profitable way units

Password_parameter section:

Password_life_time: Specifies the number of days that the same password is allowed to use. If the Password_grace_time parameter is specified at the same time, if the password is not changed within grace period, the password is invalidated and the connection database is denied. If the Password_grace_time parameter is not set, the default value unlimited raises a database warning, but allows the user to continue the connection.

Password_reuse_time and Password_reuse_max: These two parameters must be associated with each other, password_reuse_time specify the number of days before the password can be reused, and password_reuse_ MAX Specifies the number of times the password changed before the current password was reused. All two parameters must be set to an integer.
1. If an integer is specified for both parameters, the user cannot reuse the password until the password is changed Password_reuse_max the specified number of times after the password_reuse_time specified time.
For example: password_reuse_time=30,password_reuse_max=10, users can reuse the password after 30 days, requiring the password to be changed more than 10 times.
2. If one of these is specified as an integer and the other is unlimited, the user can never reuse a password.
3. If you specify a default value for the Default,oracle database that is defined in profile, by default, all parameters are set to unlimited, and if you do not change profile defaults, The database always defaults to unlimited for this value.
4. If all two parameters are set to unlimited, the database ignores them.

Password_grace_time: Specify the number of Grace days, the database warns the number of days before the login expires. If the database password is not modified in the middle of this, expiration will expire.
Password_verify_function: This field allows complex pl/sql password validation scripts to be passed as parameters to the Create profile statement. Oracle databases provide a default script, but you can create your own validation rules or use Third-party software validation. For a function name, specifies the name of the password validation rule, and specifying NULL means that the password validation feature is not used. If you specify an expression for the password parameter, the expression can be in any format except for the database standard quantum query.

try to create a hybrid profile (with resource limits and password restrictions):
　　
Sys@oral> CREATE profile Sec_profile LIMIT
2 Sessions_per_user Unlimited
3 Cpu_per_session Unlimited
4 Cpu_per_call 6000
5 Connect_time 60
6 Logical_reads_per_session DEFAULT
7 Logical_reads_per_call 6000
8 Composite_limit 6000000
9 Private_sga 66K
Ten failed_login_attempts 6
One Password_life_time 60
Password_reuse_time 60
Password_reuse_max 5
1/24 Password_lock_time
Password_grace_time 10
Password_verify_function verify_function
17/
CREATE Profile Sec_profile LIMIT
*
ERROR at line 1:
Ora-07443:function verify_function not found

Cause of error: The Verify_function function does not exist, and to create the function in advance, the Verify_function function is created by the Utlpwdmg.sql script, where the directory is $oracle_home/rdbms/admin/

To create a verify_function password complexity check function using the Utlpwdmg.sql script:
Sys@oral> @?/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.

In the script, the password complexity check rule for the Verify_function function is as follows:
1 Minimum password length of 4 characters
2 password can not be the same as the user name
3 The password must contain at least one character, one digit and one special character
4 password requires at least 3 characters and the previous password is not the same

Almost all parameters are used in the experiment, and the only thing that does not contain is Idle_time, which inherits the definition in Defalt
Make a step-by-step interpretation of the profile created above to deepen your understanding:
Line 2nd: Unlimited number of concurrent connection sessions for users
Line 3rd: No restrictions on the use of CPU time for each session connected to the user
Line 4th: The CPU time consumed by one call cannot exceed 60 seconds (no more than one minute)
Line 5th: No more than 60 minutes for each session connected to the user (no more than one hours)
Line 6th: The total amount of physical read and logical read blocks used in one session is consistent with the definition in default profile
Line 7th: No more than 6,000 blocks of physical read and logical read blocks used in one call
Line 8th: A session total resource consumption of no more than 6 million service units (services units)
Line 9th: One session cannot allocate more than 66K of space in the SGA
Line 10th: Allow 6 error attempts before the account is locked
Line 11th: Password expires after this lifecycle
Line 12th: Password reuse time 60 days
Line 13th: Passwords need to be changed 5 times before password reuse
Line 14th: The user will be locked out for 1 hours after the number of error attempts is exceeded
Line 15th: When the password expires, the original password can also be used for 10 days
Line 16th: Using the password complexity check function verify_function the password

Lines 12th and 13th, after two policies have been used, only 5 password modifications have been completed and more than 60 days later, before the password can be used again

Delete Profile is very simple, syntax: DROP profile profile [CASCADE];
If the profile you created is already authorized to a specific user, you need to cascade the corresponding limit for the cascading of the options, which will be limited by the system default profiles after the restriction is retracted.

Vii. When profile comes into effect

The restrictions on passwords in profile are always valid and unrestricted.
The resource constraints in profile are related to the setting of the Resource_limit parameter, valid when True, or False (default).

Viii. Assigning profiles to users:
sql> alter user Dinya profile Sec_profile;
sql> alter user Dinya profile default;
Sql> Select Username,profile from dba_users where USERNAME = ' Dinya ';

11g Oracle User password expiration issue

11g previous version, default user does not have password expiration limit, the default profile password expires in Oracle 11g is 180 days

Check: SELECT * from Dba_profiles where profile= ' DEFAULT ' and resource_name= ' password_life_time ';
Expired passwords can be alter user userxxx identified by xxx; Resolved, can be modified to the same as before

If you want to set a password, you can log in with the administrator and execute the following command:
ALTER profile DEFAULT LIMIT Password_life_time Unlimited;

Other related parameters:
SELECT * from Dba_profiles where profile= ' DEFAULT ' and resource_name like ' password% ';

User Rights

I. Classification of authority:
System permissions: The system requires users to use the database permissions. (System permissions are for users).
Entity permissions: The access rights of a user to a table or view of another user. (For a table or view).

second, the System Authority management:

1. Classification of System privileges:
DBA: With full privileges, the system has the highest privileges, and only DBAs can create the database structure.
RESOURCE: Users with RESOURCE permissions can create entities only and cannot create database structures.
Connect: Users who have connect permissions can only log on to Oracle and cannot create entities and cannot create database structures.

For ordinary users: Grant Connect, resource permissions.
For DBA administration User: Grant Connect,resource, dba authority.

2, the System Authority authorization order:
[System permissions can only be granted by DBA User: sys, system (only two users in the beginning)]
Authorization Command:sql> Grant Connect, resource, dba to User name 1 [, username 2] ...;
[Normal users can have the same user rights as the system through authorization, but they will never reach the same permissions as the SYS user, and the system user's permissions can be reclaimed. ]

Cases:
Sql> Connect System/manager
sql> Create User User50 identified by USER50;
Sql> Grant Connect, resource to User50;

Query where the user has permissions:
Sql> select * from Dba_role_privs; --where grantee= ' SYS ';
Sql> select * from Dba_sys_privs;
Sql> select * from Role_sys_privs; --where role= ' DBA ';

Delete user:sql> drop user username cascade; Plus cascade deletes the user, along with what it creates.

3, System Authority Transfer:
The WITH ADMIN option option is added, the resulting permission can be passed.
sql> Grant Connect, Resorce to user50 with admin option; Can pass the granted permission.

4, System rights Recycling: System permissions can only be reclaimed by DBA user
Command:sql> Revoke Connect, resource from User50;

System permissions are not cascaded, that is, a grant B permissions, b grant C permissions, if a to recover the rights of B, C's permissions are not affected;
System permissions can be reclaimed across users, that is, a can reclaim the rights of C users directly.

Third, entity Rights Management

1. Entity Permissions Categories: Select, UPDATE, INSERT, ALTER, INDEX, delete, all//all including all permissions
Execute//execute Stored procedure permissions
User01:
Sql> Grant SELECT, UPDATE, insert on product to User02;
Sql> grant all in product to User02;

User02:
Sql> select * from User01.product;
At this time user02 check user_tables, does not include user01.product this table, but if check all_tables can be found, because he can visit.

3. Grant the operation rights of the table to all users:
Sql> grant all in product to public; Public represents all users, and the all permission here does not include drop.

[Entity Rights data Dictionary]:
Sql> Select owner, table_name from All_tables; Tables that users can query
Sql> Select table_name from User_tables; User-Created Tables
Sql> Select Grantor, TABLE_SCHEMA, table_name, privilege from All_tab_privs; A table (authorized) that is entitled to access.
Sql> Select grantee, owner, TABLE_NAME, privilege from User_tab_privs; Table granting permissions (permissions granted)

4. DBA users can operate any base table (without authorization, including deletion) for all users:
DBA User:
sql> Create Table Stud02.product (
ID Number (10),
Name VARCHAR2 (20));
sql> drop table stud02.emp;
Sql> CREATE TABLE Stud02.employee
As
SELECT * from Scott.emp;

5. Entity permission transfer (with GRANT OPTION):
User01:
Sql> Grant SELECT, update on product to USER02 with GRANT option; User02 get permission and can be passed.

6. Entity Rights recovery:
User01:
　Sql>revoke Select, update on the product from User02; All the permissions passed will be lost.