User Management ( Create,Grant,Revoke)

                                          User Management-I

 Creating the user

SQL> Create user fm identified by fm default tablespace fm_ts

temporary tablespace temp quota unlimted on fm_ts quota 100m on fm_demo_ts

password expire account lock;

Assigning the quota to the user on other tablespace

SQL> alter user fm quota 10m on tbs1;

To unlock the user account

SQL> alter user fm account unlock;

To force user to change the Password

SQL> alter user fm password expire;

Granting privileges to users

SQL> grant create session, create table, create sequence to fm;

SQL> conn fm/fm;

SQL> select * from session_privs;

SQL> select * from role_sys_privs;

Checking the privileges for the user

SQL> connect / as sysdba;

SQL> select owner,role,privilege from role_tab_privs where owner=’FM’;   

SQL> select grantee,owner,table_name from dba_tab_privs where grantee=’FM2’;

Creating and assigning  the custom roles:-

SQL> create role fm_role1;

SQL> grant create session, create table, create sequence to fm_role1;

SQL> grant fm_role1 to fm;

SQL> select role from dba_roles where role=’FM_ROLE1’;

Assingning object privileges on a table to other users

SQL> connect fm/fm

SQL> grant select,insert,update on emp to role1;

SQL> grant role1 to fm2;

SQL> connect fm2/fm2;

SQL> select * from  fm.emp values(&n);

Revoking the privileges from user

SQL> revoke insert,update on fm.emp from fm2;

Some the key seeded (predefined) roles:

dba, connect,resource, exp_full_database, imp_full_database etc

Dropping the user Account

SQL> drop user fm;

SQL> drop user fm cascade;

Views:-

dba_users;                       dba_role_privs;

user_users;                       dba_sys_privs;

all_users;                         dba_tab_privs;

dba_ts_quotas;                           role_tab_privs;      

user_ts_quots                             dba_roles;

role_sys_privs;                          role_sys_privs;

Assigning sysdba privilages to user

Sql> grant sysdba,sysoper to fm;

Sql> sho parameter resource_limit;

Sql> alter system set resource_limit=true scope=both;

Sql> create profile prof1 limit

          Failed_login_attempts 3

          Session_per_user 5

          Idle_time 1;

Sql> alter user fm profile prof1;

Sql> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql;

Sql> alter profile prof1 limit

          Password_verify_function verify_function;

Sql> alter user fm identified by fm;

Sql> alter profile prof1 limit

          Password_verify_function null;