» Documentation

Example Secure Database

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