Oracle442 Application Scenarios-----------role management

--------------------------------Role Management------------------------------------

First, the concept and characteristics of the role

1. What is a role?

A role is a collection of commands related to permissions. The primary purpose of using roles is to simplify the management of permissions.

2. What are the characteristics of a character?
A. Granting and reclaiming system permissions using GRANT and REVOKE
B. Roles can be assigned to roles and users, regardless of their own
C. Roles can be composed of system and object permissions
D. Ability to enable and disable roles
E. Ability to designate a password
F. Roles are not owned by any user, no matter what the program
G. Roles have their own descriptive narrative in the data dictionary

Ii. defining roles ahead of time

Defining roles in advance refers to the roles provided by Oracle, each of which is used to run some specific management tasks, and we describe the frequently used pre-defined role connect,resource,dba.

1. Connect role

The Connect role has most of the permissions that a general application developer needs. When a user is established, in most cases it is sufficient to grant the connect and resource roles to the user only.

Then the Connect role has the following system permissions:

Alter session

Create cluster

Create DATABASE link

Create session

CREATE view

Create sequence

2. Resource role

The resource role has other permissions that the application developer needs, such as setting up stored procedures, triggers, and so on. It is important to note that the resource role implies unlimited tablespace system permissions.

The resource role includes the following system permissions:

Create cluster

Create Indextype

CREATE table

Create sequence

Create type

CREATE PROCEDURE

Create Trigger

3. DBA role

The DBA role has full system permissions. And with the admin option option, the default DBA user is SYS and the system they are able to grant other users no matter what the systems permissions.

Note, however, that the DBA role does not have the privileges of SYSDBA and sysoper (starting and shutting down the database).

Oracle Permissions Settings

First, the Classification of rights:
System permissions: The system specifies the user's permission to use the database. (System permissions are for users).

Entity permissions: A permission user's access to other users ' tables or views. (For a table or view).

Second, System Rights Management:

1, System Permissions classification:
DBA: Has all the privileges, is the system's highest privilege, only has the DBA ability to create the database structure.
RESOURCE: Users with RESOURCE permissions can only create entities and cannot create database structures.

Connect: A user with connect permission can only log in to Oracle. Cannot create an entity and cannot create a database structure.
For normal users: Grant Connect, resource permissions.
For DBA administration users: Grant Connect,resource, dba authority.

2, System Authority authorization command:
[System permissions can only be granted by the DBA User: sys, system (which can only be two users at the beginning)]
Authorization Commands:sql> Grant Connect, resource, DBA to UserName1 [, username2] ...;
[Ordinary users are authorized to have the same user rights as the system.] However, you can never reach the same permissions as the SYS user. The permissions of the system user can also be recycled. ]
-------------------------------------------------------------------------------

Scenario 36: Viewing user information in Enterprise Manager

Scenario 37: Use Dba_users view to view user information

SELECT username,password,lock_date,expiry_date,profile,account_status,created from Dba_users;

Scenario 38: Creating a user in Enterprise Manager

Application Scenario 39:
To create a new user:
Create user Hrman identified by Haixu default tablespace users temporary tablespace temp;

Query whether the user exists: (note. Although the above creation user is username is lowercase hrman but in the database deposit username word is: hrman)

Select username Account_status created from dba_users where username= ' Hrman ';

Scenario 40: Changing user attributes in Enterprise Manager

Scenario 41: Changing user information with the ALTER USER statement
1, change the user password:
ALTER user<username> identified by< NEW password>

Eg:alter USER Userman identified by Newpasswrod;
2. Set Password expiration:
ALTER USER Userman Passwrod EXPIRE;
3. Lock the User:
ALTER USER Userman account LOCK;
4. Unlocking the user
ALTER USER Userman account UNLOCK;

Scenario 42: Managing User Rights in Enterprise Manager

Scenario 43: Using SQL statements to manage user permissions
1.grant< Heartache Permissions > to <username>
Eg:grant SYSDBA to Hrman;
GRANT CREATE SESSION to Hrman;
GRANT CREATE TABLE to Hrman;
GRANT CREATE SEQUENCE to Hrman;
Grant create user, change, delete user right
GRANT CREATE user,alter User,drop USER, to Hrman;
2. Granting data Object permissions
GRANT < Data object permissions >ON< Data Objects >TO<username>
Eg:grant SELECT on Hrman USERS to DEPARTMENT;
GRANT INSERT on Hrman USERS to DEPARTMENT;
GRANT UPDATE on Hrman USERS to DEPARTMENT;
GRANT DELETE on Hrman USERS to DEPARTMENT;
3. Revoke User Rights
REVOKE < permissions or roles >FROM<username>
Eg:revoke SYSDBA from Hrman;

Scenario 44: Allocate space quotas for your app
The amount of space that the user can occupy in the specified tablespace.
ALTER user<username>quota< Space Quota size >ON< table space >
Eg:alter USER Hrman QUOTA 100M on USERS;

Scenario 45: Deleting a user

DROP USER Hrman;

Scenario 46: Viewing user reply information
COL USERNAME FORMAT A10
COL user# FORMAT A10
COL TYPE FORMAT A10
COL Logon_time A20
COL PROCESS FORMAT A10
COL program FORMAT A20
SELECT username,user#,type,logon_time,process,program,status from V$session;

Scenario 47: Viewing User rights information
COL GRANTEE Fromat A15
COL table_name FORMAT A15
COL Priviege FORMAT A15
COL PRIVILEGE FORMAT A15
SELECT Grantee,table_name,privilege grantor from Dba_tab_privs WHERE rownum<=10;

Scenario 48: Using a password file
Orapwd FILE = filename PASSWORD = PASSWORD ENTRIES = max_users force = <y/n>

The information of the password file is stored in the view v$pwfile_users,
SELECT * from V$pwfile_users;

Scenario 49: A workaround to forget the DBA password
Connect to Sqlplus as SYSDBA:
Sqlplus "/as sysdba"
The user password to change sys is newpassword:
ALTER USER SYS identified by Newpasswrod;
COMMIT;
Last Test Connection:
CONN Sys/as SYSDBA
Enter Password:
NewPassword

Scenario 50: View information for a role
SELECT * from Dba_roles; CONN

Scenario 51: Creating a role in Enterprise Manager

Scenario 52: Creating roles using the Create ROLE statement
CREATE role< role Name >identified by < verify password >
Eg:create ROLE MYROLE1 identified by MYROLEPWD;

Scenario 53: Authorizing the role

Scenario 54: Specifying the user's role

GRANT <role>to <USERNAME>
Eg:grant CONNECT to Hrman;
To cancel a user role using revoke:
REVOKE <ROLES> from<username>
Eg:revoke CONNECT from Hrman;

Scenario 55: Changing Role properties
Cancels the password validation for the role. Discard roles
ALTER ROLE MYROLE1 not identified;

Scenario 56: Deleting a role
DROP ROLE MYROLE1;

Scenario 57: View role information for a user
SELECT * from Dba_role_privs;

Oracle442 Application Scenarios-----------role management