How to implement Oracle Modify user rights.

This will introduce the implementation process for Oracle to modify user permissions, including some rights management aspects. Hopefully this article will help you understand Oracle's ability to modify user rights.

Oracle database user and Rights management

Oracle is a multi-user system that allows many users to share system resources. In order to ensure the security of database system, the database management system is configured with good security mechanism.

2.1 Oracle Database Security Policy

Establish a system-level security guarantee

System-level privileges are achieved by granting the user system-level rights, system-level rights (System privileges), including: establishing a tablespace, establishing a user, modifying a user's rights, deleting a user, and so on. System privileges can be granted to users, or they can be recycled at any time. There are more than 80 Oracle system privileges.

Establish an object-level security guarantee

Object-level privileges are implemented by granting the user the right to manipulate (query, add, or remove) the specific tables, views, sequences, and so on in the database.

Establish a user-level security guarantee

User-level security is achieved through user passwords and role mechanisms (a set of entitlements). The purpose of introducing the role mechanism is to simplify the authorization and management of the user. This is done by grouping users into their functions, creating roles for each user, and assigning roles to users who have the same privileges.

2.2 User Management

Oracle user-managed content primarily includes user creation, modification, and deletion

User's establishment

Sql>create USER Jxzy
>identified by Jxzy_password
>default tablespace System
>quata 5M on system; Maximum space limit for use by the user

Oracle Modify User Rights

Sql>create USER Jxzy
>identified by JXZY_PW
>quata 10M on system;

Delete users and their built objects

Sql>drop USER Jxzy CASCADE; Delete the entity it was created at the same time

2.3 System Privilege Management and control

ORACLE provides more than 80 system privileges, each of which allows the user to perform one or a class of database operations.

Granting System privileges

Sql>grant CREATE user,alter User,drop USER
>to jxzy_new
>with ADMIN OPTION;

Reclaim System Privileges

Sql>revoke CREATE user,alter User,drop USER
>from jxzy_new//But no cascade recycle function

Show system privileges granted (System-level privileges for a user)

Sql>select*from Sys.dba_sys_privs

2.4 Object Privilege Management and control

Oracle Object privilege refers to the right of a user to perform special operations on a specified table. These special operations include add, delete, change, view, execute (stored procedure), reference (Other table fields as outer

Key), index, and so on.

Granting 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

Reclaim Object Privileges

Sql>revoke UPDATE on Office_orgaization
>from new_administrator//Cascade recovery function

Sql>revoke all on Office_organization
>from New_administrator

To display all object privileges that have been granted

Sql>select*from Sys.dba_tab_privs

2.5 Management of the roles

Oracle's role is a named set of related privileged groups (including system privileges and object privileges) that Oracle uses to simplify privileged management and grant it to users or other roles.

The Oracle database system pre-defined the five roles of Connect, RESOURCE, DBA, Exp_full_database, Imp_full_database. Connect has the CREATE TABLE, view, order

Resource has the privilege of creating a process, trigger, table, sequence, DBA has all system privileges; exp_full_database, imp_full_database have unloading and loading

privileges to the database.

Query Sys.dba_sys_privs to understand the rights that each role has.

To grant a user role

Sql>grant DBA to New_administractor
>with GRANT OPTION;

==============================================================

Oracle's users are divided into system and object permissions based on the permissions they are granted. The highest of these permissions is SYSDBA. SYSDBA has the privilege of controlling all of Oracle's actions, such as creating,

Starting, shutting down, recovering the database, making the database archive/non-archive, backup table space and other critical actions can only be performed by users with SYSDBA permissions. These tasks are even common DBAs

The role is not. Sysoper is a similar privilege to SYSDBA, but less than sysdba sysoper privileges with ADMIN option,create database,recover

DATABASE until these few permissions. The two authentication methods are the same way, so the following only describes the SYSDBA certification management.

Generally, there are two ways to manage SYSDBA: * * * for system authentication and password file authentication. The specific choice of authentication method depends on: you want to maintain the database on the machine that Oracle is running on

, or manage all Oracle databases distributed across different machines on a single machine. If you choose to maintain the database in the local, then choose * * * SYSTEM certification may be a simple and easy to do

If you have a lot of databases and want to centralize management, you can choose the password file authentication method.

A more intuitive explanation of this choice tradeoff process:

Use * * * for System authentication method configuration process:

1. Establish a legal account in the * * * system.

Specifically, on NT, first establish a local user group named ORA__DBA, where Sid is the SID of the DB instance, or establish a ORA_DBA group that does not correspond to any

A separate instance of Oracle. This way, when there are several Oracle instances on an NT, they are not managed separately. Then create a user on NT and put it into the group. But actually

These two steps have been completed automatically during the Oracle8i installation process and are generally not manually performed.

Step three: In Sqlnet.ora (located in the $oracle_home/network/admin directory), set the Sqlnet.authentication _services to

Sqlnet. Authentication_services= (NTS), meaning to use NT Authentication mode.

The fourth step, in the Init.ora, the Remote_login_password set to none, meaning is not password authentication method.

Once you have completed the above steps, you can log on to NT directly in Sql*plus and Server Manager connect INTERNAL (Connect/as SYSDBA) as a superuser to login to

Oracle, perform some work that only super users can do.

Under Unix, things are a little different. After all, this is two completely different system.

First, set up a DBA group before you install Oracle, which is not a step in the Oracle. Typically, you create a user named Oracle and add it to the DBA group

In

The second step is to set Remote_login_password to none. After Oracle8.1, this parameter defaults to exclusive. Be sure to remember to change over.

Third, log in to UNIX with the user name, run Sql*plus or server MANAGER, and enter the following command: Connect INTERNAL (connect/as SYSDBA) to log in to Oracle

In

Specific steps for using password file authentication:

Oracle provides ORAPWD utilities to create password files, and the steps to use ORAPWD to establish this authentication method are as follows:

1. Use the Orapwd utility to create a password file. Grammar:

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

Detailed Explanation:

The file name contains the full path name, and if not specified, ORACLE places it under $oracle_home/dbs (under Unix) or $oracle_home/database (NT).

The user password is the user internal password. Of course, you can then add other super users to the inside.

Entries represents the maximum number of super users allowed. This is an optional one. The first two must be specified. It is generally set to be larger than the actual need to avoid enough.

2. Set Remote_login_password in Init.ora to exclusive or shared. Use exclusive to indicate that only the current instance uses this password file. and allowed to have

Other users are logged into the system as SYSDBA, and if shared is selected, more than one instance uses this password file, along with a strong constraint: SYSDBA permission can only be granted

The two usernames for SYS and internal. (In fact, internal is not an actual user, but Sys is an alias for SYSDBA login.) ）

Also remember to set the Sqlnet.authentication _services in the Sqlnet.ora file to none. Generally under UNIX It is the default setting. Under NT, if you choose a typical installation, you will

OS authentication is used, and the password file authentication method is used when customizing. You will be prompted to enter the internal password during the installation process. This way, you don't have to create a password file and set it manually.

Internal's password.

3. Run the following command with Sql*plus or Server Manager to log into the system: CONNECT internal/password.

Note the point:

1. When Oracle8.1.6 is installed under WIN2000 to create a database, there are often errors in credential retrieval failures. This is because Oracle cannot apply the results of OS authentication. Can generally be modified by

Sqlnet.ora in Sqlnet.authentication _services for none to solve. At this point, Oracle will use the password file authentication method.

2. Since Oracle has several pre-built users, it is a good idea to change these user passwords immediately after the installation is complete. The system default passwords are: Internal/oracle,

Sys/change_on_install, System/manager.

3. When choosing the password file authentication method, you can add other super users to the system. For example, use the following statement to add the user Scott to the Superuser: (by a person with SYSDBA permissions

Line

Sql>grant SYSDBA to Scott, so Scott users have SYSDBA permissions. Note that at this point the Scott user can log in in two ways: Scott, SYS. When Scott was logged in, he didn't

When there is input as SYSDBA, Scott is logged in as a normal user. When you enter as SYSDBA when logging in, the user that Scott is logged in to is actually sys.

4. User names with SYSDBA permissions 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

Password file, re-create a. This requires shutting down the database, deleting the password file, recreating a new password file, and entering a large enough number in the entries. Start Oracle again.

At this point, all formerly north-authorized super users no longer exist and need to be re-authorised. So before re-creating the password file, you need to query the view, write down the user name, and then after creating the password text

Re-authorize the item after the

6. Internal user Password Forgotten processing method:

There are two ways to do this:

1. Alter USER SYS identified by new password;//This also changes the internal password, which is passed in oracle8i

2. Re-create a new password file and specify a new password.

How to implement Oracle Modify user rights.