| Author |
 Topic  |
|
|
anas.zeta
Starting Member
Jordan
6 Posts |
 Posted - 08/12/2012 : 05:44:17
|
i wanna create and set a user login that have exclusively access to specific db via "enterprise manager" and "my application" and prohibited from any windows and sql admins of sql servers accessing it from "enterprise manager" or any other method like backup files.
anas zeta |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 08/12/2012 : 13:03:44
|
You can't lock out the admin.
Too old to Rock'n'Roll too young to die. |
 |
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 08/13/2012 : 01:47:59
|
It is possible to block based on IP. Are you able to map the potential users to certain ip ?
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
komkrit
Yak Posting Veteran
Thailand
60 Posts |
Posted - 08/14/2012 : 06:29:37
|
You can achieve that goal by "LOGON TRIGGER" for SQL SERVER 2005 onward.
For example, here is logon trigger that denied accessing from SSMS(even though that login is sa)
You may modified a little bit trigger to match your requirements.
---------------
CREATE TRIGGER my_trigger
ON ALL SERVER WITH EXECUTE AS 'your_login_name'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'your_login_name'
AND (SELECT program_name FROM sys.sysprocesses WHERE SPID = @@SPID) LIKE 'Microsoft SQL Server Management Studio%'
ROLLBACK;
END;
---------------
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
|
komkrit
Yak Posting Veteran
Thailand
60 Posts |
Posted - 08/14/2012 : 06:33:29
|
Another thing to concern,
SQL Server 2005 SP2 or later version can only use LOGON TRIGGER.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3441 Posts |
Posted - 08/14/2012 : 07:12:21
|
quote:
Originally posted by komkrit
You can achieve that goal by "LOGON TRIGGER" for SQL SERVER 2005 onward.
For example, here is logon trigger that denied accessing from SSMS(even though that login is sa)
You may modified a little bit trigger to match your requirements.
---------------
CREATE TRIGGER my_trigger
ON ALL SERVER WITH EXECUTE AS 'your_login_name'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'your_login_name'
AND (SELECT program_name FROM sys.sysprocesses WHERE SPID = @@SPID) LIKE 'Microsoft SQL Server Management Studio%'
ROLLBACK;
END;
---------------
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.
That's pretty damn paranoid! -- cool though!
Doesn't stop you from accessing the db in other ways - download toad or use sqlcmd..
The best thing to do would be to change the sa password and then put the credentials in a fire proof safe.
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
anas.zeta
Starting Member
Jordan
6 Posts |
Posted - 08/15/2012 : 06:19:23
|
quote:
Originally posted by webfred
You can't lock out the admin.
Too old to Rock'n'Roll too young to die.
i need to lock out the admin, just for my db...
anas zeta |
|
|
|
anas.zeta
Starting Member
Jordan
6 Posts |
Posted - 08/15/2012 : 06:20:24
|
quote:
Originally posted by jackv
It is possible to block based on IP. Are you able to map the potential users to certain ip ?
Jack Vamvas
--------------------
http://www.sqlserver-dba.com
no jack, i cant...
anas zeta |
|
|
|
anas.zeta
Starting Member
Jordan
6 Posts |
Posted - 08/15/2012 : 06:25:07
|
quote:
Originally posted by komkrit
You can achieve that goal by "LOGON TRIGGER" for SQL SERVER 2005 onward.
For example, here is logon trigger that denied accessing from SSMS(even though that login is sa)
You may modified a little bit trigger to match your requirements.
---------------
CREATE TRIGGER my_trigger
ON ALL SERVER WITH EXECUTE AS 'your_login_name'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'your_login_name'
AND (SELECT program_name FROM sys.sysprocesses WHERE SPID = @@SPID) LIKE 'Microsoft SQL Server Management Studio%'
ROLLBACK;
END;
---------------
komkrit its nice way, but i need something more common for any new admin users that i didnt defined...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.
anas zeta |
|
|
|
anas.zeta
Starting Member
Jordan
6 Posts |
Posted - 08/15/2012 : 06:31:47
|
quote:
Originally posted by Transact Charlie
Doesn't stop you from accessing the db in other ways - download toad or use sqlcmd..
The best thing to do would be to change the sa password and then put the credentials in a fire proof safe.
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/
my client refuse to use any other parties.. and even i used sa user login and not worked..
anas zeta |
|
|
|
komkrit
Yak Posting Veteran
Thailand
60 Posts |
Posted - 08/15/2012 : 13:11:39
|
Dear anas zeta,
You just need modification a little bit my example to match your requirement.
To prohibit newly undefined sysadmin account, use following IF condition
-------------------
IF IS_SRVROLEMEMBER('sysadmin')
ROLLBACK
-------------------
You should specific application name together to narrow scope of prohibition
-------------------
IF IS_SRVROLEMEMBER('sysadmin')
AND APP_NAME() LIKE 'Microsoft SQL Server Management Studio%'
ROLLBACK
-------------------
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
Edited by - komkrit on 08/15/2012 13:14:35 |
|
|
|
komkrit
Yak Posting Veteran
Thailand
60 Posts |
Posted - 08/15/2012 : 13:30:48
|
Bare in mind that sa can do anything.
If you create trigger, anyway they can overwrite or delete trigger.
Try to separate sensitive data into another instance of SQL and give permission to each login strictly.
If there are too many sa and you can not control others sa,
Try to enable audit for tracking. Or encrypt data by key with password that only you can decrypt them, so others sa can not readable.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
|
anas.zeta
Starting Member
Jordan
6 Posts |
Posted - 08/16/2012 : 02:57:25
|
quote:
Originally posted by komkrit
Bare in mind that sa can do anything.
If you create trigger, anyway they can overwrite or delete trigger.
Try to separate sensitive data into another instance of SQL and give permission to each login strictly.
If there are too many sa and you can not control others sa,
Try to enable audit for tracking. Or encrypt data by key with password that only you can decrypt them, so others sa can not readable.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.
thnx alot...
anas zeta |
|
|
| |
Topic |
|
sql authentication
