USER MANAGEMENT
How to create user?
Syntax sys>> create user <username> identified by <password>;
Create user u1 identified by u1;
How to assign the privileges to user?
Grant connect,resource to u1;
How to assign the tablespace to user?
Alter user u1 default tablespace userdata;
How to assign the tablespace while creating the user?
Create user u2 identified by u2 default tablespace ts1;
How to assign the privileges while creating the user?
Grant connect,resource to u3 identified by u3;
What is ROLE?
Role : set of privileges or group of privileges
How to create the role?
Create role <rolename>;(without password)
Create role <rolename> identified by <password>;(using password)
Create role r1;
Create role r2 identified by r2;
How to assign the privileges to role?
Grant <privileges> to <rolename>;
Privileges are of 2 types
System level: to perform some action against your database
Object level: on top of objects
Granting system level privileges?
Grant create tablespace to u1;
Sys>Grant create tablespace to u2 with admin option;
U2>grant create tablespace u3;
Sys>revoke create tablespace from u2;
Granting object level privileges?
U1>grant select on emp to u2;
U1>grant select on dept to u2 with grant option;
U2>grant select on u1.dept to u3;
U1>revoke select on emp from u2;
To see user information?
Desc dba_users
Select username from dba_users;
To check username and default tablespace for user?
select username,default_tablespace from dba_users;
to check roles information?
Desc dba_roles
Select * from dba_roles;
To see what are the privileges in a role?
Select * from role_sys_privs;
What is profile?
Profile : profile is a set of resources
To check profile information?
Desc dba_profiles
Select * from dba_profiles;
How to create profile?
Create profile pr1 limit
Failed_login_attempts 3
Sessions_per_user 2
Idle_time 5;
How to assign the profile to users?
Alter user u1 profile pr1;
Before creating the profile first we need to set one parameter in parameter file that is resource_limit=true
For this
Step 1 : shut immediate (NOT IN REAL TIME ONLY FOR PRACTICE)
Exit
Step 2 cd $ORACLE_HOME/dbs
Step 3 vi init$ORACLE_SID.ora
Step 4 resource_limit=true
Step 5 sqlplus / as sysdba
Step 6 startup
To check which profile for user?
Select username,profile from dba_users;
Authentication
- Database level :connect to database with password
- o/s level :connect to database without password
- network level :connect to database remotely
how to create o/s level user?
Create user <loginid> identified externally;
Before creating this one
Wee need to set one parameter os_authent_prefix=’’
Shut immediate
Edit the parameter file
Start the database
Create user oracle identified externally;
How to drop the user?
Drop user <username>; (if user does not contains any objects)
Drop user <username> cascade; (if user contains objects)
Loading...
