Controlling User Access
- Controlling User Access
- Who is a Database Administrator
- Creating a Oracle User and Password and granting privileges.
- Single User/multiple Users set of Users accessing the Database.
With the Oracle Server Database security we can do the following :
1. Control Database Access
2. Give access to specific objects in the database
3. Confirm the given and received privileges with Oracle Data Dictionary
4. Create Synonyms for Database Objects.
Database Security classified into two categories:
a.System Security
b.Data Security
a.System Security covers the access and use of database at system level ex
Username and Password (SCOTT/TIGER)
b.Database Security covers access and use of database objects and actions that the users can have on objects.
2. Privileges
Privileges are rights to execute particular SQL statements.
The DBA is a high-level User with the ability to create Users and Grant Users access to database and objects.
Users require System Privileges to gain access to Database and Object Privileges to manipulate the content of objects in the database.
Users can also be given the privilege to grant additional privileges to other users or to Roles (Named group of related privileges)
Schema is a collection of objects such as Tables, Views, and Sequences.
The Schema is owned by a database user.
System Privileges :
More than 100 distinct system privileges are available for Users and Roles.
These privileges are provided by a DBA.
CREATE USER – Privilege to create Oracle User.
DROP USER – Privilege to drop Oracle User.
DROP ANY TABLE – Privilege to drop any Table from any schema.
SELECT ANY TABLE – Privilege to query records from any Tables, views in any schema.
CREATE ANY TABLE – Privilege to create Table in any schema.
+
3. Creating Users
Normally the DBA creates Users with the CREATE USER statement
Syntax :
CREATE USER
IDENTIFIED BY
CREATE USER hrms
IDENTIFIED BY hrms
/
4. User System Privileges
a.After the User is created the DBA can grant system privileges to that User.
Syntax :
GRANT privilege [,privilege…]
TO user [,user|role,PUBLIC…]
GRANT create session TO HRMS
/
Normally Oracle Developer will be granted the following
system privileges :
CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE
GRANT create table TO HRMS
/
GRANT UNLIMITED TABLESPACE TO OE
/
GRANT create sequence TO HRMS
/
GRANT create view TO HRMS
/
GRANT create procedure TO HRMS
/
GRANT create session,create table,create sequence,create view
TO HRMS
/
Example to Illustrate WITH ADMIN OPTION while granting a System Privilege to User so that the user can
grant the privilege to other users.
1.
— Connect as SYS SYSDBA —
CONNECT sys/sys@orcl AS SYSDBA;
2.
— Create User1 —
CREATE USER user1 IDENTIFIED BY user1
/
3.
— Grant the CREATE SESSION Privilege to User1 WITH ADMIN Option —
GRANT create session TO user1 WITH ADMIN OPTION
/
4.
— Create User2 —
CREATE USER user2 IDENTIFIED BY user2
/
5.
— Connect as User2 —
CONNECT user2/user2@ORCL;
Since User2 is not granted the CREATE SESSION privilege he cannot login to his user.
6.
— Connect as User1 —
CONNECT user1/user1@ORCL;
7.
— Grant CREATE SESSION to USER2 from USER1 Login — (He has Admin Option)
GRANT create session TO user2
/
8.
— Connect as User2 —
CONNECT user2/user2@ORCL;
9.
— SQL Query to look at Privileges granted for User2 —
SELECT username,privilege,admin_option FROM user_sys_privs
/
Example to Illustrate REVOKE the privileges granted to User2 by User1 without the Dependence of a SYSDBA.
1.
— Connect as User1 —
CONNECT user1/user1@ORCL;
2.
— REVOKE the CREATE SESSION privilege granted to User2 —
REVOKE create session FROM user2
/
3.
— Connect as User2 —
CONNECT user2/user2@ORCL;
This will error out since the privilege granted has been revoked.
5.
What is a Role
A Role is a named group of related privileges that can be granted to the user.
A User can have access to several roles and several users can be assigned the same role.
- First the Role has to be created and then the privileges has to be assigned to that role.
Syntax :
CREATE ROLE role
/
Example to Illustrate on creating a Role by assigning certain privileges to the Role and assigning this role to a different sets of Users.
1.
— Connect as SYS SYSDBA —
CONNECT sys/sys@orcl AS SYSDBA;
2.
— Create the Role —
CREATE ROLE test_user_roles
/
3.
— Grant the privileges to the Role —
GRANT create session,create table,create sequence,create view,create procedure TO test_user_roles
/
4.
— SQL Query to display the System Privileges granted to Role —
SELECT role,privilege FROM role_sys_privs WHERE role = ‘TEST_USER_ROLES’
/
5.
— Grant this Role to a set of Users —
GRANT test_user_roles TO user1,user2
/
6.
— Connect as User1 —
CONNECT user1/user1@ORCL;
7.
— SQL Query to look at Priviliges granted for User1 —
SELECT username,granted_role,default_role FROM user_role_privs
/
— This SQL Query will not return records since the privileges have not been directly granted to the User —
SELECT username,privilege,admin_option FROM user_sys_privs
/
8.
— SQL Query to look at Priviliges granted for User2 —
SELECT username,granted_role,default_role FROM user_role_privs
/
Example to Illustrate on creating a Role by assigning certain privileges to the Role and assigning this role to a different sets of Users.
1.
— Connect as SYS SYSDBA —
CONNECT sys/sys@orcl AS SYSDBA;
2.
— Revoke the Role from User1,User2 —
REVOKE test_user_roles FROM user1,user2
/
6. Changing your Password
a.The DBA creates the User and initializes a Password
b.This password can be changed using ALTER USER statement
ALTER USER HRMS
IDENTIFIED BY hr
/
The User can change his password using the SQL*Plus command
PASSW too.
7. Object Privileges
An object privilege is a privilege or right to perform a particular action on a specific table,view,sequence or procedure.
Each individual object has a particular set of grantable privileges
An owner has all the object privileges on the object.
An owner can give specific privileges to other users for his objects.
Syntax :
GRANT object_privilege_name [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION]
/
Example to grant SELECT privileges to User2 for a Table created by User1 and giving the WITH GRANT to User2 so that he can pass on this same to User3.
1.
— Connect as SYS SYSDBA —
CONNECT sys/sys@orcl AS SYSDBA;
2.
— Create User3 —
CREATE USER user3 IDENTIFIED BY user3
/
3.
— Grant CREATE SESSION to User1,User2,User3 —
GRANT create session TO user1,user2,user3
/
4.
— Grant CREATE TABLE to User1 and Grant UNLIMITED TABLESPACE to User1 —
GRANT create table TO user1
/
GRANT unlimited tablespace TO user1
/
5.
— Connect to User1 and Create a Table —
CONNECT user1/user1@orcl;
CREATE TABLE test_table(id NUMBER)
/
6.
— Connect to User2 and try selecting the User1 created Table —
CONNECT user2/user2@orcl;
SELECT * FROM user1.test_table
/
This will error out since User2 has not been granted SELECT privileges on the Object owned by User1
7.
— Connect to SYS User to grant the SELECT object privilege to User2 —
— Connect as SYS SYSDBA —
CONNECT sys/sys@orcl AS SYSDBA;
GRANT select
ON user1.test_table
TO user2
WITH GRANT OPTION
/
8.
— Connect to User2 and now try selecting the User1 created Table —
CONNECT user2/user2@orcl;
SELECT * FROM user1.test_table
/
9.
— Now Grant the Object Access Privilege to User3 from User2 —
GRANT select
ON user1.test_table
TO user3
/
10.
— Now Connect to User3. User3 will be able to access the Object owned by User1 —
CONNECT user3/user3@orcl;
SELECT * FROM user1.test_table
/
11.
— Now REVOKE the Privilege from User2 (In Turn User3 will not have the privileges) —
CONNECT sys/sys@orcl AS SYSDBA;
REVOKE select
ON user1.test_table
FROM user2
/
— Now Connect to User2 —
CONNECT user2/user2@orcl;
— Select the Records —
SELECT * FROM user1.test_table
/
— Now Connect to User3 —
CONNECT user3/user3@orcl;
— Select the Records —
SELECT * FROM user1.test_table
/
Important Note :
If the user grants a SELECT privilege on a Table to User B including the WITH GRANT OPTION user B can grant to User C the SELECT privilege with WITH GRANT OPTION and User C can then grant to user D the SELECT privilege.
If user A revokes privileges from User B then the privileges granted to users C and D are also revoked.
— Granting to more than one User —
GRANT select
ON scott.emp
TO hr,hrms
/
— Granting to all the Users —
GRANT select
ON hrms.e
TO PUBLIC
/
— Granting on specific Columns —
GRANT update(dname,loc)
ON SCOTT.dept
TO scott,hrms_role
/
8. Confirming Privileges Granted
Data Dictionary View Description
ROLE_SYS_PRIVS System Privileges granted to Roles
ROLE_TAB_PRIVS Table Privileges granted to Roles
USER_ROLE_PRIVS Roles accessible by the User
USER_TAB_PRIVS_MADE Object Privileges granted on the User’s Objects
USER_TAB_PRIVS_RECD Object Privileges granted to the User
USER_COL_PRIVS_MADE Object Privileges granted on the columns of the User’s Object
USER_COL_PRIVS_RECD Object Privileges granted to the User on specific columns
USER_SYS_PRIVS System Privileges granted to the User
— Query to look at Grants given to Objects from a User —
— This will give information on Object Privileges granted on Objects to Users from a User —
SELECT grantee,owner,table_name,privilege FROM dba_tab_privs WHERE OWNER = ‘SCOTT’
/
— Query to look at Privileges granted to a User System Privileges —
SELECT username,privilege,admin_option FROM user_sys_privs
/
9. Revoking Object Privileges
a.We use the REVOKE statement to revoke or remove privileges granted to other objects
b.Privileges granted to others through the WITH GRANT OPTION are also revoked.
REVOKE {privilege [, privilege …]||ALL}
ON object
FROM {user[, user…]|ROLE|PUBLIC}
[CASCADE CONSTRAINTS];
If a user were to leave the Company and you revoke his privileges you must regrant any privileges that this user may have granted to other users.
If you drop the User account without revoking privileges from it then the system privileges granted by this user to other users are not affected.
Ex.
— Query to Revoke the SELECT and INSERT privileges given to user SCOTT on EMP Table —
REVOKE select, insert
ON SCOTT.emp
FROM HR
/