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 operate (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 (a set of rights. 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 user jxzy
> Identified by jxzy_password
> Default tablespace System
> Quata 5 m on system; // maximum space limit for users

 

Modify user permissions in Oracle

SQL> create user jxzy
> Identified by jxzy_pw
> Quata 10 m on system;

 

Delete users and their created objects

SQL> drop user jxzy cascade; // 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
> From jxzy_new // but no Cascade reclaim Function

Displays System privileges granted (SYSTEM privileges of 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 addition, deletion, modification, viewing, execution (stored procedure), reference (other table fields as external

Key), index, etc.

Grant object privileges

SQL> grant select, insert (office_num, office_name ),
> Update (DESC) on office_organization
> To new_adminidtrator
> With grant option; // cascade authorization

SQL> grant all on office_organization
> To new_administrator

Revoke object privileges

SQL> revoke update on office_orgaization
> From new_administrator // cascade recovery function

SQL> revoke all on office_organization
> From new_administrator

Show all granted object privileges

SQL> select * From SYS. dba_tab_privs

 

2.5 role management

An Oracle role is a named privileged group (including system and object privileges). Oracle uses it to simplify Privilege Management and grant it 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 ability to create tables, views, and orders.

Columns and other privileges; resource has the creation process, trigger, table, sequence, and other privileges; DBA has all system privileges; exp_full_database, imp_full_database has the ability to unload and install

Database access privileges.

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 creation,

Start, close, and restore the database so that the database can be archived/non-archived, and key actions such as table space backup can only be performed by users with sysdba permissions. These tasks are even common DBA

The role does not work either. Sysoper is a permission similar to sysdba, except that sysoper privileges with admin option, create database, recover

Database until permissions. 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: You want to maintain the database on the machine running Oracle

To manage all 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 task.

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 should not be in any

A 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. But in fact

These two steps have been automatically completed during the Oracle8i installation process. Generally, you do not need to perform them manually.

Step 3: Set sqlnet. Authentication _ services

Sqlnet. authentication_services = (CNT), 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

In Oracle, perform *** operations that only super users can perform.

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/DBS (UNIX) or $ ORACLE_HOME/Database (NT) by default.

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 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, while Password File authentication is used for custom authentication. During installation, an internal password is prompted. In this way, you do not need to manually create a password file and set it.

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

In sqlnet. ora, sqlnet. Authentication _ services is 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, 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 user Scott to a Super User: (executed by a person with sysdba permissions)

Rows)

SQL> grant sysdba to Scott; then the Scott user has the sysdba permission. Note: At this time, Scott users can log on as Scott, SYS. Scott does not

Scott logs on as sysdba 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. When you need to create more users with sysdba permissions, You need to delete the original

To create a new password file. 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

And then re-authorize.

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.