Oracle user and permission management

Oracle user and permission management

Oracle permission Classification

1. system permissions: database operation permissions, including but not limited:

CREATE TABLE

CREATE VIEW

CREATE SESSION

CREATE SEQUENCE

CREATE PROCEDURE

......

2. object permissions: Permission for accessing and modifying tables and views created by users, including but not limited:

SELECT

UPDATE

INSERT

DELETE

......

Permission allocation and Revocation

1. Permission assignment:

GRANT permissions TO users/roles

Example 1: grant the access permission to the employee table under the hr user to SCOTT.

Grant select on hr. employee TO scott;

Example 2: Assign role CONNECT to user SCOTT

Grant connect to scott

2. Permission revocation:

REVOKE permission FROM user/role

Example 1: revoke scott's access to the hr user's employee table

REVOKE hr. employee FROM scott;

Example 2: revoke scott's CONNECT role

Revoke connect from scott;

Permission inheritance and Revocation

1. Scenario Simulation: The Oracle headquarters has the permission to manage Oracle products. The Oracle headquarters grants Oracle China the permission to manage business matters in China. If Oracle China needs to assign this permission twice, you can use the with admin option (system permission inheritance)/with grant option (Object permission inheritance) when granting permissions to the headquarters)

Example: SYS grants scott the permission to create a table, and scott can grant other users the permission to create a table.

SOL> CONN SYS

Grant create table to scott withadmin option;

Note:

Create table is a system permission. Use the keyword "with admin option" to grant permission inheritance;

In this case, SCOTT can grant the table creation permission to any other user.

SQL> CONN SCOTT

Grant create table TOtest;

Use withgrant option to inherit object permissions.

For example, grant the access permission to the employee table under the hr user to SCOTT, and the SCOTT user can inherit

SQL> CONN hr

Grant select on hr. employee TO scott with grant option;

CONN scott

Grant select ONhr. employee TO test; -- the scott user assigns the permission TO the test user twice;

2. Revoke inherited Permissions

When revoking permissions, the sub-permissions assigned by the with admin option are not revoked;

The object permissions are cascaded. For example, the scott user has the permission to access the employe table under the hr user, and the scott user has assigned the permission to the new user test again, when scott's access permissions are revoked, the system automatically revokes the access permissions assigned by scott twice;

Common role permissions and allocation principles

1. Common roles

DBA: it has all the privileges and is the highest system permission. only DBA can create a database structure.

RESOURCE: only entities can be created, and the database structure cannot be created.

CONNECT: You can only log on to Oracle, create entities, and create database structures.

2. Principles of role permission allocation:

For normal users: grant connect and resource permissions.
For DBA management users: grant the connect, resource, and dba permissions.

Common permission query view

-- 1. display the system permissions of the role

SELECT * FROMROLE_SYS_PRIVS;

-- 2. permissions of the role on table columns

SELECT * FROMROLE_TAB_PRIVS;

-- 3. query the role of a user

SELECT * FROMUSER_ROLE_PRIVS;

-- 4. displays the object permissions granted to other users. The user is the current logon user.

SELECT * FROMUSER_TAB_PRIVS_MADE

-- 5. displays the object permissions granted to other users. The user is an authorized user.

SELECT * FROMUSER_TAB_PRIVS_RECD

-- 6. displays the permissions granted to the column. The user is the current logon user.

SELECT * FROMUSER_COL_PRIVS_MADE

-- 7. display the permissions granted to the column. The user is an authorized user.

SELECT * FROMUSER_COL_PRIVS_RECD

-- 8. displays the system permissions of the currently logged-on user.

SELECT * FROMUSER_SYS_PRIVS

User Management

1. create user test and password test

Create user test identified by test;

2. Change the user test password to 123456.

Alter user test identified by 123456;

3. Unlock user SCOTT

Alter user scott account unlock;

Create a database connection

 

CREATE [PUBLIC] database link connection name

CONNECT username identified by Login Password

USING database instance name

Migration from 32-bit to 64-bit for a single Oracle instance

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian