Oracle study note 13 -- control User Permissions

The Function Room of the Database Control Language controls the user's access permissions to the database. The DBA determines the operation permissions of a user on a certain type of data. Oracle

GRANT permissions using the GRANT statement and REVOKE permissions using the REVOKE statement.

Permissions include system permissions and object permissions. system permissions are database permissions, and object permissions are the permissions for operating database objects.

Create user

Basic Syntax:

Create user user_name

Identified by password;

After the user is created successfully, the system administrator DBA will grant the user some permissions to complete the corresponding operations. Users who do not have certain operation permissions do not have

Method to complete the operation.

For example, create a user named test and password: test001.

Create user test
Identified by test001

However, the user and password created above cannot be used for logon. Because it is useless and does not have the create session permission. To grant this permission, use the grant statement.

Grant Permissions

Basic Syntax:

GRANT permission 1, permission 2,... TO user_name;

Application developers generally have the following permissions:

Create session (create session) create table (create table) create sequence (create sequence) create view (create view) create procedure (CREATE process)

For example, grant the user test the permission to create a session.

Grant create session to test;

After assigning the create session to the user, you can connect to the database normally, indicating that a session has been created. In this case

To create tables, sequences, and views, you must grant all the preceding permissions. After the create table permission is granted, the table cannot be created because

There is no tablespace. You must allocate table space to create a table.

The above operations are complicated. In fact, you can encapsulate Multiple permissions into one role, and grant the role to the user.

Role

Create a role:

Create role testrole;

Grant permissions to a role:

Grant create table, create view to testrole;

Grant a role to a user:

GRANT testrole TO test;

Oracle provides two main roles: CONNECT and RESOURCE. You can grant the two roles to the user directly.

Change User Password

Basic syntax

Alter user user_name

Identified by new_password;

To change the password.

Assign object permissions

Different objects have different object permissions. The object owner has all permissions, and the object owner can assign permissions to others. Basic Syntax:

GRANT operation ON other users. Table name TO user

For example, if the test user wants to access the emp table under the scott user and query and delete the table, he or she must have the permission to access the table.

GRANT select, delete ON scott. emp TO test;

Now, the test user can query and delete the emp table.

With grant option and PUBLIC keyword. With grant option is an authorized user and can grant permissions to others.

. Public is to assign rights to all users.

Revoke permissions

Basic syntax

Rovoke permission on user. Table name from user;

For example, revoke the test user's query and deletion permissions.

Revoke select, delete on scott. emp from test;

In addition to statement creation permissions, you can also create them directly on the Enterprise Manager console. Not here.