Last updated: 2026-02-12

Security Manager

Manage database security with Jam SQL Studio. Create and manage logins, users, roles, and permissions for SQL Server, PostgreSQL, and Oracle databases.

SQL Server Security

SQL Server uses a two-tier security model: server-level principals (logins) and database-level principals (users).

Server-Level Security

Server-level security controls who can connect to the SQL Server instance and what server-wide operations they can perform.

Logins

Logins authenticate users to the SQL Server instance. To manage logins:

1. Expand the server in Object Explorer
2. Expand Security → Logins
3. Right-click Logins and select New Login

Login Types

Server Roles

Server roles grant server-wide permissions. Built-in roles include:

Database-Level Security

Database-level security controls access to individual databases and their objects.

Database Users

Users provide access to a specific database. Each user is typically mapped to a server login.

1. Expand the database in Object Explorer
2. Expand Security → Users
3. Right-click Users and select New User
4. Enter the username and select the associated login
5. Assign database roles
6. Click Create

Database Roles

Database roles group permissions for easier management. SQL Server includes built-in roles:

Creating Custom Roles

1. Expand Security → Roles → Database Roles
2. Right-click Database Roles and select New Role
3. Enter the role name
4. Add members (users or other roles)
5. Assign permissions on the Permissions tab
6. Click Create

Schemas

Schemas provide a namespace for database objects and a container for permissions.

• Default schemas - dbo, guest, sys, INFORMATION_SCHEMA
• Custom schemas - Create schemas to organize objects and simplify permissions
• Schema ownership - The owner has full control over all objects in the schema

PostgreSQL Security

PostgreSQL uses a unified role-based security model where roles can act as users, groups, or both.

Roles

In PostgreSQL, roles are the foundation of the security system. A role can:

• Own databases and objects
• Have login privileges (act as a user)
• Contain other roles (act as a group)
• Have specific attributes (SUPERUSER, CREATEDB, CREATEROLE)

Creating Roles

1. Expand the PostgreSQL server in Object Explorer
2. Expand Login/Group Roles
3. Right-click and select New Role
4. Set role properties and attributes
5. Click Create

Role Attributes

Role Membership

Roles can be members of other roles, creating a hierarchy for permission management:

• Group roles - Roles without LOGIN attribute, used to group permissions
• Member roles - Roles that belong to group roles and inherit their permissions
• INHERIT vs NOINHERIT - Controls whether member automatically gets group permissions

Database Privileges

PostgreSQL grants specific privileges on database objects:

Managing Permissions

Jam SQL Studio provides a visual interface for managing permissions on database objects.

Viewing Permissions

1. Right-click the object in Object Explorer
2. Select Properties
3. Go to the Permissions tab
4. View all granted permissions, grantees, and grant options

Granting Permissions

1. Open object properties and go to Permissions tab
2. Click Add to add a new grantee (user or role)
3. Select the permissions to grant (SELECT, INSERT, UPDATE, etc.)
4. Optionally check With Grant Option to allow the grantee to grant to others
5. Click Apply

Revoking Permissions

1. Open object properties and go to Permissions tab
2. Select the permission to revoke
3. Click Revoke or uncheck the permission
4. Click Apply

Permission Precedence

Understanding how permissions are evaluated:

• DENY overrides GRANT - A denied permission cannot be obtained through any grant
• Inherited permissions - Users inherit permissions from their roles
• Schema-level permissions - Permissions on schemas apply to all objects within
• Database-level permissions - Some permissions apply to the entire database

Oracle Security

In Oracle Database, users and schemas are the same concept — each user owns a schema with the same name. Security is managed through system privileges, object privileges, and roles.

Users (Schemas)

Oracle users are both principals and schema owners. To manage users:

1. Expand the Security folder under your Oracle connection in Object Explorer
2. Right-click Users and select Script as CREATE to see user DDL
3. Users can be granted system privileges (CREATE TABLE, CREATE SESSION) and object privileges (SELECT, INSERT on specific objects)

System Privileges

System privileges control what operations a user can perform. Common Oracle system privileges:

• CREATE SESSION — Connect to the database
• CREATE TABLE — Create tables in own schema
• CREATE VIEW — Create views in own schema
• CREATE PROCEDURE — Create PL/SQL procedures and functions
• CREATE SEQUENCE — Create sequences
• DBA — Full administrative access (use sparingly)

Object Privileges

Object privileges control access to specific database objects:

• SELECT, INSERT, UPDATE, DELETE on tables and views
• EXECUTE on procedures, functions, and packages
• ALTER on tables and sequences
• REFERENCES for foreign key constraints

Oracle Roles

Oracle roles group privileges for easier management. Common built-in roles:

• CONNECT — Basic connection privileges
• RESOURCE — Create objects (tables, sequences, procedures)
• DBA — Full database administration

Best Practices

Principle of Least Privilege

• Grant only the permissions users actually need
• Start with read-only access and add write permissions as needed
• Avoid granting db_owner or SUPERUSER unless absolutely necessary
• Review permissions regularly and remove unused access

Use Roles for Group Management

• Create roles for common permission sets (e.g., "app_readonly", "app_readwrite")
• Add users to roles instead of granting individual permissions
• Makes it easy to change permissions for entire groups
• Simplifies auditing and compliance

Secure Authentication

• Use Windows Authentication or Azure Entra ID when possible
• Require strong passwords for SQL authentication
• Disable the sa account or use a strong password
• Enable SSL/TLS for connections

Audit and Monitor

• Enable login auditing to track access attempts
• Review failed login attempts regularly
• Document permission changes with comments
• Use Schema Compare to track security object changes