ORACLE user permission management and oracle permission management
Reference from: http://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006856
Just pick up what to say !!!
1. User Management
1. Create a user
Create user dylan
Identified by 123.
Default tablespace users -- specify the tablespace
Quota 100 m on users -- specify the quota
Temporary tablespace temp -- specify temporary tablespace
Profile default; -- specifies the profile
Grant create session to dylan; -- grant the session creation permission
-- Create session
-- Unlimited tablespace
2. modify a user
Alter user dylan
Identified by 234.
Default tablespace users_new -- specify the tablespace
Quota 100 m on users_new -- specify the quota
Temporary tablespace temp -- specify temporary tablespace
Profile default;
3. delete a user
Drop user dylan cascade;
4. query user and profile information
DBA_USERS describe all database users
ALL_USERS lists the database users visible to the current user.
USER_USERS only describes the current user
DBA_TS_QUOTAS description user tablespace quota
USER_TS_QUOTAS describes the parameters of the current user password profile.
USER_RESOURCE_LIMITS: displays the current user resource limit
DBA_PROFILES: displays all profiles and restrictions
RESOURCE_COST list the consumption of each resource
V $ SESSION list information of each current SESSION, including the user name
V $ SESSTAT list user session statistics
V $ STATNAME: displays V $ SESSTAT statistics for translation
PROXY_USERS description can represent users of other user identities
Select username, VALUE | 'bytes '"Current UGA memory"
From v $ SESSION sess, V $ SESSTAT stat, V $ STATNAME name
WHERE sess. SID = stat. SID
AND stat. STATISTIC # = name. STATISTIC #
AND name. NAME = 'session uga memory ';
5. Create a profile.
Create profile query_limit
Sessions_per_user 1
Idle_time 30
Connect_time 60;
Alter user dylan profile query_limit;
△Meaning of the Profile options:
COMPOSITE_LIMIT comprehensive resource limit (total quota of resources that can be consumed by one session)
SESSIONS_PER_USER maximum number of sessions per user
CPU_PER_SESSION CPU usage time for each session (1/100 s)
CPU_PER_CALL CPU usage time for each call (1/100 s)
LOGICAL_READS_PER_SESSION maximum number of data blocks read by each session
LOGICAL_READS_PER_CALL: Maximum number of data blocks read by each call
IDLE_TIME maximum idle time (minutes)
CONNECT_TIME maximum connection time (minutes)
PRIVATE_SGA private SGA (system global zone, SQL zone, and PLSQL zone)
FAILED_LOGIN_ATTEMPTS: Maximum number of failed logon attempts (exceeding the user's lock)
PASSWORD_LIFE_TIME password lifecycle (days)
How many days can PASSWORD_REUSE_TIME be reused after the password is modified?
How many times must the PASSWORD_REUSE_MAX password be modified before it is reused?
PASSWORD_VERIFY_FUNCTION password verification function
PASSWORD_LOCK_TIME password lock time
PASSWORD_GRACE_TIME Change Password grace period days
--
For more information, see Oracle overview document. ppt
Drop profile query_limit CASCADE; -- after cascade, the current connected user automatically switches to the default profile
2. User Permissions and Roles
1. System Permissions
-- △ Parameter: O7_DICTIONARY_ACCESSIBILITY default value: false data dictionary Protection
SELECT_CATALOG_ROLE (role) common users access SYS data dictionary View
EXECUTE_CATALOG_ROLE (role) executes the directory role and can execute all system packages
DELETE_CATALOG_ROLE (role) delete system audit table records (AUD $)
Select any dictionary to access ANY object in SYS Mode
2. Object Permissions
3. User Role
-- Pre-defined role
CONNECT CREATE SESSION
Resource create cluster, create indextype, create operator, create procedure, create sequence, create table, create trigger, CREATE TYPE
All DBA system permissions WITH ADMIN OPTION
EXP_FULL_DATABASE: EXECUTE full database export permission; select any table, backup any table, execute any procedure, execute any type, administer resource manager, and INSERT, DELETE, and UPDATE on the tables SYS. INCVID, SYS. INCFIL, and SYS. INCEXP.
There are also roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE
IMP_FULL_DATABASE: Execute full-Database Import Permission. For details, see DBA_SYS_PRIVS.
RECOVERY_CATALOG_OWNER restores directory owner permissions; create session, alter session, create synonym, create view, create database link, create table, create cluster, create sequence, create trigger, and CREATE PROCEDURE
HS_ADMIN_ROLE protects access to dictionary tables of heterogeneous services; SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE
AQ_USER_ROLE has been eliminated; it is only retained to be compatible with 8.0. It provides the execution permission for the DBMS_AQ and DBMS_AQIN packages.
AQ_ADMINISTRATOR_ROLE provides advanced QUEUE management permissions; enqueue any queue, dequeue any queue, and manage any queue, SELECT privileges on AQ tables and EXECUTE privileges on AQ packages
1. Create a role
Create role dml identified by dml123;
Drop role dml;
Grant select, insert, update, select on hr. employees to dml;
Grant connect to dml;
Grant dml to dylan;
-- Column-level authorization
Grant insert (ename, job) on scott. emp to dml;
Revoke connect from dml;
Revoke select, insert, update, select on hr. employees from dml;
Revoke insert (ename, job) on scott. emp from dml;
-- Revoke references on dept FROM jward cascade constraints;
-- Enable role
Set role dml identified by dml123;
-- Disable all role
Set role none;
-- Specify role
Alter user dylan default role dml;
2. delete a role
Drop role dml;
3. with admin option and with grant option
In terms of system permission authorization corresponding to the with admin option, after authorization, permissions cannot be revoked cascade;
With grant option: for object permission authorization, permissions can be revoked cascade;
4. View authorization information:
Select grantee, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'ployees' and OWNER = 'hr ';
5. Permission information data dictionary:
DBA_COL_PRIVS describes authorization for all column objects in the database
ALL_COL_PRIVS
USER_COL_PRIVS
ALL_COL_PRIVS_MADE lists the following object authorizations of the current user. (The current user is the object owner or Authorizer)
USER_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD description column object authorization (grant permissions to the current user or PUBLIC account)
USER_COL_PRIVS_RECD
The DBA_TAB_PRIVS DBA view lists ALL object authorization in the database. The ALL view is for the USER or PUBLIC as the authorized USER.
The figure shows the target user.
ALL_TAB_PRIVS
USER_TAB_PRIVS
ALL_TAB_PRIVS_MADE ALL view lists ALL object authorizations (for the current user as the authorizer or object owner );
USER_TAB_PRIVS_MADE USER view lists all object authorization (for the current USER as the object owner)
ALL_TAB_PRIVS_RECD ALL view lists object authorization (for the USER or the PUBLIC is the Authorized Person). The USER view lists object authorization (for the current USER as the Authorized Person)
USER_TAB_PRIVS_RECD
DBA_ROLES: list all roles in the database
DBA_ROLE_PRIVS list permissions of a role
USER_ROLE_PRIVS
DBA_SYS_PRIVS lists all system permissions granted to users and Roles
USER_SYS_PRIVS lists the system permissions granted to the current user
ROLE_ROLE_PRIVS describes the role to which a role is assigned.
ROLE_SYS_PRIVS includes the role's system Permissions
ROLE_TAB_PRIVS contains the role's object permissions
SESSION_PRIVS: List currently enabled Permissions
SESSION_ROLES: List currently enabled roles
-- View user object permissions
SELECT TABLE_NAME, PRIVILEGE, grantable from DBA_TAB_PRIVS
Where grantee = 'jward ';
-------------------------------------------------------
Dylan Presents.
