Essential Scripts for DBAs

As database administrators, security should always be at the forefront of our minds. Proper SQL security practices protect sensitive data, prevent unauthorized access, and ensure compliance with regulations. In this comprehensive guide, we’ll explore essential SQL scripts that every DBA should have in their toolkit for managing database security.

Important Caution

Before implementing any of these scripts in production, always test them thoroughly in a development or staging environment first. A single misapplied command can have serious consequences for your database security and availability.

User Account Management

Proper user management is the foundation of database security:

-- Create a new user account
CREATE USER username WITH PASSWORD 'strong_password_here';

-- Grant user permissions to a database
GRANT SELECT, UPDATE, DELETE ON database.table TO username;

-- Revoke permissions when no longer needed
REVOKE SELECT, UPDATE, DELETE ON database.table FROM username;

-- Remove user accounts when they're no longer required
DROP USER username;

SQL

Remember to always use strong passwords and follow the principle of least privilege – only grant permissions that are absolutely necessary.

Database Backup and Restoration

Regular backups are your safety net against data loss:

-- Back up the database
BACKUP DATABASE database TO DISK = 'backup.sql';

-- Restore the database from backup
RESTORE DATABASE database FROM DISK = 'backup.sql';

SQL

Implementing Encryption

Protect sensitive data with column-level encryption:

CREATE TABLE [table] (
    [column] [datatype] ENCRYPTED WITH (
        ALGORITHM = 'AES_256',
        COLUMN_ENCRYPTION_KEY = [key_id]
    )
);

SQL

This creates a table with a column encrypted using the robust AES-256 algorithm.

Monitoring Database Activity

Triggers help track changes to your data:

CREATE TRIGGER [trigger_name] ON [table] AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    PRINT 'A row has been inserted, updated, or deleted from the [table] table.';
END;

SQL

Security Vulnerability Scanning

These stored procedures help identify potential security issues:

-- Create the main security check procedure
CREATE PROCEDURE sp_dbcc_check_security
(
    @dbname = NULL
)
AS
BEGIN
    -- Check permissions on master database first
    EXEC sp_dbcc_check_permissions @dbname = 'master';

    -- Then check all other databases
    IF @dbname IS NULL
    BEGIN
        SELECT db_name FROM sys.databases
        WHERE db_name <> 'master'
        ORDER BY db_name;

        FOR EACH ROW
        BEGIN
            EXEC sp_dbcc_check_permissions @dbname = @row.db_name;
        END;
    END;
END;

-- Create the permissions check procedure
CREATE PROCEDURE sp_dbcc_check_permissions
(
    @dbname = NULL,
    @schema = NULL
)
AS
BEGIN
    -- Check dbo schema permissions first
    EXEC sp_dbcc_check_permissions @dbname = @dbname, @schema = 'dbo';

    -- Then check all other schemas
    IF @schema IS NULL
    BEGIN
        SELECT schema_name FROM sys.schemas
        WHERE schema_name <> 'dbo'
        ORDER BY schema_name;

        FOR EACH ROW
        BEGIN
            EXEC sp_dbcc_check_permissions @dbname = @dbname, @schema = @row.schema_name;
        END;
    END;
END;

-- Execute the security scan
USE master;
GO
EXEC sp_dbcc_check_security;
GO

SQL

Privilege Management Scripts

Granting DELETE Privileges

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'GRANT DELETE ON "' || u.table_name || '" TO &1;'
FROM user_tables u
WHERE NOT EXISTS (SELECT '1'
    FROM all_tab_privs a
    WHERE a.grantee = UPPER('&1')
    AND a.privilege = 'DELETE'
    AND a.table_name = u.table_name);

SPOOL OFF
@temp.sql

SQL

Granting EXECUTE Privileges

SELECT 'GRANT EXECUTE ON "' || u.object_name || '" TO &1;'
FROM user_objects u
WHERE u.object_type IN ('PACKAGE','PROCEDURE','FUNCTION')
AND NOT EXISTS (SELECT '1'
    FROM all_tab_privs a
    WHERE a.grantee = UPPER('&1')
    AND a.privilege = 'EXECUTE'
    AND a.table_name = u.object_name);

SQL

Similar scripts are available for INSERT, SELECT, and UPDATE privileges in the full document.

Synonym Management

Creating Package Synonyms

SELECT 'CREATE SYNONYM "' || a.object_name || '" FOR "' || a.owner || '"."' || a.object_name || '";'
FROM all_objects a
WHERE a.object_type IN ('PACKAGE','PROCEDURE','FUNCTION')
AND a.owner = UPPER('&1')
AND NOT EXISTS (SELECT '1'
    FROM user_synonyms u
    WHERE u.synonym_name = a.object_name
    AND u.table_owner = UPPER('&1'));

SQL

Similar scripts exist for creating synonyms for sequences, tables, and views.

Identifying Users with Write Access

-- Direct grants
select distinct grantee
from dba_tab_privs
where privilege in ('INSERT', 'UPDATE', 'DELETE')
and owner = upper('&1')
union
-- Grants via a role
select distinct grantee
from dba_role_privs
    join dba_users on grantee = username
where granted_role IN (select distinct role
    from role_tab_privs
    where privilege in ('INSERT', 'UPDATE', 'DELETE')
    and owner = upper('&1')
    union
    select distinct role
    from role_sys_privs
    where privilege in ('INSERT ANY TABLE', 'UPDATE ANY TABLE', 'DELETE ANY TABLE'))
union
-- Access via ANY sys privileges
select distinct grantee
from dba_sys_privs
join dba_users on grantee = username
where privilege in ('INSERT ANY TABLE', 'UPDATE ANY TABLE', 'DELETE ANY TABLE');

SQL

Conclusion

These SQL scripts provide a comprehensive toolkit for managing database security. From user account management to privilege auditing and vulnerability scanning, these scripts help DBAs maintain secure database environments.

Remember that security is an ongoing process, not a one-time task. Regular reviews of user privileges, encryption implementations, and security scans should be part of your standard operating procedures.