oracle11g users, permissions, roles, profile management and auditing

Section Ten Chapter Safety Management

1 User Management

2 Rights Management

3 role Management ;

4 Profile Management

5 Audit

Operating system: Win7 Oracle installation directory:e-Drive

Database name:ORCL Password:123456

Connect first

Set ORACLE_SID=ORCL

Sqlplus/nolog

Conn sys/123456 as Sysdba

1User Management

Create user User3, password user3, default tablespace users, table space Quota of 10MB, Initial state is locked

Create user User3 identified by User3

Default tablespace users quota 10m on the users account lock;

Modify the user User3 password to newuser3 and unlock the user

Alter user User3 identified by Newuser3 account unlock;

Delete User

Drop user User3;

Query user information to view all user names and default tablespace for the database

Select username,default_tablespace from Dba_users;

2Rights Management

Authorization for System permissions

Create user1 first

Create user User1 identified by User1

Default tablespace users quota 10m on the users account unlock;

Password @ data name

Conn system/123456 @orcl

Grant Create session, CREATE table, create view to User1;

Collection of system permissions

Conn system/123456 @orcl

Revoke CREATE TABLE, create view from User1;

Authorization for object permissions

Conn system/123456 @orcl

Grant Select, INSERT, update on scott.emp to User1;

Collection of object permissions

Revoke Select, update on scott.emp from User1;

Query Authorization Information

select* from User_sys_privs;

3Role Management

Querying all predefined roles for the current database

select* from Dba_roles;

Custom roles

Create role High_manager_role;

Create role Middle_manager_role;

Using roles for Rights management

Grant Connect, resource, dba to High_manager_role;

Grant Select, UPDATE, INSERT, delete on scott.emp to High_manager_role;

Permission Reclamation

Revoke Update, INSERT, delete on scott.emp from High_manager_role;

Modify the role to add a password for high_manager_role highrole

Alter role High_manager_role identified by Highrole;

Role invalidation

Set role None;

Role in effect

Set role High_manager_role identified by Highrole;

Remove a role

Drop role High_manager_role;

1 granting a role

Grant Connect, resource to Middle_manager_role;

2 Recycling Roles

Revoke resource, connect from Middle_manager_role;

3 activation or masking of users

Shielding

Alter user User1 default role none;

Activated

Alter user User1 default role all;

Querying role information

Select * from role_sys_privs where role = ' DBA ';

Delete User1

Drop user user1;

Delete Middle_manager_role

Drop role Middle_manager_role;

4Profile Management

Creating profiles

User login allowed to fail 4 times, Login failed account lockout time is ten days

Create profile Pwd_profile Limit failed_login_attempts 4

Password_lock_time 10;

Assign a profile to a user

Create user User5 identified by User5 profile Pwd_profile;

Modify the Profile

Alter profile Pwd_profile limit password_life_time 10;

Delete profile

Drop profile Pwd_profile Cascade;

Querying a Profile

Select * from User_password_limits;

5Audits

Audit Start-up

Alter system set audit_trail= ' DB ' scope=spfile;

Conn sys/123456 as Sysdba

Shutdown Immediate

Startup

oracle11g users, permissions, roles, profile management and auditing