Oracle user management

Oracle user access control

1. Permissions
Oracle Database Security is divided:
System Security
Data security

System permission: database access permission
Object permission: operations on the Content of database objects

Schema: a collection of objects. Such as tables, views, and indexes.

Schema example:

Table, index, sequence, view, and other database objects under Scott user are collectively referred to as Scott schema.

Schema and user relationship: one-to-one correspondence.
Create a user in Oracle and a schema named after the user will be created.
To manage objects under the user.
Description: Select * from Scott. EMP;
Access the EMP object in Scott Schema

All object sets under Scott user are called Scott schema.

2. Oracle system permissions:

More than 166 Permissions
DBA has the highest system permissions. Typical permissions include:
Create user
Delete a user
Delete table
Backup table
......

2.1 create a user

[Oracle @ centos1 ~] $ Sqlplus/As sysdba

SQL * Plus: Release 11.2.0.1.0 production on Fri Aug 31 16:43:53 2012

Copyright (c) 1982,200 9, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the partitioning, OLAP, data mining and real application testing options

SQL> create user test identified by test;

User Created.

SQL> conn test/test;
Error:
ORA-01045: user test lacks create session privilege; logon denied
Warning: you are no longer connected to Oracle.

2.2 system permissions of users

After a user is created, the user does not have any system permissions. DBA can grant the user special system permissions:
Developers may need the following system permissions:
Create session
Create Table
Create Sequence
Create View
Create procedure

Syntax: grant privilege [, privilege...] to user [, user]

SQL> grant create session to test;

Grant succeeded.

SQL> conn test/test;
Connected.

SQL> Create Table T1 (ID number (4 ));
Create Table T1 (ID number (4 ))
*
Error at line 1:
ORA-01031: insufficient privileges

SQL> Conn/As sysdba
Connected.
SQL> grant CREATE TABLE to test;
Grant succeeded.

SQL> alter user test quota unlimited on users;
User altered.

SQL> conn test/test
Connected.
SQL> Create Table T1 (ID number (4 ));
Table created.

3. Role

Oracle introduces the role concept to simplify permission management.

For example, if you have four permissions: Create Table \ create view \ create session \ create Sequence
Allocate three users. If role is not introduced, allocate 12 users one by one.
If role Dev is introduced, you can grant these four permissions to Dev role, and then assign Dev role
In this way, you only need to perform the allocation three times.

Create a role

SQL> Create role dev;

Role created.

Grant permissions to a role
SQL> grant CREATE TABLE, create view, create session to Dev;

Grant succeeded.

Assign roles to users
SQL> grant Dev to test;

Grant succeeded.

Change User Password

SQL> grant Dev to test;

Grant succeeded.

Object permission:
Object table
View Sequence
Procedure
Alter Yes
Yes
Delete Yes
Yes
Execute Yes
Index Yes
Insert Yes
References Yes
Select Yes
Yes
Update yesyes

Object permission authorization

Grant object_priv on object to [user | role | public] with grant option

Example:

SQL> grant select on Scott. EMP to test;

Grant succeeded.

SQL> grant select on Scott. dept to Dev, test;

Grant succeeded.