Oracle BASICS (II): user and permission management, and oracle permission management

Oracle BASICS (II): user and permission management, and oracle permission management

1. Create a user(1) simple creation 1. Permissions: only DBA users have permissions or create user system permissions 2. Syntax:

• Create user Username identified by password;
• The oracle password cannot start with a number.

3. instance: username-sam_sho and password-sam123

• Create user sam_sho identified by sam123;

4. Note: 1) the newly created user does not have any permissions. The administrator must assign them permissions. Such as logon permissions:

• Grant create session to sam_sho; -- assign the logon permission
• Create session: logon permission.

2) the permissions of oracle are complex. We will handle them separately.

(2) complex creation 1. Instances

• Create usersam_shoidentified by sam123
• Default tablespace users -- default tablespace. Users is the tablespace created by oracle.
• Temporary tablespace temp -- temporary tablespace
• Quota 3 m on users; -- allocate table space, 3 m. Unlimited-unlimited size
• Grant create session to sam_sho; -- assign the logon permission
• Grant dba to sam_sho; -- assign a DBA role
• Grant select on V_table to sam_sho; -- assign the query table permission.

2. Table space understanding 1) Table space, that is, the logical Table is stored in the Table space (Tablespace) 2) A Table space points to a specific data file


3) for users of the resourse role, the table created has no tablespace restrictions.

• Grant resourse to sam_sho; -- assign the resourse role

(3) Oracle user management mechanism 1. concepts:

• Oracle dbms: Database Management System
• Db instance:
• Tablespace
• Data Object
• Table
• User
• Permission: system permission and database permission.
• Role: Set common permissions.

2. Relationship between permissions, roles, and users




Ii. logon and Password Change(1) User Logon 1. Switch between users and user logon. 1) Syntax:

• Conn username/password [as sysdba/sysoper]

2) instance:

• Conn sam_sho/sam123;
• Conn sam_sho/sam123 as sysdba;
  • The actual logon is not sam_sho. You can show the user.
  • It is related to the verification mechanism, as shown below.

2. display the name of the current Login User 1) Syntax:

• Show user;

(2) Change the password 1. Change the password for the user 1) Syntax:

• Passw [ord] User Name

2) instance:

• Passw sam_sho;
• Passw

3) Note:

• You do not need to include the user name.

2. Change the password for others. 1) permissions: DBA permissions are required, or alter user system permissions are required. 2) Syntax:

• Alter user Username identified by new password;

3) instance:

• Alter user sam_sho identified by sam456;

(3) Delete a user. 1. Permission: Generally the DBA permission is required, or the permission of the drop user is required. 2. Syntax:

• Syntax: drop user username (cascade)
• Cascade parameter: cascade the deletion of data objects such as tables created by the user. The database will remind you.

3. instance:

• Drop user sam_sho;
• Drop user sam_sho cascade; -- cascading deletion of table and other data created by the user

4. delete a user but keep its Data Objects

• Solution: Lock this user
  • Alter user account lock;
  • Alter user account unlock; -- unlock

3. User Password
1. Overview:

• DBA identity required
• Profile is a set of command sets for password restrictions and resource restrictions. When a database is created, oracle automatically creates a profile named default.
• If the profile option is not specified for the created user, oracle assigns the default option to the user.

2. account locking

• Overview: You can specify the maximum number of times a user can enter a password when logging on to the account, or specify the user's lock time (days). Generally, you can run the command as a dba.
• Instance: specify that this user can only try three logon attempts, and the lock time is 2 days.
  • Create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
  • Alter user sam123 profile lock_account; -- allocate

3. Account unlocking:

• Syntax:
  • Alter user Username account unlock;
• Instance:
  • Alter user sam123 account unlock;

4. Termination Password

• Overview: You can use the termination password to change the password periodically.
• Instance: Create a new profile and require the user to change his/her logon password every 10 days. The grace period is 2 days.
  • Create profile myProfile limit password_life_time 10 password_grace_time 2;
  • Alter user sam123 profile myProfile;

5. Historical passwords: the old and new passwords cannot be repeated.

• Create profile myProfile2 limit password_life_time 10 password_grace_time 2 password_reuse_time 1
• Alter user sam123 profile myProfile2;

6. Delete the password

• Drop profile myProfile2;

Iv. permissions and Roles(1). Overview:
1. The created user does not have any permissions (cannot log on) and must be granted various permissions. Oracle needs to reference the role concept for permission management. 2. Permission 1) system permission: the user's permissions on database management and operations on the data object itself.

• Including database creation, table creation, index creation, database login, password modification, etc.

2) object permission: the user's permission to operate Data Objects of other users.

• Permissions include select, insert, update, delete, all, and create index.

3. Role: simplify permission management. 1) pre-defined roles

• Connect: connected role
• DBA: Administrator role
• Resource: This role can be used to create tables in any tablespace.

2) custom roles
4. View permissions and Roles

• Use PL/SQL DEV to view
• Use various commands

5. Permission Allocation Method

• Directly assign Permissions
  • System permissions (related to database management ):
    • Grant create sessionto sam_sho; -- grant logon permissions.
  • Object permission (add, delete, modify, and query ):
    • GrantselectonV_tableto sam_sho; -- assign the query table permission
• Assign roles and assign permissions in batches
  • Grantdbato sam_sho; -- assign a DBA role

6. revoke permissions

• Syntax: revoke permission from user
• Instance:
  • Revoke select on emp from sam_sho; revoke the query permission of the emp table from sam_sho
  • Revoke connect from sam_sho;

(2) Permission 1. system permission: it refers to the permission to execute specific types of SQL commands. It is used to control one or more database operations that users can perform. For example, if you have the create table permission, you can create a table in the solution. If you have the create any table permission, you can create a table in any solution. 1) Content

• Create session to connect to the database
• Create table
• Create view
• Create public synonym
• Create procedure creation process, function, package
• Create trigger
• Create cluster
• Create sequence index creation
• Create type

2) display:

• Oracle provides 166 system permissions to query the data dictionary view system_privilege_map
• Select * from system_privilege_map order by name; -- Query System permission

3) grant system permissions: grant

• Generally, only DBA can complete or grant any privilege permissions.
• After the permission statement is granted, + with admin option is used to transfer system permissions (which can be understood together with the solution)
  • Grant connect to sam_sho with admin option

4) Permission revocation: revoke

• Not cascade recovery.

2. Object permission: the right to access other solution objects. You can directly access the objects in your own solution. However, if you want to access the objects in another solution, you must have the object permission. 1) content:

• Alter Modification
• Delete
• Select query
• Insert
• Update Modification
• Index
• References reference
• Execute

2) display: DBA users can view the object permissions dba_tab_privs

• Select distinct privilege from dba_tab_privs;

3) grant object permissions

• You can directly grant permissions to users or roles.
• After a permission statement is granted, + with grant option is used to transfer system permissions.
  • Grant select on emp to sam_sho with grant option
  • Only users can be granted, not roles

4) Permission revocation: revoke

• Is cascade recovery.

(3) Roles 1. predefined roles: 33 types 1) Common Content

• Connect: connected role
• DBA: Administrator role (sys and system ). Database startup and shutdown are not available
• Resource: This role can be used to create tables in any tablespace.
  • The unlimited tablespace permission is hidden.

2) display

• View predefined roles: 33 roles
  • Select * from dba_roles;
• View system permissions of a role
  • SELECT * FROM dba_sys_privs where grantee = 'dba'
  • SELECT * FROM role_sys_privs WHERE role = 'dba ';
• View the object permissions of a role
  • SELECT * FROM dba_tab_privs where grantee = 'dba'

• View the role of a user
  • SELECT * FROM dba_role_privs WHERE grantee = 'sys ';

2. Custom role 1) create a role

• Note:
  • Create role or have the create role permission
  • When creating a role, you can set a verification method, such as no verification or database verification.
• Do not verify creation (this is generally the case)
  • Create role name not identified
• Database Verification
  • Create role name identified by password;

2) grant permissions to the role

• Grant create session to role

3) instance

• Create role myRole not identified;
• Grant create session to myRole;
• Grant select on scott. emp to myRole;
• Grant myRole to sam_sho;

3. delete a role

• Drop role name;

(4) grant permissions to users. 1. grant direct permissions. For example, create session)

• Grant create session to sam_sho;
• Grant select on emp to sam_sho;

2. directly assign roles and assign permissions in batches. For example

• Grant connect to sam_sho;
  • The role granted to sam_sho connect has the logon permission. Connect, including 7 permissions.
• Grant resource to sam_sho;

(5) user permission operation 1. grant permission 1) Syntax: grant permission (role) to user name;
2) instance 1:

• Grant create table to sam_sho: grant sam_sho the permission to create a table.
• Grant dba to sam_sho: grant the role of sam_sho dba, which naturally has the role of creating a table.

3) instance 2

• Grant select on emp to sam_sho; grant the query permission of the emp table to sam_sho.
• SELECT * FROM scott. emp; can be queried, but the scott prefix must be added before the table.

2. revoke permissions: revoke1) Syntax:

• Revoke select on emp from sam_sho; revoke the query permission of the emp table from sam_sho

2) Note:

• Who authorizes and who withdraws. Or DBA
• System permission, not cascade.
• Object permission, which is cascading revocation.

3. Permission transfer 1) scott grants the query permission of the emp table to sam_sho, and sam_sho then passes this permission to rabby_zho2) Syntax:

• Use with grant option to pass object permissions
  • Grant select on emp to sam_sho with grant option; (scott login)
  • Grant select on scott. emp to rabby_zho; (sam_sho logon)
• Use with admin option for roles and system Permissions
  • Grant connect to sam_sho with admin option; (scott logon)
  • Grant connect to rabby_zho;

3) Recycling

• System permission, not cascade.
• Object permission, which is cascading revocation.

V. solution (Schema)1. problem: the same database instance DEV_SAM creates two users A and B. User A creates A table a_table and B creates A table B _table. Result A cannot access B _table, and B cannot access a_table. 2. solution:

• After a user is created, if the user creates any data object, the dbms will create a corresponding solution that corresponds to the user, in addition, the name of the solution is the same as that of the user name.
• Under this mechanism, multiple tables with the same table name can be created for the same database instance, but the table name in the same solution is unique.
• If A needs to access the data in B's solution, it requires permission transfer.
  • Syntax:
    • Grant permission to user name [with grant option] object permission
    • Grant permission to user name [with admin option] system permission
  • Instance
    • Grant select on emp to sam_sho; (scott logon)
      • When using this function, you must include the [solution] Name: select * from scott. emp.
    • Grant all on emp to sam_sho with grant option; can be transferred
      • Grant select on scott. emp to sam2;

6. Database Startup Process (Remote logon)(1) windows: 1. lsnrctl start (listener startup) 2. oradim-startup-sid database instance name

• Oradim-startup-sid orcl

3. Appendix: View windows Information

• Systeminfo

(2) Linux1, lsnrctl start (listener startup) 2. sqlplus sys/change_on_install as sysdba

• Sqlplus/nolog
• Conn sys/change_on_install as sysdba

3. startup

7. Oracle login authentication method(1) windows 1 and oracle logon authentication are different in windows and linux. 2. symptom: 1) conn XXXX/CCC as sysdba; the logon is successful and the current user is sys. 2) XXXX/CCC can be arbitrary, as long as it can be as sysdba.
2. operating system authentication 1) If [current user] belongs to the ora_dba Group of the local operating system, you can pass operating system authentication. 2) The current user refers to the user currently used by the operating system, which can be viewed simply in the windows Task Manager.


3) operating system users and groups:

• My computer --> right-click Management --> local users and groups --> users, such as user sam

• My computer --> right-click Management --> local user and group --> group, and display that sam belongs to ora_dba Group

3. oracle Database verification (password file verification) 1) for common users:

• Oracle uses database verification by default.

2) for privileged users (such as sys users, or as sysdba)

• Oracle uses the operating system authentication by default. If the verification fails, it goes to the database for verification.
• You can modify the oracle logon authentication method by configuring the NETWORK \ ADMIN \ sqlnet. ora file.
  • SQLNET. AUTHENTICATION_SERVICES = (CNT)
    • System-based, default.
    • NONE: Oracle-based verification
    • NONE, CNT: both are verified

(2) Linux, omitted.
7. Loss of the Oracle administrator password1. Recovery Method: Delete the original password file and generate a new password file. 2. Recovery steps: 1) Search for the file named PWD + database instance name. ora. This file is usually deleted in \ database \ PWDorcl. ora2). We recommend that you back up the file. 3) generate a new password file and enter the following command in the doc:

• Orapwd file = full path of the original password file \ password file Name. ora password = new password entries = 10;
• Entries = 10: Several privileged users are allowed.
• The password file name must be consistent with the original password file name.

4) the restart takes effect.
3. Example:

• Orapwd file = D: \ oracle10g \ database \ PWDorcl. ora password = abc123 entries = 10;

8. Database Administrator