Managing User, Role and Security

User, Role and DB Security

User Management

--Create User Test_User
CREATE USER test_user1 IDENTIFIED BY password123;

-- Grant CREATE SESSION- without create session, the user can't logged in
GRANT create session to Test_user1;

--How to verify the user account detail?
SELECT * 
FROM   dba_users 
WHERE  username LIKE 'TEST_USER1'; 

--Changing the password of an existing user
ALTER USER test_user1 IDENTIFIED BY password321; 

--Locking user account
ALTER USER test_user1 account LOCK;

--Unlocking user account
ALTER USER test_user1 account unlock; 

What do you get when you try to log in using locked account?
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

--Dropping User Accounts
A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user sessions using ALTER SYSTEM -KILL SESSION clause. You can drop a user from a database using the DROP USER statement. To drop a user and all the user schema objects (if any), you must have the DROP USER system privilege. Because the DROP USER system privilege is powerful, a security administrator is typically the only type of user that has this privilege. If the schema of the user contains any dependent schema objects, then use the CASCADE option to drop the user and all associated
objects and foreign keys that depend on the tables of the user successfully.

If you do not specify CASCADE and the user schema contains dependent objects, then an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the schema contains and the implications of dropping them. You can find the objects owned by a particular user by querying the DBA_OBJECTS view.

-- Getting session detail
select sid, serial#, username from v$session;

--Killing user session
Alter system kill session ‘127,55234’;

Now use the drop user command to drop the user permanently !! Make sure the objects owned by the users are not being used.

Managing Role and Privileges

--displays all the roles in database
SELECT * 
FROM   dba_roles;

--displays all the roles assigned directly to BRUCELEE
--Also displays the system priv
SELECT * 
FROM   dba_role_privs 
WHERE  grantee LIKE 'BRUCELEE%';

--Displays all the role under DEVELOPER_ROLE
SELECT * 
FROM   dba_role_privs 
WHERE  grantee LIKE 'DEVELOPER_ROLE'; 

--All the tables privilege under DEVELOPER_ROLE
SELECT * 
FROM   dba_tab_privs 
WHERE  grantee LIKE 'DEVELOPER_ROLE'; 

-- Find out the role for a given table;
SELECT * 
FROM   dba_tab_privs 
WHERE  table_name LIKE 'EMPLOYEE'; 

--displays the system privilege to ROLE
SELECT * 
FROM   role_sys_privs 
WHERE  ROLE LIKE 'DBA'; 

--displays the all the system privileged to user
SELECT * 
FROM   role_sys_privs; 
OR
--displays the system privilege to ROLE
SELECT * 
FROM   role_sys_privs 
WHERE  ROLE IN (SELECT granted_role 
                FROM   dba_role_privs 
                WHERE  grantee LIKE 'DEV_ADMIN'); 

Common Role - CONNECT, RESOURCE, DBA

What sys privilege are there under RESOURCE?
SELECT privilege 
FROM   dba_sys_privs 
WHERE  grantee = 'RESOURCE'; 

PRIVILEGE
--------------------------------------
CREATE TYPE
CREATE TABLE
CREATE CLUSTER
CREATE TRIGGER
CREATE OPERATOR
CREATE SEQUENCE
CREATE INDEXTYPE
CREATE PROCEDURE