Users and Schema
User is an account who could access the database, if granted
appropriate privileges and could create objects if granted appropriate
privileges to do so by the dba
Schema is the name of the user and generally refers to set
of objects (table,view,stored procedure) created/owned by the
user.
Objects are fully qualified as <schema_name>.<object_name>
objects are accesed by a user by just specifying the object name, however to access the objects created by other user (if he has appropriate privileges) he has to fully qualify him like this <other_users_schema>.<object_name>
Creating, Altering, Dropping Users
Creation Of User, below is the general syntax for creation users CREATE USER <username> IDENTIFIED BY <password> | EXTERNALLY | GLOBALLY
DEFAUTL TABLESPACE <tablespace_name>
TEMPORARY TABLESPACE <temporary_tablespace_name>
QUOTA UNLIMITED ON <tablespace_name>
QUOTA 100M ON <tablespace_name>
PROFILE regular
PASSWORD EXPIRE
ACCOUNT UNLOCK|LOCK Parameter in detail
Altering Users i.e modifying user's settings
Normal operation that are performed on Users are like changing the
password, selecting default tablespace for the user, granting particular
amount of quota to user on certain tablespace. Then we could lock the
user account, unlock it, change the profile of the user.
Check out the below self explanatory examples
ALTER USER ron IDENTIFIED BY <new password>;
ALTER USER myuser DEFAULT TABLESPACE <tablespace Name> TEMPORARY TABLESPACE <temporary tablespace Name> QUOTA 500M ON <tablespace name> QUOTA 5M ON <other tablespace Name> ALTER USER ron ACCOUNT LOCK; ALTER USER ron ACCOUNT UNLOCK; ALTER USER ron PASSWORD EXPIRE; ALTER USER PROFILE <NEW PROFILE>;
To drop user,
DROP USER ron cascade
cascade option drops any object created/owned by the user here its ron Profiles
Profile is collection of rules.
Rules contains the follwing
Kernel Limitations : This includes the following
Password Management : This includes the following
If no profile is specified during the account creation, user gets the default profile.
To check the contents/rules of the default profile
desc dba_profiles
select resource_name,limit from dba_profiles where profile='DEFAULT'
To assign a specific profile to user
alter user <user_name> profile <profile_name> Lets See how to create new profiles. create profile SECURE limit sessions_per_user 2 cpu_per_session 10000 cpu_per_call 1 connect_time unlimited idle_time 30 logical_reads_per_session default logical_reads_per_call default private_sga 20M failed_login_attempts 3 password_life_time 30 password_reuse_time 12 password_reuse_max unlimited password_lock_time default password_grace_time 2 password_verify_function null / To enable the password verify function execute @?/rdbms/admin/utlpwdmg.sql After which you could assign the password verify function to a profile ALTER PROFILE <profile_name> limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;
Privileges
After user is created, you need to give the user explicit permission to
connect to database to create session with the database. then to
create table he should be granted create table privilege
Other way is to grant roles, roles are database objects and are collection
of privileges predefined roles
For example 'connect' and 'resouce,'
To check the components of the connect role
select * from dba_sys_privs where grantee='CONNECT';
To grant roles to user
grant connect,resource to ron
To check Privilege of existing user, i.e
To check if user has system privileges
select * from dba_sys_privs where grantee='<USERNAME>';
To check if user has roles granted
select * from dba_role_privs where grantee='<USERNAME>';
To check if user has permission on table objects
select * from dba_tab_privs where grantee='<username>';
To check quota for user on tablespaces
select username,tablespace_name,bytes/1024/1024 from dba_ts_quotas
Sessions
Sometime it is required to kill of the session.
Query the v$session view to get the information about the sessions
select sid,serial#, osuser,username, from v$session
After getting the required session check the sid and serial number of the session to be killed
execute the following command to kill the session.
alter system kill session '<sid>.<serial#>' =============================================================================================
To get the database default settings, you could query the database_properties view.
sql>select * from database_properties
|
Database Technologies > Oracle >