ORACLE-Rights Management for users and roles

In Oracle, authorization is required to use the user after it is created.

First, user management

1. User and Role information query

-- query All users SQLSelect * from dba_users; -- system permissions granted by the user or role select * from Dba_sys_privs; -- All permissions on the data object SQL>Select * from Dba_tab_privs; -- View the permissions and roles of the current user SQL>Select * from User_sys_privs; SQL>Select * from Role_sys_privs; -- Query yourself to grant permissions to other users in the case of SQLSelect * from User_tab_privs;

2. Authorization, lock and unlock, password notconsistent, user deletion, permission recovery, etc.

Grant create  session to name// creation sessiongrant CREATE table to name// Create tables Grant Create view to name// Creating views Grant unlimited tablespace to name// no tablespace usage limit Grant  DBA to name// Administrator Authorization

--User lock/unlock
Alter user username account Lock/unlock;

--Notconsistent User password
Alter user username identified by YourPassword;

--Delete the user and delete all the tables below the user
Drop user Scott Cascade;

--Permission recovery

Revoke CREATE TABLE from Scott cascade constraints;

3. User resource configuration (cpu/memory and other resource allocation)

When a user is created, the resource configuration is not specified and is automatically set to default.

The following example password resource is assigned to the user:

 - +/unlimited; SQL>alter user Scott profile  pro_name_xx; SQL>alter user Scott profile  DEFAULT; SQL>drop profile Pwd_profile;

Resources that limit the number of times a user attempts to log on failed:

3;

Second, role management

Here are the permissions relationships that are owned by several roles in the database, and you can view all the roles with select * from Dba_roles:

Name	Script
Connect	Sql. BSQ	ALTER SESSION
		Create Cluster/database Link/sequence/session/synonym/table/view
Resource	Sql. BSQ	Create Cluster/indextype/operator/producedure/sequence/table/tigger/type
Dba	Sql. BSQ	All administrative rights
Exp_full_database	Catexp. Sql	Export permissions: Select any table Backup any table Execute any producedure Execute any type .......
Imp_full_database	Catexp. Sql	All Import Permissions
......

The tables related to the role are as follows:

Dba_col_privs all permissions on a database column
Dba_role_privs show roles that have been granted to users or other roles
Dba_tab_privs all permissions on a database object
Dba_sys_privs a user or role has been granted system permissions

1. Creation, authorization, modification and deletion of roles

The user can customize the role and then grant one or more of the permissions above

Sql>create Roke xxx identified by xxx;  -- You can set a password for a role, you need to enter a password to change and delete a role to execute. Note: The role name and user name are in the same domain and cannot have duplicate names.
SQL> Grantcreate session to Role_xxx;--Grant the Create session permission to the role role_xxx
SQL>  assigning roles to users

SQL> revoke roke_r from Scott;  --Recycling roles

Sql>alter Roke xxx not identified;

Sql>alter user user_xxx default role roke_xxx;

sql> ALTER user user_xxx default role all expect role_xxx;

Sql>drop role Role_name;

ORACLE-Rights Management for users and roles