EXAMPLE SECUREDATABASE

Create Administrator

In order to secure database, administrator account must be created. Once administrator created, the database becomes secure. To create administrator execute following SQL:

CREATE ADMIN myadmin WITH PASSWORD 'mypwd'

The command will create user "myadmin" and built in roles. The user will be added as a member of "administrator" role and inherit all privileges. If you use a manager, create a new connection in servers’ explorer tree with specifying username/password.

Create database

In order for users to access/modify data or execute procedures, the permissions must be granted. For example create database, table and test procedures:

In a manager execute following script:

CREATE DATABASE test;
GO; 
USE test; 
GO; 
CREATE TABLE test(id INT NOT NULL PRIMARY KEY, info CHAR);
GO; 
CREATE PROCEDURE test_select AS 
BEGIN
SELECT * FROM test 
END
CREATE PROCEDURE test_insert (@id INT,@info CHAR ) AS
BEGIN
INSERT INTO test VALUES(@id,@info) 
END
CREATE PROCEDURE test_update (@id INT,@info CHAR ) AS
BEGIN
UPDATE test SET info = @info WHERE id = @id 
END
CREATE PROCEDURE test_delete (@id INT ) AS
BEGIN
DELETE FROM test WHERE id = @id 
END

Create role

Next, create a role with privileges to access/modify tables and execute procedures. Execute SQL script:

USE test; 
GO; 
CREATE ROLE db_test_access;

Grant privileges

USE test; 
GO; 
-- grant table access rights 
GRANT INSERT ON test TO ROLE db_test_access WITH GRANT OPTION;  
GRANT SELECT ON test TO ROLE db_test_access WITH GRANT OPTION;  
GRANT UPDATE ON test TO ROLE db_test_access WITH GRANT OPTION;  
GRANT DELETE ON test TO ROLE db_test_access WITH GRANT OPTION;  
-- grants execute rights 
GRANT EXECUTE ON test_select TO ROLE db_test_access WITH GRANT OPTION;  
GRANT EXECUTE ON test_update TO ROLE db_test_access WITH GRANT OPTION;  
GRANT EXECUTE ON test_delete TO ROLE db_test_access WITH GRANT OPTION;  
GRANT EXECUTE ON test_insert TO ROLE db_test_access WITH GRANT OPTION;

Optional [WITH GRANT OPTION] indicates that the role/user may pass/grant the permission to other user/role.

Create user with role

Now, create a user and add member role db_test_access to user. The user will have the rights of the role. If a new permissions added to the role, user gains the permission too, or, if revoked from role, users' permission also be revoked.

Execute SQL script:

USE test; 
GO; 
CREATE USER myuser WITH PASSWORD '123'
GO; 
ADD ROLE db_test_access TO USER myuser

In a manager add new server connection with users' "myuser" login and execute procedures:

USE test; 
GO; 
EXEC test_insert @id=0,@info='test0000'
EXEC test_update @id=0,@info='test0001'
EXEC test_select

to delete, execute:

USE test; 
GO; 
EXEC test_delete @id=0

Revoke privileges

Revoking rights from role will affect the user. Execute revoke script in a query window with "myadmin" credentials:

USE test; 
GO; 
REVOKE SELECT ON test FROM ROLE db_test_access;

And then execute the SQL with "myuser" credentials:

USE test; 
GO; 
EXEC test_select -- should fail

If we grant exclusive SELECT permission by "myadmin", the user will be able to select even if "db_test_access" has no permissions. With "myadmin" execute:

USE test; 
GO; 
GRANT SELECT ON test TO USER myuser;

now, with "myuser" credentials, should execute without errors:

USE test; 
GO; 
EXEC test_select