Home » Oracle » 10, users and security

10, users and security

10, users and security, in need of friends can refer to.

One, user and mode

Pattern is a collection of database objects owned by the user. The Oracle, the object is to organize by the user, and the user mode is I corresponding relationship, and both of the same name (when the user access to other schema objects, need additional model name as a prefix.)

1.1 create user

Create user user_name

Identified by password

Default tablespace tablespace_name

[quota unlimited on users]

Temporary tablespace temp_tablespace

[password expiire] - - forcing users to change their passwords every time they log in.

1.2 modify user

Alter, user, user_name, identified, by, password_name, - modify password

Alter user user_name quota 10 on user_tablespace; modify tablespace quota

Alter, user, user_name, account, lock - - lock user

Alter, user, user_name, account, unlock - - Unlock user

Alter, user, user_name, default, tablespace, tablespace_name; - modify default tablespace

Alter, user, user_name, temporary, tablespace, temp_tablespace_name -- modify temporary tablespace

Drop user user_name cascade; - delete user

Drop, user, user_name, including, contents, and, datafiles, cascade, constraints -- delete user 2

Two, the allocation of resources profile

Profile is a user profile, which is a password restricted, named.
resource limit

Create profile profile_name limit

Failed_login_attempts 3 - the number of errors allowed

Password_lock_time 10 - the number of days to lock after the number of errors has been reached..

Password_file_time 10 - the valid time of the password

Password_grace_time 2 - the time available after the password expires; 2 days later it will be forced to change the password

P320, some parameters about managing resources (CPU, time, session time, etc.)

Alter, user, user_name, profile, profile_name -- specify the profile file for the user.

Alter profile profile_name limit



XXX 3; - modify profile

Drop profile profile_name; - delete profile

Select, profile, from, dba_users, where, username ='username'; query the resource file.
owned by a user
Select, resource_name, resource_type, limit, from, dba_profiles, where, profile='profile_name'-- query the details of the resource file.

Three, grant of authority to

3.1 system permissions

Grant, system_priv[, system_priv,...]

To {PUBLIC role |user |}[, {PUBLIC| role user}]
[with admin option]; - grant system permissions

With admin option: is granted to the user, and the role can authorize the appropriate permissions to other users or roles.

Revoke, system_priv[, system_priv]...

From {PUBLIC role |user |}[, {user PUBLIC role}] | |... - recovery system permissions

System permission information view:

Dba_sys_privs: administrator has all system privileges information

Session_privs: permission information for current database users

All system permissions information in system_privilege_map: system

The system privileges are recovered, and the users who have access to the permissions are not affected,.

3.2 object permissions

Grant [{object_priv (column_list)]} [object_priv[(column_list), "
All [priviileges] on [schema.]object
To {user role | | PUBLIC}[, {user role public
| |}]...
[with grant option];

The user granted by with grant option: can grant the appropriate permissions to other users, the role (but this option does not modify the role).

Object_priv: object permissions

Column_list: specifies the corresponding column, which is valid only for this column,.

Revoke {object_priv [all [privileges]}
, object_priv] |...
On [schema.]object

From {user role PUBLIC}
| |;
[cascade constraints];

Object permission information view:

Dba_tab_privs: object permission information for all users or roles

Dba_col_privs: column permissions information for all users or roles

All object rights granted by the all_tab_privs_made: object owner or authorized user

The object permissions all_tab_privs_recd: users have

All column permissions granted by the all_col_privs_made: object owner or authorized user

The column permissions all_col_privs_recd: users have

Four, administrative roles

A role is a combination of a set of related permissions with names. We can grant permissions to roles, and then give roles to users to control user privileges at the role level.

Predefined roles:

1, connect roles: connections, sessions, clusters, sequences, synonyms, views, tables,.

2, resource roles: clusters, sequences, types, tables, processes, triggers,.

3, the DBA role: contains the with admin option option, the default DBA user is the system.dba role, does not have the SYSDBA privilege, and the SYSDBA privilege automatically holds all permissions for the DBA role.

4, exp_full_database: the role of.
for performing data export operations
5, imp_full_database:, the role of.
for data import operations
6, execute_catalog_role:'s execute object permissions on all system PL/SQL packages

7, delete_catalog_role:

8, select_catalog_role

9, recovery_catalog_owner

Create role role_name {not identified identified by password_name} -- |; create role.

Grant, system_priv, to, role_name, - - to authorize roles, where you can refer to the user authorization section.

Alter, user, user_name, default, role, role_name - set default roles for users (a user can have multiple roles)

Alter, role, role_name, not, identified, - modify roles as non validating methods,

Alter, role, role_name, identified, by, password_name, - modify roles as validation methods,

Drop role role_name; - delete user

Role information view:

Dba_roles: records all the roles in the database

Dba_role_privs: records all the roles that have been granted users and roles

User_roles: contains the role information that has been granted to the current user

Role_role_privs: contains the role information granted by the role

Role_sys_privs: contains system permissions information for roles

Role_tab_privs: contains the object permission information for the role

Session_roles: contains the role information contained in the current session