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, and 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 any role and user other than themselves
C. Roles can be composed of system and object permissions
D. Roles can be enabled and disabled
E. Can specify a password
F. Roles are not owned by any user and are not in any scenario
G. Roles have their own descriptions in the data dictionary

Second, pre-defined roles

Predefined roles refer to the roles provided by Oracle, each of which is used to perform specific administrative tasks, and we describe the commonly used predefined role connect,resource,dba.

1. Connect role

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

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 additional 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 contains 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 all of the system permissions, and with the admin option option, the default DBA user is SYS and the system they can grant to other users any 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 full privileges, is the highest system privilege, and only the DBA can create the database structure.
RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.
Connect: A user with connect permission can only log on to Oracle, not 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 (only two users at the beginning)]
Authorization Command:sql> Grant Connect, resource, DBA to username 1 [, user name 2] ...;
[An ordinary user with authorization can have the same user rights as the system, but never the same permissions as the SYS user, and 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;

The query exists for the user: (Note that although the user name created above is a lowercase hrman, the user name is stored in the database as: Hrman)

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

Scenario 40: Modifying user properties in Enterprise Manager

Scenario 41: Modifying user information by using the ALTER USER statement
1, modify 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 < user name >
Eg:grant SYSDBA to Hrman;
GRANT CREATE SESSION to Hrman;
GRANT CREATE TABLE to Hrman;
GRANT CREATE SEQUENCE to Hrman;
Grant create user, modify, delete user permissions
GRANT CREATE user,alter User,drop USER, to Hrman;
2. Granting data Object permissions
GRANT < Data object permissions >ON< Data Objects >TO< user names >
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< user names >
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< user name >QUOTA< space quota size >ON< tablespace >
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 modify 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: Modifying 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