oracle--User Management and rights assignment

Here are some examples to understand Oracle's knowledge points

User Login (skip user name and password login):

Sqlplus/nolog

Connect/as SYSDBA

A. User

1. Create a user (example)

Create user Olay identified by 123;

2. Delete the user (along with the objects owned by the user)

Drop user Olay Cascade;

3. Change user password (password)

Alter user Olay identified by 123456;

4. Unlock the locked user

Alter user Olay account unlock;

Two: Table space

1. Create a tablespace (create a user for Olay, password 123, and set the default tablespace to users, temp table space is temporary)

Create user Olay identified by 123 default Tablespace users temporary Tablespac e temp;

If the user can use a maximum size of 10MB on the Users table space, the

Create user Olay identified by 123 default tablespace users temporary tablespace temp

Quota 10m on users;

If the user uses the users table space that is not controlled by size, the

Create user Olay identified by 123 default tablespace users temporary tablespace temp

Quota Unlimited on users;

Note: If you want to disable users from using a tablespace, set the quota to 0 by quota

2. Delete table space (XXX is the tablespace name)

Drop tablespace XXX including contents and datafiles;

Warm tips:

After creating the user, you need to pay attention to the following points:

1. If you do not specify a default tablespace when creating a user, Oracle will use the system table space as the user default table space

2. If you do not specify a temporary tablespace when creating a user, Oracle will use the default tablespace as the user's temporary table space

3. The initial establishment of the user without any permissions, must be authorized before the operation

4. If the user is not set up to use the size of the table space, then the user has a quota of 0 for that table space.

Three: User Rights Management

There are two categories of permissions in an Oracle database: System permissions and Object permissions

The following describes two kinds of permissions, respectively

1 System permissions

(1) System permissions refer to the right to execute a specific type of SQL command. It is used to control one or a set of database operations that a user can perform

Oracle provides more than 160 system permissions, often with:

Create session connection database CREATE table build tables create view build views create public synonym synonyms

CREATE PROCEDURE build Process function package CREATE TRIGGER build trigger Create cluster cluster

More available data dictionary (SYSTEM_PRIVILEGE_MAP)

SELECT * from System_privilege_map order by name;

(2) Authorization

If the database administrator gives user a system permissions with the grant command with the WITH admin option, that user A has permission to grant the system granted permissions to User B. In this case,

If an administrator uses the REVOKE command to revoke system permissions for a user, system permissions for User B are still valid (this differs from object permissions)

Case: Creation of two user ken,tom. System grants User Ken (create session and CREATE table) two permissions and is granted with the WITH admin option

Grant create session, create table to Ken with admin option;

At this point Ken will be able to get the two permissions it has to continue to forward to other users. So you can log in to Ken's user and give Tom permission.

Reclaim System permissions

Revoke create session, create table from Ken;

2. Object permissions

Refers to the right to access other schema objects, the user can directly access their own schema object, but if you want to access the object of another scenario, you must have access to the object's permissions

The common object permissions are:

Alter DELETE SELECT Insert Update index (index) references (reference) execute (EXECUTE)

More to see all object permissions provided by Oracle

Select distinct privilege from Aba_tab_privs;

For example, if a Smith user wants to access the Scott.emp table (Scott: scheme, EMP: table), you must have permission to the object on the Scott.emp table.

Grant SELECT, INSERT, delete on scott.emp to Smith;

Reclaim Object permissions

Revoke SELECT, INSERT, delete on scott.emp from Smith;

Warm tips:

If the database administrator recycles the object permissions of a user with the revoke command, User B's object permissions are also recycled.

Reference reading:

Oracle Tutorial: User-managed backup http://www.linuxidc.com/Linux/2011-10/44976.htm

Recommended reading:

Oracle Foundation teaches replication of databases via Rman http://www.linuxidc.com/Linux/2013-07/87072.htm

Rman Backup strategy Development reference content Http://www.linuxidc.com/Linux/2013-03/81695.htm

Rman Backup Learning Note http://www.linuxidc.com/Linux/2013-03/81892.htm

Oracle database Backup encrypted Rman encryption http://www.linuxidc.com/Linux/2013-03/80729.htm

See Oracle Feature page for more information on Oracle HTTP://WWW.LINUXIDC.COM/TOPICNEWS.ASPX?TID=12

oracle--User Management and rights assignment