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

Create and manage roles

A role is a set of system or object permissions that can be granted or revoked as a unit, and permissions that have been granted can be temporarily activated or disabled in the session.

1. Create roles and grant permissions

CREATEROLE rolename

Case study:

SQL > createrole hr_junior

SQL > grantcreate session to hr_junior

SQL > grant select on hr.regions to hr_junior

SQL > grantselect on hr.locations to hr_junior

SQL > grantselect on hr.countries to hr_junior

SQL > grantselect on hr.job_history to hr_junior

SQL > grantselect on hr.departments to hr_junior

SQL > grantselect on hr.jobs to hr_junior

SQL > grantselect on hr.employees to hr_junior

SQL > createrole hr_senior

SQL > granthr_junior to hr_senior with admin option

SQL > grantinsert, update, delete on hr.employees to hr_senior

SQL > grantinsert, update, delete on hr.job_history to hr_senior

SQL > createrole hr_manager

SQL > granthr_senior to hr_manager with admin option

SQL > grantall on hr.regions to hr_manager

SQL > grantall on hr.locations to hr_manager

SQL > grantall on hr.countries to hr_manager

SQL > grantall on hr.departments to hr_manager

SQL > grantall on hr.job_history to hr_manager

SQL > grantall on hr.jobs to hr_manager

SQL > grantall on hr.employees to hr_manager

SQL > granthr_manager to scott

SQL > granthr_junior to peenboo

2. Predefined roles

There are at least 50 predefined roles in the Oracle database, and each DBA must be familiar with the roles:

CONNECT (connection)-for backward compatibility, only CREATE SESSION permissions are available in 11g.

RESOURCE is also used for backward compatibility, and this role can create database objects (such as tables) and process objects (such as pl/sql procedures). The role also includes UNLIMITED TABLESPACE permissions.

DBA has most system permissions, as well as multiple object permissions and roles, and any user granted DBA permissions can manage almost all aspects of the database (except startup and shutdown).

SELECT_CATALOG_ROLE has more than 2000 object permissions for data dictionary objects, but no system permissions or user permissions. This is useful for new administrators who must monitor and report the database, but do not see user data.

SCHEDUALER_ADMIN has the system permissions required to manage scheduler jobs for the scheduling service.

PUBLIC this role is always granted to each database user account. If a permission is granted to PUBLIC, it is granted to all users.

SQL > GRANT select onhr.regions to public;-all users have the right to query hr.regions

3. Enable roles

SQL > select* from dba_role_privs where grantee = 'PEENBOO'

-- see what roles have been granted to PEENBOO

SQL > alter user peenboo default role none;-- change the default behavior

When peenboo logs in, no roles are enabled

SQL > grantconnect to peenboo

SQL > alteruser peenboo default role connect

SQL > select* from dba_role_privs where grantee= 'PEENBOO'

In an application, you can embed software commands to enable the HR_JUNIOR role. The basic commands to enable this role in a session are:

SET ROLE rolename

For security reasons, you can create roles using the following syntax:

CREATEROLE rolename IDENTIFIED USING procedure_name

-- this role can only be enabled by running the procedure_name-named PL/SQL procedure. This process can perform any number of checks.

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.