Tag Archives: Security

Check User Logins in SQL Server

Check User Logins in SQL Server

Instance Or Server Level :
Logins: In this define SQL Server User and integrity mode user

Database Levels
USERS: SQL Server define separate Database user at database security tab.

Note: Login name is mapped with Database user

Check all the roles and user at database level

select name, type_desc , authentication_type_desc from sys.database_principals;

Check roles and user at Server level

select name , type_desc , is_disabled, default_database_name from sys.server_principals;

Check the database roles

select dp.type_desc , dp.name ,p.name
from sys.database_role_members r
JOIN
sys.database_principals dp on r.role_principal_id = dp.principal_id
JOIN
sys.database_principals p on r.member_principal_id = p.principal_id;

Check the Server Roles

Select dp.type_desc, dp.name, p.name
from sys.server_role_members r
join
sys.server_principals dp on r.role_principal_id = dp.principal_id
JOIN
sys.server_principals p on r.member_pricipal_id = p.principal_id
GO

Check the Database Permission

select class_desc , permission_name,p.name
from
sys.database_permissions dp
JOIN
sys.database_Principals p on dp.grantee_principal_id = p.principal_id

Check the Server Permissions

select class_desc , permission_name,p.name
from
sys.server_permissions dp
JOIN
sys.Server_principals p on dp.grantee_principal_id = p.principal_id;

Advertisements

Remove the public privileges from oracle database

Remove the public privileges from oracle common objects

Public is the role when you assign any privileges to public then all user has that permission too.
Remove the public privileges from oracle objects like utl_mail, utl_http, utl_file, utl_url comes under security.
Only grant access to the user which need it, other wise any guest user can used it for security breach.

Steps to remove the public privileges for ult procedures:

1. Check the status of all objects in database – valid or invalid.

select distinct status from dba_objects;

2. If invalid object present, find the list of object that is invalid

Select owner, object_name, object_type from dba_objects where status='INVALID';

3. Check the following object present in database from which priviliges need to remove.

select distinct object_name from dba_objects where object_name in
('UTL_SMTP','UTL_HTTP','UTL_URL','UTL_FILE','UTL_RAW','UTL_TCP','UTL_MAIL');

4. Revoke the public privileges.

REVOKE EXECUTE ON utl_tcp FROM public
REVOKE EXECUTE ON utl_smtp FROM public;
REVOKE EXECUTE ON utl_http FROM public;
REVOKE EXECUTE ON UTL_FILE FROM public;
REVOKE EXECUTE ON UTL_URL FROM public;
REVOKE EXECUTE ON UTL_RAW FROM public;

5. Run the SQL script for compile the invalid objects.

@OraHome1/rdbms/admin/utlrp.sql

6. Check the count of invalid packages.

select distinct owner from dba_objects where status='INVALID';

7. Check the user which is in OPEN status and having invalid objects and compare with upper list in step 2.

select username,account_status from dba_users where username in (select distinct owner from dba_objects where status='INVALID') and account_status='OPEN';