How to modify user permissions in Oracle

Here we will introduce the implementation process of modifying user permissions in Oracle, including some permission management aspects. I hope this article will help you understand how to modify the user permissions of Oracle.

ORACLE Database User and permission management

ORACLE is a multi-user system that allows many users to share system resources. To ensure the security of the database system, the database management system is configured with a sound security mechanism.

2. 1 ORACLE Database Security Policy

Establish system-level security assurance

System-level privileges are implemented by granting users system-level rights. System-level rights system privileges include creating tablespaces, creating users, modifying users' rights, and deleting users. System privileges can be granted to users or recycled at any time. The ORACLE system has over 80 privileges.

Establish object-level security assurance

Object-level privileges are implemented by granting users the right to query, add, delete, and modify specific tables, views, and sequences in the database.

Establish user-level security assurance

User-level security assurance is implemented through user passwords and role mechanisms. The role mechanism is introduced to simplify user authorization and management. The practice is to group users by their functions, create roles for each user, and assign roles to users. users with the same roles have the same privileges.

2.2 user management

The content of ORACLE user management mainly includes User Creation, modification and deletion.

User Creation

 
 

  
  
SQL>CREATE USERJxzy
  
  
> IDENTIFIEDBYJxzy_password
  
  
>DEFAULTTABLESPACE system
  
  
> QUATA 5 MONSystem; // maximum space limit for users
 
 

Modify user permissions in Oracle

 
 

  
  
SQL>CREATE USER jxzy  
  
  
>IDENTIFIED BY jxzy_pw  
  
  
>QUATA 10M ON system; 
 
 

Delete users and their created objects

 
 

  
  
SQL>DROP USERJxzyCASCADE; // Delete the created object at the same time
 
 

2.3 system privilege management and control

ORACLE provides over 80 SYSTEM privileges, each of which allows you to perform one or more database operations.

Grant System privileges

 
 

  
  
SQL>GRANT CREATE USER,ALTER USER,DROP USER 
  
  
>TO jxzy_new  
  
  
>WITH ADMIN OPTION; 
 
 

Revoke SYSTEM privileges

 
 

  
  
SQL>REVOKE CREATE USER,ALTER USER,DROP USER 
  
  
>FROMJxzy_new
  
  
// But no Cascade recovery function
 
 

Displays System privileges granted to a user)

 
 

  
  
SQL>SELECT*FROM sys.dba_sys_privs 
 
 

2.4 Object Privilege Management and Control

ORACLE Object Privilege refers to the user's right to perform special operations on the specified table. These special operations include adding, deleting, modifying, viewing, and executing stored procedures), referencing other table fields as foreign keys, and indexing.

Grant object privileges

 
 

  
  
SQL>GRANT SELECT,INSERT(Office_num, office_name ),
  
  
>UPDATE(Desc)ONOffice_organization
  
  
>TONew_adminidtrator
  
  
>WITH GRANT OPTION;
  
  
// Cascade authorization
  
  
SQL>GRANT ALL ONOffice_organization
  
  
>TONew_administrator
 
 

Revoke object privileges

 
 

  
  
SQL>REVOKE UPDATE ONOffice_orgaization
  
  
>FROMNew_administrator
  
  
// Cascade recovery
  
  
SQL>REVOKE ALL ONOffice_organization
  
  
>FROMNew_administrator
 
 

Show all granted object privileges

 
 

  
  
SQL>SELECT*FROM sys.dba_tab_privs 
 
 

2.5 role management

The role of ORACLE is the name of the relevant privileged groups including SYSTEM privileges and object privileges), ORACLE uses it to simplify privilege management, it can be granted to users or other roles.

The ORACLE Database System pre-defines five roles: CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, and IMP_FULL_DATABASE. CONNECT has the privilege to create tables, views, sequences, and other resources. RESOURCE has the privilege to create processes, triggers, tables, sequences, and other resources. DBA has all system privileges; EXP_FULL_DATABASE and IMP_FULL_DATABASE have the privilege of detaching and loading databases.

By querying sys. dba_sys_privs, you can understand the permissions of each role.

Grant User Roles

 
 

  
  
SQL>GRANT DBA TO new_administractor  
  
  
>WITH GRANT OPTION;  
  
  
============================================================== 
 
 

Oracle users are divided into system and object permissions based on the granted permissions. The highest permission is sysdba. Sysdba has the privilege to control all behaviors of Oracle, such as creating, starting, closing, and restoring databases, so that databases can be archived or not archived, backup tablespace and other key actions can only be performed by users with sysdba permissions. These tasks do not work even for common DBA roles. Sysoper is a permission similar to sysdba, except that SYSOPER privileges with admin option, create database, and recover database until are less than sysdba. The two authentication methods are the same, so the following describes only the authentication management of sysdba.

There are two methods to manage sysdba: *** system authentication and password file authentication. The specific authentication method depends on whether you want to maintain the database on the machine running Oracle or manage all the Oracle databases distributed on different machines on one machine. If you choose to maintain the database on the local machine, selecting ** for system authentication may be a simple and easy way. If you have many databases and want to perform centralized management, you can select the password file authentication method.

The following describes the trade-off process:

The configuration process using *** for system authentication:

1. Create a valid account in the *** system.

Specifically, on NT, first create a local user group named oraw.dba, where SID is the SID of the database instance, or create an ORA_DBA group, this group does not apply to any separate Oracle instance. In this way, when an NT has several Oracle instances, you do not need to manage them separately. Create a user on NT and add it to the group. However, these two steps have been completed automatically during the Oracle8I installation process. Generally, you do not need to perform them manually.

Step 3: In sqlnet. ora is located in the $ ORACLE_HOME/NETWORK/ADMIN directory. AUTHENTICATION _ SERVICES is set to SQLNET. AUTHENTICATION_SERVICES = (ETS), which means to use the NT authentication method.

Step 4: In INIT. ORA, set REMOTE_LOGIN_PASSWORD to NONE, meaning password authentication is not required.

After completing the preceding steps, you can directly connect internal (CONNECT/as sysdba) in SQL * Plus and SERVER MANAGER to log on to Oracle AS a Super User after logging on to NT, execute *** tasks that can only be performed by Super Users.

In Unix, the situation is somewhat different. After all, this is a completely different *** operating system.

First, create a DBA group before installing Oracle. This step is not required. Otherwise, Oracle cannot be installed. Generally, a user named Oracle is created and added to the DBA group.

Step 2: Set REMOTE_LOGIN_PASSWORD to NONE. After Oracle8.1, this parameter is EXCLUSIVE by default. Remember to change it.

Step 3: log on to Unix with the user name, run SQL * Plus or SERVER MANAGER, and enter the following command: CONNECT INTERNAL (CONNECT/AS SYSDBA) to log on to Oracle.

To use the password file for authentication, follow these steps:

Oracle provides the orapwd utility to create a password file. The specific steps for using orapwd to establish this authentication method are as follows:

1. Use the Orapwd utility to create a PASSWORD file. Syntax:

Orapwd file = file name password = internal user password entried = entries.

Explanation:

The file name must contain the complete full path name. If not specified, Oracle places it under $ ORACLE_HOME/dbsUnix by default) or $ ORACLE_HOME/DATABASENT.

The user password is the user's internal password. Of course, you can add other super users to it later.

Entries indicates the maximum number of Super Users Allowed. This is optional. The first two must be specified. It is usually set to be larger than the actual needs, so as not to be insufficient.

2. Set REMOTE_LOGIN_PASSWORD in INIT. ORA to EXCLUSIVE or SHARED. Use EXCLUSIVE to use this password file only for the current INSTANCE. In addition, other users are allowed to log on to the system as sysdba. If SHARED is selected, it indicates that more than one instance uses this password file, with a strong constraint: sysdba permissions can only be granted to sys and internal. In fact, internal is not a real user, but sys is an alias for sysdba logon .)

Remember to set sqlnet. AUTHENTICATION _ SERVICES to NONE in the SQLNET. ora file. It is usually set by default in Unix. In NT, if you select a typical installation, OS authentication is used, and password file authentication is used for custom installation. During installation, an INTERNAL password is prompted. In this way, you do not need to manually create a password file or set an INTERNAL password.

3. Run the following command in SQL * Plus or SERVER MANAGER to log on to the system: CONNECT INTERNAL/password.

Note:

1. When Oracle8.1.6 is installed in WIN2000 to create a database, a credential retrieval failure error often occurs. This is because Oracle cannot apply OS authentication results. You can modify sqlnet. AUTHENTICATION _ SERVICES in SQLNET. ora to NONE. In this case, Oracle uses Password File authentication.

2. Since Oracle has several pre-built users, it is best to change the passwords of these users immediately after the installation is complete. The default passwords are internal/oracle, sys/change_on_install, and system/manager.

3. When selecting the password file authentication method, you can add another super user to the system. For example, use the following statement to add SCOTT to a Super User: (executed by a person with sysdba permissions)

SQL> GRANT SYSDBA TO SCOTT; then the SCOTT user has the sysdba permission. Note: At this time, SCOTT users can log on as scott and SYS. When SCOTT does not enter as sysdba during logon, SCOTT is logged on AS a common user. When the as sysdba is entered during logon, the user that SCOTT logs on to is actually sys.

4. the username with sysdba permission in the current system can be queried from the data dictionary view v $ pwfile_user:

SELECT * from v $ PWFILE_USERS; as shown in.

5. the maximum number of users with sysdba permissions in the system is determined by the ENTRIES parameter when the password file is created. To create more users with sysdba permissions, You need to delete the original password file and recreate one. In this case, you need to close the database, delete the password file, re-create a new password file, and enter a large enough number in entries. Start Oracle again. At this time, all original North authorized Super Users no longer exist, and need to be re-authorized. Therefore, before re-creating the password file, you must first query the view, write down the user name, and then re-authorize after creating the password file.

6. How to Deal with the password forgotten by Internal:

There are two methods:

1. alter user sys identified by new password; // This also changes the Internal password, which is passed in Oracle8I

2. Create a new password file and specify a new password.