References organized from: http://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006856
Just pick dry words!!!
One user management
1. Create user
Create user Dylan
Identified by 123
Default Tablespace users--Specify table space
Quota 100m on users--Specify quotas
Temporary tablespace Temp--Specify temporary table space
Profile default; --Specify Profile
Grant create session to Dylan; --Grant Create session permissions
--create Session Creation Sessions
--unlimited tablespace do not restrict the use of table spaces
2, modify the user
Alter user Dylan
Identified by 234
Default Tablespace users_new--Specify table space
Quota 100m on users_new--Specify quotas
Temporary tablespace Temp--Specify temporary table space
Profile default;
3. Delete users
Drop user Dylan Cascade;
4. Query user and profile information
Dba_users Describe all database users
All_users Lists the database users that are visible to the current user, not a single description
User_users Describe the current user only
Dba_ts_quotas describing user table space quotas
User_ts_quotas describes the current user password profile parameters
User_resource_limits Show Current user resource limits
Dba_profiles Show all profiles and their limitations
Resource_cost List each resource consumption
V$session Lists each current session information including the user name
V$sesstat List User Session statistics
V$statname Show V$sesstat statistics for translation
Proxy_users Describe a user who can represent another user
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 Creating Profile Profiles
Create Profile Query_limit
Sessions_per_user 1
Idle_time 30
Connect_time 60;
Alter user Dylan profile Query_limit;
The profile options mean:
Composite_limit Comprehensive resource limits (the total amount of resources a session can consume)
Sessions_per_user Maximum number of sessions per user
Cpu_per_session CPU Elapsed time per session (1/100s)
Cpu_per_call CPU Elapsed time per call (1/100s)
Logical_reads_per_session Maximum number of data blocks read per session
Logical_reads_per_call Maximum number of data blocks per call read
Idle_time Maximum idle time (minutes)
Connect_time Maximum connection time (minutes)
Private_sga Private SGA (System Global, SQL and Plsql zones)
Failed_login_attempts Maximum failed logon attempts (exceeding user lock)
Password_life_time Password life cycle (days)
Password_reuse_time How many days before password modification must be reused
Password_reuse_max How many times must the password be modified before it is reused?
Password_verify_function Password validation functions
Password_lock_time Password Lock Timing Room
Password_grace_time Change Password grace period days
--
See the Oracle Profile for details. ppt
DROP profile Query_limit CASCADE; --cascade the current connected user automatically switches to default profile
two user rights and roles
1. System permissions
--Parameter: o7_dictionary_accessibility default value: false Data Dictionary protection
Select_catalog_role (ROLE)Normal user access to SYS data dictionary view
Execute_catalog_role (ROLE) Perform directory roles and be able to execute all system packages
Delete_catalog_role (ROLE) Delete system Audit table records (aud$)
SELECT any DICTIONARY Accessing any object in the SYS mode
2. Object permissions
3. User Role
--pre-defined roles
CONNECT CREATE SESSION
RESOURCE Create CLUSTER, create Indextype, create OPERATOR, create PROCEDURE, create SEQUENCE, create TABLE, createTRIGGER, CREATE TYPE
Dba All system permissions with ADMIN OPTION
Exp_full_database Perform full-Library export permissions; SELECT any table, BACKUP any table, execute any PROCEDURE, execute any TYPE, administerRESOURCE 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 Perform full library import permissions; see Dba_sys_privs
Recovery_catalog_owner Restore 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 Protection of heterogeneous Service data dictionary table access; Select_catalog_role and Execute_catalog_role
Aq_user_role Retired, reserved for compatibility only 8.0; Provides execute permissions for Dbms_aq and Dbms_aqin packages
Aq_administrator_role Provides administrative advanced queue 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;
--Assign role
ALTER USER Dylan DEFAULT ROLE DML;
2. Delete a role
Drop role DML;
3. With admin option and with GRANT option
With admin option to assign rights to the system, it is not allowed to cascade to recover the rights;
With GRANT option, the rights of the object are assigned, and the right can be cascaded to recover the permission;
4. View authorization information:
SELECT GRANTEE, OWNER, grantor, PRIVILEGE, grantable
From Dba_tab_privs
WHERE table_name = ' EMPLOYEES ' and OWNER = ' HR ';
5. Permission Information Data dictionary:
Dba_col_privs Describes all Column object authorizations in the database
All_col_privs
User_col_privs
All_col_privs_made Lists the current user authorization for the following objects. (The current user is an object owner or an authorized person)
User_col_privs_made
All_col_privs_recd Description Column Object Authorization (for the current user or public is the grantee)
User_col_privs_recd
Dba_tab_privs The DBA view lists all object authorizations in the database, and the all view is granted to the user or public;
Figure is granted for the current user.
All_tab_privs
User_tab_privs
All_tab_privs_made The all View lists all object authorizations (for the current user is the grantee or object owner);
User_tab_privs_made User View lists all object authorizations (for the current user is the object owner)
All_tab_privs_recd The all View Lists object authorizations (for users or public is the grantee).User View Lists object authorization (for the current user as the grantee)
User_tab_privs_recd
Dba_roles List all roles in the database
Dba_role_privs List permissions owned by a role
User_role_privs
Dba_sys_privs List all system permissions granted to users and roles
User_sys_privs List system permissions granted to the current user
Role_role_privs Describe the role that is granted to a role
Role_sys_privs System permissions that contain roles
Role_tab_privs object permissions that contain roles
Session_privs list the currently enabled permissions
Session_roles List the roles that are currently enabled
--View User object permissions
SELECT table_name, PRIVILEGE, grantable from Dba_tab_privs
WHERE GRANTEE = ' Jward ';
-------------------------------------------------------
Dylan presents.
Oracle User Rights Management
