Oracle instances, users, permissions, and Roles

　　1, the database instance: After the database is created there will be a series of memory space for the database service and the acquired process, known as an instance of the database. Each database will have at least one instance for its service. The memory structure in the instance is called the system Global Area (SGA), and the system allocates a very considerable amount of memory to the SGA based on the performance of the current computer system.

2. Although multiple databases can be installed in an Oracle database server, a database needs to occupy very large memory space, so a server typically installs only one database. Each database can have many users, different users have their own database objects (such as database tables), if a user access other users of the database object, must be granted by the other user a certain permission. Tables created by different users can only be accessed by the current user. As a result, in Oracle development, different applications can only use different user access.

　　3. Oracle Users and Permissions

Oracle, it is generally not easy to create multiple databases on a server, in a database, different projects are accessed by different users, each user has its own database objects created, so the concept of user is very important in Oracle. Users of Oracle can be created with the Create user command. Its syntax is:

CREATE user username identified by password [account lock| UNLOCK]

lock| Unlock is locked when the user is created, and is locked by default. A locked user does not have a normal logon for database operations.

Although the user was successfully created, it was not possible to log on to the Oracle database system properly because the user did not have any permissions. If the user is able to log in properly, at least CREATE SESSION system permissions are required.

Oracle user rights to database management or object operations are divided into system permissions and database object permissions. System permissions such as: CREATE session,create table, users with system permissions, allowing the corresponding system operation. Database object permissions, such as the increase and deletion of data in the table, and so on, users with database object permissions can do the corresponding operation of the object.

Another concept is database roles (role), which is a collection of several system permissions. Here are a few common roles:

　　①connect roles are primarily used by temporary users, especially those who do not need to build a table, and usually only give them connect role. Connect is a simple permission to use Oracle, a user with the connect role that can establish a connection session with the server (session, a client-to-server connection, called a conversation).

　　②resource Roles , more reliable and formal database users can grant resource role. Resource provides users with additional permissions to create their own tables, sequences, procedures (procedure), triggers (trigger), indexes, and so on.

　　③dba role , DBA role has all of the system permissions----including unlimited space limits and the ability to grant various permissions to other users. The user system has a DBA role.

In general, a regular user (such as Scott) with Connect and resource two roles can perform regular database development work.

A permission can be granted to a role, and permissions and roles can be granted to a user. System permissions can only be authorized by the DBA user, the object permissions are authorized by the user who owns the object, the authorization syntax is: Grant role | permissions to User (role)

Reclaim Permissions

REVOKE Roles | Permissions from user (role)

To modify a user's password

ALTER user username identified by new password

Modify the user in a locked (non-locked) state

ALTER User Username Account lock| UNLOCK

Oracle instances, users, permissions, and Roles