Shulou(Shulou.com)06/01 Report--

-user and Rights Management-

-1, user--

Create User username Identified By "password"

Default Tablespace tablespace

-specify a default tablespace. If not, SYSTEM tablespace will be designated as the user's default tablespace. In order to improve database performance and facilitate data management, Oracle recommends that users specify a default tablespace--

Temporary Tablespace temporary tablespace

Quota space quota size On tablespace

Password Expire

Account Lock | Unlock

Click (here) to collapse or open

Create User user1 Identified By "1234"

Default Tablespace Users

Temporary Tablespace temp

Quota 20m On Users-if you want users to have unlimited space quotas on all tablespaces, simply assign them Unlimited tablespaces system permissions-

Password Expire

Account Unlock;-- deletes users--

Drop User user name

It should be noted that if a user's schema already contains some database objects, the user cannot delete it directly and must specify cascade--

Drop User scott Cascade

-- the relevant information of the user is obtained from the data dictionary view dba_users

Select username,Password,default_tablespace,account_status From dba_users;-- modifies user password--

Password user name-if no user name is specified, it defaults to the current user

Alter User user Identified By "New password"

Alter can also modify other information--

Alter User user1

Default Tablespace ts1

Temporary Tablespace ts1

Management of Quota Unlimited On ts1;-- system permissions--

-- grant command syntax for granting permissions to the user--

Grant permission 1, permission 2. To user 1, user 2. With Admin Option;-- the advantage of with admin option is that the holder of the right can grant the right to other users--

-permissions include system permissions and object permissions-

-system permissions--

Create Session,Create Table,Create Resource,Create View,Create User,Create Trigger,Create Tablespace,Create procedure.

Alter Session,Database,User

-- in order for a user to operate the database, at least 3 permissions with create session,create table and specified (table) space quotas are required--

Grant Create Session,Create Table,Unlimited Tablespace To user name

-- recall permissions--

-- Note: system permissions can be delegated, but not indirectly when reclaimed--

Revoke system permissions 1, system permissions 2.

From user 1, user 2.

-- object permissions are mainly access to data in database objects, which are mainly for ordinary users--

Grant object permission 1 (column name), object permission 2 (column name).

On object To user 1, user 2. With Grant Option

Grant Select On dept To user1 With Grant Option

Grant Insert (empno), Update (sal) On emp To user1,user2

Grant Execute On functional To user1-- reclaim permissions--

-- permission insert,update,references can specify related columns when assigning them, but not columns when reclaiming, but only table names--

Revoke object permissions 1, object permissions 2... On object

From user 1, user 2.

Revoke Update (sal) On emp From user2-- error statement--

All system permissions in the Revoke Update On emp From user2-- database--

Select Name From system_privilege_map;--sys queries any user's system permissions from the data dictionary view dba_sys_privs--

Ordinary Select grantee,Privilege,admin_option From dba_sys_privs Where grantee='SCOTT'---- users query the system permissions directly obtained by users through user_sys_privs, that is, the system permissions granted to the current user through the grant command

Select username,Privilege,admin_option From user_sys_privs

-- you can query a user's system permissions in the current session from the data dictionary view session_privs

-- session_privs permissions = = permissions obtained directly + system permissions indirectly obtained by the user from the role--

-- data dictionary views related to object permissions: dba_tab_privs,dba_col_privs,user_tab_privs and user_col_privs--

Click (here) to collapse or open

-- dba_tab_privs queries any user's object permissions

Select grantee,Privilege,grantor,table_name,grantable From dba_tab_privs

Where grantee='USER1'

-- dba_col_privs queries the user's permissions on the column--

Select grantee,table_name,column_name,Privilege,grantor,grantable

From dba_col_privs Where grantee='USER1';Select grantee,table_name,column_name,Privilege,grantor,grantable

From user_tab_privs Where grantee='SCOTT';-- role--

-- connect role contains permissions such as create Session--

/ * resource role contains permissions such as crate sequence,create trigger,create cluster,create procedure

Create type,create operator,create table, create indextype * /

Create Role role name

Drop Role role name

Grant Select On emp To role1

Revoke Select On emp From role1

-- query for role information--

/ * all roles in the dba_roles record database

Dba_role_privs records all roles granted to a user or another role

User_role_privs records all roles granted to the current user

Role_role_privs records other roles contained in one role

Role_sys_privs records the system permissions contained in a role

Role_tab_privs records the object permissions contained in a role

Session_roles records the roles used in the current session * /

-- get all the roles in the system--

Select Role From dba_roles-- queries the roles and default roles owned by user user1--

Select granted_role,default_role From dba_role_privs Where grantee='user1'-- queries the system permissions contained in the role connect--

The management of Select Privilege,admin_option From role_sys_privs Where Role='CONNECT'--profile

-- query the user's session information, force the termination of the user's session, and lock the user's account

Select username,Sid,serial# From v$session Where username='SCOTT'

Alter System Kill Session '144 and 7 are the sid and serial#-- of the user session respectively.

Alter User scott Account Lock;--profile creation--

Create Profile profile name Limit

Password restriction

Resource restriction

-- specify profile only one profile-- can be specified per user

Create User user2 Identified By "1234" Profile p1

Alter User scott Profile p1mure-the profile- specified for each user can be obtained from the data dictionary dba_users

Select Profile From dba_users Where username='SCOTT'

-- data dictionary dba_profiles--

Select resource_name,Limit From dba_profiles Where Profile='P1'

-- Delete profile--

Drop Profile profile name [Cascade];-- Profile restriction--

/ * number of failed logins allowed by Failed_Login_Attempts. Default is 10.

Password_Lock_Time account lock time. Default is 1 day.

The validity period of the Password_Life_Time password. Default is 180 days.

Extension of the validity period of Password_Grace_Time password. Default value is-week, 7 days.

The number of days that Password_Reuse_Time must pass in order to reuse passwords that have been used in the past

The number of times Password_Reuse_Max must use different passwords in order to reuse passwords that have been used in the past

Password_Reuse_Time and Password_Reuse_Max must be used together * /

-- if the user fails to log in for 3 times, the user account will be locked for 5 days--

Create Profile p1 Limit Failed_Login_Attempts 3 Password_Lock_Time 5Mui-users must change their passwords five times in 30 days before they can use the passwords they have used in the past

Create Profile p2 Limit Password_Reuse_Time 30 Password_Reuse_Max 5Mui-use profile to control the use of resources by users-

If you want profile to restrict users' use of system resources, you need to set the value of the parameter Resource_limit to True

Alter System Set resource_limit=TrueSessions_Per_User the number of concurrent sessions allowed by a user

The sum of Cpu time used by Cpu_Per_Session users in a session, in 0.01s

The number of blocks that Logical_Reads_Per_Session users can access within a session (including physical and logical reads)

Connect_Time the duration of a user's session, in minutes, after which the session is automatically disconnected

Idle_Time the continuous idle time allowed by a user session, in minutes, after which the session will be disconnected automatically

Private_Sga if the connection mode of the database server is in shared mode, this parameter is used to limit the Sga space allocated for a user session

The Cpu time, in 0.01 seconds, used by each command executed by the Cpu_Per_Call user

The number of blocks that can be accessed by each command executed by the Logical_Reads_Per_Call user

If you can limit the Cpu time consumed by users, the number of data blocks accessed, and so on, then you can effectively

Prevent situations such as the execution of an endless loop and accessing all data in a large table.

Create Profile p3 Limit

Sessions_Per_User 100

Logical_Reads_Per_Session 500

Connect_Time 5Create Profile p4 Limit

Cpu_Per_Call 10

Logical_Reads_Per_Call 50Universe * if no profile file is specified for the user, the user will be subject to the default profile restriction. If you want to apply the same restriction to all users, just modify

The restrictions contained in this default profile and do not specify other profile * / for the user

Select resource_name,Limit From dba_profiles Where Profile='DEFAULT'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.