Shulou(Shulou.com)06/01 Report--

There must be a principle in database management, that is, the principle of minimizing permissions. If necessary, the corresponding permissions should be given.

The daily administrative tasks of the database administrator for users are as follows:

-create a new user

-Delete a user

-Delete the table

-back up the table

1. Create a user yuri and set the password to password.

Create user yuri identified by password

2. After the user is created, DBA will grant the user some system permissions.

Take application developers as an example, they generally have the following system permissions:

-create session

-create table

-create sequence

-create view

-create procedure

For example, the following permissions are granted for yuri

Grant create session, create table, create sequence, create view to yuri

Second, the role of roles

The purpose of a role is to define a role with which permissions it has and to assign the user to that role. Facilitate unified management

1. Create a role

Create role mang

2. Give permissions to the role

Grant create table, create view to mang

3. Grant roles to users

Grant mang to yuri

DBA can create users and change passwords.

Users can use the ALTER USER statement to change the password themselves.

4. Change the password of the yuri user to 1q2w3e

Alter user yuri identified by 1q2w3e

Object permissions

Object permission TableViewSequenceALTER

DELETE

INDEX

INSERT

REFERENCES

SELECT

UPDATE

Different objects have different object permissions

The owner of the object has all permissions

The owner of the object can assign permissions to the outside

Syntax:

Grant object_priv [(columns)] on object to {user | role | public} [with grant option]

1. Assign the right to query employees table to yuri users.

Grant select on employees to yuri

Grant the specified columns to the yuri user and mang role to update department_name and location_id

Grant update (department_name, location_id) on departments to yuri, mang

WITH GRANT OPTION and PUBLIC keywords

WITH GRANT OPTION gives users the right to assign permissions as well.

Grant select, insert on departments to demo with grant option

Assign permissions to all users in the database

Grant select on alice.departments to public

Confirm the permissions granted

The data dictionary view describes the system permissions owned by the ROLE_SYS_PRIVS role, the object permissions owned by the ROLE_TAB_PRIVS role, the role owned by the USER_ROLE_PRIVS user, the system permissions owned by the USER_SYS_PRIVS user, the system permissions assigned by the USER_TAB_PRIVS_MADE user, about the table object permissions owned by the USER_TAB_PRIVS_RECD user, the object permissions assigned by the USER_COL_PRIVS_MADE user, about the columns. Object permissions on columns that the USER_COL_PRIVS_RECD user has

Revoke object permissions

Revoke permissions using the REVOKE statement

Permissions assigned using the WITH GRANT OPTION clause are also revoked

Syntax:

Revoke {privilege [, privilege...] | all} on object from {user [, user...] | role | public} [cascade constraints]

1. Revoke the SELECT and INSERT permissions granted to the employees table of yuri users.

Revoke select, instert on employees from yuri

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.